The Anti-Kyte
ORA-02030 and invisible objects. The database bites back !
Being Luis Suarez’s agent must be an interesting job right now.
Maybe the man was a bit peckish.
Alternatively, maybe he’s resigned to the FA’s reluctance to introduce a mid-season break and was simply making his own arrangements for time off during the season.
Either way, this particular agent may well be trying to sign Luis up for an ad campaign for a popular brand of toothpaste.
Oracle DBA’s may sometimes have some sympathy with Suarez, although they’re more likely to end up chewing the desk in frustration, rather than their fellow DBA’s (unless the Christmas Party has really gotten out of hand).
Every so often, Oracle throws out an error that, on the face of it, makes absolutely no sense…
Let’s start by creating an ordinary, every-day user – not quite a dba :
GRANT connect, resource TO nqdba IDENTIFIED BY pwd;
At this point, as you’d expect, this user doesn’t have access to very much :
SQL> desc dba_tables ERROR: ORA-04043: object "SYS"."DBA_TABLES" does not exist SQL> desc v$instance ERROR: ORA-04043: object "SYS"."V_$INSTANCE" does not exist
It’s probably worth noting that the public synonym is being referenced for V$INSTANCE.
Although the describe is on V$INSTANCE, the error is about V_$INSTANCE. The relevance of this will become apparent shortly.
For now though, our hard-pressed DBA may decide to solve this problem for the user by doing the following :
GRANT SELECT ON dba_tables TO nqdba / Grant succeeded.
…and now for V$INSTANCE…
GRANT SELECT ON v$instance TO nqdba
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
That’s interesting, the grant on DBA_TABLES works no problem. However, V$INSTANCE is having none of it.
Also, the error is on V$INSTANCE – i.e. the synonym, rather than V_$INSTANCE, the underlying object.
I wonder if there’s an object other than the synonym with this name …
SQL> SELECT owner, object_type 2 FROM dba_objects 3 WHERE object_name = 'V$INSTANCE' 4 / OWNER OBJECT_TYPE ------------------------------ ------------------- PUBLIC SYNONYM SQL>
So, there’s apparently nothing apart from the synonym.
To eliminate the synonym as the cause of our problem, let’s try the following :
CREATE PUBLIC SYNONYM sparkly_white FOR v$instance / GRANT SELECT ON sparkly_white TO nqdba / Grant succeeded. SQL>
So, granting via a synonym that’s NOT V$INSTANCE works fine. Therefore, there must be an object owned by SYS that is also called V$INSTANCE.
Them Dynamically Fixed thingysThe error states that we can only select from “fixed tables/views”. The syntax here is interesting.
V$ views are more properly referred to as Dynamic Performance Views. These views are based on what are known as fixed tables. These tables are essentially representations of C structs deep in the Oracle Kernel.
Let’s see what the V_$INSTANCE view is actually based on…
set long 5000 SELECT text FROM dba_views WHERE owner = 'SYS' AND view_name = 'V_$INSTANCE';
…run this and we get…
select "INSTANCE_NUMBER","INSTANCE_NAME","HOST_NAME",
"VERSION","STARTUP_TIME","STATUS",
"PARALLEL","THREAD#","ARCHIVER",
"LOG_SWITCH_WAIT","LOGINS","SHUTDOWN_PENDING",
"DATABASE_STATUS","INSTANCE_ROLE","ACTIVE_STATE",
"BLOCKED","EDITION"
from v$instance
which is extremely confusing. The view is apparently pointing back to the synonym ( which after all, is the only object that we can find with that name in DBA_OBJECTS).
At this point, we give up on the conventional data dictionary views and dive into the twighlight world of V$FIXED_VIEW_DEFINITION. We should find the true view statement here, with luck…
SELECT view_definition FROM v$fixed_view_definition WHERE view_name = 'V$INSTANCE' /
Finally, we can see that there is actually an object called v$instance apart from the synonym, although this query yields the scarcely-more-helfpul…
SELECT INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME ,
VERSION , STARTUP_TIME , STATUS ,
PARALLEL , THREAD# , ARCHIVER ,
LOG_SWITCH_WAIT , LOGINS , SHUTDOWN_PENDING,
DATABASE_STATUS, INSTANCE_ROLE, ACTIVE_STATE,
BLOCKED, EDITION
FROM GV$INSTANCE
WHERE inst_id = USERENV('Instance')
If we now perform the same check to find out what GV$INSTANCE is pointing at, the results are a bit more revealing :
select ks.inst_id,ksuxsins,ksuxssid,
ksuxshst,ksuxsver,ksuxstim,
decode(ksuxssts,0,'STARTED',1,'MOUNTED',2,'OPEN',3,'OPEN MIGRATE','UNKNOWN'),
decode(ksuxsshr,0,'NO',1,'YES',2,NULL),ksuxsthr,
decode(ksuxsarc,0,'STOPPED',1,'STARTED','FAILED'),
decode(ksuxslsw,0,NULL,2,'ARCHIVE LOG',3,'CLEAR LOG',4,
'CHECKPOINT', 5,'REDO GENERATION'),
decode(ksuxsdba,0,'ALLOWED','RESTRICTED'),
decode(ksuxsshp,0,'NO','YES'),
decode(kvitval,0,'ACTIVE',2147483647,'SUSPENDED','INSTANCE RECOVERY'),
decode(ksuxsrol,1,'PRIMARY_INSTANCE',2,'SECONDARY_INSTANCE','UNKNOWN'),
decode(qui_state,0,'NORMAL',1,'QUIESCING',2,'QUIESCED','UNKNOWN'),
decode(bitand(ksuxsdst, 1), 0, 'NO', 1, 'YES', 'NO'),
decode(ksuxsedition, 1, 'PO', 2, 'SE', 4, 'EE', 8,'XE', 'UNKNOWN')
from x$ksuxsinst ks, x$kvit kv, x$quiesce qu
where kvittag = 'kcbwst'
Finally, we can see that V$INSTANCE is ultimately referencing fixed tables.
The solutionThe solution to this problem is simple enough…well, simple enough when you know it. Simply grant the privilege on the underlying view. So…
GRANT SELECT ON v_$instance TO nqba / Grant succeeded.V$FIXED_TABLE
At this point you may be thinking that it would be really useful if there was a list of those dynamic/fixed view thingys available. Well, as you’re a proper DBA, you probably wouldn’t use the term thingys, but I know where you’re coming from…
SELECT name, type FROM v$fixed_table WHERE name = 'V$INSTANCE' /
There you go. If you’re trying to grant select on any of the tables listed in V$FIXED_TABLE, you’ll more than likely hit this particular error. Additonally, if the table is listed here, it means that you should be able to still see it even if the database itself is not opened.
To demonstrate, connect to an idle instance. If you’re running XE, simply shutdown the database then issue the following command at the prompt :
sqlplus /nolog
This will enable the prompt but you won’t be connected to the database. To connect :
conn sys as sysdba
… and supply the password when prompted.
You will now get the message :
Connected to an idle instance
NOTE – an alternative way to do this on linux would be to switch to the oracle owner :
sudo su oracle sqlplus / as sysdba
So, you’re connected to an idle instance. It hasn’t just been banned for 10 matches for being a bit bitey, it’s idle because it’s not been started.
Now mount the database ( at this point it will still not be properly started – i.e. started and open) :
startup mount
Now we can see that the database is not currently mounted, after all, you can’t query anything…
SELECT * FROM dba_tables
*
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only
SQL>
…except for…
SQL> SELECT instance_name, status 2 FROM v$instance; INSTANCE_NAME STATUS ---------------- ------------ XE MOUNTED SQL>
Just as useful, you can still see V$FIXED_TABLE when the database is mounted ( or unmounted for that matter), so you can always check to see which tables you can query.
Incidentally, once you’ve finished playing, you can either shutdown the database again :
shutdown
…or open it…
ALTER DATABASE OPEN /
Deb has expressed her utter bafflement as to why a footballer would bite a fellow player. Then again, that’s not too surprising…she is a vegitarian after all.
Filed under: Oracle, SQL Tagged: grant select on v$ views, ORA-02030, synonyms, v$fixed_table, v$fixed_view_definition, v$instance, v_$instance
Customising the Oracle XE Menu in XFCE – why it’s better to be vegetarian
Roberto Goldbrick. The name of the central character in a biting satire about a Premiership footballer ? Actually, it’s the name of the horse I drew out in the Office Grand National Sweep Stake.
“Oh well”, said Deb as the winner crossed the line with Roberto nowhere to be seen, “by next week it’ll be a value frozen lasagne”.
That’s the thing about vegetarians, they do like to assert their moral superiority at times. It can be quite difficult to find a suitable riposte. After all, you feel a bit of a twit accusing someone of vegicide.
In order to overcome my disappointment, I’ve taken refuge in Mint 14 XFCE running on my netbook.
Using the steps here and here I’ve managed to install Oracle 11gXE without any problems….apart from the fact that the Menu items now appear on the Others menu.
Being a lightweight desktop, XFCE doesn’t provide a default GUI to enable menu editing, so I’ve had to do a bit of investigation…
In order to get my Oracle Menu, together with the correct entries, there are three types of file I need to deal with.
desktop filesEach menu item has it’s own .desktop file. These can be found in /usr/share/applications.
To see all the files that were created as part of the XE installation:
cd /usr/share/applications ls oracle*.desktop
You should see…
oraclexe-backup.desktop oraclexe-registerforonlineforum.desktop oraclexe-getstarted.desktop oraclexe-restore.desktop oraclexe-gettingstarted.desktop oraclexe-runsql.desktop oraclexe-gotoonlineforum.desktop oraclexe-startdb.desktop oraclexe-readdocumentation.desktop oraclexe-stopdb.desktop
If we have a look at the contents of the oraclexe-startdb.desktop file :
[Desktop Entry] Exec=/u01/app/oracle/product/11.2.0/xe/config/scripts/startdb.sh Terminal=false MultipleArgs=true Type=Application Categories=Applications; Icon=oraclexe-startdatabase.png MimeType=Application/database Encoding=UTF-8 Name=Start Database Name[pt_BR]=Iniciar Banco de Dados Name[ja]=データベースの起動 Name[zh_CN]=启动数据库
Most of that is fairly straight forward. The file details the script that should be executed, whether a Terminal should be opened, the icon to display on the menu etc.
The menu it needs to appear on? That’s a different story.
These files act as containers for the .desktop files within the Menu structure. They should all be in /usr/share/desktop-directories.
The Oracle menu however, has been created in /usr/share/desktop-menu-files.
Before we start moving it around, it’s probably worth having a look at it’s contents (the filename is oraclexe-11g.directory):
[Desktop Entry] Icon=oraclexe-11g.png Type=Directory Encoding=UTF-8 Name=Oracle Database 11g Express Edition Name[pt_BR]=Oracle Database 11g Express Edition Name[ja]=Oracle Database 11g Express Edition Name[zh_CN]=Oracle Database 11g Express Edition
On it’s own, it doesn’t really move the story on much, save for the fact that it is defined as being of type Directory.
Finally, however, we get to the heart of the menu system.
The Menu fileThe file /usr/share/xfcemint/xfce-applications.menu is where all of the magic happens. This is an XML file which controls the contents of the Menu itself. Let’s take a look at the definition for the System menu :
<Menu>
<Name>System</Name>
<Directory>xfce-system.directory</Directory>
<Include>
<Or>
<Category>Emulator</Category>
<Category>System</Category>
</Or>
</Include>
<Exclude>
<Or>
<Filename>Thunar.desktop</Filename>
<Filename>xfce4-session-logout.desktop</Filename>
</Or>
</Exclude>
</Menu>
We can see that the entry references the appropriate .directory file.
We can also see that the menu can include either categories of .desktop files or specifically named files.
OK, so in this case, the named files are specifically excluded from the menu, but the principle should be sound.
There would appear to be two ways to do this. The first is to specify the files to include on the menu explicitly. The second is to edit the .desktop files to specify a category which we can then include in the Oracle menu by means of the tags in the .desktop and .menu files.
Either way, the first thing we need to do is to copy the oracle .directory file to the correct location :
sudo cp /usr/share/desktop-menu-files/oraclexe-11g.directory /usr/share/desktop-directories/.Option 1 – specify the files
We now need to add an entry into the xfce-applications.menu. OK, strictly speaking, we now need to backup the xfce-applications.menu file, just in case our XML skills aren’t quite as good as we thought they were :
sudo cp /usr/share/xfcemint/xfce-applications.menu xfce-applications.bak
Now, let’s add in the Oracle menu :
<Menu>
<Name>Oracle Database 11g Express Edition</Name>
<Directory>oraclexe-11g.directory</Directory>
<Include>
<Filename>oraclexe-startdb.desktop</Filename>
<Filename>oraclexe-stopdb.desktop</Filename>
<Filename>oraclexe-runsql.desktop</Filename>
<Filename>oraclexe-backup.desktop</Filename>
<Filename>oraclexe-restore.desktop</Filename>
<Filename>oraclexe-gettingstarted.desktop</Filename>
<Filename>oraclexe-readdocumentation.desktop</Filename>
<Filename>oraclexe-registerforonlineforum.desktop</Filename>
<Filename>oraclexe-gotoonlineforum.desktop</Filename>
<Filename>oraclexe-getstarted.desktop</Filename>
</Include>
</Menu>
NOTE – I added this above the entry for the Others menu, which seems to be a catch-all for anything that doesn’t fit in any of the menus previously defined in the file.
Save the change, now go back to the desktop and fire up the Menu…
Well, that seemed to work quite well. However, if we wanted to add another item to the menu, we would need to go and edit xfce-applications.menu all over again.
A more dynamic approach would be to use a Category. This would mean that any new .desktop files of the appropriate category would automatically be included.
To do this, we’ll need to add the category to both our existing desktop files and to the menu definition in xfce-applications.menu.
Let’s call the category XE.
First of all, we need to change the appropriate desktop files. Time for a small script…save_category.sh
#!/bin/sh # # Script to add a category to all of the oracle .desktop files # for dtfile in $(ls /usr/share/applications/oraclexe-*.desktop) do cp $dtfile $dtfile.bak sed -i 's/Categories=Applications;/Categories=Applications;XE/' $dtfile done exit 0
We need to run this using sudo as we don’t have permissions on these files…
sudo sh change_category.sh
If we have a look at the .desktop files now, we should see that the category tag has been changed and the category XE added :
[Desktop Entry] Exec=/u01/app/oracle/product/11.2.0/xe/config/scripts/startdb.sh Terminal=false MultipleArgs=true Type=Application Categories=Applications;XE Icon=oraclexe-startdatabase.png MimeType=Application/database Encoding=UTF-8 Name=Start Database Name[pt_BR]=Iniciar Banco de Dados Name[ja]=データベースの起動 Name[zh_CN]=启动数据库
Now we need to go back to the menu and replace the hard-coded filenames with the new category :
<Menu>
<Name>Oracle Database 11g Express Edition</Name>
<Directory>oraclexe-11g.directory</Directory>
<Include>
<Category>XE</Category>
</Include>
</Menu>
Now, when we hit the big shiny menu button…you get exactly the same result.
If you want to do more than just get your Oracle XE stuff onto the menu, there’s a really useful howto on the XFCE wiki.
As for me, domestic drudgery calls. Looks like I’m going to have to ruthlessly skin some defenceless potatoes.
Filed under: Linux Tagged: bash for loop, desktop files, directory files, oracle xe on xfce mint, xfce, xfce-applications.menu
Facial Hair and Funky Job Titles – A Personal History of the Internet Age
I had a rare technical issue with my blog recently. My e-mailed cry for help was answered and my problem resolved. The helpful soul at WordPress who aided me in my time of need was Jackie, who rejoices in the title of Hapiness Engineer.
I’d imagine that WordPress must have a novel and somewhat refreshing approach to Job titles.
We could apply this to Deb, for example. As a Health Professional working with the under fives, she could be a Small Human Maintenance Consultant.
There are some similarities between Deb’s job and my own. Whilst I have on occasion dealt with people whose job titles include words like ‘Chief’ and ‘Officer’, I also occasionally have to deal with the aftermath of toys being violently ejected from prams.
At the moment however, I find myself leading a team, all of whom are rather younger than me. This presents a number of challenges.
The realisation that I have children that are older than some of my colleagues is particularly acute when we discuss technological issues.
In computing terms, I grew up before the Internet was the all-pervasive presence it is now.
I learned to touch-type many years ago, in order to reduce the number of typos I made whilst writing code.
This is beginning to feel somewhat quaint with the proliferation of declarative development environments and technologies.
I suppose the point of this post is to wander back through the technologies that influenced my career as a programmer and explain why they are so important in shaping the technological landscape we now operate in.
Working at Bell Labs in the early 70s must’ve been something special. Whilst not chronologically first on my list, the C language takes it’s place in the pantheon.
40 years on, Dennis Ritchie‘s masterpiece, comprising a mere 32 keywords, has provided the basis for most of the mainstream languages that have followed ( C++, Java, C#, take your pick).
Providing a viable alternative to Assembler meant that C was portable. Provided you could write a compiler for the hardware you wanted to use, you could run C programs on it.
Not content with that, Kernaghan and Ritchie then produced the seminal C Programming Language manual.
Not only did this introduce the world to the much imitated “hello world” program, it served as a de-facto standards reference for the language into the 1990s.
C may no longer be what all the cool kids are using, but it’s influence is still all pervasive. To take a not entirely random example…
UnixWhilst it slightly pre-dates C, Ken Thompson‘s Unix got it’s big break when re-written in the language in the early 70s.
This made it as portable as C itself, thus opening the way for a move away from mainframes and onto more affordable hardware.
Today Unix, in all it’s variations, remains the dominant operating system in the datacentre.
The multi-user, multi-tasking OS is on the back-end of a large chunk of the World Wide Web.
For me, it was the first OS that I really got to grips with. The diversity of tools and flexibility of being able to write utility scripts quickly and simply is still a joy…especially to anyone who has been subjected to DOS (and later Windows) batch scripting.
At this point it’s worth stopping for a moment and considering that Ritchie, Thompson and Kernaghan were all colleagues at Bell Labs. As Dream Team’s go, this is the programming equivalent of having Pele, Cruyff and Maradona playing up front (but with less arguing over who takes the free-kicks).
A major reason for Unix’s current ubiquity is….
GNU/LinuxLinus Torvalds had developed a version of unix that would run on standard PC hardware, but had no tools written for it.
Richard Stallman‘s GNU project had a set of tools in need of an Operating System to run on.
Put the two together and….
Linux is now the OS of choice in a sizeable percentage of the world’s Server rooms.
There’s also a better than even chance that Linux is at the heart of your smart-phone.
In the same way that Unix opened up the choice of hardware for servers, Linux paved the way for running Enterprise class servers on much cheaper kit than had hitherto been available.
The other significant contribution that Linux has made to computing history is that is has served as the platform that really kick-started and then sustained the open source movement.
The advent of the “gift economy” where code is developed and made available freely, has resulted in some of the best software available over the last 20 years.
Meanwhile, somewhere in Switzerland….
HTML and HTTPPerhaps the most profound example of building on pre-existing technology to create something truly life changing can be found in the work of Tim Berners-Lee.
Take SGML – the root of all Markup Languages and use it to create a means of linking between documents.
To serve the HTML pages, write an extension to the File Transfer Protocol (FTP).
Put them together and …. long before the Large Hadron Collider was commissioned CERN was itself the source of a Big Bang.
You knew we’d get here eventually.
When Oracle version 2.0 was released onto the market in 1979, it became what is commonly credited as the first commercially available relational database.
Larry Ellison and friends were onto a winner.
Fast forward through the introduction of PL/SQL ( in Forms 3.0 in 1988) and data driven web applications (powered by a selection of RDBMS’s) and Oracle is now busy trying to take over the world.
In the meantime, it’s given me a platform on which to make a career. So, in this list at least ( i.e. my list), it certainly deserves it’s place.
You can find a timeline of the company’s history on their website.
Incidentally, whilst reading up on the history of Oracle, I came across the name of Bob Miner – Oracle’s lead engineer in it’s early days.
Obviously a brilliant software engineer (he’s credited with writing most of Oracle 3), Miner was also renound for looking after the people he managed.
In his Wikipedia entry, Larry Ellison is quoted as saying that Miner was “loyal to the people before the company”.
Wouldn’t you like to work for someone like that ?
The book case that Deb has ordered is just being delivered. Time for me to end my wander down memory lane and return to my role as Furniture Assembly Drone and Feng Shue Vision Implementer ( Deb has the vision and I just move the furniture around).
Filed under: Uncategorized Tagged: Bob Miner, c programming language, GNU Linux, html, http
How long is a (piece of) String. Cricketing Greats and the length function
My Dad gave me a book recently – the 100 Greatest Cricketers. As well as selecting what – in his opinion at least – were the 100 finest exponents of the game, the author also decided to rank them in order from 1 to 100.
At this point, for those who don’t know, I should perhaps explain that Cricket is one of those games given by the English to the rest of the world….to prove what sporting losers the English could be.
For any Australians reading, to whom this statement may ring hollow given their teams current travails, fret not. Historically, England’s brief ascendancy the battle for the Ashes tends to come to a juddering halt when least expected, usually in the form of a 4-0 thrashing ( think 1958-59, 1989).
Anyway, back to the book. Comparing players across different eras is hard enough – just how would you evaluate the relative merits of Sachin Tendulkar and Sir Jack Hobbs, for example ?
But comparing the relative merits of a batsmen and bowler who were contemporaries in the same team is equally problematic. Who is the greater cricketer out of Dennis Lillee and Greg Chappell, or Malcolm Marshall and Viv Richards ?
All of this brings to mind the saying, “how long is a piece of string”.
In SQL, at least, we do have an answer to this question…or do we ?
Simple enough really, if you want to know the length of a string, you just need to do something like this (Fred Trueman would approve):
SELECT LENGTH('line')
FROM dual;
All as expected. Obviously, if the string you pass in is NULL, then LENGTH will return 0…or will it ?
Time for a cricketing themed example…
CREATE TABLE greats(
first_name VARCHAR2(50),
last_name VARCHAR2(50),
sobriquet VARCHAR2(100))
/
INSERT INTO greats( first_name, last_name, sobriquet)
VALUES( 'SACHIN', 'TENDULKAR', 'Little Master')
/
INSERT INTO greats( first_name, last_name, sobriquet)
VALUES( 'JACK', 'HOBBS', 'The Master')
/
INSERT INTO greats( first_name, last_name, sobriquet)
VALUES('RAHUL', 'DRAVID', 'The Wall')
/
INSERT INTO greats( first_name, last_name, sobriquet)
VALUES('VICTOR', 'TRUMPER', NULL)
/
INSERT INTO greats( first_name, last_name, sobriquet)
VALUES('DONALD', 'BRADMAN', 'The Don')
/
INSERT INTO greats( first_name, last_name, sobriquet)
VALUES('FREDERICK', 'SPOFFORTH', 'The Demon')
/
COMMIT;
Not quite sure why Victor Trumper never got his own nickname. By all accounts he was a modest man, but a genius of a cricketer….rather like a lot of the other players in our table. Anyway, this does at least give us the chance to observe what happens when we do this :
SQL> SELECT last_name, LENGTH(sobriquet) 2 FROM greats; LAST_NAME LENGTH(SOBRIQUET) ------------------------------ ------------------ TENDULKAR 13 HOBBS 10 DRAVID 8 TRUMPER BRADMAN 7 SPOFFORTH 9 6 rows selected. SQL>
Unlike the COUNT function, LENGTH is not always guaranteed to return an Integer. If the string passed in is NULL, then it will return NULL.
To avoid being tripped up by this, you may well therefore consider the following to be prudent :
SELECT last_name,
NVL(LENGTH(sobriquet),0)
FROM greats;
I had almost managed to get all the way through this without mentioning England’s latest demonstration of grace in defeat….but Deb has just walked in and “reminded” me about the rugby last week. The Welsh have never been bashful about winning.
Filed under: Oracle, SQL Tagged: length, NVL
Disabling the Oracle Diagnostic and Tuning Pack APIs – If you want something done, do it yourself
At last, we have reached the final episode of the Star Wars themed odyssey through the tangled web that is Oracle’s Diagnostic and Tuning Pack licensing.
Just as well really, Deb has flatly refused to give over any more evenings to my “research” – i.e. re-watching all of the films. Even the appeal of Ewan MacGregor’s Alec Guiness impression has now waned.
Just to recap then, so far I’ve looked at :
- 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.
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.
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 NotesAt 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 ApplicationThe 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 schemaThe 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 SynonymsWe’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.
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 columnsIf 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 ReportAs 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 SQLDeveloperUnless 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.
ConclusionThe 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.
Filed under: Oracle, PL/SQL, SQL Tagged: CONTROL_MANAGEMENT_PACK_ACCESS, create or replace public synonym, dbms_addm, dbms_advisor, DBMS_FEATURE_USAGE_INTERNAL.EXEC_DB_USAGE_SAMPLING, DBMS_MANAGEMENT_PACKS, dbms_sqltune, dbms_workload_replay, dbms_workload_repository, Diagnostic and Tuning Pack, diagnostic pack, Disable Diagnostic and Tuning Packs, public synonyms, REGEXP_INSTR, REGEXP_REPLACE, SQLDeveloper Last AWR Report, tuning pack
SQLDeveloper and a very expensive query
The magic of the FA Cup. It’s one of those cliches that you are brought up to believe as an article of faith if you’re English.
It is supposed to refer to the glorious unpredictability in a knockout cup competition where there is no seeding.
In recent years, it’s become a lazy journalist’s phrase. An attempt to sensationalise a result that, usually, isn’t that surprising.
In this year’s fourth round, however, Non-league Luton Town went to Premier League Norwich and won, 1-0.
The first time in 24 years that a top-flight club has been knocked out by one outside of the Football League has taken it’s toll.
Simon, life-long Luton fan, now has some very achy face muscles as a result of walking around with a huge grin on his face for the last week.
At this point, I could try to relate this sporting miracle back to the Star Wars theme that’s been running through this series of posts on Oracle Licensing, but it’s a bit difficult.
Luton, plucky underdog rebels. Norwich City the Evil Empire…I just can’t really see it.
Delia Smith as Palpatine with Chris Hughton as Darth Vader ? Somehow it just doesn’t seem to work.
Anyway, back to the license stuff. So far, we’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
This time, we’re going to turn our attention to SQLDeveloper and of the ways in which it attempts to allow users to avoid using the Diagnostic and Tuning Packs.
Disabling the Diagnostic and Tuning Packs – the documented approachesThe Oracle licensing documentation itself makes mention of two ways of “disabling” these Packs.
Oracle Enterprise Manager has options available to turn off Diagnostic and Tuning Pack features. It appears that setting these will prevent Enterprise Manager itself from using them.
Then, there is the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter, available from 11g. Setting this to NONE should avoid any standard Oracle jobs/processes from using the Diagnostic and Tuning Pack features in a way that will register in the feature usage checks.
SQLDeveloper offers something that is ostensibly similar to the OEM options.
The SQLDeveloper IDE has come on in leaps and bounds in terms of the functionality offered.
This additional functionality has also brought with it a mechanism to safeguard against inadvertant usage of the Diagnostic and Tuning Packs from within the tool. However, this is not entirely foolproof…
To start with, lets take a look at SQLDeveloper’s Database Licensing screen.
Open SQLDeveloper and go to the Tools Menu and select Preferences.
Under the Database node in the tree, select Licensing.
For each Connection you have defined you’ll see three check boxes …
According to the SQLDeveloper help, the default value of these settings ( i.e. filled boxes) means that
it is unknown whether the relevant packs are licensed on the database.
To make sure that SQLDeveloper knows that these are NOT licensed, change the checkboxes to be blank…
Look Mum, no packs !
Now click OK to save.
Once again, to make sure that the new settings are effective, shutdown and re-start SQLDeveloper.
Then go and review the settings.
All looking good so far.
At this point, we just need to check a couple of things. First, we need to make sure that the
database initialization parameter is set correctly :
SELECT value FROM v$parameter WHERE name = 'control_management_pack_access' /
The query should return ‘NONE’.
The second step is to make sure that we have no detected usages of AWR Report on the database :
SELECT currently_used, detected_usages, first_usage_date FROM dba_feature_usage_statistics WHERE name = 'AWR Report' / CURRENTLY_USED DETECTED_USAGES FIRST_USAGE_DATE -------------- --------------- ---------------- FALSE 0
To make sure that no usage has been detected since the last usage audit run, we can do a double-check using
the code specified in $WRI_DBU_FEATURE_USAGE_METADATA for AWR Report. NOTE – unless you’re running as SYS, you’ll need to add the schema name to the tables in this query (as I have here) :
with last_period as
(select * from sys.wrm$_wr_usage
where upper(feature_type) like 'REPORT'
and usage_time >= (select nvl(max(last_sample_date), sysdate-7) from sys.wri$_dbu_usage_sample) )
select decode (count(*), 0, 0, 1),
count(*),
feature_list
from last_period,
(select substr(sys_connect_by_path(feature_count, ','),2) feature_list
from
(select feature_count,
count(*) over () cnt,
row_number () over (order by 1) seq
from
(select feature_name || ':' || count(*) feature_count
from last_period
group by feature_name)
)
where seq=cnt
start with seq=1
connect by prior seq+1=seq)
group by feature_list
/
Provided this query returns no rows, then we’re good to go.
Now, SQLDeveloper has a number of useful reports bundled with it. These are available on the Reports tab.
If this isn’t visible, then you can open it by going to the View menu and selecting Reports.
Under the Data Dictionary Reports, there is a sub-folder containing ASH and AWR reports.
Hmmm, should I press the big red button ?
If you now run the Last AWR Report, the output is likely to be fairly light on detail. However, if you look at the sourcecode for this query, by hitting the
Run Report in SQL Worksheet button, you can see that the sourcecode looks like this :
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;
OK, so DBA_HIST_SNAPSHOT is one of those Diagnostic Pack views, but the SQLDeveloper settings and inititialization parameter should have protected
us from any accidental usage of the Diagnostic Pack shouldn’t it ?
Let’s re-run the detection query to check…
with last_period as
(select * from sys.wrm$_wr_usage
where upper(feature_type) like 'REPORT'
and usage_time >= (select nvl(max(last_sample_date), sysdate-7) from sys.wri$_dbu_usage_sample) )
select decode (count(*), 0, 0, 1),
count(*),
feature_list
from last_period,
(select substr(sys_connect_by_path(feature_count, ','),2) feature_list
from
(select feature_count,
count(*) over () cnt,
row_number () over (order by 1) seq
from
(select feature_name || ':' || count(*) feature_count
from last_period
group by feature_name)
)
where seq=cnt
start with seq=1
connect by prior seq+1=seq)
group by feature_list
/
DECODE(COUNT(*),0,0,1) COUNT(*) FEATURE_LIST
---------------------- ---------- --------------
1 1 AWR Report:1
Oh. Despite our best efforts, it only takes one curious soul playing with SQLDeveloper reports and the DBA is going to have his or her work cut-out.
If you want to calculate the cost of this query, you might want to consider an alternative to Explain Plan…. the Oracle Store.
Incidentally, if you want to do any conversion, the dollar exchange rate for stirling is currently around 1.6….and you can get a season ticket for Luton for around £300.
Let’s assume that the database on which you’ve encountered this problem is Standard Edition running on 4 quad-core CPUs.
Because it’s standard edition, you’re licensed per socket rather than per core.
So, on current prices you’d have paid £46920 for your licenses ( £11730 per processor) and would expect an annual maintenance fee in the region of £10323.
The problem is, due to your usage of the Diagnostic Pack, you now become liable for
- Upgrading your licenses to Enterprise Edition
- purchasing the license for the Diagnostic Pack
Remember, the Diagnostic Pack is only available as an option on Enterprise Edition.
OK, here goes…
Enterprise Edition processor licenses are based on the number of cores rather than the number of physical CPUs. For most CPU types, the calculation works out to one core being the equivalent of 0.75 processors.
Our box has 16 cores ( 4 per CPU), so this will work out to 12 Processor Licenses.
An Enterprise Edition per Processor License is £31839.
Twelve of those comes to £382068.
To rub a bit of salt into the wound, the first year support cost is a shade over £7000 per processor ( £84000).
The Diagnostic Pack comes in at £3352.00 per processor with a first year support cost of £737.33. Both figures are per processor.
For the sake of simplicity ( and for any wild optimists reading this), we’ll assume that you can offset the purchase cost of your existing database licenses against the new Enterprise Edition licenses.
Even then, the numbers are frighteningly big…
Enterprise Edition Database (12 @ £31839) £382068
plus First Year Support ( 12@ £7004.64) £84055.68
Diagnostic Pack ( 12@ £3352) £40224.00
plus First Year Support (12 @ £737.33) £8847.96
Sub-Total £514795.64
Less Standard Edition Licenses (4 @ 11730) – £46920
Less Standard Edition Support ( 4 @ 2580.86) -£10323.
Total due to Oracle : £457,552.64.
As you’re likely to have a similar spec Development server ( and possibly even a DR server) runnng clones of this database, you’ll need to double or even triple this figure. Yep it’s likely to be per server.
In Oracle terms then, we can probably conclude that…
SELECT output FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(dbid,instance_id,start_id,end_id)) /
…is a million dollar question.
Coming Soon…A New HopeTo be fair to SQLDevelepor, this particular problem is not directly of the tool’s own making ( although closing this particular loophole would be greatly appreciated).
The Licensing of the Diagnostic and Tuning Packs has been a running sore since Oracle released the 10g database in 2003. Yes, 10 years later and we’re still looking for a solution.
In the next ( and final) post in this particular saga, I’ll take matters into my own hands in an attempt to make things a little easier all round.
I’ll even dispense with the Star Wars references alltogether (maybe).
In the meantime, I’m off to the SQLDeveloper forum…”Help me Jeff Smith, you’re my only hope… “
Filed under: Oracle, SQL, SQLDeveloper Tagged: $WRI_DBU_FEATURE_USAGE_METADATA, CONTROL_MANAGEMENT_PACK_ACCESS, DBA_FEATURE_USAGE_STATISTICS, Diagnostic and Tuning Pack, Oracle Database Licensing, SQLDeveloper Licensing settings, v$parameter
