Disabling the Oracle Diagnostic and Tuning Pack APIs – If you want something done, do it yourself

At last, we have reached the final episode of the Star Wars themed odyssey through the tangled web that is Oracle’s Diagnostic and Tuning Pack licensing.
Just as well really, Deb has flatly refused to give over any more evenings to my “research” – i.e. re-watching all of the films. Even the appeal of Ewan MacGregor’s Alec Guiness impression has now waned.
Just to recap then, so far I’ve looked at :

Now, finally we’re going to have a look at how we can minimize the chances of an errant select statement causing a whole heap of trouble.
Yes, we’re going to have a go at disabling access to the Diagnostic and Tuning Pack APIs without (hopefully), breaking anything.

A brief word about DBMS_MANAGEMENT_PACKS

There are ways of disabling AWR. Apart from this only being part of the tuning pack and therefore not the panacea we’re looking for, the original method for doing this did sum up the confusion surrounding licensing.
Oracle’s advice was to run the following (NOTE – please don’t run until you’ve read on) :

 
BEGIN 
	DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS( interval => 0); 
END; 

Have you spotted the floor in this plan ? Yep, DBMS_WORKLOAD_REPOSITORY is part of the Tuning pack.
So, if you’re not licensed for the Tuning Pack then you shouldn’t be using this package. Have a look at Jonathan Lewis’ take on this.

In 11.1, the DBMS_MANAGEMENT_PACKS package came along.
The only documentation I could find was on the ever-reliable Morgan’s library.

Apparently, you can now use this package to disable AWR…

 
BEGIN 
	DBMS_MANAGEMENT_PACKS.MODIFY_AWR_SETTINGS(interval => 0); 
END; 
/ 

Like I said, this isn’t exactly what we’re after here.
Furthermore, I’d treat this with some caution. I would want to be sure that the MODIFY_AWR_SETTINGS procedure wasn’t simply calling DBMS_WORKLOAD_REPOSITORY – something it’s difficult to verify without testing as the package body itself is wrapped.

However, there are a couple of other package members that are of interest.

Of particular interest are the PURGE procedure and the REPORT function.

The comments for the PURGE procedure are :

 
--    PROCEDURE DBMS_MANAGEMENT_PACKS.purge 
--    PURPOSE: Remove/deactivate objects in the database that are inconsistent 
--             with the proposed setting of the 
--             "control_management_pack_access" parameter 
--    PARAMETERS: 
--         LICENSE_LEVEL 
--             Any valid value for init.ora parameter 
--             "control_management_pack_access". NULL is also a valid value, 
--             and it is equivalent to using teh function with the current 
--             value of the init.ora parameter. 
PROCEDURE purge(license_level IN varchar2); 

And for the REPORT function :

 
--    FUNCTION DBMS_MANAGEMENT_PACKS.report 
--    PURPOSE: Get a text report of what changes will be done to the system 
--             if the "purge" procedure is called with a specific level. 
--    PARAMETERS: 
--         LICENSE_LEVEL 
--             Any valid value for init.ora parameter 
--             "control_management_pack_access". NULL is also a valid value, 
--             and it is equivalent to using teh function with the current 
--             value of the init.ora parameter. 
--    RETURN: a clob containing a text explanation of the changes. 
FUNCTION report(license_level IN varchar2) RETURN clob; 

OK, so let’s connect as SYS and have a look at what we can get from the report function.
First of all, let’s check the setting of the control_management_pack_access parameter :

 
SELECT value 
FROM v$parameter 
WHERE name = 'control_management_pack_access' 
/ 

VALUE 
------------------------------ 
NONE 

Now let’s see what we can get from the report.

 
set long 999999 
SELECT DBMS_MANAGEMENT_PACKS.REPORT(NULL) 
FROM dual 
/	 

List of objects to be deleted by DBMS_MANAGEMENT_PACKS.PURGE                                                                                                                                                                                                    
Requested license level is none                                                                                                                                                                                                                                  
------------------------------------------------------------ 
 
 
 

So, it would appear that, despite what the comments say, this package won’t actually do
anything. Hopefully, this is simply a placeholder and the functionality we need will be
available in a future version of the RDBMS. In the meantime however, we need to find some
other way of resolving matters.

The thing about Public Synonyms

I’ve learned a number of interesting techniques from Alexander Kornbrust over the years.
If you ever get the chance to see him present on Oracle Security, I would highly recommend it.

One Oracle cracking technique in particular involves the exploitation of Public Synonyms by re-pointing a synonym to an object other than that orginally intended.

Time for a quick example.

First of all, I’m going to create this table under my own schema.


CREATE TABLE star_wars_films(
    title VARCHAR2(35),
    description VARCHAR2(100))
/

INSERT INTO star_wars_films( title, description)
VALUES( 
    'Episode 1 - The Phantom Menace',
    'Boy meets annoying CGI character and woman with a mad hairdresser.')
/

INSERT INTO star_wars_films( title, description)
VALUES( 
    'Episode 2 - Attack of the Clones',
    'Yoda gets out for a bit of exercise. Christopher Lee gives electrifying performance')
/

INSERT INTO star_wars_films( title, description)
VALUES( 
    'Episode 3 - Revenge of the Sith',
    q'[Influence of Padme's flamboyant taste induces Annakin to have a complete image makeover]')
/

INSERT INTO star_wars_films( title, description)
VALUES(
    'Episode 4 - A New Hope',
    'Alec Guiness does Alec Guiness impression. Death Star engineers learn importance of proper testing')
/

INSERT INTO star_wars_films( title, description)
VALUES( 
    'Episode 5 - The Empire Strikes Back',
    'Akward moment at family reunion. Han Solo catches a cold')
/

INSERT INTO star_wars_films( title, description)
VALUES( 
    'Episode 6 - Return of the Jedi',
    'WARNING - animated teddy bears present choking hazard. Unsuitable for Sith Lords')
/

COMMIT;

CREATE OR REPLACE PUBLIC SYNONYM star_wars_films FOR mike.star_wars_films
/

GRANT SELECT ON star_wars_films TO PUBLIC
/

Now, if I connect as another user ( hr, for example), I can do this :

SELECT *
FROM star_wars_films
/
...
Episode 1 - The Phantom Menace      Boy meets annoying CGI character and woman with a mad hairdresser.                                 
Episode 2 - Attack of the Clones    Yoda gets out for a bit of exercise. Christopher Lee gives electrifying performance                
Episode 3 - Revenge of the Sith     Influence of Padme's flamboyant taste induces Annakin to have a complete image makeover            
Episode 4 - A New Hope              Alec Guiness does Alec Guiness impression. Death Star engineers learn importance of proper testing 
Episode 5 - The Empire Strikes Back Akward moment at family reunion. Han Solo catches a cold                                           
Episode 6 - Return of the Jedi      WARNING - animated teddy bears present choking hazard. Unsuitable for Sith Lords                   

In desparation, Darth Debbie has hacked into my database to put a stop to all this Star Wars nonsense…

CREATE TABLE foot_down(message VARCHAR2(100)) 
/ 

INSERT INTO foot_down(message) 
VALUES('You are only allowed to watch Love Actually or Bridget Jones !') 
/ 

COMMIT; 

GRANT SELECT on foot_down TO PUBLIC 
/ 

CREATE OR REPLACE PUBLIC SYNONYM star_wars_films for deb.foot_down 
/

Now, when we run the same query as another user…

SELECT *
FROM star_wars_films
/

MESSAGE 
--------------------------------------------------------------------------- 
You are only allowed to watch Love Actually or Bridget Jones ! 

How does this relate to the problem at hand ?
Well, all of the objects that comprise the Diagnostic and Tuning Pack APIs have Public Synonyms.
The tools and scripts that utilize these APIs rely on the synonyms being present. At least, I haven’t noticed too many references to SYS.object_name in the code that I’ve looked at.

Let’s see how we can apply this technique to minimize the possibility of inadvertant access to the APIs.

Disclaimer and Other Notes

At this point, I should make it clear that what follows has been tested to the extent I’ve set out here and no further.
I’ve used Oracle 11gXE (11.2.0.2) for my testing. I haven’t tested this on any other Oracle Database Editions or versions.
If you’re interested in deploying this fix, I’d suggest that you conduct you’re own testing first.
This would need to be done under an OTN license – not on a database that you’re using for development of an application and certainly not on any production instance.
I’d also suggest you’d run the database with the fix in place for at least a week and keep an eye on the alert.log for any issues that may arise.

Now that’s out of the way…

The Disable Tuning Pack API Application

The application consists of the following database components

  • A schema to own the database objects
  • A table of the API members for each of the Diagnostic and Tuning packs
  • A table containing a warning message
  • A function that raises an error using the text of the message
  • Packages with the same signatures as those included in the APIs

Once we’ve got that little lot together, we’ll then re-point all of the public synonyms to the relevant objects in our application.

The DISABLE_PACK_DT schema

The first step is to create the application owner schema :

CREATE USER disable_pack_dt IDENTIFIED BY pwd 
/ 

ALTER USER disable_pack_dt DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP 
/ 

ALTER USER disable_pack_dt QUOTA UNLIMITED on USERS 
/

Additionally, this schema requires privileges on some of the packages that we’ll be replacing. To do this, connect as SYS and :

--
-- Must run as SYS as SYSDBA.
-- Grant execute on the underlying packages, not the synonyms !
--
GRANT EXECUTE ON sys.dbms_workload_replay TO disable_pack_dt
/
GRANT EXECUTE ON sys.dbms_advisor TO disable_pack_dt
/
GRANT SELECT ON sys.wri$_adv_parameters TO disable_pack_dt
/

The API members table

This table holds details of all of the database objects that comprise the Diagnostic and Tuning API. NOTE – it does not include the underlying tables of the views in question.
YOu can find more details on how I derived the contents of the table here.

--
-- Run as a user with CREATE ANY TABLE privileges
--
CREATE TABLE disable_pack_dt.pack_members(
    object_name VARCHAR2(30),
    object_type VARCHAR2(20),
    pack_name VARCHAR2(10),
    full_member VARCHAR2(1),
    notes VARCHAR2(4000))
/

--
-- Script to populate the disable_pack_dt.pack_members table with
-- Diagnostic and Tuning Pack API Objects that are individually specified by
-- the license.
--

--
--  DBMS_WORKLOAD_REPOSITORY
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBMS_WORKLOAD_REPOSITORY', 'PACKAGE', 'DIAGNOSTIC',
    'Y', NULL)
/

--
-- DBMS_ADDM
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBMS_ADDM', 'PACKAGE', 'DIAGNOSTIC',
    'Y', NULL)
/

--
-- DBMS_ADVISOR - NOTE - this is a member of BOTH packs
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBMS_ADVISOR', 'PACKAGE', 'BOTH',
    'N', 
    'DIAGNOSTIC PACK - Only if advisor_name => ADDM OR task_name LIKE ADDM%'
    ||' TUNING PACK - where advisor_name => SQL Tuning Advisor OR SQL Access Advisor')
/

--
-- DBMS_WORKLOAD_REPLAY
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBMS_WORKLOAD_REPLAY', 'PACKAGE', 'DIAGNOSTIC',
    'N', 'COMPARE_PERIOD_REPORT function only')
/

--
-- V$ACTIVE_SESSION_HISTORY
-- NOTE - this is a synonym for V_$ACTIVE_SESSION_HISTORY
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'V$ACTIVE_SESSION_HISTORY', 'SYNONYM', 'DIAGNOSTIC',
    'Y', 'Synonym for V_$ACTIVE_SESSION_HISTORY')
/

INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'V_$ACTIVE_SESSION_HISTORY', 'VIEW', 'DIAGNOSTIC',
    'Y', 'Synonym for this view is V_$ACTIVE_SESSION_HISTORY')
/

--
-- DBA_STREAMS_TP_PATH_BOTTLENECK
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBA_STREAMS_TP_PATH_BOTTLENECK', 'VIEW', 'DIAGNOSTIC',
    'Y', NULL)
/

--
--  DBA_STREAMS_TP_COMPONENT_STAT
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBA_STREAMS_TP_COMPONENT_STAT', 'VIEW', 'DIAGNOSTIC',
    'N', 'Only rows where STATISTIC_UNIT = PERCENT')
/

--
-- DBMS_SQLTUNE
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'DBMS_SQLTUNE', 'PACKAGE', 'TUNING',
    'Y', NULL)
/

--
-- V$SQL_MONITOR - NOTE - this is a public synonym for V_$SQL_MONITOR !!!
-- Therefore, we'll specify the underlying view here as well
--
INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'V$SQL_MONITOR', 'SYNONYM', 'TUNING',
    'Y', 'Synonym for V_$SQL_MONITOR')
/

INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'V_$SQL_MONITOR', 'VIEW', 'TUNING',
    'Y', 'Synonym for this view is V$SQL_MONITOR')
/

--
-- V$SQL_PLAN_MONITOR - public synonym for V_SQL_PLAN_MONITOR.
-- Again, specify the underlying view here as well
--

INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'V_$SQL_PLAN_MONITOR', 'VIEW', 'TUNING',
    'Y', 'Synonym for this view is V$SQL_PLAN_MONITOR')
/

INSERT INTO disable_pack_dt.pack_members(
    object_name, object_type, pack_name, 
    full_member, notes)
VALUES(
    'V$SQL_PLAN_MONITOR', 'SYNONYM', 'TUNING',
    'Y', 'Synonym for V_$SQL_PLAN_MONITOR')
/

COMMIT;

--
-- Find all of the Diagnostic Pack views that are not specified by
-- name in the License and insert them into the PACK_MEMBERS table
--
INSERT INTO disable_pack_dt.pack_members( 
    object_name, object_type, pack_name,
    full_member, notes)
    SELECT object_name, object_type, 'DIAGNOSTIC',
        'Y', NULL
    FROM sys.dba_objects
    WHERE owner = 'SYS'
    AND object_type = 'VIEW'
    AND (
        object_name LIKE 'DBA_ADDM_%'
    OR ( 
        object_name LIKE 'DBA_HIST_%'
        AND object_name NOT IN (
            'DBA_HIST_SNAPSHOT', 'DBA_HIST_DATABASE_INSTANCE', 
            'DBA_HIST_SNAP_ERROR', 'DBA_HIST_SEG_STAT', 
            'DBA_HIST_SEG_STAT_OBJ', 'DBA_HIST_UNDOSTAT')
        )
    )
    UNION    
    SELECT object_name, object_type, 'DIAGNOSTIC',
        'N', 
        'Only where ADVISOR_NAME = ADDM or TASK_NAME LIKE ADDM% '
        ||'or TASK_ID relates back to TASK_NAME that is like ADDM%'
    FROM sys.dba_objects
    WHERE owner = 'SYS'
    AND object_type = 'VIEW'
    AND object_name LIKE 'DBA_ADVISOR_%'
/

COMMIT;

The message table

For the Views that comprise these APIs, we’re going to re-direct any queries to a table that simply contains a message.

Although not all of the Views are “full members” of the APIs, I have decided to treat them in the same way for the purposes of this application.
For example, we could simply amend the DBA_STREAMS_TP_COMPONENT_STAT view to return only rows where the static_unit is not ‘PERCENT’ :

  CREATE OR REPLACE FORCE VIEW "SYS"."DBA_STREAMS_TP_COMPONENT_STAT" ("COMPONENT_ID", "COMPONENT_NAME", "COMPONENT_DB", "COMPONENT_TYPE", "SUB_COMPONENT_TYPE", "SESSION_ID", "SESSION_SERIAL#", "STATISTIC_TIME", "STATISTIC_NAME", "STATISTIC_VALUE", "STATISTIC_UNIT", "ADVISOR_RUN_ID", "ADVISOR_RUN_TIME") AS 
  SELECT C.COMPONENT_ID,
       nvl(C.COMPONENT_NAME, C.SPARE3) COMPONENT_NAME,
       C.COMPONENT_DB,
       decode(C.COMPONENT_TYPE,
              1, 'CAPTURE',
              2, 'PROPAGATION SENDER',
              3, 'PROPAGATION RECEIVER',
              4, 'APPLY',
              5, 'QUEUE',
              NULL),
       decode(S.SUB_COMPONENT_TYPE,
              -- Capture sub-components
              11, 'LOGMINER READER',
              12, 'LOGMINER PREPARER',
              13, 'LOGMINER BUILDER',
              14, 'CAPTURE SESSION',
              -- Apply sub-components
              41, 'PROPAGATION SENDER+RECEIVER',
              42, 'APPLY READER',
              43, 'APPLY COORDINATOR',
              44, 'APPLY SERVER',
              NULL),
       S.SESSION_ID,
       S.SESSION_SERIAL#,
       S.STATISTIC_TIME,
       S.STATISTIC_NAME,
       -- State is a varchar2 stored in spare3, everything else is a number
       decode(S.STATISTIC_NAME,
              'STATE', S.SPARE3,
              S.STATISTIC_VALUE),
       S.STATISTIC_UNIT,
       S.ADVISOR_RUN_ID,
       S.ADVISOR_RUN_TIME
FROM streams$_component C,
     streams$_component_stat_out S
WHERE C.COMPONENT_ID = S.COMPONENT_ID
  AND S.STATISTIC_NAME IS NOT NULL
  AND S.STATISTIC_NAME NOT IN (
       'SEND RATE TO APPLY',
       'BYTES SENT VIA SQL*NET TO DBLINK')
 AND NVL(s.statistic_unit, 'X') != 'PERCENT';

However, this would likely make things a bit confusing. After all, the result of the following query would probably leave you scratching your head if you didn’t know of the little addition to the predicate :

SELECT *
FROM dba_streams_tp_component_stat
WHERE s.statistic_unit = 'PERCENT';

We want any SQL adventurer on our database to know that this View is off limits and why.
In any case, if the intrepid sole in question still wishes to make use of the view ( with the appropriate insurance of the additional predicate) then they can always by-pass the synonym by prefixing the schema owner ( i.e. SYS. DBA_STREAMS_TP_COMPONENT_STAT).
Therefore, any atempt to access any of these views will be re-directed to this table :

CREATE TABLE disable_pack_dt.disable_pack_message(
    urgent_please_read VARCHAR2(4000))
/

INSERT INTO disable_pack_dt.disable_pack_message( urgent_please_read)
VALUES(
    'Diagnostic and Tuning Packs are NOT LICENSED on this database.'||CHR(10)
    ||'Please do not access any of the pack API objects or underlying tables.'||CHR(10)
    ||'For a full list please see the table disable_pack_dt.pack_members')
/

COMMIT;

GRANT SELECT ON disable_pack_dt.disable_pack_message TO PUBLIC
/

The Error Function

This function simply raises an error using the text in the message table :

CREATE OR REPLACE FUNCTION disable_pack_dt.get_err_msg_fn
    RETURN VARCHAR2 IS
-------------------------------------------------------------------------------
-- This function will never return a value. It will always raise an error
-- with the message text being the value in 
-- DISABLE_PACK_MESSAGE.URGENT_PLEASE_READ.
--
-------------------------------------------------------------------------------
    l_msg disable_pack_dt.disable_pack_message.urgent_please_read%TYPE;
BEGIN
    SELECT urgent_please_read
    INTO l_msg
    FROM disable_pack_dt.disable_pack_message;
    RAISE_APPLICATION_ERROR(-20999, l_msg);
END;
/

Full member Packages

Of the five packages that are members of the Diagnostic and Tuning pack, four are wholly part of on or other of the packs. Two others are only considered part of these packs under certain circumstances.

As the packages are likely to be invoked in a rather different context to the views – i.e. in a PL/SQL block rather than in a SELECT statement – we need to come up with something a bit different for them.

What we’re going to do is to create a wrapper for each of the packages. For the three packages whose use is totally prohibited, we want any call to them to result in displaying the same error message as for the views.

To do this, we need to create the wrapper packages in the DISABLE_PACK_DT schema and point the Public Synonyms to them.

For the packages that are only partial members of the API, we need only to block any prohibited calls whilst passing through anything else.

For each package member we want to block, we simply need to re-direct the call to the get_err_msg_fn function we’ve just created.

You can get the names of the three full member packages with this query :

SQL> SELECT object_name 
  2  FROM disable_pack_dt.pack_members 
  3  WHERE object_type = 'PACKAGE' 
  4  AND full_member = 'Y' 
  5  ORDER BY 1; 

OBJECT_NAME 
------------------------------ 
DBMS_ADDM 
DBMS_SQLTUNE 
DBMS_WORKLOAD_REPOSITORY 

SQL> 

Before listing the sourcecode for the blocker packages, I should point out that I did save myself some typing here by getting the code out of the DBA_SOURCE view. As a result, the code doesn’t follow my normal coding conventions.

Here then, is the code for the header and body for each of the packages :

BLOCK_DBMS_ADDM

CREATE OR REPLACE PACKAGE disable_pack_dt.BLOCK_DBMS_ADDM
authid current_user
IS

procedure analyze_db ( task_name             in out varchar2,
begin_snapshot        in number,
end_snapshot          in number,
db_id                 in number := NULL);



procedure analyze_inst ( task_name             in out varchar2,
begin_snapshot        in number,
end_snapshot          in number,
instance_number       in number := NULL,
db_id                 in number := NULL);


procedure analyze_partial ( task_name             in out varchar2,
instance_numbers      in varchar2,
begin_snapshot        in number,
end_snapshot          in number,
db_id                 in number := NULL);


procedure insert_finding_directive ( task_name           in varchar2,
dir_name            in varchar2,
finding_name        in varchar2,
min_active_sessions in number := 0,
min_perc_impact     in number := 0);


procedure insert_sql_directive ( task_name           in varchar2,
dir_name            in varchar2,
sql_id              in varchar2,
min_active_sessions in number := 0,
min_response_time   in number := 0);


procedure insert_segment_directive ( task_name           in varchar2,
dir_name            in varchar2,
owner_name          in varchar2,
object_name         in varchar2 := NULL,
sub_object_name     in varchar2 := NULL);


procedure insert_segment_directive ( task_name           in varchar2,
dir_name            in varchar2,
object_number       in number);


procedure insert_parameter_directive ( task_name           in varchar2,
dir_name            in varchar2,
parameter_name      in varchar2);

procedure delete_finding_directive ( task_name           in varchar2,
dir_name            in varchar2);

procedure delete_sql_directive ( task_name           in varchar2,
dir_name            in varchar2);

procedure delete_segment_directive ( task_name           in varchar2,
dir_name            in varchar2);

procedure delete_parameter_directive ( task_name           in varchar2,
dir_name            in varchar2);


procedure delete ( task_name             in varchar2);



function get_report ( task_name             in varchar2)
return clob;


function get_ash_query ( task_name in varchar2, finding_id in number)
return varchar2;

END BLOCK_DBMS_ADDM;
/ 
CREATE OR REPLACE PACKAGE BODY disable_pack_dt.BLOCK_DBMS_ADDM
IS

procedure analyze_db ( task_name             in out varchar2,
begin_snapshot        in number,
end_snapshot          in number,
db_id                 in number := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure analyze_inst ( task_name             in out varchar2,
begin_snapshot        in number,
end_snapshot          in number,
instance_number       in number := NULL,
db_id                 in number := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure analyze_partial ( task_name             in out varchar2,
instance_numbers      in varchar2,
begin_snapshot        in number,
end_snapshot          in number,
db_id                 in number := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure insert_finding_directive ( task_name           in varchar2,
dir_name            in varchar2,
finding_name        in varchar2,
min_active_sessions in number := 0,
min_perc_impact     in number := 0) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



procedure insert_sql_directive ( task_name           in varchar2,
dir_name            in varchar2,
sql_id              in varchar2,
min_active_sessions in number := 0,
min_response_time   in number := 0) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



procedure insert_segment_directive ( task_name           in varchar2,
dir_name            in varchar2,
owner_name          in varchar2,
object_name         in varchar2 := NULL,
sub_object_name     in varchar2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



procedure insert_segment_directive ( task_name           in varchar2,
dir_name            in varchar2,
object_number       in number) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



procedure insert_parameter_directive ( task_name           in varchar2,
dir_name            in varchar2,
parameter_name      in varchar2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure delete_finding_directive ( task_name           in varchar2,
dir_name            in varchar2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure delete_sql_directive ( task_name           in varchar2,
dir_name            in varchar2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure delete_segment_directive ( task_name           in varchar2,
dir_name            in varchar2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


procedure delete_parameter_directive ( task_name           in varchar2,
dir_name            in varchar2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



procedure delete ( task_name             in varchar2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 




function get_report ( task_name             in varchar2)
return clob IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



function get_ash_query ( task_name in varchar2, finding_id in number)
return varchar2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


END BLOCK_DBMS_ADDM;
/

BLOCK_DBMS_SQLTUNE

CREATE OR REPLACE PACKAGE disable_pack_dt.BLOCK_DBMS_SQLTUNE AUTHID CURRENT_USER AS
ADV_SQLTUNE_NAME  CONSTANT VARCHAR2(18) := 'SQL Tuning Advisor';

SCOPE_LIMITED       CONSTANT VARCHAR2(7)  := 'LIMITED';
SCOPE_COMPREHENSIVE CONSTANT VARCHAR2(13) := 'COMPREHENSIVE';

TIME_LIMIT_DEFAULT  CONSTANT   NUMBER := 1800;

TYPE_TEXT           CONSTANT   VARCHAR2(4) := 'TEXT'       ;
TYPE_XML            CONSTANT   VARCHAR2(3) := 'XML'        ;
TYPE_HTML           CONSTANT   VARCHAR2(4) := 'HTML'       ;

LEVEL_TYPICAL       CONSTANT   VARCHAR2(7) := 'TYPICAL'    ;
LEVEL_BASIC         CONSTANT   VARCHAR2(5) := 'BASIC'      ;
LEVEL_ALL           CONSTANT   VARCHAR2(3) := 'ALL'        ;

SECTION_FINDINGS    CONSTANT   VARCHAR2(8) := 'FINDINGS'   ;
SECTION_PLANS       CONSTANT   VARCHAR2(5) := 'PLANS'      ;
SECTION_INFORMATION CONSTANT   VARCHAR2(11):= 'INFORMATION';
SECTION_ERRORS      CONSTANT   VARCHAR2(6) := 'ERRORS'     ;
SECTION_ALL         CONSTANT   VARCHAR2(3) := 'ALL'        ;
SECTION_SUMMARY     CONSTANT   VARCHAR2(7) := 'SUMMARY'    ;

DATE_FMT       constant varchar2(21)       :=  'mm/dd/yyyy hh24:mi:ss';

REC_TYPE_ALL          CONSTANT   VARCHAR2(3)  := 'ALL';
REC_TYPE_SQL_PROFILES CONSTANT   VARCHAR2(8)  := 'PROFILES';
REC_TYPE_STATS        CONSTANT   VARCHAR2(10) := 'STATISTICS';
REC_TYPE_INDEXES      CONSTANT   VARCHAR2(7)  := 'INDEXES';
REC_TYPE_PX           CONSTANT   VARCHAR2(18) := 'PARALLEL_EXECUTION';
REC_TYPE_ALTER_PLANS  CONSTANT   VARCHAR2(17) := 'ALTERNATIVE_PLANS';

MODE_REPLACE_OLD_STATS CONSTANT   NUMBER := 1;
MODE_ACCUMULATE_STATS  CONSTANT   NUMBER := 2;

SINGLE_EXECUTION       CONSTANT   POSITIVE := 1;
ALL_EXECUTIONS         CONSTANT   POSITIVE := 2;
LIMITED_COMMAND_TYPE   CONSTANT   BINARY_INTEGER  := 1;
ALL_COMMAND_TYPE       CONSTANT   BINARY_INTEGER  := 2;

REGULAR_PROFILE        CONSTANT   VARCHAR2(11) := 'SQL PROFILE';
PX_PROFILE             CONSTANT   VARCHAR2(10) := 'PX PROFILE';

STS_STGTAB_10_2_VERSION     CONSTANT NUMBER := 1;
STS_STGTAB_11_1_VERSION     CONSTANT NUMBER := 2;
STS_STGTAB_11_2_VERSION     CONSTANT NUMBER := 3;
STS_STGTAB_11_202_VERSION   CONSTANT NUMBER := 4;


NO_RECURSIVE_SQL            CONSTANT VARCHAR2(30) := 'N';
HAS_RECURSIVE_SQL           CONSTANT VARCHAR2(30) := 'Y';



FUNCTION create_tuning_task(
sql_text    IN CLOB,
bind_list   IN sql_binds := NULL,
user_name   IN VARCHAR2  := NULL,
scope       IN VARCHAR2  := SCOPE_COMPREHENSIVE,
time_limit  IN NUMBER    := TIME_LIMIT_DEFAULT,
task_name   IN VARCHAR2  := NULL,
description IN VARCHAR2  := NULL)
RETURN VARCHAR2;

FUNCTION create_tuning_task(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER   := NULL,
scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name       IN VARCHAR2 := NULL,
description     IN VARCHAR2 := NULL)
RETURN VARCHAR2;

FUNCTION create_tuning_task(
begin_snap      IN NUMBER,
end_snap        IN NUMBER,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER   := NULL,
scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name       IN VARCHAR2 := NULL,
description     IN VARCHAR2 := NULL)
RETURN VARCHAR2;

FUNCTION create_tuning_task(
sqlset_name       IN VARCHAR2,
basic_filter      IN VARCHAR2 :=  NULL,
object_filter     IN VARCHAR2 :=  NULL,
rank1             IN VARCHAR2 :=  NULL,
rank2             IN VARCHAR2 :=  NULL,
rank3             IN VARCHAR2 :=  NULL,
result_percentage IN NUMBER   :=  NULL,
result_limit      IN NUMBER   :=  NULL,
scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
task_name         IN VARCHAR2 :=  NULL,
description       IN VARCHAR2 :=  NULL,
plan_filter       IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

FUNCTION create_tuning_task(
spa_task_name     IN VARCHAR2,
spa_task_owner    IN VARCHAR2 :=  NULL,
spa_compare_exec  IN VARCHAR2 :=  NULL,
basic_filter      IN
VARCHAR2 :=  NULL,
time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
task_name         IN VARCHAR2 :=  NULL,
description       IN VARCHAR2 :=  NULL)
RETURN VARCHAR2;

PROCEDURE set_tuning_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN VARCHAR2);

PROCEDURE set_tuning_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER);

PROCEDURE set_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);

PROCEDURE set_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN NUMBER);

FUNCTION execute_tuning_task(
task_name        IN VARCHAR2,
execution_name   IN VARCHAR2               := NULL,
execution_params IN dbms_advisor.argList   := NULL,
execution_desc   IN VARCHAR2               := NULL)
RETURN VARCHAR2;
PROCEDURE execute_tuning_task(
task_name        IN VARCHAR2,
execution_name   IN VARCHAR2               := NULL,
execution_params IN dbms_advisor.argList   := NULL,
execution_desc   IN VARCHAR2               := NULL);


procedure interrupt_tuning_task(task_name IN VARCHAR2);

PROCEDURE cancel_tuning_task(task_name IN VARCHAR2);

PROCEDURE reset_tuning_task(task_name IN VARCHAR2);

PROCEDURE drop_tuning_task(task_name IN VARCHAR2);

PROCEDURE resume_tuning_task(
task_name    IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL);

FUNCTION report_tuning_task(
task_name      IN VARCHAR2,
type           IN VARCHAR2 := TYPE_TEXT,
level          IN VARCHAR2 := LEVEL_TYPICAL,
section        IN VARCHAR2 := SECTION_ALL,
object_id      IN NUMBER   := NULL,
result_limit   IN NUMBER   := NULL,
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN clob;

FUNCTION script_tuning_task(
task_name      IN VARCHAR2,
rec_type       IN VARCHAR2 := REC_TYPE_ALL,
object_id      IN NUMBER   := NULL,
result_limit   IN NUMBER   := NULL,
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN CLOB;


PROCEDURE create_sql_plan_baseline(
task_name            IN VARCHAR2,
object_id            IN NUMBER := NULL,
plan_hash_value      IN NUMBER,
owner_name           IN VARCHAR2 := NULL);

PROCEDURE implement_tuning_task(
task_name      IN VARCHAR2,
rec_type       IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL);


FUNCTION report_auto_tuning_task(
begin_exec     IN VARCHAR2  := NULL,
end_exec       IN VARCHAR2  := NULL,
type           IN VARCHAR2  := TYPE_TEXT,
level          IN VARCHAR2  := LEVEL_TYPICAL,
section        IN VARCHAR2  := SECTION_ALL,
object_id      IN NUMBER    := NULL,
result_limit   IN NUMBER    := NULL)
RETURN CLOB;



TYPE sqlset_cursor IS REF CURSOR;


PROCEDURE create_sqlset(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);

FUNCTION create_sqlset(
sqlset_name   IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
sqlset_owner  IN VARCHAR2 := NULL)
RETURN VARCHAR2;

PROCEDURE drop_sqlset(
sqlset_name   IN VARCHAR2,
sqlset_owner  IN VARCHAR2 := NULL);

PROCEDURE delete_sqlset(
sqlset_name  IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL);

PROCEDURE load_sqlset(
sqlset_name       IN VARCHAR2,
populate_cursor   IN sqlset_cursor,
load_option       IN VARCHAR2 := 'INSERT',
update_option     IN VARCHAR2 := 'REPLACE',
update_condition  IN VARCHAR2 :=  NULL,
update_attributes IN VARCHAR2 :=  NULL,
ignore_null       IN BOOLEAN  :=  TRUE,
commit_rows       IN POSITIVE :=  NULL,
sqlset_owner      IN VARCHAR2 :=  NULL);

PROCEDURE capture_cursor_cache_sqlset(
sqlset_name         IN VARCHAR2,
time_limit          IN POSITIVE := 1800,
repeat_interval     IN POSITIVE := 300,
capture_option      IN VARCHAR2 := 'MERGE',
capture_mode        IN NUMBER   := MODE_REPLACE_OLD_STATS,
basic_filter        IN VARCHAR2 := NULL,
sqlset_owner        IN VARCHAR2 := NULL,
recursive_sql       IN VARCHAR2 := HAS_RECURSIVE_SQL);

PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN                    
VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL);

PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL);

PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN NUMBER   := NULL,
sqlset_owner    IN VARCHAR2 := NULL);

PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name  IN VARCHAR2,
attribute_value IN NUMBER   := NULL,
sqlset_owner    IN VARCHAR2 := NULL);

FUNCTION add_sqlset_reference(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN NUMBER;

PROCEDURE remove_sqlset_reference(
sqlset_name  IN VARCHAR2,
reference_id IN NUMBER,
sqlset_owner IN VARCHAR2 := NULL);

FUNCTION select_sqlset(
sqlset_name       IN VARCHAR2,
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
plan_filter       IN VARCHAR2 := NULL,
sqlset_owner      IN VARCHAR2 := NULL,
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;

FUNCTION select_cursor_cache(
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;

FUNCTION select_workload_repository(
begin_snap        IN NUMBER,
end_snap          IN NUMBER,
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;

FUNCTION select_workload_repository(
baseline_name     IN VARCHAR2,
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED;

FUNCTION select_sql_trace(
directory              IN VARCHAR2,
file_name              IN VARCHAR2 := NULL,
mapping_table_name     IN VARCHAR2 := NULL,
mapping_table_owner    IN VARCHAR2 := NULL,
select_mode            IN POSITIVE := SINGLE_EXECUTION,
options                IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
pattern_start          IN VARCHAR2 := NULL,
pattern_end            IN VARCHAR2 := NULL,
result_limit           IN POSITIVE := NULL)
RETURN sys.sqlset PIPELINED;

FUNCTION select_sqlpa_task(
task_name         IN VARCHAR2,
task_owner        IN VARCHAR2 := NULL,
execution_name    IN VARCHAR2 := NULL,
level_filter      IN VARCHAR2 := 'REGRESSED',
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL')
RETURN sys.sqlset PIPELINED;


PROCEDURE create_stgtab_sqlset(
table_name           IN VARCHAR2,
schema_name          IN VARCHAR2 := NULL,
tablespace_name      IN VARCHAR2 := NULL,
db_version           IN NUMBER   := NULL);

PROCEDURE pack_stgtab_sqlset(
sqlset_name          IN VARCHAR2,
sqlset_owner          
IN VARCHAR2 := NULL,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL,
db_version           IN NUMBER   := NULL);

PROCEDURE unpack_stgtab_sqlset(
sqlset_name          IN VARCHAR2 := '%',
sqlset_owner         IN VARCHAR2 := NULL,
replace              IN BOOLEAN,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL);

PROCEDURE remap_stgtab_sqlset(
old_sqlset_name        IN VARCHAR2,
old_sqlset_owner       IN VARCHAR2 := NULL,
new_sqlset_name        IN VARCHAR2 := NULL,
new_sqlset_owner       IN VARCHAR2 := NULL,
staging_table_name     IN VARCHAR2,
staging_schema_owner   IN VARCHAR2 := NULL);

FUNCTION transform_sqlset_cursor(
populate_cursor IN sqlset_cursor)
RETURN sys.sqlset PIPELINED;

FUNCTION accept_sql_profile(
task_name    IN VARCHAR2,
object_id    IN NUMBER   := NULL,
name         IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
category     IN VARCHAR2 := NULL,
task_owner   IN VARCHAR2 := NULL,
replace      IN BOOLEAN  := FALSE,
force_match  IN BOOLEAN  := FALSE,
profile_type IN VARCHAR2 := REGULAR_PROFILE)
RETURN VARCHAR2;

PROCEDURE accept_sql_profile(
task_name    IN VARCHAR2,
object_id    IN NUMBER   := NULL,
name         IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
category     IN VARCHAR2 := NULL,
task_owner   IN VARCHAR2 := NULL,
replace      IN BOOLEAN  := FALSE,
force_match  IN BOOLEAN  := FALSE,
profile_type IN VARCHAR2 := REGULAR_PROFILE);

PROCEDURE drop_sql_profile(
name          IN VARCHAR2,
ignore        IN BOOLEAN  := FALSE);

PROCEDURE alter_sql_profile(
name                 IN VARCHAR2,
attribute_name       IN VARCHAR2,
value                IN VARCHAR2);

PROCEDURE import_sql_profile(
sql_text      IN CLOB,
profile       IN sqlprof_attr,
name          IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
category      IN VARCHAR2 := NULL,
validate      IN BOOLEAN  := TRUE,
replace       IN BOOLEAN  := FALSE,
force_match   IN BOOLEAN  := FALSE);

PROCEDURE import_sql_profile(
sql_text      IN CLOB,
profile_xml   IN CLOB,
name          IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
category      IN VARCHAR2 := NULL,
validate      IN BOOLEAN  := TRUE,
replace       IN BOOLEAN  := FALSE,
force_match   IN BOOLEAN  := FALSE);

FUNCTION sqltext_to_signature(sql_text    IN CLOB,
force_match IN BOOLEAN  := FALSE)
RETURN NUMBER;

FUNCTION sqltext_to_signature(sql_text    IN CLOB,
force_match IN BINARY_INTEGER)
RETURN NUMBER;


PROCEDURE create_stgtab_sqlprof(
table_name            IN VARCHAR2,
schema_name           IN VARCHAR2 := NULL,
tablespace_name       IN VARCHAR2 := NULL);

PROCEDURE pack_stgtab_sqlprof(
profile_name          IN VARCHAR2 := '%',
profile_category      IN VARCHAR2 := 'DEFAULT',
staging_table_name    IN VARCHAR2,
staging_schema_owner  IN VARCHAR2 := NULL);

PROCEDURE unpack_stgtab_sqlprof(
profile_name          IN VARCHAR2 := '%',
profile_category      IN VARCHAR2 := '%',
replace               IN BOOLEAN,
staging_table_name    IN VARCHAR2,
staging_schema_owner  IN VARCHAR2 := NULL);

PROCEDURE remap_stgtab_sqlprof(
old_profile_name      IN VARCHAR2,
new_profile_name      IN VARCHAR2 := NULL,
new_profile_category  IN VARCHAR2 := NULL,
staging_table_name    IN VARCHAR2,
staging_schema_owner  IN VARCHAR2 := NULL);

FUNCTION report_sql_monitor(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
sql_exec_start            in date     default  NULL,
sql_exec_id               in number   default  NULL,
inst_id                   in number   default  NULL,
start_time_filter         in date     default  NULL,
end_time_filter           in date     default  NULL,
instance_id_filter        in number   default  NULL,
parallel_filter           in varchar2 default  NULL,
plan_line_filter          in number   default  NULL,
event_detail              in varchar2 default  'yes',
bucket_max_count          in number        
default  128,
bucket_interval           in number   default  NULL,
base_path                 in varchar2 default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default 'TYPICAL',
type                      in varchar2 default 'TEXT',
sql_plan_hash_value       in number   default  NULL)
RETURN clob;


FUNCTION report_sql_monitor_xml(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
sql_exec_start            in date     default  NULL,
sql_exec_id               in number   default  NULL,
inst_id                   in number   default  NULL,
start_time_filter         in date     default  NULL,
end_time_filter           in date     default  NULL,
instance_id_filter        in number   default  NULL,
parallel_filter           in varchar2 default  NULL,
plan_line_filter          in number   default  NULL,
event_detail              in varchar2 default  'yes',
bucket_max_count          in number   default  128,
bucket_interval           in number   default  NULL,
base_path                 in varchar2 default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default 'TYPICAL',
auto_refresh              in number   default  NULL,
sql_plan_hash_value       in number   default  NULL)
return xmltype;


FUNCTION report_sql_monitor_list(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
inst_id                   in number   default  NULL,
active_since_date         in date     default  NULL,
active_since_sec          in number   default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default  'TYPICAL',
auto_refresh              in number   default  NULL,
base_path                 in varchar2 default  NULL,
type                      in varchar2 default 'TEXT')
RETURN clob;


FUNCTION report_sql_monitor_list_xml(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
inst_id                   in number   default  NULL,
active_since_date         in date     default  NULL,
active_since_sec          in number   default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default  'TYPICAL',
auto_refresh              in number   default  NULL,
base_path                 in varchar2 default  NULL)
RETURN xmltype;


FUNCTION report_sql_detail(
sql_id                   in  varchar2   default NULL,
sql_plan_hash_value      in  number     default NULL,
start_time               in  date       default NULL,
duration                 in  number     default NULL,
inst_id                  in  number     default NULL,
dbid                     in  number     default NULL,
event_detail             in  varchar2   default 'yes',
bucket_max_count         in  number     default 128,
bucket_interval          in  number     default NULL,
top_n                    in  number     default 10,
report_level             in  varchar2   default 'typical',
type                     in  varchar2   default 'ACTIVE',
data_source              in  varchar2   default 'auto',
end_time                 in  date       default NULL,
duration_stats           in  number     default NULL)
RETURN clob;


FUNCTION report_sql_detail_xml(
sql_id                   in  varchar2   default NULL,
sql_plan_hash_value      in  number     default NULL,
start_time               in  date       default NULL,
duration                 in  number     default NULL,
inst_id                  in  number     default NULL,
dbid                     in  number     default NULL,
event_detail             in  varchar2   default 'yes',
bucket_max_count         in  number     default 128,
bucket_interval          in    
number     default NULL,
top_n                    in  number     default 10,
report_level             in  varchar2   default 'typical',
data_source              in  varchar2   default 'auto',
end_time                 in  date       default NULL,
duration_stats           in  number     default NULL)
return xmltype;


FUNCTION extract_bind(
bind_data   IN RAW,
bind_pos    IN PLS_INTEGER) RETURN SQL_BIND;

FUNCTION extract_binds(
bind_data IN RAW)
RETURN SQL_BIND_SET PIPELINED;

PROCEDURE set_auto_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2);

PROCEDURE set_auto_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN NUMBER);

FUNCTION build_stash_xml(
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
session_inst_id           in number   default  NULL,
px_mode                   in varchar2 default  'yes',
start_time                in date     default  NULL,
end_time                  in date     default  NULL,
missing_seconds           in number   default  NULL,
instance_low_filter       in number   default  0,
instance_high_filter      in number   default  10000,
bucket_max_count          in number   default  128,
bucket_interval           in number   default  NULL,
report_level              in varchar2 default 'TYPICAL',
cpu_cores                 in binary_integer  default  NULL,
is_hyper                  in varchar2        default  NULL)
RETURN xmltype;

PROCEDURE check_sqlset_privs(
sqlset_name   IN VARCHAR2,
sqlset_owner  IN VARCHAR2,
sqlset_create IN BOOLEAN := false,
read_only     IN BOOLEAN := false);

PROCEDURE check_sql_profile_priv(priv IN VARCHAR2);

PROCEDURE cap_sts_cbk(
sqlset_name    IN VARCHAR2,
iterations     IN POSITIVE,
cap_option     IN VARCHAR2,
cap_mode       IN NUMBER,
cbk_proc_name  IN VARCHAR2,
basic_filter   IN VARCHAR2 := NULL,
sqlset_owner   IN VARCHAR2 := NULL);

FUNCTION prepare_sqlset_statement(
sqlset_name        IN            VARCHAR2,
sqlset_owner       IN            VARCHAR2,
basic_filter       IN            VARCHAR2 := NULL,
stmt_filter        IN            BOOLEAN  := FALSE,
object_filter      IN            VARCHAR2 := NULL,
plan_filter        IN            VARCHAR2 := NULL,
rank1              IN            VARCHAR2 := NULL,
rank2              IN            VARCHAR2 := NULL,
rank3              IN            VARCHAR2 := NULL,
result_percentage  IN            NUMBER   := 1,
result_limit       IN            NUMBER   := NULL,
attribute_list     IN            VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY BINARY_INTEGER,
wrap_obj_ctor      IN            BOOLEAN := FALSE,
check_binds        IN            BOOLEAN := TRUE,
sts_id             OUT           NUMBER,
first_rows_hint    IN            BOOLEAN  :=  TRUE)
RETURN VARCHAR2;

FLAG_PREPAWR_WRAPCTOR  CONSTANT NUMBER := POWER(2, 0);
FLAG_PREPAWR_NOCKBINDS CONSTANT NUMBER := POWER(2, 1);
FLAG_PREPAWR_INCLBID   CONSTANT NUMBER := POWER(2, 2);

FUNCTION prepare_awr_statement(
begin_snap         IN             NUMBER,
end_snap           IN             NUMBER,
basic_filter       IN             VARCHAR2 := NULL,
stmt_filter        IN             BOOLEAN  := FALSE,
object_filter      IN             VARCHAR2 := NULL,
rank1              IN             VARCHAR2 := NULL,
rank2              IN             VARCHAR2 := NULL,
rank3              IN             VARCHAR2 := NULL,
result_percentage  IN             NUMBER   := 1,
result_limit       IN             NUMBER   := NULL,
attribute_list     IN             VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY  BINARY_INTEGER,
flags              IN             NUMBER   := 0)
RETURN VARCHAR2;

PROCEDURE sqlset_progress_stats(
sqlset_name        IN            VARCHAR2,
sqlset_owner       IN            VARCHAR2,
basic_filter       IN            VARCHAR2 := NULL,
plan_filter        IN            VARCHAR2 := NULL,
rank1              IN            VARCHAR2 := NULL,
rank2              IN            VARCHAR2 := NULL,
rank3           
IN            VARCHAR2 := NULL,
result_percentage  IN            NUMBER   := 1,
result_limit       IN            NUMBER   := NULL,
sql_count          OUT           NUMBER,
workload_time      OUT           NUMBER,
exec_type#         IN            PLS_INTEGER);

PROCEDURE examine_stgtab(
stgtab_owner       IN            VARCHAR2,
stgtab             IN            VARCHAR2,
sts_name           OUT           VARCHAR2,
sts_owner          OUT           VARCHAR2);

END BLOCK_DBMS_SQLTUNE;
/
CREATE OR REPLACE PACKAGE BODY disable_pack_dt.BLOCK_DBMS_SQLTUNE AS 

FUNCTION create_tuning_task(
sql_text    IN CLOB,
bind_list   IN sql_binds := NULL,
user_name   IN VARCHAR2  := NULL,
scope       IN VARCHAR2  := SCOPE_COMPREHENSIVE,
time_limit  IN NUMBER    := TIME_LIMIT_DEFAULT,
task_name   IN VARCHAR2  := NULL,
description IN VARCHAR2  := NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_tuning_task(
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER   := NULL,
scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name       IN VARCHAR2 := NULL,
description     IN VARCHAR2 := NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_tuning_task(
begin_snap      IN NUMBER,
end_snap        IN NUMBER,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER   := NULL,
scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,
task_name       IN VARCHAR2 := NULL,
description     IN VARCHAR2 := NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_tuning_task(
sqlset_name       IN VARCHAR2,
basic_filter      IN VARCHAR2 :=  NULL,
object_filter     IN VARCHAR2 :=  NULL,
rank1             IN VARCHAR2 :=  NULL,
rank2             IN VARCHAR2 :=  NULL,
rank3             IN VARCHAR2 :=  NULL,
result_percentage IN NUMBER   :=  NULL,
result_limit      IN NUMBER   :=  NULL,
scope             IN VARCHAR2 :=  SCOPE_COMPREHENSIVE,
time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
task_name         IN VARCHAR2 :=  NULL,
description       IN VARCHAR2 :=  NULL,
plan_filter
IN VARCHAR2 :=  'MAX_ELAPSED_TIME',
sqlset_owner      IN VARCHAR2 :=  NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_tuning_task(
spa_task_name     IN VARCHAR2,
spa_task_owner    IN VARCHAR2 :=  NULL,
spa_compare_exec  IN VARCHAR2 :=  NULL,
basic_filter      IN VARCHAR2 :=  NULL,
time_limit        IN NUMBER   :=  TIME_LIMIT_DEFAULT,
task_name         IN VARCHAR2 :=  NULL,
description       IN VARCHAR2 :=  NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE set_tuning_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE set_tuning_task_parameter(
task_name IN VARCHAR2,
parameter IN VARCHAR2,
value     IN NUMBER) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE set_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE set_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN NUMBER) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION execute_tuning_task(
task_name        IN VARCHAR2,
execution_name   IN VARCHAR2               := NULL,
execution_params IN dbms_advisor.argList   := NULL,
execution_desc   IN VARCHAR2               := NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

PROCEDURE execute_tuning_task(
task_name        IN VARCHAR2,
execution_name   IN VARCHAR2               := NULL,
execution_params IN dbms_advisor.argList   := NULL,
execution_desc   IN VARCHAR2               := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



procedure interrupt_tuning_task(task_name IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE cancel_tuning_task(task_name IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE reset_tuning_task(task_name IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE drop_tuning_task(task_name IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE resume_tuning_task(
task_name    IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION report_tuning_task(
task_name      IN VARCHAR2,
type           IN VARCHAR2 := TYPE_TEXT,
level          IN VARCHAR2 := LEVEL_TYPICAL,
section        IN VARCHAR2 := SECTION_ALL,
object_id      IN NUMBER   := NULL,
result_limit   IN NUMBER   := NULL,
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN clob IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION script_tuning_task(
task_name      IN VARCHAR2,
rec_type       IN VARCHAR2 := REC_TYPE_ALL,
object_id      IN NUMBER   := NULL,
result_limit   IN NUMBER   := NULL,
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN CLOB IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE create_sql_plan_baseline(
task_name            IN VARCHAR2,
object_id            IN NUMBER := NULL,
plan_hash_value      IN NUMBER,
owner_name           IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE implement_tuning_task(
task_name      IN VARCHAR2,
rec_type       IN VARCHAR2 := REC_TYPE_SQL_PROFILES,
owner_name     IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg :=  
disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION report_auto_tuning_task(
begin_exec     IN VARCHAR2  := NULL,
end_exec       IN VARCHAR2  := NULL,
type           IN VARCHAR2  := TYPE_TEXT,
level          IN VARCHAR2  := LEVEL_TYPICAL,
section        IN VARCHAR2  := SECTION_ALL,
object_id      IN NUMBER    := NULL,
result_limit   IN NUMBER    := NULL)
RETURN CLOB IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

PROCEDURE create_sqlset(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_sqlset(
sqlset_name   IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
sqlset_owner  IN VARCHAR2 := NULL)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE drop_sqlset(
sqlset_name   IN VARCHAR2,
sqlset_owner  IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE delete_sqlset(
sqlset_name  IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE load_sqlset(
sqlset_name       IN VARCHAR2,
populate_cursor   IN sqlset_cursor,
load_option       IN VARCHAR2 := 'INSERT',
update_option     IN VARCHAR2 := 'REPLACE',
update_condition  IN VARCHAR2 :=  NULL,
update_attributes IN VARCHAR2 :=  NULL,
ignore_null       IN BOOLEAN  :=  TRUE,
commit_rows       IN POSITIVE :=  NULL,
sqlset_owner      IN VARCHAR2 :=  NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE capture_cursor_cache_sqlset(
sqlset_name         IN VARCHAR2,
time_limit          IN POSITIVE := 1800,
repeat_interval     IN POSITIVE := 300,
capture_option      IN VARCHAR2 := 'MERGE',
capture_mode        IN NUMBER   := MODE_REPLACE_OLD_STATS,
basic_filter        IN VARCHAR2 := NULL,
sqlset_owner        IN VARCHAR2 := NULL,
recursive_sql       IN VARCHAR2 := HAS_RECURSIVE_SQL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name  IN VARCHAR2,
attribute_value IN VARCHAR2 := NULL,
sqlset_owner    IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
attribute_name  IN VARCHAR2,
attribute_value IN NUMBER   := NULL,
sqlset_owner    IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE update_sqlset(
sqlset_name     IN VARCHAR2,
sql_id          IN VARCHAR2,
plan_hash_value IN NUMBER,
attribute_name  IN VARCHAR2,
attribute_value IN NUMBER   := NULL,
sqlset_owner    IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION add_sqlset_reference(
sqlset_name  IN VARCHAR2,
description  IN VARCHAR2 := NULL,
sqlset_owner IN VARCHAR2 := NULL)
RETURN NUMBER IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE remove_sqlset_reference(
sqlset_name  IN VARCHAR2,
reference_id IN NUMBER,
sqlset_owner IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_sqlset(
sqlset_name       IN VARCHAR2,
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 :=    
NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
plan_filter       IN VARCHAR2 := NULL,
sqlset_owner      IN VARCHAR2 := NULL,
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_cursor_cache(
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_workload_repository(
begin_snap        IN NUMBER,
end_snap          IN NUMBER,
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_workload_repository(
baseline_name     IN VARCHAR2,
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
ranking_measure1  IN VARCHAR2 := NULL,
ranking_measure2  IN VARCHAR2 := NULL,
ranking_measure3  IN VARCHAR2 := NULL,
result_percentage IN NUMBER   := 1,
result_limit      IN NUMBER   := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL',
recursive_sql     IN VARCHAR2 := HAS_RECURSIVE_SQL)
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_sql_trace(
directory              IN VARCHAR2,
file_name              IN VARCHAR2 := NULL,
mapping_table_name     IN VARCHAR2 := NULL,
mapping_table_owner    IN VARCHAR2 := NULL,
select_mode            IN POSITIVE := SINGLE_EXECUTION,
options                IN BINARY_INTEGER := LIMITED_COMMAND_TYPE,
pattern_start          IN VARCHAR2 := NULL,
pattern_end            IN VARCHAR2 := NULL,
result_limit           IN POSITIVE := NULL)
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_sqlpa_task(
task_name         IN VARCHAR2,
task_owner        IN VARCHAR2 := NULL,
execution_name    IN VARCHAR2 := NULL,
level_filter      IN VARCHAR2 := 'REGRESSED',
basic_filter      IN VARCHAR2 := NULL,
object_filter     IN VARCHAR2 := NULL,
attribute_list    IN VARCHAR2 := 'TYPICAL')
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE create_stgtab_sqlset(
table_name           IN VARCHAR2,
schema_name          IN VARCHAR2 := NULL,
tablespace_name      IN VARCHAR2 := NULL,
db_version           IN NUMBER   := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE pack_stgtab_sqlset(
sqlset_name          IN VARCHAR2,
sqlset_owner         IN VARCHAR2 := NULL,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL,
db_version           IN NUMBER   := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE unpack_stgtab_sqlset(
sqlset_name          IN VARCHAR2 := '%',
sqlset_owner         IN VARCHAR2 := NULL,
replace              IN BOOLEAN,
staging_table_name   IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg :=                     
disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE remap_stgtab_sqlset(
old_sqlset_name        IN VARCHAR2,
old_sqlset_owner       IN VARCHAR2 := NULL,
new_sqlset_name        IN VARCHAR2 := NULL,
new_sqlset_owner       IN VARCHAR2 := NULL,
staging_table_name     IN VARCHAR2,
staging_schema_owner   IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION transform_sqlset_cursor(
populate_cursor IN sqlset_cursor)
RETURN sys.sqlset PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION accept_sql_profile(
task_name    IN VARCHAR2,
object_id    IN NUMBER   := NULL,
name         IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
category     IN VARCHAR2 := NULL,
task_owner   IN VARCHAR2 := NULL,
replace      IN BOOLEAN  := FALSE,
force_match  IN BOOLEAN  := FALSE,
profile_type IN VARCHAR2 := REGULAR_PROFILE)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE accept_sql_profile(
task_name    IN VARCHAR2,
object_id    IN NUMBER   := NULL,
name         IN VARCHAR2 := NULL,
description  IN VARCHAR2 := NULL,
category     IN VARCHAR2 := NULL,
task_owner   IN VARCHAR2 := NULL,
replace      IN BOOLEAN  := FALSE,
force_match  IN BOOLEAN  := FALSE,
profile_type IN VARCHAR2 := REGULAR_PROFILE) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE drop_sql_profile(
name          IN VARCHAR2,
ignore        IN BOOLEAN  := FALSE) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE alter_sql_profile(
name                 IN VARCHAR2,
attribute_name       IN VARCHAR2,
value                IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE import_sql_profile(
sql_text      IN CLOB,
profile       IN sqlprof_attr,
name          IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
category      IN VARCHAR2 := NULL,
validate      IN BOOLEAN  := TRUE,
replace       IN BOOLEAN  := FALSE,
force_match   IN BOOLEAN  := FALSE) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE import_sql_profile(
sql_text      IN CLOB,
profile_xml   IN CLOB,
name          IN VARCHAR2 := NULL,
description   IN VARCHAR2 := NULL,
category      IN VARCHAR2 := NULL,
validate      IN BOOLEAN  := TRUE,
replace       IN BOOLEAN  := FALSE,
force_match   IN BOOLEAN  := FALSE) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION sqltext_to_signature(sql_text    IN CLOB,
force_match IN BOOLEAN  := FALSE)
RETURN NUMBER IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION sqltext_to_signature(sql_text    IN CLOB,
force_match IN BINARY_INTEGER)
RETURN NUMBER IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE create_stgtab_sqlprof(
table_name            IN VARCHAR2,
schema_name           IN VARCHAR2 := NULL,
tablespace_name       IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE pack_stgtab_sqlprof(
profile_name          IN VARCHAR2 := '%',
profile_category      IN VARCHAR2 := 'DEFAULT',
staging_table_name    IN VARCHAR2,
staging_schema_owner  IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE unpack_stgtab_sqlprof(
profile_name          IN VARCHAR2 := '%',
profile_category      IN VARCHAR2 := '%',
replace               IN BOOLEAN,
staging_table_name    IN VARCHAR2,
staging_schema_owner  IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE remap_stgtab_sqlprof(
old_profile_name      IN VARCHAR2,
new_profile_name      IN VARCHAR2 := NULL,
new_profile_category
IN VARCHAR2 := NULL,
staging_table_name    IN VARCHAR2,
staging_schema_owner  IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION report_sql_monitor(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
sql_exec_start            in date     default  NULL,
sql_exec_id               in number   default  NULL,
inst_id                   in number   default  NULL,
start_time_filter         in date     default  NULL,
end_time_filter           in date     default  NULL,
instance_id_filter        in number   default  NULL,
parallel_filter           in varchar2 default  NULL,
plan_line_filter          in number   default  NULL,
event_detail              in varchar2 default  'yes',
bucket_max_count          in number   default  128,
bucket_interval           in number   default  NULL,
base_path                 in varchar2 default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default 'TYPICAL',
type                      in varchar2 default 'TEXT',
sql_plan_hash_value       in number   default  NULL)
RETURN clob IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION report_sql_monitor_xml(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
sql_exec_start            in date     default  NULL,
sql_exec_id               in number   default  NULL,
inst_id                   in number   default  NULL,
start_time_filter         in date     default  NULL,
end_time_filter           in date     default  NULL,
instance_id_filter        in number   default  NULL,
parallel_filter           in varchar2 default  NULL,
plan_line_filter          in number   default  NULL,
event_detail              in varchar2 default  'yes',
bucket_max_count          in number   default  128,
bucket_interval           in number   default  NULL,
base_path                 in varchar2 default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default 'TYPICAL',
auto_refresh              in number   default  NULL,
sql_plan_hash_value       in number   default  NULL)
return xmltype IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION report_sql_monitor_list(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
inst_id                   in number   default  NULL,
active_since_date         in date     default  NULL,
active_since_sec          in number   default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default  'TYPICAL',
auto_refresh              in number   default  NULL,
base_path                 in varchar2 default  NULL,
type                      in varchar2 default 'TEXT')
RETURN clob IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION report_sql_monitor_list_xml(
sql_id                    in varchar2 default  NULL,
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
inst_id                   in number   default  NULL,
active_since_date         in date     default  NULL,
active_since_sec          in number   default  NULL,
last_refresh_time         in date     default  NULL,
report_level              in varchar2 default  'TYPICAL',
auto_refresh              in number   default  NULL,
base_path                 in varchar2 default  NULL)
RETURN xmltype IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION report_sql_detail(
sql_id                   in  varchar2   default NULL,
sql_plan_hash_value      in  number   
default NULL,
start_time               in  date       default NULL,
duration                 in  number     default NULL,
inst_id                  in  number     default NULL,
dbid                     in  number     default NULL,
event_detail             in  varchar2   default 'yes',
bucket_max_count         in  number     default 128,
bucket_interval          in  number     default NULL,
top_n                    in  number     default 10,
report_level             in  varchar2   default 'typical',
type                     in  varchar2   default 'ACTIVE',
data_source              in  varchar2   default 'auto',
end_time                 in  date       default NULL,
duration_stats           in  number     default NULL)
RETURN clob IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION report_sql_detail_xml(
sql_id                   in  varchar2   default NULL,
sql_plan_hash_value      in  number     default NULL,
start_time               in  date       default NULL,
duration                 in  number     default NULL,
inst_id                  in  number     default NULL,
dbid                     in  number     default NULL,
event_detail             in  varchar2   default 'yes',
bucket_max_count         in  number     default 128,
bucket_interval          in  number     default NULL,
top_n                    in  number     default 10,
report_level             in  varchar2   default 'typical',
data_source              in  varchar2   default 'auto',
end_time                 in  date       default NULL,
duration_stats           in  number     default NULL)
return xmltype IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION extract_bind(
bind_data   IN RAW,
bind_pos    IN PLS_INTEGER) RETURN SQL_BIND IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION extract_binds(
bind_data IN RAW)
RETURN SQL_BIND_SET PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 




PROCEDURE set_auto_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE set_auto_tuning_task_parameter(
parameter IN VARCHAR2,
value     IN NUMBER) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION build_stash_xml(
session_id                in number   default  NULL,
session_serial            in number   default  NULL,
session_inst_id           in number   default  NULL,
px_mode                   in varchar2 default  'yes',
start_time                in date     default  NULL,
end_time                  in date     default  NULL,
missing_seconds           in number   default  NULL,
instance_low_filter       in number   default  0,
instance_high_filter      in number   default  10000,
bucket_max_count          in number   default  128,
bucket_interval           in number   default  NULL,
report_level              in varchar2 default 'TYPICAL',
cpu_cores                 in binary_integer  default  NULL,
is_hyper                  in varchar2        default  NULL)
RETURN xmltype IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE check_sqlset_privs(
sqlset_name   IN VARCHAR2,
sqlset_owner  IN VARCHAR2,
sqlset_create IN BOOLEAN := false,
read_only     IN BOOLEAN := false) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE check_sql_profile_priv(priv IN VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE cap_sts_cbk(
sqlset_name    IN VARCHAR2,
iterations     IN POSITIVE,
cap_option     IN VARCHAR2,
cap_mode       IN NUMBER,
cbk_proc_name  IN VARCHAR2,
basic_filter   IN VARCHAR2 := NULL,
sqlset_owner   IN VARCHAR2 := NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 

END; 


FUNCTION prepare_sqlset_statement(
sqlset_name        IN            VARCHAR2,
sqlset_owner       IN            VARCHAR2,
basic_filter       IN            VARCHAR2 := NULL,
stmt_filter        IN            BOOLEAN  := FALSE,
object_filter      IN            VARCHAR2 := NULL,
plan_filter        IN            VARCHAR2 := NULL,
rank1              IN            VARCHAR2 := NULL,
rank2              IN            VARCHAR2 := NULL,
rank3              IN            VARCHAR2 := NULL,
result_percentage  IN            NUMBER   := 1,
result_limit       IN            NUMBER   := NULL,
attribute_list     IN            VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY BINARY_INTEGER,
wrap_obj_ctor      IN            BOOLEAN := FALSE,
check_binds        IN            BOOLEAN := TRUE,
sts_id             OUT           NUMBER,
first_rows_hint    IN            BOOLEAN  :=  TRUE)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION prepare_awr_statement(
begin_snap         IN             NUMBER,
end_snap           IN             NUMBER,
basic_filter       IN             VARCHAR2 := NULL,
stmt_filter        IN             BOOLEAN  := FALSE,
object_filter      IN             VARCHAR2 := NULL,
rank1              IN             VARCHAR2 := NULL,
rank2              IN             VARCHAR2 := NULL,
rank3              IN             VARCHAR2 := NULL,
result_percentage  IN             NUMBER   := 1,
result_limit       IN             NUMBER   := NULL,
attribute_list     IN             VARCHAR2 := NULL,
attribute_selected IN OUT NOCOPY  BINARY_INTEGER,
flags              IN             NUMBER   := 0)
RETURN VARCHAR2 IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE sqlset_progress_stats(
sqlset_name        IN            VARCHAR2,
sqlset_owner       IN            VARCHAR2,
basic_filter       IN            VARCHAR2 := NULL,
plan_filter        IN            VARCHAR2 := NULL,
rank1              IN            VARCHAR2 := NULL,
rank2              IN            VARCHAR2 := NULL,
rank3              IN            VARCHAR2 := NULL,
result_percentage  IN            NUMBER   := 1,
result_limit       IN            NUMBER   := NULL,
sql_count          OUT           NUMBER,
workload_time      OUT           NUMBER,
exec_type#         IN            PLS_INTEGER) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE examine_stgtab(
stgtab_owner       IN            VARCHAR2,
stgtab             IN            VARCHAR2,
sts_name           OUT           VARCHAR2,
sts_owner          OUT           VARCHAR2) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


END BLOCK_DBMS_SQLTUNE;

/

BLOCK_DBMS_WORKLOAD_REPOSITORY

NOTE – for this to compile, we first need to create the AWRRPT_INSTANCE_LIST_TYPE database type in the DISABLE_PACK_DT schema

--
-- Create this type in the DISABLE_PACK_DT schema to allow compilation of
-- BLOCK_DBMS_WORKLOAD_REPOSITORY package.
--
CREATE TYPE disable_pack_dt.awrrpt_instance_list_type AS TABLE OF NUMBER
/
CREATE OR REPLACE PACKAGE disable_pack_dt.BLOCK_DBMS_WORKLOAD_REPOSITORY AS


MIN_INTERVAL    CONSTANT NUMBER := 10;                       /* 10 minutes */
MAX_INTERVAL    CONSTANT NUMBER := 52560000;                  /* 100 years */

MIN_RETENTION   CONSTANT NUMBER := 1440;                          /* 1 day */
MAX_RETENTION   CONSTANT NUMBER := 52560000;                  /* 100 years */




PROCEDURE create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL'
);

FUNCTION create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL'
)  RETURN NUMBER;


PROCEDURE drop_snapshot_range(low_snap_id      IN NUMBER,
high_snap_id     IN NUMBER,
dbid             IN NUMBER DEFAULT NULL
);



PROCEDURE modify_snapshot_settings(retention  IN NUMBER DEFAULT NULL,
interval   IN NUMBER DEFAULT NULL,
topnsql    IN NUMBER DEFAULT NULL,
dbid       IN NUMBER DEFAULT NULL
);


PROCEDURE modify_snapshot_settings(retention  IN NUMBER   DEFAULT NULL,
interval   IN NUMBER   DEFAULT NULL,
topnsql    IN VARCHAR2,
dbid       IN NUMBER   DEFAULT NULL
);



PROCEDURE add_colored_sql(sql_id         IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL
);



PROCEDURE remove_colored_sql(sql_id         IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL
);



PROCEDURE create_baseline(start_snap_id  IN NUMBER,
end_snap_id    IN NUMBER,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
);

FUNCTION create_baseline(start_snap_id  IN NUMBER,
end_snap_id    IN NUMBER,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
)  RETURN NUMBER;

PROCEDURE create_baseline(start_time     IN DATE,
end_time       IN DATE,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
);

FUNCTION create_baseline(start_time     IN DATE,
end_time       IN DATE,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
)  RETURN NUMBER;

FUNCTION select_baseline_details(l_baseline_id   IN NUMBER,
l_beg_snap      IN NUMBER DEFAULT NULL,
l_end_snap      IN NUMBER DEFAULT NULL,
l_dbid          IN NUMBER DEFAULT NULL)
RETURN awrbl_details_type_table PIPELINED;

FUNCTION select_baseline_metric(l_baseline_name  IN VARCHAR2,
l_dbid           IN NUMBER DEFAULT NULL,
l_instance_num   IN NUMBER DEFAULT NULL)
RETURN awrbl_metric_type_table PIPELINED;

PROCEDURE rename_baseline(old_baseline_name IN VARCHAR2,
new_baseline_name IN VARCHAR2,
dbid              IN NUMBER DEFAULT NULL
);

PROCEDURE modify_baseline_window_size(window_size IN NUMBER,
dbid        IN NUMBER DEFAULT NULL
);

PROCEDURE drop_baseline(baseline_name IN VARCHAR2,
cascade       IN BOOLEAN DEFAULT false,
dbid          IN NUMBER DEFAULT NULL
);


PROCEDURE create_baseline_template(start_time     IN DATE,
end_time       IN DATE,
baseline_name  IN VARCHAR2,
template_name  IN VARCHAR2,
expiration     IN NUMBER DEFAULT NULL,
dbid           IN NUMBER DEFAULT NULL
);

PROCEDURE create_baseline_template(day_of_week          IN VARCHAR2,
hour_in_day          IN NUMBER,
duration             IN NUMBER,
start_time           IN DATE,
end_time             IN DATE,
baseline_name_prefix IN VARCHAR2,
template_name        IN VARCHAR2,
expiration           IN NUMBER DEFAULT 35,
dbid                 IN NUMBER
DEFAULT NULL
);

PROCEDURE drop_baseline_template(template_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL
);


FUNCTION awr_report_text(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED;

FUNCTION awr_report_html(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;

FUNCTION awr_global_report_text(l_dbid     IN NUMBER,
l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER       
DEFAULT 0)
RETURN awrdrpt_text_type_table PIPELINED;
FUNCTION awr_global_report_text(l_dbid     IN NUMBER,
l_inst_num IN VARCHAR2,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrdrpt_text_type_table PIPELINED;

FUNCTION awr_global_report_html(l_dbid     IN NUMBER,
l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;
FUNCTION awr_global_report_html(l_dbid     IN NUMBER,
l_inst_num IN VARCHAR2,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;

FUNCTION awr_sql_report_text(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_sqlid    IN VARCHAR2,
l_options  IN NUMBER DEFAULT 0)
RETURN awrsqrpt_text_type_table PIPELINED;

FUNCTION awr_sql_report_html(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_sqlid    IN VARCHAR2,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED;


FUNCTION awr_diff_report_text(dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED;

FUNCTION awr_diff_report_html(dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;

FUNCTION awr_global_diff_report_text(dbid1     IN NUMBER,
inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED;
FUNCTION awr_global_diff_report_text(dbid1     IN NUMBER,
inst_num1 IN VARCHAR2,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN VARCHAR2,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED;

FUNCTION awr_global_diff_report_html(dbid1     IN NUMBER,
inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;
FUNCTION awr_global_diff_report_html(dbid1     IN NUMBER,
inst_num1 IN VARCHAR2,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN VARCHAR2,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED;


FUNCTION ash_report_text(l_dbid          IN NUMBER,
l_inst_num      IN NUMBER,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrrpt_text_type_table PIPELINED;

FUNCTION ash_report_html(l_dbid          IN NUMBER,
l_inst_num      IN NUMBER,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrrpt_html_type_table PIPELINED;
FUNCTION ash_global_report_text(l_dbid          IN NUMBER,
l_inst_num      IN       
VARCHAR2,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrdrpt_text_type_table PIPELINED;
FUNCTION ash_global_report_html(l_dbid          IN NUMBER,
l_inst_num      IN VARCHAR2,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrrpt_html_type_table PIPELINED;
PROCEDURE control_restricted_snapshot(allow IN BOOLEAN);

PROCEDURE awr_set_report_thresholds(top_n_events      IN NUMBER DEFAULT NULL,
top_n_files       IN NUMBER DEFAULT NULL,
top_n_segments    IN NUMBER DEFAULT NULL,
top_n_services    IN NUMBER DEFAULT NULL,
top_n_sql         IN NUMBER DEFAULT NULL,
top_n_sql_max     IN NUMBER DEFAULT NULL,
top_sql_pct       IN NUMBER DEFAULT NULL,
shmem_threshold   IN NUMBER DEFAULT NULL,
versions_threshold IN NUMBER DEFAULT NULL
);

PROCEDURE purge_sql_details(numrows IN NUMBER DEFAULT NULL,
dbid    IN NUMBER DEFAULT NULL);


PROCEDURE update_object_info(maxrows   IN  NUMBER  DEFAULT 0);

END BLOCK_DBMS_WORKLOAD_REPOSITORY;
/
CREATE OR REPLACE PACKAGE BODY disable_pack_dt.BLOCK_DBMS_WORKLOAD_REPOSITORY AS



MIN_INTERVAL    CONSTANT NUMBER := 10;                       /* 10 minutes */
MAX_INTERVAL    CONSTANT NUMBER := 52560000;                  /* 100 years */

MIN_RETENTION   CONSTANT NUMBER := 1440;                          /* 1 day */
MAX_RETENTION   CONSTANT NUMBER := 52560000;                  /* 100 years */




PROCEDURE create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL'
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_snapshot(flush_level IN VARCHAR2 DEFAULT 'TYPICAL'
)  RETURN NUMBER IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE drop_snapshot_range(low_snap_id      IN NUMBER,
high_snap_id     IN NUMBER,
dbid             IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 




PROCEDURE modify_snapshot_settings(retention  IN NUMBER DEFAULT NULL,
interval   IN NUMBER DEFAULT NULL,
topnsql    IN NUMBER DEFAULT NULL,
dbid       IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE modify_snapshot_settings(retention  IN NUMBER   DEFAULT NULL,
interval   IN NUMBER   DEFAULT NULL,
topnsql    IN VARCHAR2,
dbid       IN NUMBER   DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 




PROCEDURE add_colored_sql(sql_id         IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 




PROCEDURE remove_colored_sql(sql_id         IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 




PROCEDURE create_baseline(start_snap_id  IN NUMBER,
end_snap_id    IN NUMBER,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_baseline(start_snap_id  IN NUMBER,
end_snap_id    IN NUMBER,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
)  RETURN NUMBER IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE create_baseline(start_time     IN DATE,
end_time       IN DATE,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION create_baseline(start_time     IN DATE,
end_time       IN DATE,
baseline_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL,
expiration     IN NUMBER DEFAULT NULL
)  RETURN NUMBER IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_baseline_details(l_baseline_id   IN NUMBER,
l_beg_snap      IN NUMBER DEFAULT NULL,
l_end_snap      IN NUMBER DEFAULT NULL,
l_dbid          IN NUMBER DEFAULT NULL)
RETURN awrbl_details_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION select_baseline_metric(l_baseline_name  IN VARCHAR2,
l_dbid           IN NUMBER DEFAULT NULL,
l_instance_num   IN NUMBER DEFAULT NULL)
RETURN awrbl_metric_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE rename_baseline(old_baseline_name IN VARCHAR2,
new_baseline_name IN VARCHAR2,
dbid              IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE modify_baseline_window_size(window_size IN NUMBER,
dbid        IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE                   
drop_baseline(baseline_name IN VARCHAR2,
cascade       IN BOOLEAN DEFAULT false,
dbid          IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE create_baseline_template(start_time     IN DATE,
end_time       IN DATE,
baseline_name  IN VARCHAR2,
template_name  IN VARCHAR2,
expiration     IN NUMBER DEFAULT NULL,
dbid           IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE create_baseline_template(day_of_week          IN VARCHAR2,
hour_in_day          IN NUMBER,
duration             IN NUMBER,
start_time           IN DATE,
end_time             IN DATE,
baseline_name_prefix IN VARCHAR2,
template_name        IN VARCHAR2,
expiration           IN NUMBER DEFAULT 35,
dbid                 IN NUMBER
DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE drop_baseline_template(template_name  IN VARCHAR2,
dbid           IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION awr_report_text(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_report_html(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_global_report_text(l_dbid     IN NUMBER,
l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrdrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION awr_global_report_text(l_dbid     IN NUMBER,
l_inst_num IN VARCHAR2,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrdrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_global_report_html(l_dbid     IN NUMBER,
l_inst_num IN AWRRPT_INSTANCE_LIST_TYPE,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION awr_global_report_html(l_dbid     IN NUMBER,
l_inst_num IN VARCHAR2,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_sql_report_text(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_sqlid    IN VARCHAR2,
l_options  IN NUMBER DEFAULT 0)
RETURN awrsqrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_sql_report_html(l_dbid     IN NUMBER,
l_inst_num IN NUMBER,
l_bid      IN NUMBER,
l_eid      IN NUMBER,
l_sqlid    IN VARCHAR2,
l_options  IN NUMBER DEFAULT 0)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION awr_diff_report_text(dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_diff_report_html(dbid1     IN NUMBER,
inst_num1 IN NUMBER,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN NUMBER,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN          
awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_global_diff_report_text(dbid1     IN NUMBER,
inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION awr_global_diff_report_text(dbid1     IN NUMBER,
inst_num1 IN VARCHAR2,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN VARCHAR2,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrdrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION awr_global_diff_report_html(dbid1     IN NUMBER,
inst_num1 IN AWRRPT_INSTANCE_LIST_TYPE,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN AWRRPT_INSTANCE_LIST_TYPE,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION awr_global_diff_report_html(dbid1     IN NUMBER,
inst_num1 IN VARCHAR2,
bid1      IN NUMBER,
eid1      IN NUMBER,
dbid2     IN NUMBER,
inst_num2 IN VARCHAR2,
bid2      IN NUMBER,
eid2      IN NUMBER)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



FUNCTION ash_report_text(l_dbid          IN NUMBER,
l_inst_num      IN NUMBER,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


FUNCTION ash_report_html(l_dbid          IN NUMBER,
l_inst_num      IN NUMBER,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION ash_global_report_text(l_dbid          IN NUMBER,
l_inst_num      IN VARCHAR2,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2  DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrdrpt_text_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION ash_global_report_html(l_dbid          IN NUMBER,
l_inst_num      IN VARCHAR2,
l_btime         IN DATE,
l_etime         IN DATE,
l_options       IN NUMBER    DEFAULT 0,
l_slot_width    IN NUMBER    DEFAULT 0,
l_sid           IN NUMBER    DEFAULT NULL,
l_sql_id        IN VARCHAR2        
DEFAULT NULL,
l_wait_class    IN VARCHAR2  DEFAULT NULL,
l_service_hash  IN NUMBER    DEFAULT NULL,
l_module        IN VARCHAR2  DEFAULT NULL,
l_action        IN VARCHAR2  DEFAULT NULL,
l_client_id     IN VARCHAR2  DEFAULT NULL,
l_plsql_entry   IN VARCHAR2  DEFAULT NULL,
l_data_src      IN NUMBER    DEFAULT 0
)
RETURN awrrpt_html_type_table PIPELINED IS 
l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

PROCEDURE control_restricted_snapshot(allow IN BOOLEAN) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE awr_set_report_thresholds(top_n_events      IN NUMBER DEFAULT NULL,
top_n_files       IN NUMBER DEFAULT NULL,
top_n_segments    IN NUMBER DEFAULT NULL,
top_n_services    IN NUMBER DEFAULT NULL,
top_n_sql         IN NUMBER DEFAULT NULL,
top_n_sql_max     IN NUMBER DEFAULT NULL,
top_sql_pct       IN NUMBER DEFAULT NULL,
shmem_threshold   IN NUMBER DEFAULT NULL,
versions_threshold IN NUMBER DEFAULT NULL
) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


PROCEDURE purge_sql_details(numrows IN NUMBER DEFAULT NULL,
dbid    IN NUMBER DEFAULT NULL) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 



PROCEDURE update_object_info(maxrows   IN  NUMBER  DEFAULT 0) IS 
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 


END BLOCK_DBMS_WORKLOAD_REPOSITORY;

/

Partial API member packages

This leaves us with two packages to deal with, DBMS_ADVISOR and DBMS_WORKLOAD_REPLAY.

As we don’t necessarily want to block all access to these packages, we need to pass on any legitimate calls to their functions or procedures, whilst stopping anything that would require a license.

DBMS_WORKLOAD_REPLAY is by far the more straightforward case. Only a call to the COMPARE_PERIOD_REPORT function of this package will require a DIAGNOSTIC pack license.

DBMS_WORKLOAD_REPLAY

CREATE OR REPLACE PACKAGE disable_pack_dt.BLOCK_DBMS_WORKLOAD_REPLAY AS

PROCEDURE PROCESS_CAPTURE( capture_dir        IN VARCHAR2,
parallel_level     IN NUMBER DEFAULT NULL);

FUNCTION PROCESS_CAPTURE_COMPLETION
RETURN NUMBER;

FUNCTION PROCESS_CAPTURE_REMAINING_TIME
RETURN NUMBER;

PROCEDURE INITIALIZE_REPLAY( replay_name     IN VARCHAR2,
replay_dir      IN VARCHAR2 );

PROCEDURE SET_ADVANCED_PARAMETER( pname  IN VARCHAR2,
pvalue IN VARCHAR2);
PROCEDURE SET_ADVANCED_PARAMETER( pname  IN VARCHAR2,
pvalue IN NUMBER);
PROCEDURE SET_ADVANCED_PARAMETER( pname  IN VARCHAR2,
pvalue IN BOOLEAN);

FUNCTION GET_ADVANCED_PARAMETER( pname IN VARCHAR2)
RETURN VARCHAR2;

PROCEDURE RESET_ADVANCED_PARAMETERS;

PROCEDURE SET_REPLAY_TIMEOUT(enabled       IN  BOOLEAN DEFAULT TRUE,
min_delay     IN  NUMBER  DEFAULT 10,
max_delay     IN  NUMBER  DEFAULT 120,
delay_factor  IN  NUMBER  DEFAULT 8);

PROCEDURE GET_REPLAY_TIMEOUT(enabled       OUT  BOOLEAN,
min_delay     OUT  NUMBER,
max_delay     OUT  NUMBER,
delay_factor  OUT  NUMBER);

PROCEDURE  PREPARE_REPLAY(synchronization         IN BOOLEAN,
connect_time_scale      IN NUMBER   DEFAULT 100,
think_time_scale        IN NUMBER   DEFAULT 100,
think_time_auto_correct IN BOOLEAN  DEFAULT TRUE,
scale_up_multiplier     IN NUMBER   DEFAULT 1,
capture_sts             IN BOOLEAN  DEFAULT FALSE,
sts_cap_interval        IN NUMBER   DEFAULT 300);

PROCEDURE  PREPARE_REPLAY(synchronization         IN VARCHAR2 DEFAULT 'SCN',
connect_time_scale      IN NUMBER   DEFAULT 100,
think_time_scale        IN NUMBER   DEFAULT 100,
think_time_auto_correct IN BOOLEAN  DEFAULT TRUE,
scale_up_multiplier     IN NUMBER   DEFAULT 1,
capture_sts             IN BOOLEAN  DEFAULT FALSE,
sts_cap_interval        IN NUMBER   DEFAULT 300);

PROCEDURE  START_REPLAY;

PROCEDURE  PAUSE_REPLAY;

PROCEDURE  RESUME_REPLAY;

FUNCTION IS_REPLAY_PAUSED
RETURN BOOLEAN;

PROCEDURE  CANCEL_REPLAY(reason    IN VARCHAR2 DEFAULT NULL);

FUNCTION GET_REPLAY_INFO(dir    IN VARCHAR2)
RETURN NUMBER;

PROCEDURE DELETE_REPLAY_INFO(replay_id    IN NUMBER);

PROCEDURE REMAP_CONNECTION(connection_id         IN  NUMBER,
replay_connection     IN  VARCHAR2);


TYPE_XML            CONSTANT   VARCHAR2(3) := 'XML'        ;
TYPE_HTML           CONSTANT   VARCHAR2(4) := 'HTML'       ;
TYPE_TEXT           CONSTANT   VARCHAR2(4) := 'TEXT'       ;

FUNCTION  REPORT( replay_id        IN NUMBER,
format           IN VARCHAR2 )
RETURN    CLOB;

PROCEDURE COMPARE_PERIOD_REPORT( replay_id1 IN NUMBER,
replay_id2 IN NUMBER,
format     IN VARCHAR2,
result     OUT CLOB );

FUNCTION COMPARE_SQLSET_REPORT( replay_id1    IN NUMBER,
replay_id2    IN NUMBER,
format        IN VARCHAR2,
r_level       IN VARCHAR2 := 'ALL',
r_sections    IN VARCHAR2 := 'ALL',
result        OUT CLOB )
RETURN VARCHAR2;


PROCEDURE EXPORT_AWR( replay_id             IN NUMBER );
PROCEDURE EXPORT_PERFORMANCE_DATA( replay_id IN NUMBER);

FUNCTION IMPORT_AWR( replay_id       IN NUMBER,
staging_schema  IN VARCHAR2,
force_cleanup   IN BOOLEAN DEFAULT FALSE )
RETURN NUMBER;
FUNCTION IMPORT_PERFORMANCE_DATA(
replay_id       IN NUMBER,
staging_schema  IN VARCHAR2,
force_cleanup   IN BOOLEAN DEFAULT FALSE )
RETURN NUMBER;

FUNCTION CALIBRATE (capture_dir          IN VARCHAR2,
process_per_cpu      IN BINARY_INTEGER DEFAULT 4,
threads_per_process  IN BINARY_INTEGER DEFAULT 50)
RETURN CLOB;

FUNCTION GET_CAPTURED_TABLES(capture_dir IN VARCHAR2)
RETURN CLOB;


FUNCTION GET_DIVERGING_STATEMENT(replay_id    IN NUMBER,
stream_id    IN NUMBER,
call_counter IN NUMBER)
RETURN CLOB;

PROCEDURE POPULATE_DIVERGENCE(replay_id    IN NUMBER,
stream_id    IN NUMBER  DEFAULT NULL,
call_counter IN NUMBER  DEFAULT NULL);

FUNCTION POPULATE_DIVERGENCE_STATUS(replay_id    IN NUMBER)
RETURN VARCHAR2;

FUNCTION DIVERGING_STATEMENT_STATUS(replay_id    IN NUMBER,
stream_id    IN NUMBER,
call_counter IN NUMBER)
RETURN VARCHAR2;

PROCEDURE ADD_FILTER( fname          IN VARCHAR2,
fattribute     IN VARCHAR2,
fvalue         IN VARCHAR2);
PROCEDURE ADD_FILTER( fname          IN VARCHAR2,
fattribute     IN VARCHAR2,
fvalue         IN NUMBER);

PROCEDURE DELETE_FILTER( fname       IN VARCHAR2);

PROCEDURE REUSE_REPLAY_FILTER_SET(replay_dir  IN VARCHAR2,
filter_set  IN VARCHAR2);


PROCEDURE CREATE_FILTER_SET(replay_dir     IN VARCHAR2,
filter_set     IN VARCHAR2,
default_action IN VARCHAR2 DEFAULT 'INCLUDE');

PROCEDURE USE_FILTER_SET(filter_set     IN VARCHAR2);

KECP_CLIENT_CONNECT_LOGIN      CONSTANT   NUMBER   := 1;
KECP_CLIENT_CONNECT_ADMIN      CONSTANT   NUMBER   := 2;
KECP_CLIENT_CONNECT_GOODBYE    CONSTANT   NUMBER   := 3;
KECP_CLIENT_CONNECT_THRDFAIL   CONSTANT   NUMBER   := 4;
KECP_CLIENT_CONNECT_CHKPPID    CONSTANT   NUMBER   := 5;
KECP_CLIENT_CONNECT_CLOCK_TICK CONSTANT   NUMBER   := 6;
KECP_CLIENT_CONNECT_CHK_VSN    CONSTANT   NUMBER   := 7;

KECP_CMD_END_OF_REPLAY         CONSTANT   NUMBER   := 1;
KECP_CMD_REPLAY_CANCELLED      CONSTANT   NUMBER   := 2;

FUNCTION CLIENT_CONNECT(who         IN NUMBER,
arg         IN NUMBER DEFAULT 0)
RETURN   NUMBER;

PROCEDURE CLIENT_VITALS(id          IN BINARY_INTEGER,
name        IN VARCHAR2,
value       IN NUMBER);

FUNCTION PROCESS_REPLAY_GRAPH
RETURN NUMBER;

FUNCTION SYNCPOINT_WAIT_TO_POST(wait_point IN NUMBER)
RETURN NUMBER;

TYPE uc_graph_record IS RECORD(time NUMBER, user_calls NUMBER, flags NUMBER);
TYPE uc_graph_table  IS TABLE OF uc_graph_record;

PROCEDURE export_uc_graph(replay_id NUMBER);
PROCEDURE import_uc_graph(replay_id NUMBER);
FUNCTION user_calls_graph(replay_id IN NUMBER)
RETURN uc_graph_table PIPELINED;
FUNCTION stop_sts_c(sts_name  IN VARCHAR2,
sts_owner IN VARCHAR2,
in_db_caprep OUT BOOLEAN)
RETURN BOOLEAN;



FUNCTION get_processing_path(capture_id IN NUMBER)
RETURN VARCHAR2;


FUNCTION get_replay_path(replay_id IN NUMBER)
RETURN VARCHAR2;

PROCEDURE initialize_replay_internal( replay_name    IN  VARCHAR2,
replay_dir     IN  VARCHAR2,
replay_type    IN  VARCHAR2);

PROCEDURE get_perf_data_export_status( replay_id      IN  NUMBER,
awr_data      OUT  VARCHAR2,
sts_data      OUT  VARCHAR2);

PROCEDURE set_attribute(capture_id IN NUMBER,
replay_id  IN NUMBER,
name       IN VARCHAR2, -- VARCHAR2(50)
value      IN VARCHAR2); -- VARCHAR2(200)

FUNCTION  get_attribute(capture_id IN NUMBER,
replay_id  IN NUMBER,
name       IN VARCHAR2)
RETURN VARCHAR2;

PROCEDURE delete_attribute(capture_id IN NUMBER,
replay_id  IN NUMBER,
name       IN VARCHAR2);

PROCEDURE persist_attributes(capture_id IN NUMBER);

PROCEDURE sync_attributes_from_file(capture_id IN NUMBER);

END BLOCK_DBMS_WORKLOAD_REPLAY;
/
CREATE OR REPLACE PACKAGE BODY disable_pack_dt.BLOCK_DBMS_WORKLOAD_REPLAY AS
-------------------------------------------------------------------------------
-- Only the COMPARE_PERIOD_REPORT function is part of the DIAGNOSTIC pack.
-- For this pack, refer to the erroring function.
-- For everything else, just call the underlying procedure or function.
--
-------------------------------------------------------------------------------
PROCEDURE PROCESS_CAPTURE( 
    capture_dir IN VARCHAR2,
    parallel_level IN NUMBER DEFAULT NULL)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE(
        capture_dir => capture_dir,
        parallel_level => parallel_level);
END;

FUNCTION PROCESS_CAPTURE_COMPLETION
    RETURN NUMBER
IS
BEGIN 
    RETURN sys.DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE_COMPLETION;
END;

FUNCTION PROCESS_CAPTURE_REMAINING_TIME
    RETURN NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE_REMAINING_TIME;
END;

PROCEDURE INITIALIZE_REPLAY( 
    replay_name IN VARCHAR2,
    replay_dir IN VARCHAR2 )
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY(
        replay_name => replay_name,
        replay_dir => replay_dir);
END;

PROCEDURE SET_ADVANCED_PARAMETER( 
    pname  IN VARCHAR2,
    pvalue IN VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.SET_ADVANCED_PARAMETER(
        pname => pname,
        pvalue => pvalue);
END;

PROCEDURE SET_ADVANCED_PARAMETER( 
    pname  IN VARCHAR2,
    pvalue IN NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.SET_ADVANCED_PARAMETER(
        pname => pname,
        pvalue => pvalue);
END;

PROCEDURE SET_ADVANCED_PARAMETER( 
    pname IN VARCHAR2,
    pvalue IN BOOLEAN)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.SET_ADVANCED_PARAMETER(
        pname => pname,
        pvalue => pvalue);
END;


FUNCTION GET_ADVANCED_PARAMETER( pname IN VARCHAR2)
    RETURN VARCHAR2
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_ADVANCED_PARAMETER( pname => pname);
END;

PROCEDURE RESET_ADVANCED_PARAMETERS IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.RESET_ADVANCED_PARAMETERS;
END;

PROCEDURE SET_REPLAY_TIMEOUT(
    enabled IN BOOLEAN DEFAULT TRUE,
    min_delay     IN  NUMBER  DEFAULT 10,
    max_delay     IN  NUMBER  DEFAULT 120,
    delay_factor  IN  NUMBER  DEFAULT 8)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.SET_REPLAY_TIMEOUT(
        enabled => enabled,
        min_delay => min_delay,
        max_delay => max_delay,
        delay_factor => delay_factor);
END;

PROCEDURE GET_REPLAY_TIMEOUT(
    enabled       OUT  BOOLEAN,
    min_delay     OUT  NUMBER,
    max_delay     OUT  NUMBER,
    delay_factor  OUT  NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.GET_REPLAY_TIMEOUT(
        enabled => enabled,
        min_delay => min_delay,
        max_delay => max_delay,
        delay_factor => delay_factor);
END;


PROCEDURE  PREPARE_REPLAY(
    synchronization         IN BOOLEAN,
    connect_time_scale      IN NUMBER   DEFAULT 100,
    think_time_scale        IN NUMBER   DEFAULT 100,
    think_time_auto_correct IN BOOLEAN  DEFAULT TRUE,
    scale_up_multiplier     IN NUMBER   DEFAULT 1,
    capture_sts             IN BOOLEAN  DEFAULT FALSE,
    sts_cap_interval        IN NUMBER   DEFAULT 300)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
        synchronization => synchronization,
        connect_time_scale => connect_time_scale,
        think_time_scale => think_time_scale,
        think_time_auto_correct => think_time_auto_correct,
        scale_up_multiplier => scale_up_multiplier,
        capture_sts => capture_sts,
        sts_cap_interval => sts_cap_interval);
END;

PROCEDURE  PREPARE_REPLAY(
    synchronization         IN VARCHAR2 DEFAULT 'SCN',
    connect_time_scale      IN NUMBER   DEFAULT 100,
    think_time_scale        IN NUMBER   DEFAULT 100,
    think_time_auto_correct IN BOOLEAN  DEFAULT TRUE,
    scale_up_multiplier     IN NUMBER   DEFAULT 1,
    capture_sts             IN BOOLEAN  DEFAULT FALSE,
    sts_cap_interval        IN NUMBER   DEFAULT 300)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(
        synchronization => synchronization,
        connect_time_scale => connect_time_scale,
        think_time_scale => think_time_scale,
        think_time_auto_correct => think_time_auto_correct,
        scale_up_multiplier => scale_up_multiplier,
        capture_sts => capture_sts,
        sts_cap_interval => sts_cap_interval);
END;

PROCEDURE  START_REPLAY IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.START_REPLAY;
END;

PROCEDURE  PAUSE_REPLAY IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.PAUSE_REPLAY;
END;

PROCEDURE  RESUME_REPLAY IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.RESUME_REPLAY;
END;

FUNCTION IS_REPLAY_PAUSED
    RETURN BOOLEAN
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.IS_REPLAY_PAUSED;
END;

PROCEDURE  CANCEL_REPLAY(
    reason    IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.CANCEL_REPLAY( reason => reason);
END;


FUNCTION GET_REPLAY_INFO(dir    IN VARCHAR2)
    RETURN NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_REPLAY_INFO(dir => dir);
END;

PROCEDURE DELETE_REPLAY_INFO(
    replay_id    IN NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.DELETE_REPLAY_INFO( replay_id => replay_id);
END;

PROCEDURE REMAP_CONNECTION(
    connection_id         IN  NUMBER,
    replay_connection     IN  VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
        connection_id => connection_id,
        replay_connection => replay_connection);
END;


FUNCTION  REPORT( 
    replay_id        IN NUMBER,
    format           IN VARCHAR2 )
    RETURN    CLOB
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.REPORT(
        replay_id => replay_id,
        format => format);
END;

PROCEDURE COMPARE_PERIOD_REPORT( 
    replay_id1 IN NUMBER,
    replay_id2 IN NUMBER,
    format     IN VARCHAR2,
    result     OUT CLOB )
-------------------------------------------------------------------------------
-- This procedure is the one that we need to block. Instead of passing the
-- call through to the underlying package, call the 
-- DISABLE_PACK_DT.GET_ERR_MSG_FN to force an error.
--
-------------------------------------------------------------------------------
IS
    l_msg VARCHAR2(4000);
BEGIN 
    l_msg := disable_pack_dt.get_err_msg_fn; 
END; 

FUNCTION COMPARE_SQLSET_REPORT( 
    replay_id1    IN NUMBER,
    replay_id2    IN NUMBER,
    format        IN VARCHAR2,
    r_level       IN VARCHAR2 := 'ALL',
    r_sections    IN VARCHAR2 := 'ALL',
    result        OUT CLOB )
    RETURN VARCHAR2
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.COMPARE_SQLSET_REPORT(
        replay_id1 => replay_id1,
        replay_id2 => replay_id2,
        format => format,
        r_level => r_level,
        r_sections => r_sections,
        result => result);
END;

PROCEDURE EXPORT_AWR( replay_id             IN NUMBER ) IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.EXPORT_AWR( replay_id => replay_id);
END;

PROCEDURE EXPORT_PERFORMANCE_DATA( replay_id IN NUMBER) IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.EXPORT_PERFORMANCE_DATA( replay_id => replay_id);
END;

FUNCTION IMPORT_AWR( 
    replay_id       IN NUMBER,
    staging_schema  IN VARCHAR2,
    force_cleanup   IN BOOLEAN DEFAULT FALSE )
    RETURN NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.IMPORT_AWR(
        replay_id => replay_id,
        staging_schema => staging_schema,
        force_cleanup => force_cleanup);
END;


FUNCTION IMPORT_PERFORMANCE_DATA(
    replay_id       IN NUMBER,
    staging_schema  IN VARCHAR2,
    force_cleanup   IN BOOLEAN DEFAULT FALSE )
    RETURN NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.IMPORT_PERFORMANCE_DATA(
        replay_id => replay_id,
        staging_schema => staging_schema,
        force_cleanup => force_cleanup);
END;

FUNCTION CALIBRATE (
    capture_dir          IN VARCHAR2,
    process_per_cpu      IN BINARY_INTEGER DEFAULT 4,
    threads_per_process  IN BINARY_INTEGER DEFAULT 50)
    RETURN CLOB 
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.CALIBRATE(
        capture_dir => capture_dir,
        process_per_cpu => process_per_cpu,
        threads_per_process => threads_per_process);
END;

FUNCTION GET_CAPTURED_TABLES(capture_dir IN VARCHAR2)
    RETURN CLOB
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_CAPTURED_TABLES(
        capture_dir => capture_dir);
END;


FUNCTION GET_DIVERGING_STATEMENT(
    replay_id    IN NUMBER,
    stream_id    IN NUMBER,
    call_counter IN NUMBER)
    RETURN CLOB
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_DIVERGING_STATEMENT(
        replay_id => replay_id,
        stream_id => stream_id,
        call_counter => call_counter);
END;

PROCEDURE POPULATE_DIVERGENCE(
    replay_id    IN NUMBER,
    stream_id    IN NUMBER  DEFAULT NULL,
    call_counter IN NUMBER  DEFAULT NULL)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.POPULATE_DIVERGENCE(
        replay_id => replay_id,
        stream_id => stream_id,
        call_counter => call_counter);
END;

FUNCTION POPULATE_DIVERGENCE_STATUS(replay_id    IN NUMBER)
    RETURN VARCHAR2
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.POPULATE_DIVERGENCE_STATUS( replay_id => replay_id);
END;

FUNCTION DIVERGING_STATEMENT_STATUS(
    replay_id    IN NUMBER,
    stream_id    IN NUMBER,
    call_counter IN NUMBER)
    RETURN VARCHAR2 
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.DIVERGING_STATEMENT_STATUS(
        replay_id => replay_id,
        stream_id => stream_id,
        call_counter => call_counter);
END;

PROCEDURE ADD_FILTER( 
    fname          IN VARCHAR2,
    fattribute     IN VARCHAR2,
    fvalue         IN VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.ADD_FILTER(
        fname => fname,
        fattribute => fattribute,
        fvalue => fvalue);
END;

PROCEDURE ADD_FILTER( 
    fname          IN VARCHAR2,
    fattribute     IN VARCHAR2,
    fvalue         IN NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.ADD_FILTER(
        fname => fname,
        fattribute => fattribute,
        fvalue => fvalue);
END;        

PROCEDURE DELETE_FILTER( fname IN VARCHAR2) IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.DELETE_FILTER( fname => fname);
END;

PROCEDURE REUSE_REPLAY_FILTER_SET(
    replay_dir  IN VARCHAR2,
    filter_set  IN VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.REUSE_REPLAY_FILTER_SET(
        replay_dir => replay_dir,
        filter_set => filter_set);
END;

PROCEDURE CREATE_FILTER_SET(
    replay_dir     IN VARCHAR2,
    filter_set     IN VARCHAR2,
    default_action IN VARCHAR2 DEFAULT 'INCLUDE')
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.CREATE_FILTER_SET(
        replay_dir => replay_dir,
        filter_set => filter_set,
        default_action => default_action);
END;

PROCEDURE USE_FILTER_SET(filter_set     IN VARCHAR2) IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.USE_FILTER_SET( filter_set => filter_set);
END;

FUNCTION CLIENT_CONNECT(
    who         IN NUMBER,
    arg         IN NUMBER DEFAULT 0)
    RETURN   NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.CLIENT_CONNECT(
        who => who,
        arg => arg);
END;

PROCEDURE CLIENT_VITALS(
    id          IN BINARY_INTEGER,
    name        IN VARCHAR2,
    value       IN NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.CLIENT_VITALS(
        id => id,
        name => name,
        value => value);
END;

FUNCTION PROCESS_REPLAY_GRAPH
    RETURN NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.PROCESS_REPLAY_GRAPH;
END;

FUNCTION SYNCPOINT_WAIT_TO_POST(wait_point IN NUMBER)
    RETURN NUMBER
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.SYNCPOINT_WAIT_TO_POST( wait_point => wait_point);
END;

PROCEDURE export_uc_graph(replay_id NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.EXPORT_UC_GRAPH( replay_id => replay_id);
END;

PROCEDURE import_uc_graph(replay_id NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.EXPORT_UC_GRAPH( replay_id => replay_id);
END;

FUNCTION user_calls_graph(replay_id IN NUMBER)
RETURN uc_graph_table PIPELINED
IS
    --
    -- This is a bit tricky - can't just pass through the call to a pipelined
    -- function as we'll get PLS-00653 - aggregate/table functions are not allowed
    -- in a PL/SQL scope. So...
    --
    CURSOR c_function IS
        SELECT time, user_calls, flags
        FROM TABLE( sys.DBMS_WORKLOAD_REPLAY.USER_CALLS_GRAPH( replay_id));
    l_row c_function%ROWTYPE;
BEGIN
    LOOP
        FETCH c_function INTO l_row.time, l_row.user_calls, l_row.flags;
        EXIT WHEN c_function%NOTFOUND;
        PIPE ROW( l_row);
    END LOOP;
    CLOSE c_function;
    RETURN;
END;

FUNCTION stop_sts_c(
    sts_name  IN VARCHAR2,
    sts_owner IN VARCHAR2,
    in_db_caprep OUT BOOLEAN)
    RETURN BOOLEAN
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.STOP_STS_C(
        sts_name => sts_name,
        sts_owner => sts_owner,
        in_db_caprep => in_db_caprep);
END;


FUNCTION get_processing_path(capture_id IN NUMBER)
    RETURN VARCHAR2
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_PROCESSING_PATH( capture_id => capture_id);
END;


FUNCTION get_replay_path(replay_id IN NUMBER)
    RETURN VARCHAR2
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_REPLAY_PATH( replay_id => replay_id);
END;

PROCEDURE initialize_replay_internal( 
    replay_name    IN  VARCHAR2,
    replay_dir     IN  VARCHAR2,
    replay_type    IN  VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY_INTERNAL(
        replay_name => replay_name,
        replay_dir => replay_dir,
        replay_type => replay_type);
END;

PROCEDURE get_perf_data_export_status( 
    replay_id      IN  NUMBER,
    awr_data      OUT  VARCHAR2,
    sts_data      OUT  VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.GET_PERF_DATA_EXPORT_STATUS(
        replay_id => replay_id,
        awr_data => awr_data,
        sts_data => sts_data);
END;

PROCEDURE set_attribute(
    capture_id IN NUMBER,
    replay_id  IN NUMBER,
    name       IN VARCHAR2, -- VARCHAR2(50)
    value      IN VARCHAR2) -- VARCHAR2(200)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.SET_ATTRIBUTE(
        capture_id => capture_id,
        replay_id => replay_id,
        name => name,
        value => value);
END;

FUNCTION  get_attribute(
    capture_id IN NUMBER,
    replay_id  IN NUMBER,
    name       IN VARCHAR2)
    RETURN VARCHAR2
IS
BEGIN
    RETURN sys.DBMS_WORKLOAD_REPLAY.GET_ATTRIBUTE(
        capture_id => capture_id,
        replay_id => replay_id,
        name => name);
END;

PROCEDURE delete_attribute(
    capture_id IN NUMBER,
    replay_id  IN NUMBER,
    name       IN VARCHAR2)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.DELETE_ATTRIBUTE(
        capture_id => capture_id,
        replay_id => replay_id,
        name => name);
END;

PROCEDURE persist_attributes(capture_id IN NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.PERSIST_ATTRIBUTES(capture_id => capture_id);
END;

PROCEDURE sync_attributes_from_file(capture_id IN NUMBER)
IS
BEGIN
    sys.DBMS_WORKLOAD_REPLAY.SYNC_ATTRIBUTES_FROM_FILE(
        capture_id => capture_id);
END;

END BLOCK_DBMS_WORKLOAD_REPLAY;
/

For the DBMS_ADVISOR package, things are bit more complicated.
Remember, this is the only package ( as at 11g) that is a member of both the Diagnostic and the Tuning Pack.

For the use of this package to be deemed part of the Diagnostic Pack, a package member must be called with an advisor_name parameter value of ADDM or with a task_name parameter that starts ADDM.
For the Tuning Pack, the advisor_name parameter must be set to either ‘SQL Tuning Advisor’ or ‘SQL Access Advisor’.

We can perform all of these checks in a simple function like this :

FUNCTION license_check_fn( i_name IN VARCHAR2, i_value IN VARCHAR2) 
    RETURN BOOLEAN 
IS 
    l_msg VARCHAR2(4000); 
BEGIN 
    IF UPPER(i_name) = 'ADVISOR_NAME' 
        AND UPPER(i_value) IN ( 
            'ADDM', 'SQL TUNING ADVISOR', 'SQL ACCESS ADVISOR') 
    THEN 
        -- 
        -- Cause the pack license error to be raised. 
        -- 
        l_msg := disable_pack_dt.get_err_msg_fn; 
    ELSIF UPPER(i_name) = 'TASK_NAME' 
        AND UPPER( i_value) LIKE 'ADDM%' 
    THEN 
        -- 
        -- cause the pack license error to be raised. 
        -- 
        l_msg := disable_pack_dt.get_err_msg_fn; 
    END IF; 
    -- 
    -- If we get here then we haven't raised an error so OK to continue. 
    -- 
    RETURN TRUE; 
END;

If we just include this as a private function in the body of the package, we should be ready to go.
So, start with the pacakge header :

CREATE OR REPLACE PACKAGE disable_pack_dt.BLOCK_DBMS_ADVISOR
authid current_user
IS


ADV_NAME_DEFAULT         constant varchar2(30) := 'Default Advisor';
ADV_NAME_ADDM            constant varchar2(30) := 'ADDM';
ADV_NAME_SQLACCESS       constant varchar2(30) := 'SQL Access Advisor';
ADV_NAME_UNDO            constant varchar2(30) := 'Undo Advisor';
ADV_NAME_SQLTUNE         constant varchar2(30) := 'SQL Tuning Advisor';
ADV_NAME_SEGMENT         constant varchar2(30) := 'Segment Advisor';
ADV_NAME_SQLWM           constant varchar2(30) := 'SQL Workload Manager';
ADV_NAME_TUNEMV          constant varchar2(30) := 'Tune MView';
ADV_NAME_SQLPA           constant varchar2(30) := 'SQL Performance Analyzer';
ADV_NAME_SQLREPAIR       constant varchar2(30) := 'SQL Repair Advisor';
ADV_NAME_COMPRESS        constant varchar2(30) := 'Compression Advisor';

ADV_ID_DEFAULT           constant number := 0;
ADV_ID_ADDM              constant number := 1;
ADV_ID_SQLACCESS         constant number := 2;
ADV_ID_UNDO              constant number := 3;
ADV_ID_SQLTUNE           constant number := 4;
ADV_ID_SEGMENT           constant number := 5;
ADV_ID_SQLWM             constant number := 6;
ADV_ID_TUNEMV            constant number := 7;
ADV_ID_SQLPA             constant number := 8;
ADV_ID_SQLREPAIR         constant number := 9;
ADV_ID_COMPRESS          constant number := 10;


ADVISOR_ALL           constant number       := -995;
ADVISOR_CURRENT       constant number       := -996;
ADVISOR_DEFAULT       constant number       := -997;
ADVISOR_UNLIMITED     constant number       := -998;
ADVISOR_UNUSED        constant number       := -999;


SQLACCESS_GENERAL       constant varchar2(20) := 'SQLACCESS_GENERAL';
SQLACCESS_OLTP          constant varchar2(20) := 'SQLACCESS_OLTP';
SQLACCESS_WAREHOUSE     constant varchar2(20) := 'SQLACCESS_WAREHOUSE';

SQLACCESS_ADVISOR       constant varchar2(30) := ADV_NAME_SQLACCESS;
TUNE_MVIEW_ADVISOR      constant varchar2(30) := ADV_NAME_TUNEMV;
SQLWORKLOAD_MANAGER     constant varchar2(30) := ADV_NAME_SQLWM;

TYPE argList IS TABLE OF sys.wri$_adv_parameters.value%TYPE;

TYPE varchar2adv IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;



procedure cancel_task (task_name       in varchar2);


procedure create_task (advisor_name          in varchar2,
task_id               out number,
task_name             in out varchar2,
task_desc             in varchar2 := null,
template              in varchar2 := null,
is_template           in varchar2 := 'FALSE',
how_created           in varchar2 := null);


procedure create_task (advisor_name          in varchar2,
task_name             in varchar2,
task_desc             in varchar2 := null,
template              in varchar2 := null,
is_template           in varchar2 := 'FALSE',
how_created           in varchar2 := null);


procedure create_task (parent_task_name      in varchar2,
rec_id                in number,
task_id               out number,
task_name             in out varchar2,
task_desc             in varchar2,
template              in varchar2);

procedure delete_task (task_name       in varchar2);

procedure execute_task(task_name IN VARCHAR2);

FUNCTION execute_task(
task_name        IN VARCHAR2,
execution_type   IN VARCHAR2 := NULL,
execution_name   IN VARCHAR2 := NULL,
execution_desc   IN VARCHAR2 := NULL,
execution_params IN argList  := NULL)
RETURN VARCHAR2;

procedure interrupt_task (task_name       in varchar2);


procedure mark_recommendation (task_name       in varchar2,
id              in number,
action          in varchar2);

procedure reset_task(task_name       in varchar2);

procedure resume_task(task_name       in varchar2);


procedure set_task_parameter (task_name      in varchar2,
parameter      in varchar2,
value          in varchar2);

procedure set_task_parameter (task_name      in varchar2,
parameter      in varchar2,
value          in number);


procedure set_default_task_parameter (advisor_name   in varchar2,
parameter      in varchar2,
value       
in varchar2);

procedure set_default_task_parameter (advisor_name   in varchar2,
parameter      in varchar2,
value          in number);


PROCEDURE create_object(task_name     IN VARCHAR2 ,
object_type   IN VARCHAR2 ,
attr1         IN VARCHAR2 := null,
attr2         IN VARCHAR2 := null,
attr3         IN VARCHAR2 := null,
attr4         IN clob := NULL,
object_id    OUT NUMBER);


PROCEDURE create_object(task_name     IN VARCHAR2 ,
object_type   IN VARCHAR2 ,
attr1         IN VARCHAR2 := null,
attr2         IN VARCHAR2 := null,
attr3         IN VARCHAR2 := null,
attr4         IN clob := NULL,
attr5         IN VARCHAR2 := null,
object_id    OUT NUMBER);


PROCEDURE update_object(task_name     IN VARCHAR2 ,
object_id     IN NUMBER ,
attr1         IN VARCHAR2 := null,
attr2         IN VARCHAR2 := null,
attr3         IN VARCHAR2 := null,
attr4         IN clob := NULL,
attr5         IN VARCHAR2 := null);



procedure create_file (buffer         in clob,
location       in varchar2,
filename       in varchar2);

function get_task_report (task_name      in varchar2,
type           in varchar2 := 'TEXT',
level          in varchar2 := 'TYPICAL',
section        in varchar2 := 'ALL',
owner_name     in varchar2 := NULL,
execution_name in varchar2 := NULL,
object_id      in number   := NULL)
return clob;

function get_task_script (task_name      in varchar2,
type           in varchar2 := 'IMPLEMENTATION',
rec_id         in number   := NULL,
act_id         in number   := NULL,
owner_name     in varchar2 := NULL,
execution_name in varchar2 := NULL,
object_id      in number   := NULL)
return clob;


procedure implement_task (task_name       in varchar2,
rec_id          in number := NULL,
exit_on_error   in boolean := NULL);


procedure quick_tune (advisor_name           in varchar2,
task_name              in varchar2,
attr1                  in clob := null,
attr2                  in varchar2 := null,
attr3                  in number := null,
template               in varchar2 := null,
implement              in boolean := FALSE,
description            in varchar2 := null);


procedure tune_mview (task_name      in out varchar2,
mv_create_stmt in     clob);



procedure update_rec_attributes (task_name            in varchar2,
rec_id               in number,
action_id            in number,
attribute_name       in varchar2,
value                in varchar2);


procedure get_rec_attributes (task_name            in varchar2,
rec_id               in number,
action_id            in number,
attribute_name       in varchar2,
value                out varchar2,
owner_name           in varchar2 := NULL);


procedure update_task_attributes (task_name       in varchar2,
new_name        in varchar2 := null,
description     in varchar2 := null,
read_only       in varchar2 := null,
is_template     in varchar2 := null,
how_created     in varchar2 := null);




function format_message_group(group_id IN number, msg_type IN number := 0)
return varchar2;



function format_message(msg_id IN varchar2)
return varchar2;


procedure check_privs;


procedure check_read_privs(owner_name IN VARCHAR2);


procedure setup_repository;


procedure add_sqlwkld_statement (workload_name        in varchar2,
module               in varchar2 := '',
action               in varchar2 := '',
cpu_time             in number := 0,
elapsed_time         in number := 0,
disk_reads           in number := 0,
buffer_gets          in number := 0,
rows_processed       in number := 0,
optimizer_cost       in number := 0,
executions           in number := 1,
priority             in number := 2,
last_execution_date  in date := SYSDATE,
stat_period          in number := 0,
username             in varchar2,
sql_text             in clob);


procedure add_sqlwkld_ref (task_name      in varchar2,
workload_name  in varchar2,
is_sts         in number := 0);


procedure add_sts_ref (task_name      in varchar2,
sts_owner      in varchar2,
workload_name  in varchar2);


procedure create_sqlwkld (workload_name    
in out varchar2,
description              in varchar2 := null,
template                 in varchar2 := null,
is_template              in varchar2 := 'FALSE');


procedure delete_sqlwkld (workload_name            in varchar2);


procedure delete_sqlwkld_ref (task_name       in varchar2,
workload_name   in varchar2,
is_sts          in number := 2);


procedure delete_sts_ref (task_name       in varchar2,
sts_owner       in varchar2,
workload_name   in varchar2);


procedure delete_sqlwkld_statement (workload_name     in varchar2,
sql_id            in number);

procedure delete_sqlwkld_statement (workload_name     in varchar2,
search            in varchar2,
deleted           out number);


procedure import_sqlwkld_sts (workload_name         in varchar2,
sts_owner             in varchar2,
sts_name              in varchar2,
import_mode           in varchar2 := 'NEW',
priority              in number := 2,
saved_rows            out number,
failed_rows           out number);

procedure import_sqlwkld_sts (workload_name         in varchar2,
sts_name              in varchar2,
import_mode           in varchar2 := 'NEW',
priority              in number := 2,
saved_rows            out number,
failed_rows           out number);


procedure import_sqlwkld_schema (workload_name         in varchar2,
import_mode           in varchar2 := 'NEW',
priority              in number := 2,
saved_rows            out number,
failed_rows           out number);



procedure import_sqlwkld_sqlcache (workload_name         in varchar2,
import_mode           in varchar2 := 'NEW',
priority              in number := 2,
saved_rows            out number,
failed_rows           out number);



procedure import_sqlwkld_sumadv (workload_name         in varchar2,
import_mode           in varchar2 := 'NEW',
priority              in number := 2,
sumadv_id             in number,
saved_rows            out number,
failed_rows           out number);


procedure import_sqlwkld_user (workload_name         in varchar2,
import_mode           in varchar2 := 'NEW',
owner_name            in varchar2,
table_name            in varchar2,
saved_rows            out number,
failed_rows           out number);


procedure copy_sqlwkld_to_sts (workload_name         in varchar2,
sts_name              in varchar2,
import_mode           in varchar2 := 'NEW');


procedure reset_sqlwkld (workload_name       in varchar2);


procedure set_sqlwkld_parameter (workload_name        in varchar2,
parameter            in varchar2,
value                in varchar2);

procedure set_sqlwkld_parameter (workload_name        in varchar2,
parameter            in varchar2,
value                in number);


procedure set_default_sqlwkld_parameter (parameter      in varchar2,
value          in varchar2);

procedure set_default_sqlwkld_parameter (parameter      in varchar2,
value          in number);


procedure update_sqlwkld_attributes (workload_name    in varchar2,
new_name         in varchar2 := null,
description      in varchar2 := null,
read_only        in varchar2 := null,
is_template      in varchar2 := null,
how_created      in varchar2 := null);


procedure update_sqlwkld_statement (workload_name     in varchar2,
sql_id            in number,
application       in varchar2 := null,
action            in varchar2 := null,
priority          in number := null,
username          in varchar2 := null);

procedure update_sqlwkld_statement (workload_name     in varchar2,
search            in varchar2,
updated           out number,
application       in varchar2 := null,
action            in varchar2 := null,
priority          in number := null,
username          in varchar2 := null);


procedure setup_user_environment (advisor_name    in varchar2);


procedure get_access_advisor_defaults (task_name      out varchar2,
task_id_num    out number,
workload_name  out varchar2,
work_id_num    out number);


procedure delete_directive (directive_id    in number,
instance_name   in varchar2,
task_name       in varchar2 :=   
NULL);


function evaluate_directive (directive_id      in number,
instance_name     in varchar2,
task_name         in varchar2 := NULL,
p1                in clob := NULL,
p2                in clob := NULL)
return clob;


procedure insert_directive (directive_id    in number,
instance_name   in varchar2,
task_name       in varchar2,
document        in clob);


procedure update_directive (directive_id    in number,
instance_name   in varchar2,
task_name       in varchar2,
document        in clob);


END BLOCK_DBMS_ADVISOR;
/

…and the body, including our function…

CREATE OR REPLACE PACKAGE BODY disable_pack_dt.BLOCK_DBMS_ADVISOR
IS
-------------------------------------------------------------------------------
-- The Diagnostic and Tuning Pack licenses are required when either
-- 1) advisor_name => 'ADDM'
-- 2) advisor_name => 'SQL Tuning Advisor'
-- 3) advisor_name => 'SQL Access Advisor'
-- 4) task_name LIKE 'ADDM%'
--  
-------------------------------------------------------------------------------

FUNCTION license_check_fn( i_name IN VARCHAR2, i_value IN VARCHAR2)
    RETURN BOOLEAN
-------------------------------------------------------------------------------
-- Private function called by relevant package members to check that parameter
-- values do not require the Diagnostic or Tuning Pack license to be used.
-- i_name - name of the parameter ( should be 'ADVISOR_NAME' or 'TASK_NAME')
-- i_value - the value passed in for the parameter in question.
--
-------------------------------------------------------------------------------
IS 
    l_msg VARCHAR2(4000);
BEGIN
    IF UPPER(i_name) = 'ADVISOR_NAME' 
        AND UPPER(i_value) IN ( 
            'ADDM', 'SQL TUNING ADVISOR', 'SQL ACCESS ADVISOR')
    THEN
        --
        -- Cause the pack license error to be raised.
        --
        l_msg := disable_pack_dt.get_err_msg_fn;
    ELSIF UPPER(i_name) = 'TASK_NAME'
        AND UPPER( i_value) LIKE 'ADDM%'
    THEN
        --
        -- cause the pack license error to be raised.
        --
        l_msg := disable_pack_dt.get_err_msg_fn;
    END IF;
    --
    -- If we get here then we haven't raised an error so OK to continue.
    --
    RETURN TRUE;
END;
    
procedure cancel_task (task_name       in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X')) THEN
        sys.DBMS_ADVISOR.CANCEL_TASK(task_name => task_name);
    END IF;
END;

procedure create_task (
    advisor_name          in varchar2,
    task_id               out number,
    task_name             in out varchar2,
    task_desc             in varchar2 := null,
    template              in varchar2 := null,
    is_template           in varchar2 := 'FALSE',
    how_created           in varchar2 := null)
IS
BEGIN
    IF license_check_fn( i_name => 'advisor_name', i_value => NVL( advisor_name, 'X'))
        AND license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.CREATE_TASK(
            advisor_name => advisor_name,
            task_id => task_id,
            task_name => task_name,
            task_desc => task_desc,
            template => template,
            is_template => is_template,
            how_created => how_created);
    END IF;
END;

procedure create_task (
    advisor_name          in varchar2,
    task_name             in varchar2,
    task_desc             in varchar2 := null,
    template              in varchar2 := null,
    is_template           in varchar2 := 'FALSE',
    how_created           in varchar2 := null)
IS
BEGIN
    IF license_check_fn( i_name => 'advisor_name', i_value => NVL( advisor_name, 'X'))
        AND license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.CREATE_TASK(    
            advisor_name => advisor_name,
            task_name => task_name,
            task_desc => task_desc,
            template => template,
            is_template => is_template,
            how_created => how_created);
    END IF;
END;

procedure create_task (
    parent_task_name      in varchar2,
    rec_id                in number,
    task_id               out number,
    task_name             in out varchar2,
    task_desc             in varchar2,
    template              in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.CREATE_TASK(
            parent_task_name => parent_task_name,
            rec_id => rec_id,
            task_id => task_id,
            task_name => task_name,
            task_desc => task_desc,
            template => template);
    END IF;
END;

procedure delete_task (task_name       in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.DELETE_TASK( task_name => task_name);
    END IF;
END;

procedure execute_task(task_name IN VARCHAR2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.EXECUTE_TASK(task_name => task_name);
    END IF;
END;

FUNCTION execute_task(
    task_name        IN VARCHAR2,
    execution_type   IN VARCHAR2 := NULL,
    execution_name   IN VARCHAR2 := NULL,
    execution_desc   IN VARCHAR2 := NULL,
    execution_params IN argList  := NULL)
    RETURN VARCHAR2
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        --
        -- NOTE - compiler doesn't like the execution_params value
        -- for some reason and insists on returning a 
        -- PLS-00306 error. As the parameter is set to NULL explicitly
        -- here (not even defaulted) then just pass NULL as the argument.
        --
        RETURN sys.DBMS_ADVISOR.EXECUTE_TASK(
            task_name => task_name,
            execution_type => execution_type,
            execution_name => execution_name,
            execution_desc => execution_desc,
            execution_params => NULL);
    END IF;
END;

procedure interrupt_task (task_name in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.INTERRUPT_TASK( task_name => task_name);
    END IF;
END;

procedure mark_recommendation (
    task_name       in varchar2,
    id              in number,
    action          in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.MARK_RECOMMENDATION(
            task_name => task_name,
            id => id,
            action => action);
    END IF;
END;

procedure reset_task(task_name       in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.RESET_TASK( task_name => task_name);
    END IF;
END;

procedure resume_task(task_name       in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.RESUME_TASK( task_name => task_name);
    END IF;
END;

procedure set_task_parameter (
    task_name      in varchar2,
    parameter      in varchar2,
    value          in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.SET_TASK_PARAMETER(
            task_name => task_name,
            parameter => parameter,
            value => value);
    END IF;
END;
    
procedure set_task_parameter (
    task_name      in varchar2,
    parameter      in varchar2,
    value          in number)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.SET_TASK_PARAMETER(
            task_name => task_name,
            parameter => parameter,
            value => value);
    END IF;
END;

procedure set_default_task_parameter (
    advisor_name   in varchar2,
    parameter      in varchar2,
    value          in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'advisor_name', i_value => NVL(advisor_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
            advisor_name => advisor_name,
            parameter => parameter,
            value => value);
    END IF;
END;

procedure set_default_task_parameter (
    advisor_name   in varchar2,
    parameter      in varchar2,
    value          in number)
IS
BEGIN
    IF license_check_fn( i_name => 'advisor_name', i_value => NVL(advisor_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.SET_DEFAULT_TASK_PARAMETER(
            advisor_name => advisor_name,
            parameter => parameter,
            value => value);
    END IF;
END;

PROCEDURE create_object(
    task_name     IN VARCHAR2 ,
    object_type   IN VARCHAR2 ,
    attr1         IN VARCHAR2 := null,
    attr2         IN VARCHAR2 := null,
    attr3         IN VARCHAR2 := null,
    attr4         IN clob := NULL,
    object_id    OUT NUMBER)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        --
        -- For some reason, the DBMS_ADVISOR package has
        -- CREATE_OBJECT overloaded with one extra attr parameter.
        -- The problem is that, if you pass by reference, you'll get
        -- a PLS-00307 - "Too many declarations of CREATE_OBJECT match this call"
        -- To avoid this, pass by position.
        --
        sys.DBMS_ADVISOR.CREATE_OBJECT(
            task_name,
            object_type,
            attr1,
            attr2,
            attr3,
            attr4,
            object_id);
    END IF;
END;

PROCEDURE create_object(
    task_name     IN VARCHAR2 ,
    object_type   IN VARCHAR2 ,
    attr1         IN VARCHAR2 := null,
    attr2         IN VARCHAR2 := null,
    attr3         IN VARCHAR2 := null,
    attr4         IN clob := NULL,
    attr5         IN VARCHAR2 := null,
    object_id    OUT NUMBER)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.CREATE_OBJECT(
            task_name => task_name,
            object_type => object_type,
            attr1 => attr1,
            attr2 => attr2,
            attr3 => attr3,
            attr4 => attr4,
            attr5 => attr5,
            object_id => object_id);
    END IF;
END;

PROCEDURE update_object(
    task_name     IN VARCHAR2 ,
    object_id     IN NUMBER ,
    attr1         IN VARCHAR2 := null,
    attr2         IN VARCHAR2 := null,
    attr3         IN VARCHAR2 := null,
    attr4         IN clob := NULL,
    attr5         IN VARCHAR2 := null)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.UPDATE_OBJECT(
            task_name => task_name,
            object_id => object_id,
            attr1 => attr1,
            attr2 => attr2,
            attr3 => attr3,
            attr4 => attr4,
            attr5 => attr5);
    END IF;
END;

procedure create_file (
    buffer         in clob,
    location       in varchar2,
    filename       in varchar2)
IS
BEGIN
    sys.DBMS_ADVISOR.CREATE_FILE(
        buffer => buffer,
        location => location,
        filename => filename);
END;

function get_task_report (
    task_name      in varchar2,
    type           in varchar2 := 'TEXT',
    level          in varchar2 := 'TYPICAL',
    section        in varchar2 := 'ALL',
    owner_name     in varchar2 := NULL,
    execution_name in varchar2 := NULL,
    object_id      in number   := NULL)
    return clob
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        RETURN sys.DBMS_ADVISOR.GET_TASK_REPORT(
            task_name => task_name,
            type => type,
            level => level,
            section => section,
            owner_name => owner_name,
            execution_name => execution_name,
            object_id => object_id);
    END IF;
END;

function get_task_script (
    task_name      in varchar2,
    type           in varchar2 := 'IMPLEMENTATION',
    rec_id         in number   := NULL,
    act_id         in number   := NULL,
    owner_name     in varchar2 := NULL,
    execution_name in varchar2 := NULL,
    object_id      in number   := NULL)
    return clob
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        RETURN sys.DBMS_ADVISOR.GET_TASK_SCRIPT(
            task_name => task_name,
            type => type,
            rec_id => rec_id,
            act_id => act_id,
            owner_name => owner_name,
            execution_name => execution_name,
            object_id => object_id);
    END IF;
END;

procedure implement_task (
    task_name       in varchar2,
    rec_id          in number := NULL,
    exit_on_error   in boolean := NULL)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.IMPLEMENT_TASK(
            task_name => task_name,
            rec_id => rec_id,
            exit_on_error => exit_on_error);
    END IF;
END;

procedure quick_tune (
    advisor_name           in varchar2,
    task_name              in varchar2,
    attr1                  in clob := null,
    attr2                  in varchar2 := null,
    attr3                  in number := null,
    template               in varchar2 := null,
    implement              in boolean := FALSE,
    description            in varchar2 := null)
IS
BEGIN
    IF license_check_fn( i_name => 'advisor_name', i_value => NVL( advisor_name, 'X'))
        AND license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.QUICK_TUNE(
            advisor_name => advisor_name,
            task_name => task_name,
            attr1 => attr1,
            attr2 => attr2,
            attr3 => attr3,
            template => template,
            implement => implement,
            description => description);
    END IF;
END;
        
procedure tune_mview (
    task_name      in out varchar2,
    mv_create_stmt in     clob)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.TUNE_MVIEW(
            task_name => task_name,
            mv_create_stmt => mv_create_stmt);
    END IF;
END;


procedure update_rec_attributes (
    task_name            in varchar2,
    rec_id               in number,
    action_id            in number,
    attribute_name       in varchar2,
    value                in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.UPDATE_REC_ATTRIBUTES(
            task_name => task_name,
            rec_id => rec_id,
            action_id => action_id,
            attribute_name => attribute_name,
            value => value);
    END IF;
END;

procedure get_rec_attributes (
    task_name            in varchar2,
    rec_id               in number,
    action_id            in number,
    attribute_name       in varchar2,
    value                out varchar2,
    owner_name           in varchar2 := NULL)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.GET_REC_ATTRIBUTES(
            task_name => task_name,
            rec_id => rec_id,
            action_id => action_id,
            attribute_name => attribute_name,
            value => value,
            owner_name => owner_name);
    END IF;
END;

procedure update_task_attributes (
    task_name       in varchar2,
    new_name        in varchar2 := null,
    description     in varchar2 := null,
    read_only       in varchar2 := null,
    is_template     in varchar2 := null,
    how_created     in varchar2 := null)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.UPDATE_TASK_ATTRIBUTES(
            task_name => task_name,
            new_name => new_name,
            description => description,
            read_only => read_only,
            is_template => is_template,
            how_created => how_created);
    END IF;
END;

function format_message_group(
    group_id IN number, 
    msg_type IN number := 0)
    return varchar2 
IS
BEGIN
    RETURN sys.DBMS_ADVISOR.FORMAT_MESSAGE_GROUP(
        group_id => group_id,
        msg_type => msg_type);
END;

function format_message(msg_id IN varchar2)
    return varchar2
IS
BEGIN
    RETURN sys.DBMS_ADVISOR.FORMAT_MESSAGE( msg_id => msg_id);
END;

procedure check_privs
IS
BEGIN
    sys.DBMS_ADVISOR.CHECK_PRIVS;
END;

procedure check_read_privs(owner_name IN VARCHAR2)
IS
BEGIN
    sys.DBMS_ADVISOR.CHECK_READ_PRIVS( owner_name => owner_name);
END;

procedure setup_repository
IS
BEGIN
    sys.DBMS_ADVISOR.SETUP_REPOSITORY;
END;

procedure add_sqlwkld_statement (
    workload_name        in varchar2,
    module               in varchar2 := '',
    action               in varchar2 := '',
    cpu_time             in number := 0,
    elapsed_time         in number := 0,
    disk_reads           in number := 0,
    buffer_gets          in number := 0,
    rows_processed       in number := 0,
    optimizer_cost       in number := 0,
    executions           in number := 1,
    priority             in number := 2,
    last_execution_date  in date := SYSDATE,
    stat_period          in number := 0,
    username             in varchar2,
    sql_text             in clob)
IS
BEGIN
    sys.DBMS_ADVISOR.ADD_SQLWKLD_STATEMENT(
        workload_name => workload_name,
        module => module,
        action => action,
        cpu_time => cpu_time,
        elapsed_time => elapsed_time,
        disk_reads => disk_reads,
        buffer_gets => buffer_gets,
        rows_processed => rows_processed,
        optimizer_cost => optimizer_cost,
        executions => executions,
        priority => priority,
        last_execution_date => last_execution_date,
        stat_period => stat_period,
        username => username,
        sql_text => sql_text);
END;

procedure add_sqlwkld_ref (
    task_name      in varchar2,
    workload_name  in varchar2,
    is_sts         in number := 0)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.ADD_SQLWKLD_REF(
            task_name => task_name,
            workload_name => workload_name,
            is_sts => is_sts);
    END IF;
END;

procedure add_sts_ref (
    task_name      in varchar2,
    sts_owner      in varchar2,
    workload_name  in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.ADD_STS_REF(
            task_name => task_name,
            sts_owner => sts_owner,
            workload_name => workload_name);
    END IF;
END;

procedure create_sqlwkld (
    workload_name            in out varchar2,
    description              in varchar2 := null,
    template                 in varchar2 := null,
    is_template              in varchar2 := 'FALSE')
IS
BEGIN
    sys.DBMS_ADVISOR.CREATE_SQLWKLD(
        workload_name => workload_name,
        description => description,
        template => template,
        is_template => is_template);
END;

procedure delete_sqlwkld (workload_name            in varchar2)
IS
BEGIN
    sys.DBMS_ADVISOR.DELETE_SQLWKLD(
        workload_name => workload_name);
END;

procedure delete_sqlwkld_ref (
    task_name       in varchar2,
    workload_name   in varchar2,
    is_sts          in number := 2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.DELETE_SQLWKLD_REF(
            task_name => task_name,
            workload_name => workload_name,
            is_sts => is_sts);
    END IF;
END;

procedure delete_sts_ref (
    task_name       in varchar2,
    sts_owner       in varchar2,
    workload_name   in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.DELETE_STS_REF(
            task_name => task_name,
            sts_owner => sts_owner,
            workload_name => workload_name);
    END IF;
END;

procedure delete_sqlwkld_statement (
    workload_name     in varchar2,
    sql_id            in number)
IS
BEGIN
    sys.DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT(
        workload_name => workload_name,
        sql_id => sql_id);
END;

procedure delete_sqlwkld_statement (
    workload_name     in varchar2,
    search            in varchar2,
    deleted           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.DELETE_SQLWKLD_STATEMENT(
        workload_name => workload_name,
        search => search,
        deleted => deleted);
END;

procedure import_sqlwkld_sts (
    workload_name         in varchar2,
    sts_owner             in varchar2,
    sts_name              in varchar2,
    import_mode           in varchar2 := 'NEW',
    priority              in number := 2,
    saved_rows            out number,
    failed_rows           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.IMPORT_SQLWKLD_STS(
        workload_name => workload_name,
        sts_owner => sts_owner,
        sts_name => sts_name,
        import_mode => import_mode,
        priority => priority,
        saved_rows => saved_rows,
        failed_rows => failed_rows);
END;

procedure import_sqlwkld_sts (
    workload_name         in varchar2,
    sts_name              in varchar2,
    import_mode           in varchar2 := 'NEW',
    priority              in number := 2,
    saved_rows            out number,
    failed_rows           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.IMPORT_SQLWKLD_STS(
        workload_name => workload_name,
        sts_name => sts_name,
        import_mode => import_mode,
        priority => priority,
        saved_rows => saved_rows,
        failed_rows => failed_rows);
END;

procedure import_sqlwkld_schema (
    workload_name         in varchar2,
    import_mode           in varchar2 := 'NEW',
    priority              in number := 2,
    saved_rows            out number,
    failed_rows           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.IMPORT_SQLWKLD_SCHEMA(
        workload_name => workload_name,
        import_mode => import_mode,
        priority => priority,
        saved_rows => saved_rows,
        failed_rows => failed_rows);
END;

procedure import_sqlwkld_sqlcache (
    workload_name         in varchar2,
    import_mode           in varchar2 := 'NEW',
    priority              in number := 2,
    saved_rows            out number,
    failed_rows           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.IMPORT_SQLWKLD_SQLCACHE(
        workload_name => workload_name,
        import_mode => import_mode,
        priority => priority,
        saved_rows => saved_rows,
        failed_rows => failed_rows);
END;

procedure import_sqlwkld_sumadv (
    workload_name         in varchar2,
    import_mode           in varchar2 := 'NEW',
    priority              in number := 2,
    sumadv_id             in number,
    saved_rows            out number,
    failed_rows           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.IMPORT_SQLWKLD_SUMADV(
        workload_name => workload_name,
        import_mode => import_mode,
        priority => priority,
        sumadv_id => sumadv_id,
        saved_rows => saved_rows,
        failed_rows => failed_rows);
END;

procedure import_sqlwkld_user (
    workload_name         in varchar2,
    import_mode           in varchar2 := 'NEW',
    owner_name            in varchar2,
    table_name            in varchar2,
    saved_rows            out number,
    failed_rows           out number)
IS
BEGIN
    sys.DBMS_ADVISOR.IMPORT_SQLWKLD_USER(
        workload_name => workload_name,
        import_mode => import_mode,
        owner_name => owner_name,
        table_name => table_name,
        saved_rows => saved_rows,
        failed_rows => failed_rows);
END;

procedure copy_sqlwkld_to_sts (
    workload_name         in varchar2,
    sts_name              in varchar2,
    import_mode           in varchar2 := 'NEW')
IS
BEGIN
    sys.DBMS_ADVISOR.COPY_SQLWKLD_TO_STS(
        workload_name => workload_name,
        sts_name => sts_name,
        import_mode => import_mode);
END;

procedure reset_sqlwkld (workload_name       in varchar2)
IS
BEGIN
    sys.DBMS_ADVISOR.RESET_SQLWKLD( workload_name => workload_name);
END;

procedure set_sqlwkld_parameter (
    workload_name        in varchar2,
    parameter            in varchar2,
    value                in varchar2)
IS
BEGIN
    sys.DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(
        workload_name => workload_name,
        parameter => parameter,
        value => value);
END;

procedure set_sqlwkld_parameter (
    workload_name        in varchar2,
    parameter            in varchar2,
    value                in number)
IS
BEGIN
    sys.DBMS_ADVISOR.SET_SQLWKLD_PARAMETER(
        workload_name => workload_name,
        parameter => parameter,
        value => value);
END;
procedure set_default_sqlwkld_parameter (
    parameter      in varchar2,
    value          in varchar2)
IS
BEGIN
    sys.DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER(
        parameter => parameter,
        value => value);
END;

procedure set_default_sqlwkld_parameter (
    parameter      in varchar2,
    value          in number)
IS
BEGIN
    sys.DBMS_ADVISOR.SET_DEFAULT_SQLWKLD_PARAMETER(
        parameter => parameter,
        value => value);
END;

procedure update_sqlwkld_attributes (
    workload_name    in varchar2,
    new_name         in varchar2 := null,
    description      in varchar2 := null,
    read_only        in varchar2 := null,
    is_template      in varchar2 := null,
    how_created      in varchar2 := null)
IS
BEGIN
    sys.DBMS_ADVISOR.UPDATE_SQLWKLD_ATTRIBUTES(
        workload_name => workload_name,
        new_name => new_name,
        description => description,
        read_only => read_only,
        is_template => is_template,
        how_created => how_created);
END;

procedure update_sqlwkld_statement (
    workload_name     in varchar2,
    sql_id            in number,
    application       in varchar2 := null,
    action            in varchar2 := null,
    priority          in number := null,
    username          in varchar2 := null)
IS
BEGIN
    sys.DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT(
        workload_name => workload_name,
        sql_id => sql_id,
        application => application,
        action => action,
        priority => priority,
        username => username);
END;

procedure update_sqlwkld_statement (
    workload_name     in varchar2,
    search            in varchar2,
    updated           out number,
    application       in varchar2 := null,
    action            in varchar2 := null,
    priority          in number := null,
    username          in varchar2 := null)
IS
BEGIN
    sys.DBMS_ADVISOR.UPDATE_SQLWKLD_STATEMENT(
        workload_name => workload_name,
        search => search,
        updated => updated,
        application => application,
        action => action,
        priority => priority,
        username => username);
END;
    
procedure setup_user_environment (advisor_name    in varchar2)
IS
BEGIN
    IF license_check_fn( i_name => 'advisor_name', i_value => NVL(advisor_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.SETUP_USER_ENVIRONMENT( advisor_name => advisor_name);
    END IF;
END;

procedure get_access_advisor_defaults (
    task_name      out varchar2,
    task_id_num    out number,
    workload_name  out varchar2,
    work_id_num    out number)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.GET_ACCESS_ADVISOR_DEFAULTS(
            task_name => task_name,
            task_id_num => task_id_num,
            workload_name => workload_name,
            work_id_num => work_id_num);
    END IF;
END;

procedure delete_directive (
    directive_id    in number,
    instance_name   in varchar2,
    task_name       in varchar2 :=   NULL)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.DELETE_DIRECTIVE(
            directive_id => directive_id,
            instance_name => instance_name,
            task_name => task_name);
    END IF;
END;

function evaluate_directive (
    directive_id      in number,
    instance_name     in varchar2,
    task_name         in varchar2 := NULL,
    p1                in clob := NULL,
    p2                in clob := NULL)
    return clob
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        RETURN sys.DBMS_ADVISOR.EVALUATE_DIRECTIVE(
            directive_id => directive_id,
            instance_name => instance_name,
            task_name => task_name,
            p1 => p1,
            p2 => p2);
    END IF;
END;

procedure insert_directive (
    directive_id    in number,
    instance_name   in varchar2,
    task_name       in varchar2,
    document        in clob)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL( task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.INSERT_DIRECTIVE(
            directive_id => directive_id,
            instance_name => instance_name,
            task_name => task_name,
            document => document);
    END IF;
END;

procedure update_directive (
    directive_id    in number,
    instance_name   in varchar2,
    task_name       in varchar2,
    document        in clob)
IS
BEGIN
    IF license_check_fn( i_name => 'task_name', i_value => NVL(task_name, 'X'))
    THEN
        sys.DBMS_ADVISOR.UPDATE_DIRECTIVE(
            directive_id => directive_id,
            instance_name => instance_name,
            task_name => task_name,
            document => document);
    END IF;
END;

END BLOCK_DBMS_ADVISOR;
/

Finally, we want to grant access to these packages :


GRANT EXECUTE ON disable_pack_dt.block_dbms_addm TO PUBLIC
/

GRANT EXECUTE ON disable_pack_dt.block_dbms_advisor TO PUBLIC
/

GRANT EXECUTE ON disable_pack_dt.block_dbms_sqltune TO PUBLIC
/

GRANT EXECUTE ON disable_pack_dt.block_dbms_workload_replay TO PUBLIC
/

GRANT EXECUTE ON disable_pack_dt.block_dbms_workload_repository TO PUBLIC
/

Now we have all of our code, all we need to do is to make sure that it is accessed …

Re-pointing the Public Synonyms

We’re going to do this in two stages – Views first and then Packages.
Before that, however, it’s probably worth generating the code required to rollback these changes should you need to :

-------------------------------------------------------------------------------
-- Script to generate commands to rollback Public Synonym changes for the
-- DISABLE_PACK_DT application.
-- NOTE - we're not checking the veracity of the object names in DBA_SYNONYMS
-- here. We are simply creating a script that may be run in the event of a 
-- rollback to put things back exactly as they were before making the changes
-- for this application.
--
-------------------------------------------------------------------------------
set heading off
set feedback off
set lines 200
set pages 5000
spool rollback_syns_slave.sql
SELECT 'CREATE OR REPLACE PUBLIC SYNONYM '||syn.synonym_name||' FOR '||syn.table_owner||'.'||syn.table_name||';'
FROM dba_synonyms syn, disable_pack_dt.pack_members mem
WHERE syn.table_name = mem.object_name
AND syn.owner = 'PUBLIC'
/
spool off

Run this and you should now have a rollback script in rollback_syns_slave.sql, containing all the CREATE OR REPLACE PUBLIC SYNONYM statements required to re-set things to the way they were before we started.
NOTE – althought there are 165 records in the PACK_MEMBERS table, three of these are synonyms. Therefore, we should only have 162 synonyms to change in total.

Now to change the View synonyms.
To save some typing, we’re going to script this bit.

set serveroutput on size unlimited
set lines 130
spool change_view_synonyms.log
DECLARE
-------------------------------------------------------------------------------
-- change_view_synonyms.sql - script to re-point the public synonyms
-- for all the views that are part of the Diagnostic and Tuning Pack APIs
-- to the DISABLE_PACK_MESSAGE table.
--
-------------------------------------------------------------------------------
    l_cmd VARCHAR2(500);
BEGIN
    FOR r_mem IN (
        SELECT object_name
        FROM disable_pack_dt.pack_members
        WHERE object_type = 'VIEW')
    LOOP
        DBMS_OUTPUT.PUT_LINE('Replacing SYNONYM for '||r_mem.object_name);
        --
        -- As ever when concatenating values from a select statement into a 
        -- command, we need to make sure that the values in question are not
        -- in fact injection statements.
        -- In this case, the values should contain only $, _ and alphanumeric
        -- characters.
        --
        IF REGEXP_INSTR( 
            REGEXP_REPLACE( r_mem.object_name, '[$,_]'), 
            '[[:punct:]]|[[:space:]]') > 0
        THEN
            RAISE_APPLICATION_ERROR(-20000, 'Object name '
                ||r_mem.object_name||' contains suspicious characters.');
        END IF;
        l_cmd := 'CREATE OR REPLACE PUBLIC SYNONYM '
            ||r_mem.object_name||' FOR disable_pack_dt.disable_pack_message';
        EXECUTE IMMEDIATE l_cmd;
    END LOOP;
END;
/
spool off

For the packages, we can just code the changes directly as there are only five of them :

spool change_pack_synonyms.log

prompt
prompt Replacing synonym for DBMS_ADDM
prompt

CREATE OR REPLACE PUBLIC SYNONYM dbms_addm 
    FOR disable_pack_dt.block_dbms_addm
/

prompt
prompt Replacing synonym for DBMS_ADVISOR
prompt

CREATE OR REPLACE PUBLIC SYNONYM dbms_advisor 
    FOR disable_pack_dt.block_dbms_advisor
/

prompt
prompt Replacing synonym for DBMS_SQLTUNE
prompt

CREATE OR REPLACE PUBLIC SYNONYM dbms_sqltune 
    FOR disable_pack_dt.block_dbms_sqltune
/

prompt
prompt Replacing synonym for DBMS_WORKLOAD_REPLAY
prompt

CREATE OR REPLACE PUBLIC SYNONYM dbms_workload_replay
    FOR disable_pack_dt.block_dbms_workload_replay
/

prompt
prompt Replacing synonym for DBMS_ADVISOR
prompt

CREATE OR REPLACE PUBLIC SYNONYM dbms_workload_repository 
    FOR disable_pack_dt.block_dbms_workload_repository
/

spool off

The tests

For each test, we need to compare the usage statistics before and after the run. The first question is, how do we get Oracle to collect the usage statistics ?
I’ve seen various things advising that you update the SYS.WRI$_DBU_USAGE_SAMPLE table. However, there is a more reliable alternative :

BEGIN
	DBMS_FEATURE_USAGE_INTERNAL.EXEC_DB_USAGE_SAMPLING( curr_date => SYSDATE);
END;
/

Note – whilst executing this procedure does not update the WRI$_DBU_USAGE_SAMPLE table, it does update
the SYS.WRI$_DBU_FEATURE_USAGE table with new stats on the features used.
We can save the results of each feature usage audit before and after each test as follows :

CREATE TABLE before_test_1 AS
  SELECT *
  FROM sys.wri$_dbu_feature_usage
  WHERE detected_usages > 0;

This means that we can check for any changes using the results from the before and after tables.
If our tests work then, provided you don’t have any of the Tuning and Diagnostic features registered as being used before you start, you should get no rows returned when you run the query :

SELECT name, TO_CHAR(last_usage_date, 'DD-MON-YYYY')
FROM after_test1
MINUS
SELECT name, TO_CHAR(last_usage_date, 'DD-MON-YYYY')
FROM before_test1;

So, to recap, before testing started I created a table called BEFORE_TEST1 containing all of the records in sys.wri$dbu_feature_usage where detected usages was greater than zero.

I then ran the test, then generated a usage statistics collection and then created an AFTER_TEST table.
By comparing the most recently created table with it’s immediate predecessor, I was able to tell if the test had caused any additional features to have been used.

Test 1 – Accessing a View

Connect as the HR user and …

SQL> SELECT *      
  2  FROM dba_advisor_tasks; 

URGENT_PLEASE_READ 
-------------------------------------------------------------------------------- 
Diagnostic and Tuning Packs are NOT LICENSED on this database. 
Please do not access any of the pack API objects or underlying tables. 
For a full list please see the table disable_pack_dt.pack_members 

The check returned no rows.

Test 2 – Specify view columns

If column names are specified anywhere in the select statement, including the predicate, we should get an error…

spool test2.log 
SELECT owner, task_name, description 
FROM dba_advisor_tasks 
/

SELECT owner, task_name, description 
                         * 
ERROR at line 1: 
ORA-00904: "DESCRIPTION": invalid identifier 

Normally, when you get this error, you’ll check the table structure by means of a describe…

SQL> desc dba_advisor_tasks 
 Name					   Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 URGENT_PLEASE_READ				    VARCHAR2(4000) 

SQL> 

Now, you’d expect the user to select this from the table and see the message :

SQL> SELECT urgent_please_read 
  2  FROM dba_advisor_tasks 
  3  / 

URGENT_PLEASE_READ 
-------------------------------------------------------------------------------- 
Diagnostic and Tuning Packs are NOT LICENSED on this database. 
Please do not access any of the pack API objects or underlying tables. 
For a full list please see the table disable_pack_dt.pack_members 


SQL> 

NOTE – if instead, the user decides to try prefixing the object owner in the from clause, they can get to the underlying view as they will by-pass the synonym.
I’ve deliberately steered away from revoking access to these objects in order to balance the need to disable the packs with the risk of breaking something internally within Oracle.

Anyway, once again I got no rows returned from my usage check.

Test 3 – SQLDeveloper Last AWR Report

As mentioned previously, SQLDeveloper has an AWR Report available.
For this test, I am going to run the code for this report directly from SQL*Plus. As a follow-up, I’ll run it from inside SQLDeveloper.

First of all, running this from SQLPlus. This time, we need to use another user as HR does not have permissions on dba_hist_snapshot. For my testing, I used an account with DBA priviliges :

declare 
    dbid number; 
    instance_id number; 
    start_id number; 
    end_id number; 
begin 
    dbms_output.enable(1000000); 
    select max(snap_id)-1,max(snap_id) into start_id,end_id from dba_hist_snapshot; 
    select dbid into dbid from v$database; 
    select instance_number into instance_id from v$instance; 

    dbms_output.put_line('<PRE>'); 
    for rc in ( select output from 
        table(dbms_workload_repository.awr_report_text(dbid,instance_id,start_id,end_id)) 
    ) loop 
        -- dbms_output.put_line('<BR>'); 
        dbms_output.put_line(rc.output); 
    end loop; 
    dbms_output.put_line('</PRE>'); 
end; 
/

when we execute this, we get …

declare 
* 
ERROR at line 1: 
ORA-20999: Diagnostic and Tuning Packs are NOT LICENSED on this database. 
Please do not access any of the pack API objects or underlying tables. 
For a full list please see the table disable_pack_dt.pack_members 
ORA-06512: at "DISABLE_PACK_DT.GET_ERR_MSG_FN", line 14 
ORA-06512: at "DISABLE_PACK_DT.BLOCK_DBMS_WORKLOAD_REPOSITORY", line 238 
ORA-06512: at line 1 
ORA-06512: at line 13 


SQL> 

As before, when checking the before and after usage stats, there’s no change.

Test 4 – AWR Report from SQLDeveloper

Unless something very unusual happens, we should get exactly the same result when we invoke this code from the SQL Developer Reports tab …

Fingers crossed...and press the button !

Fingers crossed…and press the button !

When we execute this report, we get….

...and nothing happens. Apart from our error message popping up.

…and nothing happens. Apart from our error message popping up.

Once again, a comparison of the before and after test tables should show that no usage has been recorded for AWR Report.

Conclusion

The Oracle supplied methods of disabling the Diagnostic and Tuning packs all have their limitations.
Turning the relevant features off in Enterprise Manager will only stop their use from within Enterprise Manager.
The same can be said of the control_management_pack_access parameter.
As we have seen, the License options in SQLDeveloper also has it’s limitations.

Whilst the synonyms approach, is not foolproof, it does offer significantly improved protection against accidentally using features for which you are not licensed.

Deb reckons that with the money this will save on licenses, I can afford to take her to the pictures.
Funny, I didn’t think that Star Wars 7 was out yet.

About these ads

9 thoughts on “Disabling the Oracle Diagnostic and Tuning Pack APIs – If you want something done, do it yourself

    • Fred,

      DBMS_AWR.DISABLE_AWR simply disables the background gathering of AWR stats which happens by default.
      What we are after here is a way to prevent access to any of the Diagnostic or Tuning pack components through calling their database objects.

      Mike
      HTH

      Mike

  1. Thanks for the information here. Do you know how to disable access to those packs in Oracle 10g. I’m using Oracle Standard Edition and I need to disable this.

    • Poli,

      I don’t think that there are too many changes between 10g and 11g in terms of the pack members.
      When you populate the PACK_MEMBERS table, the script should only pick up the views that exist in the database so that only those that exist in 10g should be created.
      I’d suggest that you then have a look at the Licensing documentation for 10g to double-check the packages that are part of these packs in 10g. You can find the docs on the Tahiti site at http://docs.oracle.com/cd/B19306_01/license.102/b14199/toc.htm.

      HTH

      Mike

  2. Hi Mike
    I’ve just read your articles about this Diagnostics pack madness.

    Do you have experience, does a real company in the real world care about disabling these features?
    Do you know any case when Oracle sued a company or revoked it’s DB license because it (accidentally or mistakenly) used these features without appropriate license?

    What can a company do if an employee accidentally runs an AWR report on an unlicensed production DB?

    • csadam,

      I do have experience of real companies in the real world who are keen to restrict access to these features.
      Almost without exception, these are companies which have been subject to significant invoices for back-dated license fees due to, often minor, uses of said features.
      This often has a disproportionate effect on smaller companies as, despite the fact that the components of these packages are included in every edition of the Oracle Database, they are considered to be options of Enterprise Edition only.
      Therefore, if it’s determined that these features have been used on Standard Edition, the company becomes liable not only for the cost of the Pack License, but for the back-dated upgrade from Standard Edition to Enterprise Edition.

      As for the options open to you if you discover an accidental usage of one of these packs…
      One point I would emphasise here is that, irrespective of whether you are using a database for production purposes or for development, testing, Disaster Recovery etc, they are equally subject to Oracle Licenses.

      I’m sure you can think of several technical options to resolve this issue. However, any such approach may be legally or morally questionable. For this reason, I would not presume to explore such alternatives in this answer.

      I think the first step would be to evaluate the potential financial impact of the breach.
      If the impact is likely to be substantial then you might consider taking advice from an Oracle License Specialist( NOTE – I am most definitely NOT one of these).
      Alternatively, it may even be worth contacting your Oracle Representative directly if you have a particularly strong relationship with them.
      I am aware of cases where Oracle have been prepared to cut a deal rather than exacting the full amount that they are technically due. Such deals have involved agreements to purchase licenses for additional Oracle Products.
      Apologies for the somewhat nebulous answer but, as you can appreciate, this subject is something of a minefield and I can only comment based on my own personal experiences.

      HTH

      Mike

  3. Thank you so much for sharing this. What I really do not understand is, that this is legal. Imho courts should stop this based on the fact that license terms are nontransparent to the customer an therefore legally void. There are built in traps in the license terms which obviously have a criminal intent.

    • Nils,
      not being a lawyer, I wouldn’t presume to comment on the legality or otherwise of the license.
      I must say however, that I personally do not believe that this has been conceived as some cunning plan by Oracle to fleece it’s customers.
      I remember when the Diagnostic and Tuning Packs first came out in 10g, there were rumours that the decision to make them additional cost options was made at the last minute, which would explain why the situation was so confused.
      Additionally, when I approached the SQLDeveloper Team at Oracle with the bug in the AWR report, it was immediately treated with the utmost priority and fixed in the subsequent release.
      Obviously, I share your frustration. However, I think this is more a case of “cock-up” than conspiracy.

      Mike

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s