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 |