Previous Table of Contents Next


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’,’35’, ‘ALTER DATABASE’,
                  ‘36’,’CREATE ROLLBACK SEGMENT’,
                  ‘37’,’ALTER ROLLBACK SEGMENT’,
             ‘38’,’DROP ROLLBACK SEGMENT’,
             ‘39’, ‘CREATE TABLESPACE’,
                  ‘40’,’ALTER TABLESPACE’,’41’, ‘DROP TABLESPACE’,
                  ‘42’,’ALTER SESSION’,’43’,’ALTER USER’,
                  ‘44’,’COMMIT’,’45’,’ROLLBACK’,
             ‘46’,’SAVEPOINT’,null) “Command”, status
from v$session
where username is not null
/
prompt
prompt #######
prompt # END #
prompt #######
spool off

A script to monitor segment fragmentation:

set echo off
set verify off
clear col
clear break
accept owner prompt ‘Enter schema owner: ‘
col segment_name hea ‘SEGMENT|NAME’ for a20
col segment_type hea ‘SEGMENT|TYPE’ for a10
col extents hea ‘EXT’ for 999
col bytes for 9,999,999,999
col max_extents hea ‘MAX|EXT’
col pct_mac hea ‘PCT OF|MAX EXT’
tti ‘SEGMENT FRAGMENTATION REPORT’
spool ext
select segment_name,
       segment_type,
       bytes,
       extents,
       max_extents,
       round(extents/max_extents * 100) pct_max
from dba_segments
where owner = ‘&owner’
  and rownum < 16
order by 6 desc
/
spool off
clear col
clear break


SQL> @ext
Enter schema owner: RYAN

Sun Apr 05                                              page  1
                         SEGMENT FRAGMENTATION REPORT

SEGMENT             SEGMENT                      MAX
NAME                TYPE         BYTES  EXT      EXT    PCT_MAX
------------------- ------- ---------- ---- -------- ----------
EMP_TBL             TABLE      460,800    9       10         90
CUSTOMERS           TABLE       10,240    1      121          1
INVOICES            TABLE       10,240    1      121          1
ACCT_PAY            TABLE       10,240    1      121          1
EMPLOYEES           TABLE       10,240    1      121          1
HISTORY             TABLE       10,240    1      121          1
PRODUCTS            TABLE       10,240    1      121          1
UNDERPAY_TABLE      TABLE       10,240    1      121          1
PAY_STATUS_TABLE    TABLE       10,240    1      121          1
PAYMENT_TABLE       TABLE       10,240    1      121          1
PRODUCT             TABLE       10,240    1      121          1
PROJECTS            TABLE       10,240    1      121          1
VENDORS             TABLE       10,240    1      121          1
ACCT_REC            TABLE       10,240    1      121          1
ORDERS              TABLE       10,240    1      121          1

This report is designed to display the percentage of extents used by a segment according to the MAXEXTENTS parameter that was set upon table creation. For example, EMP_TBL has used 9 out of its 10 allocated extents. You probably want to think about rebuilding this table or increasing the MAXEXTENTS. The good thing about the MAXEXTENTS parameter is that the DBA has a better idea of how the database is growing. This may cause more work up front, but will save headaches in the long run.


Previous Table of Contents Next