Previous | Table of Contents | Next

Page 276

CAUTION
Do not attempt to modify the package body of any of the Oracle-supplied packages for recompilation.
You're liable to break something.

Making Sure the Packages Are Installed Correctly

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.

Hands-On with the Oracle-Supplied Packages

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.

Monitoring Programs with DBMS_APPLICATION_INFO

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.sql—Setting 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);
                                                                  continues
Page 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 279

As 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.

Recompiling Packages with DBMS_DDL

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.sql—Recompile 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

Previous | Table of Contents | Next