DBMS_AUTO_PARTITION Package
The
DBMS_AUTO_PARTITION
package provides administrative routines for
managing automatic partitioning of schemas and tables.
- CONFIGURE Procedure
This procedure configures settings for automatic partitioning in Autonomous Database. - VALIDATE_CANDIDATE_TABLE Function
This function checks if the given table is a valid candidate for automatic partitioning in Autonomous Database. - RECOMMEND_PARTITION_METHOD Function
This function returns a recommendation ID that can be used withAPPLY_RECOMMENDATION
procedure to apply the recommendation, or can be used withDBA_AUTO_PARTITION_RECOMMENDATIONS
view to retrieve details of the recommendations for automatic partitioning in Autonomous Database. - APPLY_RECOMMENDATION Procedure
This procedure applies the given recommendation in an Autonomous Database. - REPORT_ACTIVITY Function
This function returns a report of the automatic partitioning operations executed during a specific period in an Autonomous Database. - REPORT_LAST_ACTIVITY Function
This function returns a report of the most recent automatic partitioning operation executed in an Autonomous Database.
Parent topic: Autonomous Database Supplied Package Reference
CONFIGURE Procedure
This procedure configures settings for automatic partitioning in Autonomous Database.
Syntax
DBMS_AUTO_PARTITION.CONFIGURE (
PARAMETER_NAME IN VARCHAR2,
PARAMETER_VALUE IN VARCHAR2,
ALLOW IN BOOLEAN DEFAULT TRUE);
Parameters
Parameter | Description |
---|---|
|
Name of the automatic partitioning configuration parameter to update. It can have one of the following values:
AUTO_PARTITION_MODE sets
the mode of automatic partitioning operation, and has
one of the following values:
AUTO_PARTITION_SCHEMA sets
schemas to include or exclude from using automatic
partitioning. Its behavior is controlled by the allow
parameter. The automatic partitioning process manages
two schema lists.
Initially, both lists are empty, and all
schemas in the database can use automatic partitioning.
If the inclusion list contains one or more schemas,
then only the schemas listed in the inclusion list can
use automatic partitioning. If the inclusion list is
empty and the exclusion list contains one or more
schemas, then all schemas use automatic partitioning
except the schemas listed in the exclusion list. If
both lists contain one or more schemas, then all
schemas use automatic partitioning except the schemas
listed in the exclusion list.
Initially, both lists are empty, and all tables in the database can use automatic partitioning. If the inclusion list contains one or more tables, then only the tables listed in the inclusion list can use automatic partitioning. If the inclusion list is empty and the exclusion list contains one or more tables, then all tables use automatic partitioning except the tables listed in the exclusion list. If both lists contain one or more tables, then all tables use automatic partitioning except the tables listed in the exclusion list. If a table is not on either list, the schema inclusion and exclusion lists decide if a table is a candidate table for automatic partitioning. If there is a conflict between the schema level lists and the table level lists, the table level lists take precedence. To remove all tables from inclusion and
exclusion lists
run:
|
|
Value for the configuration setting specified in |
|
Applicable only for the
AUTO_PARTITION_SCHEMA or AUTO_PARTITION_TABLE configuration settings with one of the following values:
AUTO_PARTITION_SCHEMA and AUTO_PARTITION_TABLE configuration settings for more information about inclusion lists and exclusion lists.
|
Usage Notes
-
You can check the current setting for automatic partitioning configuration using the following SQL:
SELECT * FROM DBA_AUTO_PARTITION_CONFIG;
-
Unlike automatic indexing, automatic partitioning does not run periodically as a background task. Automatic partitioning only runs when you invoke it using the
DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD
function.
Parent topic: DBMS_AUTO_PARTITION Package
VALIDATE_CANDIDATE_TABLE Function
This function checks if the given table is a valid candidate for automatic partitioning in Autonomous Database.
Valid Candidate
- Table passes inclusion and exclusion tests specified by
AUTO_PARTITION_SCHEMA
andAUTO_PARTITION_TABLE
configuration parameters. - Table exists and has up-to-date statistics.
- Table is at least 64 GB.
- Table has 5 or more queries in the SQL tuning set that scanned the table.
- Table does not contain a
LONG
data type column. - Table is not manually partitioned.
- Table is not an external table, an internal/external hybrid table, a temporary table, an index-organized table, or a clustered table.
- Table does not have a domain index or bitmap join index.
- Table is not an advance queuing, materialized view, or flashback archive storage table.
- Table does not have nested tables, or certain other object features.
VALID
if the table is a valid candidate for autonomous partitioningINVALID: <reason>
if the table is not a valid candidate for autonomous partitioning, and <reason> is a string describing why the table is not a valid candidate.
Syntax
DBMS_AUTO_PARTITION.VALIDATE_CANDIDATE_TABLE
( SQLSET_OWNER IN VARCHAR2 DEFAULT 'SYS',
SQLSET_NAME IN VARCHAR2 DEFAULT 'SYS_AUTO_STS',
TABLE_OWNER IN VARCHAR2,
TABLE_NAME IN VARCHAR2)
RETURN VARCHAR2;
Parameters
Parameter | Description |
---|---|
|
Name of SQL tuning set representing the workload to be evaluated. |
|
Name of a table to validate as a candidate for automatic partitioning. |
Usage Notes
- As an example, you can check the validity of a sample table,
LINEORDER
in schemaTEST
, with the following SQL:SELECT DBMS_AUTO_PARTITION.VALIDATE_CANDIDATE_TABLE ( TABLE_OWNER => 'TEST', TABLE_NAME => 'LINEORDER') FROM DUAL;
Parent topic: DBMS_AUTO_PARTITION Package
RECOMMEND_PARTITION_METHOD Function
This function returns a recommendation ID that can be used with
APPLY_RECOMMENDATION
procedure to apply the recommendation, or can be
used with DBA_AUTO_PARTITION_RECOMMENDATIONS
view to retrieve details of
the recommendations for automatic partitioning in Autonomous Database.
Syntax
DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_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')
RETURN RAW;
Parameters
Parameter | Description |
---|---|
|
Name of SQL tuning set representing the workload to be evaluated. |
|
Name of a table to validate as a candidate for automatic partitioning. |
|
When the function chooses the tables for which to generate recommendations, |
|
Used to generate report for recommended partition method. See REPORT_ACTIVITY Function for details. |
|
Used to generate persistent report for recommended partition method. See REPORT_ACTIVITY Function for details. |
|
Used to generate report for recommended partition method. See REPORT_ACTIVITY Function for details. |
Usage Notes
-
The
AUTO_PARTITION_MODE
controls the actions taken by this function:IMPLEMENT
: In this mode, automatic partitioning generates a report and modifies the existing table using the recommended partition method.REPORT ONLY
: In this mode, automatic partitioning generates a report generated but existing tables are not modified. This is the default value.OFF
: In this mode, automatic partitioning prevented from producing, considering, or applying new recommendations. It does not disable existing automatic partitioned tables.
-
Unlike automatic indexing, automatic partitioning does not run periodically as a background task. Automatic partitioning only runs when you invoke it using the
DBMS_AUTO_PARTITION.RECOMMEND_PARTITION_METHOD
function.
Return Values
This function returns a recommendation ID that can be used as follows:
DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION
to apply the
recommendation,
- Use with
DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION
to apply the recommendation. - Use with
DBA_AUTO_PARTITION_RECOMMENDATIONS
view to retrieve details of the recommendations. For example:SELECT PARTITION_METHOD, PARTITION_KEY FROM DBA_AUTO_PARTITION_RECOMMENDATIONS WHERE RECOMMENDATION_ID = :RECOMMENDATION_ID;
Parent topic: DBMS_AUTO_PARTITION Package
APPLY_RECOMMENDATION Procedure
This procedure applies the given recommendation in an Autonomous Database.
Syntax
DBMS_AUTO_PARTITION.APPLY_RECOMMENDATION
( RECOMMENDATION_ID IN RAW,
TABLE_OWNER IN VARCHAR2 DEFAULT NULL,
TABLE_NAME IN VARCHAR2 DEFAULT NULL);
Parameters
Parameter | Description |
---|---|
|
Recommendation ID returned from |
|
When a single recommendation ID has recommendations for multiple tables, this optional parameter allows you to control which tables are partitioned.
|
Usage Note:
Regardless of AUTO_PARTITION_MODE
, this procedure raises an ORA-20000: recommendation_id was not found
if either there are no accepted recommendations associated with the RECOMMENDATION_ID
, or all accepted recommendations associated with the RECOMMENDATION_ID
have already been applied. The first case applies if RECOMMENDATION_ID
was generated with AUTO_PARTITION_MODE = OFF
. The second case applies if RECOMMENDATION_ID
was generated with AUTO_PARTITION_MODE = IMPLEMENT
.
Parent topic: DBMS_AUTO_PARTITION Package
REPORT_ACTIVITY Function
This function returns a report of the automatic partitioning operations executed during a specific period in an Autonomous Database.
Syntax
DBMS_AUTO_PARTITION.REPORT_ACTIVITY
( ACTIVITY_START IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
ACTIVITY_END IN TIMESTAMP WITH TIME ZONE DEFAULT NULL,
TYPE IN VARCHAR2 DEFAULT 'TEXT',
SECTION IN VARCHAR2 DEFAULT 'ALL',
LEVEL IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;
Parameters
Parameter | Description |
---|---|
|
Starting time automatic partitioning operations use for the report. If no value is specified, or |
|
Ending time automatic partitioning operations use for the report. If no value is specified, or |
|
Format of the report that has one of the following values:
|
|
Sections to include in the report that has one of the following values:
|
|
Level of information to include in the report that has one of the following values:
|
Usage Notes
Returns: A performance analysis report for workload executed on database after recommendation is applied. This report is not stored persistently with the recommendation.
Parent topic: DBMS_AUTO_PARTITION Package
REPORT_LAST_ACTIVITY Function
This function returns a report of the most recent automatic partitioning operation executed in an Autonomous Database.
Syntax
DBMS_AUTO_PARTITION.REPORT_LAST_ACTIVITY
( TYPE IN VARCHAR2 DEFAULT 'TEXT',
SECTION IN VARCHAR2 DEFAULT 'ALL',
LEVEL IN VARCHAR2 DEFAULT 'TYPICAL')
RETURN CLOB;
Parameters
Parameter | Description |
---|---|
|
The output format of the report, see REPORT_ACTIVITY Function for information. |
|
The sections included in the report, see REPORT_ACTIVITY Function for information. |
|
The level of information included in the report, see REPORT_ACTIVITY Function for information. |
Usage Notes
Returns: A performance analysis report for workload executed on database after latest recommendation is applied. This report is not stored persistently with the recommendation.
Parent topic: DBMS_AUTO_PARTITION Package