Oracle | DBMS_AUTO

Oracle DBMS_AUTO_SQLSET Version 26ai

General Information

Library Note

Morgan's Library Page Header

The best Oracle News for FY2026

Oracle Database 26ai is now availble. If you haven't you downloaded already: Why?

Purpose Autonomous Database Utilities for scheduling and creating AWR Snapshots

AUTHID DEFINER

Dependencies

DBA_AUTOSQLSET_SQLPLAN

DBMS_SQLTUNE_LIB

SQL_PLAN_TABLE_TYPE

Documented No

First Available 20c

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

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

Source {ORACLE_HOME}/rdbms/admin/dbmssqls.sql
{ORACLE_HOME}/rdbms/admin/prvtsqls.plb

Subprograms

CREATE_SNAPSHOT	PLAN_DATA2TABLE	PURGE_SNAPSHOT
MODIFY_SNAPSHOT_SETTINGS

CREATE_SNAPSHOT

Creates a current AWR snapshot dbms_auto_sqlset.create_snapshot;

col other format a55

      

      SELECT * FROM dba_autosqlset_snapshot ORDER BY 1;

      

      SNAP_TIME INST_NUM END_SNAP_ STATUS OTHER

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

      26-JAN-24        1 26-JAN-24      0 {"s":{"t":[

                                          {"i":0,"p":1,"e":4870,"c":4194,"r":0,"n":0,"x":0}

                                          {"i":1,"p":1,"e":3730,"c":4139,"r":0,"n":0,"x":0}

                                          {"i":2,"p":1,"e":10260,"c":10350,"r":0,"n":0,"x":0}

                                          {"i":3,"p":1,"e":2839,"c":2786,"r":0,"n":0,"x":0}

                                          {"i":4,"p":1,"e":2683,"c":2667,"r":0,"n":0,"x":0}]}}

      

      exec dbms_auto_sqlset.create_snapshot;

      

      PL/SQL procedure successfully completed.

      

      SELECT * FROM dba_autosqlset_snapshot ORDER BY 1;

      

      SNAP_TIME INST_NUM END_SNAP_ STATUS OTHER

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

      26-JAN-24        1 26-JAN-24      0 {"s":{"t":[

                                          {"i":0,"p":1,"e":4870,"c":4194,"r":0,"n":0,"x":0}

                                          {"i":1,"p":1,"e":3730,"c":4139,"r":0,"n":0,"x":0}

                                          {"i":2,"p":1,"e":10260,"c":10350,"r":0,"n":0,"x":0}

                                          {"i":3,"p":1,"e":2839,"c":2786,"r":0,"n":0,"x":0}

                                          {"i":4,"p":1,"e":2683,"c":2667,"r":0,"n":0,"x":0}]}}

      

      26-JAN-24        1 26-JAN-24      0 {"s":{"t":[

                                          {"i":0,"p":1,"e":238,"c":238,"r":0,"n":0,"x":0}

                                          {"i":1,"p":1,"e":168,"c":0,"r":0,"n":0,"x":0}

                                          {"i":2,"p":1,"e":184,"c":0,"r":0,"n":0,"x":0}

                                          {"i":3,"p":1,"e":117,"c":75,"r":0,"n":0,"x":0}

                                          {"i":4,"p":1,"e":93,"c":0,"r":0,"n":0,"x":0}]}}

MODIFY_SNAPSHOT_SETTINGS

Modifies AWR retention and interval settings

Currently unable to locate where this information is retained in the data dictionary dbms_auto_sqlset.modify_snapshot_settings( retention IN NUMBER := 31, interval IN NUMBER := 900);

exec dbms_auto_sqlset.modify_snapshot_settings(20);

      

      PL/SQL procedure successfully completed.

PLAN_DATA2TABLE

Converts a SQLSET BLOB to an array (table) data type dbms_auto_sqlset.plan_data2table(plan_data IN BLOB) RETURN sys.sql_plan_table_type;

TBD

PURGE_SNAPSHOT

In theory, purges an AWR snapshot

Testing does indicates it does not purge one or more snapshots dbms_auto_sqlset.purge_snapshot;

exec dbms_auto_sqlset.purge_snapshot;

      

      PL/SQL procedure successfully completed.