My Collection of DBA Scripts

In this post, I intend to list some scripts that I use often for daily DBA work and for trouble-shooting. I will update the list as necessary when new scripts come into play. I guess every DBA has his or her own handy scripts for work.

My List of DBA script:

event.sql

Show the overall picture of system activities by summarizing the session wait event count from v$session_wait. Used when doing health check or trouble-shooting. Check Tanel Poder's opinion about sampling v$session_wait.

get_ddl.sql

Generate DDL SQL statement for creating various objects such as tables, indexes, view etc. When want to know the constraints of a table or find the storage properties, I often find it pretty easy by obtaining the DDL of the table through this script. Also, when I find an index name looks like system-generated, I use this script to get the index DDL, which can tell me if the index is funtion-based and what kind of function it is.

login.sql

Used to give username@SID > as SQL* Plus prompte. Placed at the SQLPATH or working directory. Also give better AUTOTRACE explain plan output.

longops.sql

Display the progress of some long operations, such as table scan if it is long enough (> 6s). Some time it is useful to estimate how soon your SQL will finish.

perf_log_switch_history_daily.sql

Display number of log switch in every hour in a tabular format. Very useful to understand the workload distribution. From Jeff Hunter.
pxses.sql

I support a reporting database, on which parallel queries are frequently executed. This script displays the parallel execution server sessions, which is useful to verify if parallel execution happens and to check the degree of parallelism.

qlocks.sql

Display blocking and blocked session. An example of using this script can be found here.

sesevt.sql

Display a session information given wait event as input.

sessid.sql

Display a session information given SID as input.

sesusr.sql

Display a session information given USERNAME as input.

sqlsid.sql

Display the current running SQL of the session. Input session id (sid). Output the sql text, address, hash value (in 10g, can add sql_id)

sw.sql

Display wait event of a give session by SID. From Tanel Poder.

tabcols.sql

Display table column CBO statistics. Very useful when doing SQL tunning.

tabix.sql

List of the indexes of a table and show on which columns and in which order the indexes are.Very usefull when tune a SQL.

tbs.sql

Show a list of data files of a tablespace.

tf.sql

Show tablespace space usage. Used with tbs.sql when adding space or resize a data file of a tablespace.


xadr.sql

Obtain execution plan from the library cache. Good for 9i (The DB in my current working environment are mostly 9i). Need to input the address of the SQL (obtainable from v$sql or v$sqlarea) and child cursor number. I usually use sesusr.sql to find the SID of a user session given the user name, then use sqlsid.sql to find out the current running SQL, next using this script to find the execution plan.