Problem solve Get help with specific problems with your technologies, process and projects.

Oracle housekeeping scripts

Here are a few scripts for Oracle database "housekeeping".

The idea of this tip is to collect some useful SQL scripts in order to give the DBA a way to view to the status of all his databases. Some of those checks are:

  • Security
  • Memory efficiency
  • Fragmentation
  • RBS efficiency
  • and more...

You can also add any script that you may have found useful into the skeleton that I provide.

These housekeeping scripts provide a daily report on the status of my production databases. The reports are sent via e-mail to the DBA. There are two sections:

  1. The Unix c-sh script: The container for the SQL scripts. You can run the scripts on all databases or on a selected subset of databases. You can run them every day or only periodically. These decisions can be coded into the c-sh skeleton.
  2. A series of SQL scripts: You can have as many scipts as you want. If you decide to run all the scripts at once on all databases, you can collect all your code in one script. If you want to add some rules in your c-sh script, then you should divide the SQL code into different files. I will show you later some useful scripts, but you can collect your own.

The c-sh container:

 #!/bin/csh # change ORACLE_HOME and scripts location # create the list of SID's as required # change the e-mail address # If you run many scripts, add names (from the sql spool command) # in the 'cat' command setenv ORACLE_HOME /export/home/oracle8 cd /export/home/oracle8/scripts setenv PATH $ORACLE_HOME/bin:.:${PATH} date rm monitor_house_keeping_db_*.lst foreach db (dwpr icms) # a collection of SID's sqlplus db_control/[email protected]$db @monitor_house_keeping_daily.sql $db end cat monitor_house_keeping_db_*.lst | mailx -s "Daily House Keeping" [email protected] rm monitor_house_keeping_db_*.lst exit

The SQL scripts

 /* Here are some useful scripts. You can add as many as you want. Some of my preferred scripts were collected from this site. Collect the best and put them together.*/ set serveroutput on; exec dbms_output.enable(100000); set echo off; set feed off; spool monitor_house_keeping_db_&1 exec dbms_output.put_line('-'); exec dbms_output.put_line('-'); exec dbms_output.put_line('--------------------'); exec dbms_output.put_line('--------------------'); exec dbms_output.put_line('--- &1 ---'); exec dbms_output.put_line('--------------------'); exec dbms_output.put_line('--------------------'); -- Who is DBA set pagesize 60; set linesize 120; col User format a12; ttitle '&1 - Who is DBA'; select grantee "User", decode(admin_option,'YES','With admin option',null) " " from dba_role_privs where granted_role='DBA' and grantee not in ('SYS','SYSTEM') and grantee not like 'OPS$%'; -- Compile invalid objects (published in this site) -- Tablespaces free space (published in this site) -- Auto extensible datafiles ttitle '&1 - Auto extensible datafiles'; break on tablespace_name col tablespace_name format a20 heading "Tablespace name" col file_name format a45 heading "File name" col AUTOEXTENSIBLE format a15 heading "Auto extensible" select tablespace_name, file_name, round(bytes/1024/1024) AS "MBsize", AUTOEXTENSIBLE from DBA_DATA_FILES where AUTOEXTENSIBLE='YES' / -- Number of extents for an object set pagesize 72; set linesize 120; ttitle '&1 - Number of extents for an object'; col segment_name format a45 heading "Segment name" col segment_type format a18 heading "Type" col owner format a10 heading "Schema" break on owner on segment_type select owner, lower(segment_type) "segment_type", segment_name|| decode(partition_name,NULL,NULL,' ('||partition_name||')') "segment_name", count(*) "Count", round(sum(bytes/10124/1024)) AS "MBsize" from dba_extents where OWNER not in ('SYSTEM','SYS','DBSNMP','DB_CONTROL','FRONT','OUTLN','TOAD','PRECISE') AND OWNER not like 'OPS%' AND OWNER not like '%TEMP%' AND OWNER not in ('BCA','B05','BSC') group by owner,segment_type, segment_name, partition_name having count(extent_id) > 20 order by owner, segment_type, count(extent_id) desc / spool off; exit;


For More Information

  • Feedback: E-mail the editor with your thoughts about this tip.
  • More tips: Hundreds of free Oracle tips and scripts.
  • Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
  • Ask the Experts: Our applications, SQL, database administration, and data warehousing gurus are waiting to answer your toughest questions.
  • Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
  • Best Web Links: Oracle tips, tutorials, and scripts from around the Web.

Dig Deeper on Oracle DBA tools