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


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  Oracle Tips by Burleson


 

DROP_UNUSED
 

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.
 

SQL> EXECUTE OUTLN_PKG.DROP_UNUSED;

 

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 8.1.3.0.0  select a.table_name,

                                                         b.tablespace_name,

                                                         c.file_name from                          

                                                         dba_tables a,

                                                         dba_tablespaces b,

                                                         dba_data_files c        

                                                         where                           

                                                         a.tablespace_name =

                                                         b.tablespace_name                      

                                                         and b.tablespace_name

                                                         = c.tablespace_name                  

                                                         and c.file_id =

                                                         (select

                                                         min(d.file_id) from

                                                         dba_data_files d                      

                                                         where

                                                         c.tablespace_name =        

                                                         d.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
 

http://www.rampant-books.com/ebook_plan_stability.htm


Download your Oracle scripts now:

www.oracle-script.com

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.