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 <DB1>).
REM *
connect internal
startup nomount pfile=/disk00/oracle/software/7.3.4/dbs/initDB1.ora

REM * Create the <DB1> 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;