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 will be available on generic Linux platforms in January and soon on AIX and Windows

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.