This post shows steps to transfer the earlier created outlines to a different database where the schema under which outlines were initially created is not present.
1. Export the current outlines
SQL> select owner,name from dba_outlines; OWNER NAME ----------- ------------- ASANGA JDBC_OUTLINE ASANGA FULL_TABLEExporting with exp
exp system/std11g1db file=outlns.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) statistics=none Export: Release 11.1.0.7.0 - Production on Mon Jan 7 10:58:27 2013 Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set About to export specified tables via Conventional Path ... Current user changed to OUTLN . . exporting table OL$ 2 rows exported . . exporting table OL$HINTS 20 rows exported . . exporting table OL$NODES 26 rows exported Export terminated successfully without warnings.2. The stored outlines are owned by user asanga but this user is missing in the destination database
SQL> select username from dba_users order by 1; USERNAME ------------------------------ ANONYMOUS BEAR DBSNMP DIP EXFSYS ORACLE_OCM OUTLN PERFSTAT SYS SYSTEM TSMSYS WMSYS XDB XS$NULLIt's assumed that at the destination database the application schema is named "bear".
3. Copy the generated dump file to the destination and import it into the destination database.
imp system/racse11g1db file=outlns.dmp full=y ignore=y Import: Release 11.1.0.7.0 - Production on Mon Jan 7 11:31:05 2013 Connected to: Oracle Database 11g Release 11.1.0.7.0 - 64bit Production With the Real Application Clusters option Export file created by EXPORT:V11.01.00 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses AL32UTF8 character set (possible charset conversion) export client uses AL32UTF8 character set (possible charset conversion) . importing SYSTEM's objects into SYSTEM . importing OUTLN's objects into OUTLN . . importing table "OL$" 2 rows imported . . importing table "OL$HINTS" 20 rows imported . . importing table "OL$NODES" 26 rows imported Import terminated successfully without warnings.4. Even though the user asanga is missing in the destination database, the stored outlines will be showed as owned by user asanga.
SQL> show user USER is "SYS" SQL> select owner,name from dba_outlines; OWNER NAME -------- -------------------- ASANGA FULL_TABLE ASANGA JDBC_OUTLINEand there will be no stored outlines under user_outlines views for user "bear".
SQL> conn bear/bear Connected. SQL> select name from user_outlines; no rows selected5. But if the same SQL is executed under the user bear schema the SQL will use the appropriate outline (it's assumed use stored outline is set).
SQL> select count(*) from big_table where p_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS OUTLINE'));
SQL_ID 80151fvm1d9af, child number 0
-------------------------------------
select count(*) from big_table where p_id=:"SYS_B_0"
Plan hash value: 599409829
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 |
-------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P_ID"=:SYS_B_0)
Note
-----
- outline "FULL_TABLE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level6. User "bear" could be made to own the stored outline by creating a private outline and then refreshing the public outline with it.
SQL> create private outline myfulltable from full_table;
Outline created.
SQL> execute dbms_outln_edit.refresh_private_outline('MYFULLTABLE');
PL/SQL procedure successfully completed.
SQL> create or replace outline full_table from private MYFULLTABLE;
Outline created. After this the full_table outline will appear when user_outline is queried in "bear" schema.SQL> select name from user_outlines; NAME ----------- FULL_TABLEQuerying dba_outlines shows now the outline is owned by user "bear"
SQL> select owner,name from dba_outlines; OWNER NAME -------- -------------------- ASANGA JDBC_OUTLINE BEAR FULL_TABLE7. Verify that stored outline is still used.
SQL> select count(*) from big_table where p_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS OUTLINE'));
SQL_ID 80151fvm1d9af, child number 0
-------------------------------------
select count(*) from big_table where p_id=:"SYS_B_0"
Plan hash value: 599409829
-------------------------------------------------
| Id | Operation | Name | E-Rows |
-------------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| BIG_TABLE | 2 |
-------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.7')
DB_VERSION('11.1.0.7')
OPT_PARAM('_optimizer_extended_cursor_sharing' 'none')
OPT_PARAM('_optimizer_extended_cursor_sharing_rel' 'none')
OPT_PARAM('optimizer_index_cost_adj' 25)
OPT_PARAM('optimizer_index_caching' 90)
FIRST_ROWS(1000)
OUTLINE_LEAF(@"SEL$1")
FULL(@"SEL$1" "BIG_TABLE"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("P_ID"=:SYS_B_0)
Note
-----
- outline "FULL_TABLE" used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system levelRelated Post
Changing Execution Plan Using Stored Outline
Useful metalink notes
Stored Outline Quick Reference [ID 67536.1]
How to Transfer Stored Outlines from One Database to Another (9i and above) [ID 728647.1]
