REM Sample script to create database - Page 33 - 36 REM * *********************************************************** REM * Script to create DB1 instance with db_block_size = 8192 REM * REM * Created: Dave Kreines - 10/18/98 REM * REM * REM * *********************************************************** spool /disk00/oracle/software/7.3.4/dbs/crdbDB1.log REM * Start the instance (ORACLE_SID must be set to ). REM * connect internal startup nomount pfile=/disk00/oracle/software/7.3.4/dbs/initDB1.ora REM * Create the database. REM * create database "DB1" maxinstances 2 maxlogfiles 32 maxdatafiles 1000 character set "US7ASCII" datafile '/disk00/oracle/oradata/DB1/system01.dbf' size 50M logfile '/disk01/oracle/oradata/DB1/log01.log' size 512K, '/disk01/oracle/oradata/DB1/log02.log' size 512K, '/disk01/oracle/oradata/DB1/log03.log' size 512K, '/disk01/oracle/oradata/DB1/log04.log' size 512K; REM * Now perform all commands necessary to create REM * the final database after the CREATE DATABASE command has REM * succeeded. REM * install data dictionary: @/disk00/oracle/software/7.3.4/rdbms/admin/catalog.sql REM * install procedural components: @/disk00/oracle/software/7.3.4/rdbms/admin/catproc.sql REM * Create additional rollback segment in SYSTEM since REM * at least one non-system rollback segment is required REM * before creating a tablespace. REM * create rollback segment SYSROLL tablespace system storage (initial 25K next 25K minextents 2 maxextents 99); REM * Put SYSROLL online without shutting REM * down and restarting the database. REM * alter rollback segment SYSROLL online; REM * Create a tablespace for rollback segments. REM * create tablespace ROLLBACK datafile '/disk01/oracle/oradata/DB1/rbs01.dbf' size 25M default storage ( initial 500K next 500K pctincrease 0 minextents 2 ); REM * Create the "real" rollback segments. REM * create rollback segment RBS01 tablespace ROLLBACK storage (initial 500K next 500K minextents 2 optimal 1M); create rollback segment RBS02 tablespace ROLLBACK storage (initial 500K next 500K minextents 2 optimal 1M); create rollback segment RBS03 tablespace ROLLBACK storage (initial 500K next 500K minextents 2 optimal 1M); create rollback segment RBS04 tablespace ROLLBACK storage (initial 500K next 500K minextents 2 optimal 1M); REM * Use ALTER ROLLBACK SEGMENT ONLINE to put rollback segments online REM * without shutting down and restarting the database. REM * alter rollback segment RBS01 online; alter rollback segment RBS02 online; alter rollback segment RBS03 online; alter rollback segment RBS04 online; REM * Since we've created and brought online 4 more rollback segments, REM * we no longer need the rollback segment in the SYSTEM tablespace. REM * We could delete it, but we will leave it here in case we need it REM * in the future. alter rollback segment SYSROLL offline; REM * Create a tablespace for temporary segments. create tablespace TEMP datafile '/disk02/oracle/oradata/DB1/temp01.dbf' size 25M default storage ( initial 100K next 100K maxextents UNLIMITED pctincrease 0 ); REM * Create a tablespace for database tools. REM * create tablespace TOOLS datafile '/disk03/oracle/oradata/DB1/tools01.dbf' size 25M default storage ( initial 50K next 50K maxextents UNLIMITED pctincrease 0 ); REM * Create tablespaces for user activity. REM * create tablespace DATA datafile '/disk04/oracle/oradata/DB1/data01.dbf' size 100M default storage ( initial 250K next 250K maxextents UNLIMITED pctincrease 0 ); REM * Create tablespaces for indexes. REM * create tablespace INDEX datafile '/disk05/oracle/oradata/DB1/index01.dbf' size 100M default storage ( initial 250K next 250K maxextents UNLIMITED pctincrease 0 ); REM * Alter SYS and SYSTEM users, because Oracle will make SYSTEM REM * the default and temporary tablespace by default, and we don't REM * want that. REM * alter user sys temporary tablespace TEMP; alter user system default tablespace TOOLS temporary tablespace TEMP; REM * Now run the Oracle-supplied scripts we need for this DB REM * @/disk00/oracle/software/7.3.4/rdbms/admin/catexp.sql @/disk00/oracle/software/7.3.4/rdbms/admin/dbmspool.sql @/disk00/oracle/software/7.3.4/rdbms/admin/prvtpool.plb REM * Now run the Oracle-supplied script to create the DBA views REM * for the SYSTEM account. Change to SYSTEM first. REM * connect system/manager @/disk00/oracle/software/7.3.4/rdbms/admin/catdbsyn.sql REM * All done, so close the log file and exit. REM * spool off exit -------------------------------------------------------------------------------------- # Sample listener.ora file - page 90-91 SQLNET.AUTHENTICATION_SERVICES = (NONE) USE_PLUG_AND_PLAY_LISTENER = OFF USE_CKPFILE_LISTENER = OFF LISTENER = (ADDRESS_LIST = (ADDRESS= (PROTOCOL=IPC) (KEY= ORAC.world) ) (ADDRESS= (PROTOCOL=IPC) (KEY= ORAC) ) (ADDRESS = (COMMUNITY = TCP.world) (PROTOCOL = TCP) (Host = 10.10.1.2) (Port = 1526) ) ) STARTUP_WAIT_TIME_LISTENER = 0 CONNECT_TIMEOUT_LISTENER = 10 TRACE_LEVEL_LISTENER = OFF SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = ORAC.world) (SID_NAME = ORAC) (ORACLE_HOME = /disk01/oracle/product/7.3.4) (PRESPAWN_MAX = 10) (PRESPAWN_LIST = (PRESPAWN_DESC = (PROTOCOL = TCP) (POOL_SIZE = 10) (TIMEOUT = 0)) ) ) ) -------------------------------------------------------------------------------------- # Sample tnsnames.ora file - Page 91-92 DB1.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = tcp.world) (PROTOCOL = TCP) (HOST = Production1) (PORT = 1526) ) ) (CONNECT_DATA = (SID = SID1) ) ) DB2.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = spx.world) (PROTOCOL = SPX) (SERVICE = Server_lsnr) ) ) (CONNECT_DATA = (SID = ORCL) ) ) DB3.world = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (COMMUNITY = nmp.world) (PROTOCOL = NMP) (SERVER = FinanceServer1) (PIPE = ORAPIPE) ) ) (CONNECT_DATA = (SID = ORCL) ) ) -------------------------------------------------------------------------------------- # Sample sqlnet.ora file - Page 92 AUTOMATIC_IPC = OFF TRACE_LEVEL_CLIENT = OFF NAMES.DEFAULT_DOMAIN = world NAME.DEFAULT_ZONE = world -------------------------------------------------------------------------------------- REM Script to create monitoring tables - Page 110 CREATE TABLE dba$resource_limit (timestamp date, resource_name varchar2(30), current_utilization number, max_utilization number, initial_allocation varchar2(10), limit_value varchar2(10)); CREATE TABLE dba$sgastat (timestamp date, pool varchar2(30), name varchar2(30), bytes number); -------------------------------------------------------------------------------------- REM Script to copy current values for future use - Page 110-111 INSERT INTO dba$resource_limit (timestamp, resource_name, current_utilization, max_utilization, initial_allocation, limit_value ) SELECT TRUNC(SYSDATE), resource_name, current_utilization, max_utilization, initial_allocation, limit_value FROM v$resource_limit; insert into dba$sgastat (timestamp, pool, name, bytes ) SELECT TRUNC(SYSDATE), pool, name, bytes FROM v$sgastat; COMMIT; -------------------------------------------------------------------------------------- REM Script to identify insufficient NEXT extent space - Page 111 SELECT owner, s.tablespace_name, segment_name, s.bytes, next_extent, max(f.bytes) largest FROM dba_segments s,dba_free_space f WHERE s.tablespace_name = f.tablespace_name(+) GROUP BY owner,s.tablespace_name,segment_name,s.bytes,next_extent HAVING next_extent*2>max(f.bytes); -------------------------------------------------------------------------------------- REM Script to identify segments close to MAXEXTENTS - Page 111 SELECT owner,tablespace_name,segment_name,bytes,extents,max_extents FROM dba_segments WHERE extents*2 > max_extents; -------------------------------------------------------------------------------------- REM Script to create tables for historical data - Page 111-112 CREATE TABLE dba_tablespace_history ( timestamp DATE, tablespace_name VARCHAR2(30), num_of_files NUMBER, num_of_blocks NUMBER ) PCTFREE 0 TABLESPACE tools STORAGE (INITIAL 393216 NEXT 196608 PCTINCREASE 0); CREATE TABLE dba_segments_history ( timestamp DATE, owner VARCHAR2(30), segment_name VARCHAR2(30), partition_name VARCHAR2(30), segment_type VARCHAR2(17), tablespace_name VARCHAR2(30), bytes NUMBER, blocks NUMBER, extents NUMBER ) PCTFREE 0 TABLESPACE tools STORAGE (INITIAL 1966080 NEXT 983040 PCTINCREASE 0); -------------------------------------------------------------------------------------- REM Script to collect information about tablespaces & segments - Page 112 INSERT INTO dba_tablespace_history SELECT TRUNC(sysdate), tablespace_name, count(*), sum(blocks), sum(bytes) FROM dba_data_files GROUP BY TRUNC(sysdate),tablespace_name; INSERT INTO dba_segments_history SELECT TRUNC(sysdate), owner, segment_name, partition_name, segment_type, tablespace_name, bytes, blocks, extents FROM dba_segments; -------------------------------------------------------------------------------------- REM Script to create table history table - Page 113 CREATE TABLE dba_tables_history ( timestamp DATE, owner VARCHAR2(30), table_name VARCHAR2(30), num_of_rows NUMBER, num_of_blocks_u NUMBER, num_of_blocks_f NUMBER ) PCTFREE 0 TABLESPACE tools STORAGE (INITIAL 786432 NEXT 393216 PCTINCREASE 0); -------------------------------------------------------------------------------------- REM Script to populate table history table - Page 113 INSERT INTO dba_tables_history SELECT TRUNC(sysdate),owner,table_name,num_rows,blocks,empty_blocks FROM dba_tables; -------------------------------------------------------------------------------------- REM Script to create index history table - Page 113-114 CREATE TABLE dba_index_history ( timestamp DATE, owner varchar2(30), height NUMBER, blocks NUMBER, name VARCHAR2(30), lf_rows NUMBER, lf_blks NUMBER, lf_rows_len NUMBER, lf_blk_len NUMBER, br_rows NUMBER, br_blks NUMBER, br_rows_len NUMBER, br_blk_len NUMBER, del_lf_rows NUMBER, del_lf_rows_len NUMBER, distinct keys NUMBER, most_repeated_key NUMBER, btree_space NUMBER, used_space NUMBER, pct_used NUMBER, rows_per_key NUMBER, blks_gets_per_access NUMBER ) PCTFREE 0 TABLESPACE tools STORAGE (INITIAL 1966080 NEXT 983040 PCTINCREASE 0); -------------------------------------------------------------------------------------- REM Script to analyze & populate index history table - Page 114 ANALYZE INDEX &owner..&index_name VALIDATE STRUCTURE; INSERT INTO dba_index_history SELECT TRUNC(sysdate), '&owner', height, blocks, name, lf_rows, lf_blks, lf_rows_len, lf_blk_len, br_rows, br_blks, br_rows_len, br_blk_len, del_lf_rows, del_lf_rows_len, distinct_keys, most_repeated_key, btree_space, used_space, pct_used, rows_per_key, blks_gets_per_access FROM index_stats; -------------------------------------------------------------------------------------- REM Script to create table & index for session summary - Page 128 CREATE TABLE system.dba_audit_session_daily ( os_username varchar2(255), username varchar2(30), userhost varchar2(255), terminal varchar2(255), timestamp date, sessions number, elapse_time number, logoff_lread number, logoff_pread number, logoff_lwrite number ) TABLESPACE tools STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0); CREATE INDEX system.dba_audit_session_daily_i ON dbsa.dba_audit_session_daily (timestamp) TABLESPACE tools STORAGE (INITIAL 10M NEXT 10M PCTINCREASE 0); -------------------------------------------------------------------------------------- REM Script to copy & summarize session information - Page 128 INSERT INTO system.dba_audit_session_daily (os_username,username,userhost,terminal,timestamp,sessions,elapse_time, logoff_lread,logoff_pread,logoff_lwrite) SELECT os_username,username,userhost,terminal,TRUNC(timestamp),COUNT(*), SUM(logoff_time-timestamp),SUM(logoff_lread),SUM(logoff_pread), SUM(logoff_lwrite) FROM dba_audit_session WHERE action_name IN ('LOGOFF','LOGOFF BY CLEANUP') AND logoff_time < trunc(sysdate) GROUP BY os_username,username,userhost,terminal,TRUNC(timestamp); -------------------------------------------------------------------------------------- REM Script to clean out audit table - Page 129 DELETE FROM aud$ a WHERE logoff$time < trunc(sysdate) AND action BETWEEN 101 AND 102 AND EXISTS (SELECT 'x' FROM system.dba_audit_session_daily d WHERE trunc(a.timestamp) = d.timestamp); -------------------------------------------------------------------------------------- REM Script to create row-level audit trigger - Page 129 - 130 CREATE OR REPLACE TRIGGER log_actions AFTER INSERT OR UPDATE OR DELETE ON generic_table FOR EACH ROW DECLARE action_id char(1); event_id int; table_key varchar(80); timestamp date; table_name varchar(30); username varchar(30); BEGIN SELECT event_seq.nextval INTO event_id FROM dual; SELECT sysdate INTO timestamp FROM dual; SELECT user INTO username FROM dual; table_name := 'GENERIC_TABLE; IF DELETING THEN action_id := 'D'; table_key := to_char(GENERIC_TABLE.PRIMARY_KEY); END IF; IF UPDATING THEN action_id := 'U'; table_key := to_char(GENERIC_TABLE.PRIMARY_KEY); END IF; IF INSERTING THEN action_id := 'I'; table_key := TO_CHAR(generic_table.primary_key); END IF; rem rem The debugging lines can be left in. They will not have an effect rem unless serveroutput is turned on in SQL*Plus rem dbms_output.enable(10000); dbms_output.put_line('tablename '||table_name); dbms_output.put_line('actionid '|| action_id); dbms_output.put_line('username '|| username); dbms_output.put_line('tablekey '|| table_key); dbms_output.put_line('eventid '|| event_cdr_id); dbms_output.put_line('timestamp'|| timestamp); INSERT INTO event_table_audit_t (event_id, timestamp, table_name, table_key, action) VALUES (event_id, timestamp, table_name, table_key, action_id); END; / -------------------------------------------------------------------------------------- REM Script to generate SQL to drop scott's tables - Page 165 set verify off set pagesize 0 set termout off set feedback off set sqlprompt '' spool dropem.sql SELECT 'DROP TABLE',owner||'.'||table_name,';' FROM dba.tables WHERE owner='SCOTT'; spool off set verify on set feedback on start dropem.sql exit -------------------------------------------------------------------------------------- REM Script to generate SQL to drop any owner's tables - Page 166 set verify off set pagesize 0 set termout off set feedback off spool dropem.sql SELECT 'DROP TABLE',owner||'.'||table_name,';' FROM dba.tables WHERE owner=UPPER(&Owner); spool off set verify on set feedback on start dropem.sql exit -------------------------------------------------------------------------------------- REM Script to produce a formatted report - Page 167 set space 2 set feedback off set linesize 54 set pagesize 30 COLUMN empno heading "Employee|Number" format 9999 COLUMN ename heading "Employee|Name" format a10 COLUMN sal heading "Current|Salary" format $9999.99 COLUMN newsal heading "New|Salary" format $9999.99 COLUMN comm heading "Commission" format $9999.99 TTITLE LEFT 'The Totally Bogus Company' - RIGHT 'Page: ' FORMAT 99 SQL.PNO SKIP 2 BTITLE CENTER 'Company Confidential' SELECT empno,ename,sal,sal*1.10 newsal,comm FROM scott.emp;