Oracle Scripts

The scripts on this page were written some years ago to perform various useful tasks. They’ve largely been superseded these days by GUI tools such as TOAD, but may still be of use when working under low-tech conditions! There are scripts to…

It’s probably easiest to cut and paste the scripts from this page as required, but the full set is available in a ZIP file if you want them.

Show Indexes on a Table

This query displays all the indexes, and the columns that comprise them, for a particular table. The table needs to be in the user’s schema, if it isn’t you’ll have to use all_indexes and all_ind_columns instead.

BREAK ON index_name SKIP 1 ON uniqueness

SELECT c.index_name,i.uniqueness,c.column_name
FROM   user_indexes i,user_ind_columns c
WHERE  i.table_name = upper ('&table')
AND    i.index_name = c.index_name
ORDER BY c.index_name,c.column_position
/

Show the Size of Database Objects

This script shows the size of tables and indexes that belong to you. You should run it from time to time looking out for objects that are unnecessarily big or that have a large number of extents.

COLUMN segment_name FORMAT A30
COLUMN segment_type FORMAT A12
COLUMN extents FORMAT 9999999

SELECT segment_name,segment_type,extents,bytes,
       ROUND(bytes/(1024*1024),1) MBytes
FROM   user_segments
ORDER BY segment_name
/

Further refinement could be added by restricting the value of tablespace_name.

Show Errors in your PL/SQL Packages

This script can be used in response to the dreaded

Warning: Package Body created with compilation errors.

message. It adds a little refinement to a straight selection from user_errors. Note that if a user owns several buggy packages, this script will show errors in all of them unless suitable clauses are added to the WHERE clause.

SET arraysize 1
SET heading OFF

SELECT RTRIM(INITCAP(e.type))||' '||e.name||': Line '||
       TO_CHAR(e.line)||' Column '||TO_CHAR(e.position) linecol,
       s.text sourceline,RPAD(' ',(e.position - 1))||'^' errpos,
       e.text error
FROM   user_source s,user_errors e
WHERE  e.type = s.type
AND    e.name = s.name
AND    e.line = s.line
ORDER BY e.name,e.sequence
/
SET heading ON

Now instead of doing this…

SQL> select * from user_errors;

NAME                           TYPE          SEQUENCE      LINE  POSITION
------------------------------ ------------ --------- --------- ---------
TEXT
-------------------------------------------------------------------------
PAYROLL                        PROCEDURE            1        11        19
PLS-00201: identifier 'ENP' must be declared

… you can do this …

SQL> @err

Procedure PAYROLL: Line 11 Column 19
           update enp set sal = sal * 1.1
                  ^
PLS-00201: identifier 'ENP' must be declared

Show a source line in a PL/SQL package

This script is primarily intended to be used in response to runtime errors such as:

begin duff.show_ratios; end;
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "SCOTT.DUFF", line 14
ORA-06512: at line 1

The following script displays the specified line of code and the five lines which precede and follow it in order to set it in context:

ACCEPT package PROMPT "Enter package name : "
ACCEPT line PROMPT "Enter line number : "

COLUMN line FORMAT 9999
COLUMN text FORMAT A74

SET feedback OFF
SET pagesize 20
SET verify OFF
SET timing OFF
SET heading ON

SELECT line,text
FROM   user_source
WHERE  line BETWEEN (&line - 5) AND (&line - 1)
AND    type = 'PACKAGE BODY'
AND    name = UPPER('&package')
/
SET pagesize 0

SELECT RPAD ('=',80,'=')
FROM   dual
/
SELECT line,text
FROM   user_source
WHERE  line = &line
AND    type = 'PACKAGE BODY'
AND    name = UPPER('&package')
/
SELECT RPAD ('=',80,'=')
FROM   dual
/
SELECT line,text
FROM   user_source
WHERE  line BETWEEN (&line + 1) AND (&line + 5)
AND    type = 'PACKAGE BODY'
AND    name = UPPER('&package')
/

Using this script to diagnose the example error gives the following results:

SQL> @source
Enter package name : duff
Enter line number : 14

 LINE TEXT
----- --------------------------------------------------------------------------
    9       --
   10       ratio   NUMBER;
   11    BEGIN
   12       FOR e IN emp_cur LOOP
   13          ratio := e.sal / e.comm;
================================================================================
   14          DBMS_OUTPUT.put_line (e.ename||' '||TO_CHAR(ratio));
================================================================================
   15       END LOOP;
   16    END;
   17 END;

Note that the line number reported by Oracle is not the line on which the error actually occurred. In fact the line numbers appearing in run time errors can be wildly inaccurate (I speak from bitter experience here!). Still, this script can still be useful as a first line of support.

Show SQL Statements Currently Running

This script can be useful when trying to track down which part of a PL/SQL block (or other large code section) is running slowly. Set the code running and then run this script repeatedly in another session (you can restrict it to show only one user’s code once you find the right sid). If a particular statement spends a lot of time in the SQL area, that’s the one you need to speed up!

To run this script, you’ll need read access to v$session and v$sqltext, two views that belong to the sys schema. If you don’t have access, talk nicely to your DBA and try to persuade him/her that you don’t just want to spy on everyone else’s work…

COLUMN userID FORMAT A14
COLUMN sql_text FORMAT A65

BREAK ON userID SKIP 2

SET pagesize 0

SELECT s.schemaname||'('||TO_CHAR (s.sid)||')' userID,t.sql_text
FROM   sys.v$session s,sys.v$sqltext t
WHERE  t.address = s.sql_address
ORDER BY s.sid,t.piece
/

Leave a Comment