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).
"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."
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
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;
/