Oracle | DBMS_ACTIVITY

Oracle DBMS_ACTIVITY Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose This package is part of the Object Activity Tracking System (OATS).

AUTHID DEFINER

Dependencies

ACTIVITY_CONFIG$	ACTIVITY_TABLE$	DBMS_SYS_ERROR
ACTIVITY_MVIEW$	DBMS_OATS_LIB	DUAL
ACTIVITY_SNAP_META$	DBMS_OUTPUT	OBJ$

Documented Yes: Packages and Types Reference

First Available 20c

Pragmas: Note from Oracle "Bug 31493665: Logical standby pragma default is AUTO_WITH_COMMIT. We want to replay these routines at the PL/SQL level, not at the DML level, since snap_id's and obj#'s may be different."

PRAGMA SUPPLEMENTAL_LOG_DATA(default, AUTO_WITH_COMMIT);

Security Model Owned by SYS with EXECUTE granted to the DBA role.

Source {ORACLE_HOME}/rdbms/admin/dbmsoats.sql
{ORACLE_HOME}/rdbms/admin/privoats.plb

Subprograms

Alter an OATS configuration parameter dbms_activity.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, con_dbname IN VARCHAR2 := NULL);

col param_name format a35

      col param_str format a20

      

      SELECT param_name, param_str

      FROM activity_config$;

      

      PARAM_NAME                          PARAM_STR

      ----------------------------------- --------------------

      ACTIVITY_INTERVAL_MINUTES

      ACTIVITY_RETENTION_DAYS

      ACTIVITY_SPACE_PERCENT

      

      exec dbms_activity.configure('ACTIVITY_RETENTION_DAYS', '10');

      

      PL/SQL procedure successfully completed.

CREATE_SNAPSHOT

Create a snapshot (interval summary) of OATS data dbms_activity.create_snapshot( all_instances IN BOOLEAN := TRUE, con_dbname IN VARCHAR2 := NULL) RETURN NUMBER; PRAGMA SUPPLEMENTAL_LOG_DATA(create_snapshot, UNSUPPORTED_WITH_COMMIT);

SELECT dbms_activity.create_snapshot

      FROM dual;

      

      col s_start format a30

      col s_end format a30

      col flush_time format a20

      

      SELECT snap_id, s_start, s_end, flush_time

      FROM activity_snap_meta$

      WHERE s_end > SYSDATE-1/24;

      

      SNAP_ID   S_START                   S_END                      FLUSH_TIME

      ------- --------------------------- -------------------------- --------------------

          223 13-DEC-20 02.27.04.000 AM   13-DEC-20 02.42.05.000 AM

          224 13-DEC-20 02.42.05.000 AM   13-DEC-20 02.57.06.000 AM

          225 13-DEC-20 02.57.06.000 AM   13-DEC-20 03.12.07.000 AM

          226 13-DEC-20 03.12.07.000 AM   13-DEC-20 03.27.08.000 AM

          227 13-DEC-20 03.27.08.000 AM   13-DEC-20 03.27.08.000 AM

DELETE_SNAPSHOTS

Delete snapshots (interval summary) of OATS data

Overload 1 dbms_activity.delete_snapshot( before_snap_id IN NUMBER, con_dbname IN VARCHAR2 :=NULL) RETURN BOOLEAN; PRAGMA SUPPLEMENTAL_LOG_DATA(delete_snapshots, UNSUPPORTED_WITH_COMMIT);

SELECT COUNT(*)

      FROM activity_snap_meta$;

      

       COUNT(*)

      ---------

            146

      

      BEGIN

        IF dbms_activity.delete_snapshots(200) THEN

          dbms_output.put_line('Snapshots Deleted');

        ELSE

          dbms_output.put_line('Snapshots Not Deleted');

        END IF;

      END;

      /

      

      PL/SQL procedure successfully completed.

      

      SELECT COUNT(*)

      FROM activity_snap_meta$;

      

       COUNT(*)

      ---------

             28

Overload 2 dbms_activity.delete_snapshots( before_time IN TIMESTAMP, con_dbname IN VARCHAR2 := NULL) RETURN BOOLEAN; PRAGMA SUPPLEMENTAL_LOG_DATA(delete_snapshots, UNSUPPORTED_WITH_COMMIT);

BEGIN

        IF dbms_activity.delete_snapshots(SYSDATE-31) THEN

          dbms_output.put_line('Snapshots Deleted');

        ELSE

          dbms_output.put_line('Snapshots Not Deleted');

        END IF;

      END;

      /