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 :
- Oracle Licensing for the various Database Editions
- How Oracle audits features used on the database
- The components that comprise the Diagnostic and Tuning Pack APIs
- How SQLDeveloper can be quite expensive
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 !
When we execute this report, we get….
…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.
