DBMS_OUTLN

DBMS_OUTLN -

The DBMS_OUTLN supplied package can also be used to administer stored outlines. One of the problems with stored outlines is that it can be difficult to determine if the outline is even being used by any applications. A query of the OUTLINE_CATEGORY of V$SQL can be done to see if any cursors in the Shared Pool are currently using the outline category. Unfortunately, any older cursors will be aged out and a risk is run of deciding that the absence of a category in V$SQL means that the category has not been used, when it has! The DBA_OUTLINES view contains a column called USED to determine if the outline has ever been used or not. This flag can be cleared with the following procedure in DBMS_OUTLN:

exec dbms_outln.clear_used('employee_application');
Once the flag has been cleared, wait some sufficient period of time before checking the flag again to see if the outline category has been used since the flag was last reset.

If there are stored outlines that have not been used, use the DROP_UNUSED procedure to remove them from the database.

exec dbms_outln.drop_unused;
Alternatively, it is possible to drop stored outlines by category name with the DROP_BY_CAT procedure.

exec dbms_outln.drop_by_cat('employee_application');
The above is similar to the DROP OUTLINE command. Again, it is advisable to make a backup copy before dropping any stored outlines.

The DBMS_OUTLN procedure allows for a way of creating the stored outlines with a third method. The CREATE_OUTLINE procedure will create a stored outline for a SQL cursor currently in the Shared Pool. You will need the HASH_VALUE and CHILD_NUMBER from V$SQL for the SQL statements that you want to add to a stored outline category.

exec dbms_outln.create_outline('ky7r4nkloqqwm', 0,'employee_category');
Additionally, a category can be renamed with the UPDATE_BY_CAT procedure. The earlier example of renaming a category can be accomplished with DBMS_OUTLN as follows:

exec dbms_outln.update_by_cat('employee_application", 'emp_outline');