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…
- Show indexes on a table
- Show the size of database objects
- Show errors in your PL/SQL packages
- Show a source line in a PL/SQL package
- Show SQL statements currently running
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 /