Sample Monitoring Scripts

The following sample scripts can be used to help monitor your Oracle database. You may have to make a few modifications according to your preferences, but these should steer you in the right direction by providing a handle on the sometimes tedious monitoring process.

Monitoring the size of the audit trail:

set echo off
set feedback off
col dt hea ‘TIMESTAMP’ for a20
spool mon_aud
select to_char(sysdate,’yyyy mm/dd hh;mi;ss’) dt,
       substr(segment_name,1,10) “TABLE NAME”,
       extents,
       max_extents “MAX EXT”,
       bytes
from dba_segments
where segment_name = ‘AUD$’
/
spool off


SQL> @mon_aud

TIMESTAMP            TABLE NAME    EXTENTS   MAX EXT      BYTES
-------------------- ---------- ---------- --------- ----------
1998 04/05 09:18:07  AUD$                1       121      10240

A sample tablespace utilization report:

set echo off
create view v_alloc as
select tablespace_name ts,
       sum(bytes) alloc
from dba_data_files
group by tablespace_name
/
create view v_used as
select tablespace_name ts,
       sum(bytes) used
from dba_segments
group by tablespace_name
/
create view v_free as
select tablespace_name ts,
       sum(bytes) free
from sys.dba_free_space
group by tablespace_name
/
col ts hea ‘TABLESPACE’ for a18
col alloc for 9,999,999,999
col used for 9,999,999,999
col free for 9,999,999,999
tti ‘TABLESPACE UTILIZATION REPORT’
compute sum of alloc on report
compute sum of used on report
compute sum of free on report
compute avg of “PCT USED” on report
break on report
spool space
select a.ts,
       a.alloc,
       u.used,
       f.free,
       round(u.used/a.alloc * 100) “PCT USED”
from v_alloc a,
     v_used u,
     v_free f
where u.ts = a.ts
  and f.ts = a.ts
order by 5 desc
/
spool off
rem
drop view v_alloc;
drop view v_used;
drop view v_free;

---

NOTE:  
This script needs to be run as SYS, unless you have been explicitly granted select access on the applicable data dictionary views. Even if you have been granted the DBA role, the script will not necessarily work. Oracle does not allow objects to be created from other objects based on privileges granted from a role.

---

SQL> @space


Sun Apr 05                                                page  1
                     TABLESPACE UTILIZATION REPORT
TABLESPACE              ALLOC         USED        FREE   PCT USED
---------------- ------------ ------------ ----------- ----------
SYSTEM             10,485,760    8,792,064   1,691,648         84
ROLLBACK_DATA      10,485,760    8,140,800   2,342,912         78
TEMPORARY_DATA      2,097,152      958,464   1,136,640         46
USER_DATA           8,388,608      624,640   7,761,920          7
                 ------------ ------------ ----------- ----------
avg                                                         53.75
sum                31,457,280   18,515,968  12,933,120

A script to monitor Oracle user sessions:

set echo off
set pause off
set feedback on
set pagesize 23
set linesize 80
set newpage 0
tti off
clear col
col “Session” for a8
spool session.lst
prompt
prompt ########################
prompt # Oracle User Sessions #
prompt ########################
prompt
select substr(osuser,1,11) “OS Username”,
       process “OS PID”,
       substr(username,1,10) “Oracle ID”,
       sid||’,’||serial# “Session”,
       decode(command,’1’,’CREATE TABLE’,’2’,’INSERT’,’3’,’SELECT’,
                      ‘4’,’CREATE CLUSTER’,’5’,’ALTER CLUSTER’,
‘6’,’UPDATE’, ‘7’,’DELETE’,’8’,’DROP’,
                       ‘9’,’CREATE INDEX’,’10’,’DROP INDEX’,
                          ‘11’,’ALTER INDEX’,’12’,’DROP TABLE’,’15’,
             ‘ALTER TABLE’, ‘17’,’GRANT’,’18’,’REVOKE’,
‘19’,’CREATE SYNONYM’,’20’, ‘DROP SYNONYM’,
                  ‘21’,’CREATE VIEW’,’22’,‘DROP VIEW’,‘26’,
                  ‘LOCK TABLE’,’27’,‘NO OPERATION’,
‘28’,‘RENAME’,‘29’,‘COMMENT’, ‘30’,‘AUDIT’,
                  ‘31’,’NOAUDIT’,’32’,’CREATE EXTERNAL DATABASE’,
                  ‘33’,’DROP EXTERNAL DATABASE’,
             ‘34’,’CREATE DATABASE’,’
             ‘34’,’CREATE DATABASE’,’