How to convert stored outlines to use SQL Plan Baselines

If you have been using stored outlines for plan stability in versions prior to Oracle 11g, you should migrate them to use SQL Plan Baselines instead. Stored outlines is, according to oracle, "a legacy technique for providing plan stability".

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';