| Previous | Table of Contents | Next |
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 |