1. Using Oracle Autonomous Database Serverless
  2. Reference
  3. Autonomous Database Supplied Package Reference
  4. DBMS_SHARE Package
  5. DBMS_SHARE Constants

DBMS_SHARE Constants

These constants are used by the DBMS_SHARE package.

Detached Files

Constants used by PURGE_DETACHED_FILES Procedure.

Name Type Value Description
PURGE_DROP PLS_INTEGER 1 Attempt to drop the files, using the specified credential. If the file cannot be dropped, then the file continues to be listed in the *_SHARE_DETACHED_FILES views.
PURGE_DROP_FORCE PLS_INTEGER 2 Attempt to drop the files, using the specified credential. The file is removed from the *_SHARE_DETACHED_FILES views, even if the attempt to drop the file fails again.
PURGE_FORGET PLS_INTEGER 3 Remove the files *_SHARE_DETACHED_FILES views without attempting to drop them.

Log Level

Constants used for setting the level of information collected in the log. These levels control the number of events that get logged into ALL_SHARE_EVENTS and ALL_SHARE_RECIPIENTS.

See SET_SHARE_LOG_LEVEL Procedure and SET_RECIPIENT_LOG_LEVEL Procedure.

Name Type Value Description
LOG_LEVEL_ERRORS_ONLY PLS_INTEGER 0 Only log errors.
LOG_LEVEL_BASIC PLS_INTEGER 1 Log errors and basic information.
LOG_LEVEL_DETAIL PLS_INTEGER 2 Log errors and additional details.
LOG_LEVEL_DEBUG PLS_INTEGER 3 Log debug level of information.

Share Job Properties

Properties used in UPDATE_SHARE_JOB_PROPERTY Procedure.

Name Type Value Description
PROP_SHARE_JOB_DOP VARCHAR2(7) 'JOB_DOP' The JOB_DOP property determines how many DBMS_SCHEDULER jobs are used to publish the share. This should be a number between 1 and 5.
PROP_SHARE_JOB_CLASS VARCHAR2(9) 'JOB_CLASS' The JOB_CLASS property determines the scheduler job class that is used to published the share. See ALL_SCHEDULER_JOB_CLASSES for a list of valid values. The default value is DEFAULT_JOB_CLASS.
PROP_SHARE_JOB_PRIORITY VARCHAR2(12) 'JOB_PRIORITY' The JOB_PRIORITY property determines the relative priority when two or more shares from the same user are being published at the same time. The value should be a number, with 0 as the default. Shares with a higher priority will be processed before shares with a lower priority. Shares with the same priority are processed on a first come, first serve basis.
Standard values for job priority:
SHARE_PRIORITY_LOW     CONSTANT NUMBER := 1;
SHARE_PRIORITY_DEFAULT CONSTANT NUMBER := 2;
SHARE_PRIORITY_HIGH    CONSTANT NUMBER := 3;

Share Properties

Constants used to indicate the properties of a share.

See UPDATE_SHARE_PROPERTY Procedure, UPDATE_DEFAULT_SHARE_PROPERTY Procedure and GET_SHARE_PROPERTY Function.

Name Type Value Description
PROP_SHARE_DESC VARCHAR2(11) 'DESCRIPTION' A locally visible description of the share.
PROP_SHARE_PUBLIC_DESC VARCHAR2(18) 'PUBLIC_DESCRIPTION' An externally visible description of the share.
PROP_SHARE_SPLIT_SIZE VARCHAR2(10) 'SPLIT_SIZE' The SPLIT_SIZE property determine how large the generated parquet files will be. The algorithm works as follows:
  1. The SQL Optimizer estimates the size in bytes of a given segment (for example, a partition of a table or the results of a view).
  2. The number of bytes is divided by the SPLIT_SIZE and rounded up to produce the "number of splits".
  3. The data in the segment is then chunked into this "number of splits" using some method (for example ROWID, index columns, specified columns, etc.).
  4. Each chunk of data is then converted into a parquet file.

Note that the parquet file will generally be smaller than the SPLIT_SIZE due to the compression. In particular, SPLIT_SIZE determines the amount of data before compression.

PROP_SHARE_LOG_LEVEL VARCHAR2(9) 'LOG_LEVEL' The LOG_LEVEL property determines the amount of information that will be logged in the ALL_SHARE_EVENTS log.
PROP_SHARE_VERSION_ACCESS VARCHAR2(14) 'VERSION_ACCESS' The versions of a published share a recipient can see.

Share Recipient PAR Type

These constants are the valid values for the property PROP_RECIPIENT_PAR_TYPE in UPDATE_RECIPIENT_PROPERTY Procedure and GET_RECIPIENT_PROPERTY.

Name Type Value Description
PAR_TYPE_FOLDER PLS_INTEGER 1 PARs gives access to the entire folder.
PAR_TYPE_FILE PLS_INTEGER 2 PARs gives access to one file at a time.

Share Recipient Properties

These constants are used in GET_SHARE_PROPERTY Function, UPDATE_DEFAULT_SHARE_PROPERTY Procedure, and UPDATE_SHARE_PROPERTY Procedure.

Name Type Value Description
PROP_RECIPIENT_PAR_LIFETIME VARCHAR2(12) 'PAR_LIFETIME' The lifetime of Pre-authenticated Request URLs sent to the recipient through the delta sharing API. The default is three hours: '00 03:00:00'
PROP_RECIPIENT_MIN_PAR_LIFETIME VARCHAR2(16) 'MIN_PAR_LIFETIME' The minimum guaranteed lifetime of a Pre-authenticated Request URLs sent to the recipient through the delta sharing API. If there is an existing PAR for the same parquet file, it is reused only if the remaining life exceeds this minimum value. The default is 2 1/2 hours: '00 02:30:00'
PROP_RECIPIENT_MAX_PAR_LIFETIME VARCHAR2(16) 'MAX_PAR_LIFETIME' An administrator setting that defines the maximum allowed par lifetime for any recipient. The default is 1 day: '01 00:00:00'
PROP_RECIPIENT_TOKEN_LIFETIME VARCHAR2(14) 'TOKEN_LIFETIME' A string, in INTERVAL DAY TO SECOND format, representing the lifetime of a delta sharing bearer token. The default is one hour: '01 00:00:00'.
PROP_RECIPIENT_MAX_TOKEN_LIFETIME VARCHAR2(18) 'MAX_TOKEN_LIFETIME' An administrator setting that defines the maximum allowed bearer token lifetime for any recipient. The default is 90 days: '90 00:00:00'
PROP_RECIPIENT_EMAIL VARCHAR2(5) 'EMAIL' The email of the recipient. This is only required for delta sharing recipients.
PROP_RECIPIENT_DESCRIPTION VARCHAR2(11) 'DESCRIPTION' A textual description of the recipient.
PROP_RECIPIENT_LOG_LEVEL VARCHAR2(9) 'LOG_LEVEL' The logging level for the recipient. This controls what gets logged in USER_SHARE_RECIPIENT_EVENTS. The value should be one of the LOG_LEVEL_* constants.
PROP_RECIPIENT_SHARING_ID VARCHAR2(10) 'SHARING_ID' The sharing ID of the recipient. This is only required for Autonomous Database recipients.
PROP_RECIPIENT_PAR_TYPE VARCHAR2(8) 'PAR_TYPE' The type of pre-authenticated request to create. This should be one of PAR_TYPE_FOLDER (the default) or PAR_TYPE_FILE.
PROP_RECIPIENT_VERSION_ACCESS VARCHAR2(14) 'VERSION_ACCESS' Specifis what versions of published shares a recipient can see. One of VERSION_ACCESS_CURRENT or VERSION_ACCESS_ANY.

Share Table Properties

Constants used to indicate share table properties. These are used by GET_SHARE_TABLE_PROPERTY Function, and UPDATE_SHARE_TABLE_PROPERTY Procedure.

Name Type Value Description
PROP_SHARE_TABLE_SPLIT_METHOD VARCHAR2(12) 'SPLIT_METHOD' Specifies how a segment should be split into different files.
SPLIT_METHOD_AUTO VARCHAR2(4) 'AUTO' Autonomous Database determines the way in which segments are split.
SPLIT_METHOD_RANGE VARCHAR2(5) 'RANGE' Split segments based on the value ranges. The exact ranges are determined by analyzing the data histograms. This method requires one or more split_columns to be specified.
SPLIT_METHOD_ROWID VARCHAR2(5) 'ROWID' Split segments based on ROWID.
SPLIT_METHOD_HASH VARCHAR2(4) 'HASH' Split segments based on HASH values. This method requires one or more split_columns to be specified.
SPLIT_METHOD_VALUE VARCHAR2(5) 'VALUE' Split segments based on distinct values. This method requires one or more split_columns to be specified.
PROP_SHARE_TABLE_SPLIT_COLUMNS VARCHAR2(13) 'SPLIT_COLUMNS' Specifies what columns are used for splitting. These are required for RANGE, VALUE, AND HASH methods. The value should be a comma delimited set of shared columns (for example, 'state,city' or '"STATE","CITY"').
PROP_SHARE_TABLE_ORDER_COLUMNS VARCHAR2(13) 'ORDER_COLUMNS' Specifies what columns are used for ordering when gathering parquet. The value should be a comma delimited set of shared columns (for example, 'state,city' or '"STATE","CITY"').
PROP_SHARE_TABLE_SHARE_COLUMNS VARCHAR2(13) 'SHARE_COLUMNS' Specifies what columns are shared. The value is a comma delimited set of shared columns. For example, 'state,city' or '"STATE","CITY"'.
PROP_SHARE_TABLE_SPLIT_SIZE VARCHAR2(10) 'SPLIT_SIZE' Override share level split_size for a specific table. The value is a number.
PROP_SHARE_TABLE_GATHER_STATS VARCHAR2(12) 'GATHER_STATS' If Autonomous Database will gather statistics, the value of 'GATHER_STATS' is 'YES'. Otherwise the value is 'NO'.
PROP_SHARE_TABLE_SPLIT_ROWS VARCHAR2(10) 'SPLIT_ROWS' The SPLIT_ROWS property is an alternative way to specify the amount of data to be stored in each parquet file.
  1. Autonomous Database calculates the number of rows in a given segment (for example, a partition of a table or the results of a view).
  2. The number of rows is divided by the SPLIT_ROWS and rounded up to produce the "number of splits".
  3. The data in the segment is then chunked into this number of splits using some method (for example, ROWID, index columns, specified columns, etc.)
  4. Each chunk of data is then converted into a parquet file.

It is recommended that you use the SPLIT_SIZE property to control the number of splits since this reflects the amount of data in each row. A row with 900 columns of VARCHAR2(4000), for example, is much larger than a row with a single column of type NUMBER(1).

Only use SPLIT_ROWS if you have accounted for the average amount of data in each row.

PROP_SHARE_TABLE_FLASHBACK VARCHAR2(9) 'FLASHBACK' The FLASHBACK property determines if the table will be published using flashback queries to ensure read consistency between the different export files. The value should be YES, NO, or AUTO.

The AUTO setting will use flashback if it is available.

The YES setting will cause the publication to fail if flashback is not possible on the table or view.

The NO setting will not use flashback, which means that parquet files could represent different snapshots of the data.

Share Types

Constants used during share creation to identify the type of share.

See CREATE_SHARE Procedure.

Name Type Value Description
SHARE_TYPE_VERSIONED DBMS_ID 'VERSIONED' Share is used for sharing versioned data.
SHARE_TYPE_LIVE DBMS_ID 'LIVE' Share is used for sharing live data.

Sharing ID Type

Used in ASSERT_SHARING_ID and GET_SHARING_ID.

Name Type Value Description
SHARING_ID_TYPE_TENANCY VARCHAR2(7) 'TENANCY' The sharing ID represents an OCI Tenancy.
SHARING_ID_TYPE_COMPARTMENT VARCHAR2(11) 'COMPARTMENT' The sharing ID represents an OCI Compartment.
SHARING_ID_TYPE_DATABASE VARCHAR2(8) 'DATABASE' The sharing ID represents an OCI Autonomous Database.
SHARING_ID_TYPE_REGION VARCHAR2(6) 'REGION' The sharing ID represents an OCI Region.

Version Access

Possible values for the VERSION_ACCESS share property. This is used to implement delta "time travel". These are the valid values associated with PROP_SHARE_VERSION_ACCESS and PROP_RECIPIENT_VERSION_ACCESS.

These can be used to enable the "version" property in the delta sharing query endpoint, which allows recipients to select specific versions of the data instead of always receiving the CURRENT version. You need to enable this on both the share and the recipient.

For example:
UPDATE_SHARE_PROPERTY(
          share_name => '...',
          share_property => DBMS_SHARE.PROP_SHARE_VERSION_ACCESS,
          new_value => DBMS_SHARE.VERSION_ACCESS_ANY);

UPDATE_SHARE_PROPERTY(
          share_name => '...',
          share_property => DBMS_SHARE.PROP_RECIPIENT_VERSION_ACCESS,
          new_value => DBMS_SHARE.VERSION_ACCESS_ANY);
Name Type Value Description
VERSION_ACCESS_CURRENT PLS_INTEGER 1 A recipient can see only the CURRENT version. (Default)
VERSION_ACCESS_ANY PLS_INTEGER 2 A recipient can choose any CURRENT or RETIRED version.

Parent topic: DBMS_SHARE Package