My database had a number of stored outlines:
SELECT OWNER, CATEGORY, USED, MIGRATED,COUNT(*) FROM DBA_OUTLINES GROUP BY OWNER,CATEGORY,USED,MIGRATED ORDER BY MIGRATED;
| OWNER | CATEGORY | USED | MIGRATED | COUNT(*) |
|---|---|---|---|---|
| USER1 | DEFAULT | USED | MIGRATED | 3 |
| USER2 | DEFAULT | USED | MIGRATED | 1 |
| USER3 | DEFAULT | USED | NOT-MIGRATED | 7 |
| USER1 | DEFAULT | USED | NOT-MIGRATED | 7 |
| USER4 | DEFAULT | USED | NOT-MIGRATED | 1 |
| USER2 | DEFAULT | USED | NOT-MIGRATED | 36 |
I created the following pl/sql to convert them to SQL Plan Baselines using the package DBMS_SPM.MIGRATE_STORED_OUTLINE:
SET TRIMSPOOL ON
SET LINES 200
SET PAGES 200
ALTER SESSION SET NLS_LANGUAGE='AMERICAN'; <-- to get English messages during execution
SPOOL CONVERT_OUTLINES.LOG
SET SERVEROUTPUT ON
DECLARE
L_CLOB CLOB; -- will display the resulting report
CURSOR C1 IS
SELECT OWNER,NAME
FROM DBA_OUTLINES
WHERE MIGRATED = 'NOT-MIGRATED';
C1_REC C1%ROWTYPE;
BEGIN
DBMS_OUTPUT.ENABLE( 1000000 );
IF NOT C1%ISOPEN THEN
OPEN C1;
END IF;
LOOP
FETCH C1 INTO C1_REC;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Now converting: ' || C1_REC.OWNER || '.' || C1_REC.NAME);
L_CLOB := DBMS_SPM.MIGRATE_STORED_OUTLINE( ATTRIBUTE_NAME=>'OUTLINE_NAME', ATTRIBUTE_VALUE=>C1_REC.NAME, FIXED=>'NO');
DBMS_OUTPUT.PUT_LINE( L_CLOB );
END LOOP;
CLOSE C1;
END;
/
EXIT
The resulting log files shows that several of the stored outlines could not be converted:
Now converting: USER2.SYS_OUTLINE_11021513055564321
-------------------------------------------------------------------------------
Migrate Stored Outline to SQL Plan Baseline
Report
-------------------------------------------------------------------------------
Summary:
--------
Number of stored outlines to be migrated: 1
Stored outlines migrated successfully: 0
Stored outlines failed to be migrated: 1
Summary of Reasons for failure:
-------------------------------
Number of invalid stored outlines: 1
Details on stored outlines not migrated or name changes during migration:
-------------------------------------------------------------------------
* Notes on name change:
* New SQL plan baselines are assigned the same names as their original stored
* outlines. If a stored outline has the same name as an existing
* SQL plan baseline, a system generated name is used for the new
* SQL plan baseline.
I then checked with the developers. It turned out that the outlines that didn't convert properly were remnants from the database when it was totally different laid out - the schemas had by now diverged and I could simply ignore these obsolete outlines.
So the last step was simply to generate a drop-script for the non-migrated outlines and then execute these:
SELECT 'DROP OUTLINE ' || NAME || ';' FROM DBA_OUTLINES WHERE MIGRATED = 'NOT-MIGRATED';
