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 No
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
CONFIGURE CREATE_SNAPSHOT DELETE_SNAPSHOTS
 
CONFIGURE (new 21c)
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;
/

Related Topics
Built-in Functions
Built-in Packages
Database Security
What's New In 19c
What's New In 20c-21c

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2019 Daniel A. Morgan All Rights Reserved
  DBSecWorx