Oracle | DBMS_ACTIVITY

Oracle DBMS_ACTIVITY Version 21c

General Information

Library Note

Morgan's Library Page Header

Which has the higher priority in your organization: Deploying a new database or securing the ones you already have? Looking for a website, and resources, dedicated solely to securing Oracle databases? Check out DBSecWorx.

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

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 (new 21c)

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 (new 21c)

Delete snapshots (interval summary) of OATS data

Overload 1 dbms_activity.delete_snapshots( 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;

      /