Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson



The GENERATE_SIGNATURE procedure is mistakenly attributed to the OUTLN_PKG in the Oracle documentation set, in actuality it is located in the OUTLN_EDIT_PKG which is synonymed to be DBMS_OUTLN_EDIT. Passing the GENERATE_SIGNATURE procedure a SQL statement will return a RAW signature of the SQL which can then be used to probe the outline tables to see if that SQL has an existing OUTLINE.

When using GENERATE_SIGNATURE, it is probably easiest to create an anonymous PL/SQL script to return the signature or the SQL and OUTLINE name to you. The signature is a RAW, so in the example, shown below, we return the outline name.


SQL> set serveroutput on


SQL> declare

2  sql_text varchar2(200);

3  ret_sql_text varchar2(200);

4  ret_outline_name varchar2(32);

5  gen_sig raw(16);

6  begin

7   sql_text := 'select owner, table_name, tablespace_name

8     from

9     dba_tables

10    where owner not in '

11  ('||chr(39)||'SYS'||chr(39)||','||chr(39)||'SYSTEM'||chr(39)||')';

12   dbms_outln_edit.generate_signature(sql_text,gen_sig);

13   select a.sql_text, into ret_sql_text, ret_outline_name

14   from dba_outlines a where signature=gen_sig;

15   dbms_output.put_line('Sql text for '||name||': '||ret_sql_text);

16  end;

17* /


Sql text for TEST1: select owner,table_name,tablespace_name

from dba_tables


owner not in ('SYS','SYSTEM')                                                  


PL/SQL procedure successfully completed.

Once you have identified the SQL statement for which you want to edit the hint structure, you use the DBMS_OUTLN_EDIT procedure to perform the edits as shown above.

The above text is an excerpt from:

Using Oracle SQL Stored Outlines & Optimizer Plan Stability
ISBN 0-9740716-8-4

by Mike Ault

Download your Oracle scripts now:

The definitive Oracle Script collection for every Oracle professional DBA


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.