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 drop_unused procedure is used to drop outlines that have not been used in the compilation of SQL statements. The drop_unused procedure has no arguments.



PL/SQL procedure successfully executed.


To determine if a SQL statement OUTLINE is unused, perform a select against the DBA_OUTLINES view:


SQL> desc dba_outlines;

 Name                            Null?    Type

 ------------------------------- -------- ----

 NAME                                     VARCHAR2(30)

 OWNER                                    VARCHAR2(30)

 CATEGORY                                 VARCHAR2(30)

 USED                                     VARCHAR2(9)

 TIMESTAMP                                DATE

 VERSION                                  VARCHAR2(64)

 SQL_TEXT                                 LONG


SQL> set long 1000

SQL> select * from dba_outlines where used='UNUSED';


NAME         OWNER  CATEGORY USED   TIMESTAMP VERSION    SQL_TEXT                        

------------ ------ -------- ------ --------- ---------- ----------------------

TEST_OUTLINE SYSTEM TEST     UNUSED 08-MAY-99  select a.table_name,


                                                         c.file_name from                          

                                                         dba_tables a,

                                                         dba_tablespaces b,

                                                         dba_data_files c        


                                                         a.tablespace_name =


                                                         and b.tablespace_name

                                                         = c.tablespace_name                  

                                                         and c.file_id =


                                                         min(d.file_id) from

                                                         dba_data_files d                      


                                                         c.tablespace_name =        




1 row selected.


SQL> execute sys.outln_pkg.drop_unused;


PL/SQL procedure successfully completed.


SQL> select * from dba_outlines where used='UNUSED';


no rows selected


PL/SQL procedure successfully completed.


SQL> select * from dba_outlines where used='UNUSED';


no rows selected


Remember, the procedure drops all unused outlines so use it carefully.

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.