Alternative To SELECT_CATALOG_ROLE In Oracle

It may be useful to allow developers to look at the plsql (views, procs, etc) for a schema without granting the SELECT_CATALOG_ROLE role. This view will provide access, with the possible drawback of adding an object to the SYS schema.

Run this as sys, like:

sqlplus "/ as sysdba" @script.sql

CREATE OR REPLACE VIEW SYS.plsql_ASCHEMAOWNER (OWNER,NAME,TYPE,LINE,TEXT) AS
select OWNER,NAME,TYPE,LINE,TEXT
from sys.dba_source
where owner='ASCHEMAOWNER';

create or replace public synonym plsql_ASCHEMAOWNER for sys.plsql_ASCHEMAOWNER;
grant select on sys.plsql_ASCHEMAOWNER to public;

desc plsql_aschemaowner
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
NAME VARCHAR2(30)
TYPE VARCHAR2(12)
LINE NUMBER
TEXT VARCHAR2(4000)

Basic Oracle Role For All Users

Prior to changes in Oracle 10, an "enduser" role was useful in every database. The role alllows a connecting and a few harmless though useful grants. Every new user would have this role.

CREATE ROLE "ENDUSER" NOT IDENTIFIED;
GRANT ALTER SESSION TO "ENDUSER";
GRANT CREATE SEQUENCE TO "ENDUSER";
GRANT CREATE SESSION TO "ENDUSER";
GRANT CREATE SYNONYM TO "ENDUSER";
GRANT CREATE VIEW TO "ENDUSER";

Oracle Database Go-Live Check List

Items to review before production go-live of an Oracle database system.

+) Security
accounts (check users with roles: DBA, import any, ...)
listener password
(if non-DBAs can log on to DB machine) check file Oracle directory and permissions
check for default DB accounts that can be locked or have the passwords changed
change OS passwords
change at least DB system password

+) Automate instance/listener start and stop with the operating system startup "rc" scripts.

+) Backup/recovery
verify archive log mode is on
verify rman jobs are scheduled and running correctly
in case backup system is down, ensure a couple days of disk space for archive_log_dest
export schema rows=n weekly or monthly
depending on requirements and performance considerations, enable db_block checksum

+) Performance
configure resource governor to guide CPU usage and parallel query usage
configure resource governor to limit undo usage by non-application owner accounts
schedule statspack snap every twenty or thirty minutes
enable monitoring on application tables
schedule dbms_stats.gather_schema_stats to gather stale (usually weekly or monthly)
use dbms_stats to create and export a statistics table (useful to quickly restore older version of statistics)

+) Checks
tablespace that will not be able to grow
undo tablespace growth is capped at less than the size of the file system
alert log error messages
users with default tablespace of system
operating system error log "syslog"
network error (netstat -i)

+) Multiple temp tablespaces
one tablespace that is only available to application owner
other tablespaces to segregate other users from interfering with application temp usage