CAUTION |
Do not attempt to modify the package body of any of the Oracle-supplied packages for recompilation. |
You'll want to verify two things:
A user can check to see if they have EXECUTE privilege on the Oracle supplied packages with the following query:
SELECT table_name, grantee FROM all_tab_privs WHERE grantor='SYS' and privilege='EXECUTE' ORDER BY table_name; A typical response might be TABLE_NAME GRANTEE ------------------------------ ------------------------------ DBMS_APPLICATION_INFO PUBLIC DBMS_DDL PUBLIC DBMS_DESCRIBE PUBLIC DBMS_JOB PUBLIC DBMS_OUTPUT PUBLIC DBMS_PIPE PUBLIC DBMS_SESSION PUBLIC DBMS_SNAPSHOT PUBLIC DBMS_SPACE PUBLIC DBMS_SQL PUBLIC DBMS_STANDARD PUBLIC DBMS_TRANSACTION PUBLIC DBMS_UTILITY PUBLIC DBMS_REFRESH PUBLIC UTL_FILE PUBLIC
All of the Oracle-supplied packages should have been granted with EXECUTE to PUBLIC. I used the all_flavor of the data dictionary view to verify this. If any are missing that you intend to use, the DBA can run the appropriate script as SYS. If a package exists but is invalid, the DBA can recompile it with:
ALTER PACKAGE SYS.<name> COMPILE PACKAGE;
Where name is the name of the invalid package. The clause
COMPILE PACKAGE tells Oracle to recompile the package specification and body. If just the body needs to be recompiled,
instead run:
ALTER PACKAGE SYS.<name> COMPILE BODY;Page 277
CAUTION |
Some supplied packages may have dependencies on other packages. If you have to recompile a package header, this invalidates any dependent packages. Check again that other packages are still valid after recompiling. If only the body is recompiled, dependent packages are not invalidated. Any user-written stored subprograms and packages may also likely be affected in this manner. |
Now for the fun part! I'll illustrate some of the more interesting contents of each supplied
package with some reasonably simple and useful examples.
This package enables developers to add application tracking information in the views v$sqlarea and v$session. The kind of data that can be tracked is
Oracle doesn't do anything with this information; it's provided for the use of the DBA so he or she can run statistics against each application and component operations. Strings longer than what's supported are truncated. Listing 12.1 shows a simple example.
Listing 12.1 appinfo.sqlSetting and Reading Application Information
DECLARE module VARCHAR2(48); -- application info action VARCHAR2(32); client VARCHAR2(64); ldate VARCHAR2(30); -- to capture system date BEGIN DBMS_OUTPUT.enable; module := `CLAIM TRACKING'; action := `VERIFY ELIGIBILITY'; DBMS_APPLICATION_INFO.set_module(module, action); DBMS_APPLICATION_INFO.set_client_info(USER); DBMS_APPLICATION_INFO.read_module(module, action); DBMS_APPLICATION_INFO.read_client_info(client); DBMS_OUTPUT.put_line(client || ` is running ` || module || `: ` || action);
continuesPage 278
Listing 12.1 Continued
SELECT TO_CHAR(SYSDATE, `YYYY-MON-DD HH:MI:SS') INTO ldate FROM DUAL; END; / The response I get is SCOTT is running CLAIM TRACKING: VERIFY ELIGIBILITY While this session is still up, the DBA can check who is doing what with: COLUMN module FORMAT A20 COLUMN action FORMAT A20 COLUMN client_info FORMAT A20 SELECT client_info, module, action FROM v$session WHERE client_info IS NOT NULL; To get: CLIENT_INFO MODULE ACTION -------------------- -------------------- -------------------- SCOTT CLAIM TRACKING VERIFY ELIGIBILITY The DBA can then see the effects of everyone running this module and action with: SELECT sql_text, SUM(sharable_mem) smem, SUM(persistent_mem) pmem, SUM(runtime_mem) rmem, SUM(sorts) sorts, SUM(loads) loads, SUM(disk_reads) rdisk, SUM(buffer_gets) bget, SUM(rows_processed) prows FROM v$sqlarea WHERE module = `CLAIM TRACKING' AND action = `VERIFY ELIGIBILITY' GROUP BY sql_text; The results are pretty interesting: SQL_TEXT ------------------------------------------------------------------------------ SMEM PMEM RMEM SORTS LOADS RDISK BGET PROWS -------- -------- -------- -------- -------- -------- -------- -------- SELECT TO_CHAR(SYSDATE,'YYYY-MON-DD HH:MI:SS') FROM DUAL 4267 508 792 0 1 0 4 2 SELECT USER FROM SYS.DUAL 3596 508 688 0 1 0 4 1 begin dbms_output.get_lines(:lines, :numlines); end; 4317 592 420 0 1 0 0 1
The only query I explicitly made was the first one, but SQL*Plus apparently does some work behind the scenes.
Page 279As a module performs different actions, the developers should make the appropriate calls to set_action() to reflect this. In this manner, you can collect some interesting statistics on how badly an application is battering the server by query, by action, by module, by user, or by groups of users.
NOTE |
Even after a user disconnects, entries still exist in v$sqlarea until the related SQL gets aged out of the SGA. |
There are precisely two things you can do with this package:
A simple use for alter_compile() would be to find what stored program objects are invalid and then recompile them. Listing 12.2 illustrates this.
Listing 12.2 recompil.sqlRecompile Invalid Program Objects Only
-- recompile invalid stored program objects -- CAVEAT: does not take package dependencies -- into account! DECLARE CURSOR invalid_prog_obj IS SELECT object_name, object_type FROM user_objects WHERE status = `INVALID'; rec invalid_prog_obj%ROWTYPE; status NUMERIC; BEGIN DBMS_OUTPUT.enable; OPEN invalid_prog_obj; LOOP -- recompile each stored program object FETCH invalid_prog_obj INTO rec; EXIT WHEN invalid_prog_obj%NOTFOUND; DBMS_OUTPUT.put(`Recompile ` || rec.object_type || ` ` || rec.object_name); DBMS_DDL.alter_compile(rec.object_type, NULL, rec.object_name); DBMS_OUTPUT.put_line(` SUCCESSFUL'); -- recompile succeeded END LOOP; -- invalid program objects CLOSE invalid_prog_obj; EXCEPTION WHEN OTHERS THEN BEGIN status := SQLCODE; DBMS_OUTPUT.put_line(` FAILED with ` || SQLERRM(status)); IF (invalid_prog_obj%ISOPEN) THEN CLOSE invalid_prog_obj; END IF;
continues