Stored Outline for backup the Execution Plan in Oracle

A stored outline is used to maintain the execution plan for the SQL Queries.It is a collection of hints associated with a specific SQL statement that allows a standard execution plan to be maintained, regardless of any changes in the system environment or associated statistics. Plan stability is based on the preservation of execution plans at a point in time where the performance of a statement is considered acceptable.
The outlines are stored in the OL$, OL$HINTS, and OL$NODES tables, but the [USER|ALL|DBA]_OUTLINES and [USER|ALL|DBA]_OUTLINE_HINTS views should be used to display information about existing outlines.

Step 1: Grant the outline permission to users

---Connect As sys:
grant create any outine to  users;
grant execute_catalog_role to users;

Step 2: Identify the hash_value for the SQL statement in the V$SQL view.

SELECT hash_value, child_number, sql_text FROM v$sql WHERE sql_text LIKE 'SELECT e.empno, e.ename, d.dname, e.job%';

HASH_VALUE CHILD_NUMBER SQL_TEXT
---------- ------------ ----------------------------------------
1867249038 0            SELECT e.empno, e.ename, d.dname, e.job FROM emp e, dept d
                        WHERE e.deptno = d.deptno AND d.dname = 'SALES';

STEP 3: Create the outline for the hash value

BEGIN
DBMS_OUTLN.create_outline(
hash_value => 1867249038,
child_number => 0,
category => 'EXP_OUTLINES');
END;
/

or

exec dbms_outln.create_outline(1867249038,0,'EXP_OUTLINES');

Note: First parameter is SQL hash value, not plan_hash_value as I wrote earlier. Second parameter is child number, Give an name to outline otherwise it go to default)

STEP 4: Checked the outline is created

select * from user_outlines;

or

COLUMN name FORMAT A30
SELECT name, category, sql_text FROM user_outlines WHERE category = 'EXP_OUTLINES';

STEP 5: Exported the outline table with exp backup

exp system/xxx file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) statistics=none

STEP 6: Before import on the other server first take the export backup of outln tables

Another Server:

exp system/xxx file=myoutln.dmp tables=\(outln.ol\$,outln.ol\$hints,outln.ol\$nodes\) statistics=none--Now imported to another server backup taken from first server.

imp system/xxx file=myoutln.dmp full=y ignore=y

STEP 7: Set the parameter use Stored outline on pre-prod and check SQL query

alter system set use_stored_outlines=true;

- And it we used immediately (verified by select * from dba_outlines)

STEP 8: IF server restart then it automatically disable because this parameter not able to set in pfile. To make this change permanent created a startup trigger in preprod as sys.

create or replace trigger enable_outlines
after startup on database
begin
execute immediate('alter system set use_stored_outlines=true');
end;
/

Rollback Plan for Outline
STEP 1: Import the backup taken for the tables of pre-prod.

imp system/xxx file=myoutln.dmp full=y ignore=y

STEP2: DROP THE Trigger created to enable_outlines.

Drop trigger enable_outlines;