Oracle | DBMS_AUTO

Oracle DBMS_AUTO_CLUSTERING Version 23c

General Information

Library Note

Morgan's Library Page Header

ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.

Purpose Contains subprograms for creation of automatic clustering.

Run this package to create clustering recommendations based on workload running in the system. Also run this package to verify and implement the clustering recommendations. This package contains subprograms that display a report of the task, configure the execution and behavior of task.

AUTHID DEFINER

Dependencies

DBMS_AUTO_CLUSTERING_INTERNAL	PRVT_REPORT_TAGS
DBMS_REPORT	XMLYPE
DBMS_SPM_INTERNAL

Documented No

First Available 23.1

Pragmas PRAGMA SUPPLEMENTAL_LOG_DATA(default, NONE);

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

Source {ORACLE_HOME}/rdbms/admin/dbmsac.sql
{ORACLE_HOME}/rdbms/admin/prvtac.plb

Subprograms

APPLY_RECOMMENDATION	RECOMMEND_CLUSTERING_METHOD	REPORT_LAST_ACTIVITY
CONFIGURE	REPORT_ACTIVITY	VERIFY_RECOMMENDATION
GET_RECOMMENDATION

APPLY_RECOMMENDATION (new 23c)

Applies the clustering recommendation dbms_auto_clustering.apply_recommendation( recommendation_id IN RAW DEFAULT NULL, table_owner IN VARCHAR2 DEFAULT NULL, table_name IN VARCHAR2 DEFAULT NULL, clustering_ddl IN VARCHAR2 DEFAULT NULL, zonemap_ddl IN VARCHAR2 DEFAULT NULL, apply_mode IN VARCHAR2 DEFAULT 'FULL');

TBD

CONFIGURE (new 23c)

Sets auto clustering configuration options dbms_auto_clustering.configure( parameter_name IN VARCHAR2, parameter_value IN VARCHAR2, allow IN BOOLEAN := TRUE);

TBD

GET_RECOMMENDATION (new 23c)

Returns the clustering recommendation dbms_auto_clustering.get_recommendation( table_owner IN VARCHAR2 DEFAULT NULL, table_name IN VARCHAR2 DEFAULT NULL) RETURN RAW;

TBD

RECOMMEND_CLUSTERING_METHOD (new 23c)

Undocumented dbms_auto_clustering.recommend_clustering_method( sqlset_owner IN VARCHAR2 DEFAULT 'SYS', sqlset_name IN VARCHAR2 DEFAULT 'SYS_AUTO_STS', table_owner IN VARCHAR2 DEFAULT NULL, table_name IN VARCHAR2 DEFAULT NULL, time_limit IN INTERVAL DAY TO SECOND DEFAULT INTERVAL '1' DAY, report_type IN VARCHAR2 DEFAULT 'TEXT', report_section IN VARCHAR2 DEFAULT 'SUMMARY', report_level IN VARCHAR2 DEFAULT 'TYPICAL', verification IN BOOLEAN DEFAULT TRUE) RETURN RAW;

TBD

REPORT_ACTIVITY (new 23c)

Reports the results of Auto Clustering executions between specified
acitivy_start and activity_end by reading data from the data dictionary and
returning it, so PRAGMA is set to READ_ONLY dbms_auto_clustering.report_activity( activity_start IN TIMESTAMP WITH TIME ZONE := systimestamp - 1, activity_end IN TIMESTAMP WITH TIME ZONE := systimestamp, type IN VARCHAR2 := 'TEXT', -- HTML, TEXT, XML section IN VARCHAR2 := 'ALL', -- ALL, SUMMARY level IN VARCHAR2 := 'TYPICAL') -- ALL, BASIC, TYPICAL RETURN CLOB;

SELECT dbms_auto_clustering.report_activity(SYSTIMESTAMP-1, SYSTIMESTAMP, 'TEST', 'ALL', 'TYPICAL');

-- Feature not supported in 23.1 Beta

REPORT_LAST_ACTIVITY (new 23c)

Reports the results of last Auto Clustering execution dbms_auto_clustering.report_last_activity( type IN VARCHAR2 := 'TEXT', -- HTML, TEXT, XML section IN VARCHAR2 := 'ALL', -- ALL, SUMMARY level IN VARCHAR2 := 'TYPICAL') -- ALL, BASIC, TYPICAL RETURN CLOB;

SELECT dbms_auto_clustering.report_last_activity('TEXT', 'ALL', 'TYPICAL');

      

      -- Feature not supported in 23.1 Beta

VERIFY_RECOMMENDATION (new 23c)

Undocumented dbms_auto_clustering.verify_recommendation( recommendation_id IN RAW, table_owner IN VARCHAR2 DEFAULT NULL, table_name IN VARCHAR2 DEFAULT NULL);

TBD