Use Cloud Tables to Store Logging and Diagnostic Information

You can create Cloud Tables where table data resides on Oracle managed Cloud Storage and the table data does not consume database storage.

Parent topic: Develop

About Cloud Tables

You can create Cloud Tables as a complementary alternative to in-database tables.

All Cloud Table data is stored in Oracle managed Object Storage. Oracle managed Object Storage is external storage, outside of the database, that Autonomous Database creates and manages.

You can use Cloud Tables to store infrequently used application logging data, diagnostic information, or to store other data. In some existing applications that do not run on Autonomous Database you might store this kind of information in files on a local file system (for example using UTL_FILE APIs). Such logging mechanisms and the associated files can be very helpful when you need to diagnose and resolve application errors. However, storing information in database tables can use large amounts of database storage for data that is infrequently used. Using Cloud Tables the persistent data is saved in Oracle managed Object Storage, without consuming database storage.

Note:

The CLOUD_TABLE_COMMIT_THRESHOLD parameter applies for all Cloud Tables, and can be set by any user with the ALTER SESSION privilege. Therefore, Cloud Tables are not suitable for security-critical data where committed changes must be durable and must be immediately visible to concurrent readers. For this reason, Cloud Tables may not be appropriate for use cases such as audit log tables.

SELECT and DML Restrictions for Cloud Tables

Cloud Tables function like ordinary database tables with some restrictions. You can use SELECT and DML, data manipulation statements, with the following exceptions:

  • MERGE statements are not supported.
  • LOB columns are limited to 10MB of data.
  • DML concurrency control is different, and therefore:
    • LOCK TABLE may not prevent concurrent DML as it does for a database table.
    • INSERT does not acquire a lock on the table, and therefore INSERT is never blocked by concurrent DML operations.
    • UPDATE and DELETE operations both acquire an exclusive lock on a Cloud Table. Therefore, UPDATE or DELETE transactions block concurrent UPDATE or DELETE operations on a Cloud Table.
  • Only NOT NULL constraints are enforced.
  • DML is allowed in a Read-Write Autonomous Database as it is for any other table; Cloud Tables also allow DML operations in a Refreshable Clone.

Cloud Tables do not support the following:

  • Indexes
  • Invisible columns
  • Virtual columns
  • DML triggers
  • More than 996 columns
  • Boolean data type columns

Lifecycle Management Operations and Cloud Tables

Cloud Table data is stored in Oracle managed Object Storage. This means certain operations on Autonomous Database handle Cloud Tables differently than in-database tables, as follows:

  • Cloud Table data is excluded from an Autonomous Database instance's backup and recovery (the data is not backed up and you cannot restore Cloud Table data).

  • Cloud Table Data is protected through Oracle managed Object Storage.

  • The lifecycle management operations that impact the state of an Autonomous Database instance do not have an impact on the data stored in Cloud Tables.

Cloud Table naming in Object Storage is defined uniquely for each Autonomous Database instance, based on its OCID. This means that any operation that changes or introduces a new OCID for an existing database has an impact on Cloud Tables. The following illustrates the impact of lifecycle operations on Cloud Table data.

Lifecycle Operation Cloud Table Data Availability
Same region database clone Cloud Table is cloned without Cloud Table data
Cross-region database clone Cloud Table is cloned without Cloud Table data
Same region (local) Autonomous Data Guard Standby Cloud Table and Cloud Table data are accessible
Cross-region Autonomous Data Guard Standby Cloud Table is available on the standby, without the Cloud Table data
Same region (local) Backup-Based Disaster Recovery peer Cloud Table and Cloud Table data are accessible
Cross-region Backup-Based Disaster Recovery peer Cloud Table is available on the standby, without Cloud Table data
Lifecycle management operations impacting the SCN/timestamp of an Autonomous Database instance, including:
  • Long term backup
  • Restore database (point in time restore)
  • Clone from backup

Cloud Table will continue to be updated and the old state of Cloud Table data is not preserved or restored. This means only the current Cloud Table data is available.
Lifecycle Management operations, including:
  • Manage resource allocation
  • Move
  • Shrink
  • Rename
  • Mode: Read-only/read-write
  • Change workload type: for example from Data Warehouse to Transaction Processing
 No impact on Cloud Tables or on Cloud Table data

Buffering with Cloud Tables

By default, DML changes to Cloud Tables are exported to Object Storage when the DML commits. However, this may not perform well when DMLs are structured as small, frequently committed transactions. To improve performance in this scenario, set the CLOUD_TABLE_COMMIT_THRESHOLD parameter to enable buffering of Cloud Table DML changes within a session.

When the CLOUD_TABLE_COMMIT_THRESHOLD parameter is set to a non-zero value, the system treats the value as a change count threshold and Cloud Table changes are buffered until the number of changes reaches the specified threshold. When the threshold is reached the buffered changes are exported to Object Storage. Buffered changes are also exported when the session terminates normally, even if the CLOUD_TABLE_COMMIT_THRESHOLD has not been reached. Before buffered changes are exported, concurrent sessions do not see the changes. In rare cases involving unexpected process expiration, the buffered changes may never be exported and the changes are not durable (that is, the buffered changes are not exported to Object Store).

See CLOUD_TABLE_COMMIT_THRESHOLD for more information.

Parent topic: Use Cloud Tables to Store Logging and Diagnostic Information

Create Cloud Tables

Shows the steps to create a Cloud Table on Autonomous Database.

To create a Cloud Table:

  1. Run the CREATE_CLOUD_TABLE procedure.

    For example:

    BEGIN
  DBMS_CLOUD.CREATE_CLOUD_TABLE(
   table_name  => 'CLOUD_TABLE_TEST',
   column_list => 'I INTEGER, STR1 VARCHAR2(32)' );
END;
/

    See CREATE_CLOUD_TABLE Procedure for more information.

  2. Insert data into the Cloud Table.
    INSERT INTO cloud_table_test VALUES (1, 'xyz');

    You can use the CLOUD_TABLE_COMMIT_THRESHOLD initialization parameter to enable buffering for Cloud Tables. See CLOUD_TABLE_COMMIT_THRESHOLD for more information.

  3. Select data from a Cloud Table.
    SELECT * FROM cloud_table_test;
    I          STR1
---------- --------------------------------
1          xyz

Use DROP TABLE when you want to drop a Cloud Table.

For example:

DROP TABLE CLOUD_TABLE_TEST;

Cloud Tables do not support the recycle bin.

See Cloud Table Notes for additional information.

Parent topic: Use Cloud Tables to Store Logging and Diagnostic Information

Cloud Table Notes

Provides notes for using Cloud Tables.

The following are notes for using Cloud Tables:

  • You can grant SELECT, INSERT, and UPDATE privileges for a Cloud Table. No other privileges can be granted to a Cloud Table.

    See Configuring Privilege and Role Authorization for more information.

  • Cloud Table constraints are limited to constraints in RELY DISABLE NOVALIDATE mode, which means the constraint is not enforced. The only exception to this is for NOT NULL constraints.

    Cloud Tables support all NOT NULL constraint modes including the default mode (ENABLE VALIDATE). PRIMARY KEY, UNIQUE, FOREIGN KEY, and NOT NULL constraints are supported; CHECK constraints are not supported.

    You can declare constraints inline as part of COLUMN_LIST.

    For example:

    BEGIN
  DBMS_CLOUD.CREATE_CLOUD_TABLE(
        table_name  => 'CLOUD_TAB_WITH_CONSTRAINTS',
        column_list => 'PK INTEGER,
            DATE_ID INT REFERENCES DATE_DIM(DATE_ID) RELY DISABLE NOVALIDATE,
            VAL NUMBER NOT NULL,
            CONSTRAINT CLOUD_TAB_PK PRIMARY KEY(PK) RELY DISABLE NOVALIDATE');
END;
/

    See Cloud Table Notes for additional Cloud Table limitations.

  • The DBMS_CLOUD package is an invoker's rights package. The DBMS_CLOUD.CREATE_CLOUD_TABLE procedure only allows you to create a table in the invoker's schema.

    See Invoker’s Rights and Definer’s Rights Clause for more information.

  • The column_list parameter in a DBMS_CLOUD.CREATE_CLOUD_TABLE procedure call can include the DEFAULT clause, which functions like the DEFAULT clause in CREATE TABLE. See CREATE TABLE for more information.

    For example:

    BEGIN
  DBMS_CLOUD.CREATE_CLOUD_TABLE(
   table_name  => 'CLOUD_TABLE_TEST_DEFAULT',
   column_list => 'I INTEGER, STR2 VARCHAR2(32) DEFAULT ''ABC''');
END;
/

    Then you can insert with default values. For example:

    INSERT INTO cloud_table_test_default (i) VALUES (1); 
1 row created. 
INSERT INTO cloud_table_test_default VALUES (2, default);
1 row created.
INSERT INTO cloud_table_test_default VALUES (3, null);
1 row created.
INSERT INTO cloud_table_test_default VALUES (4, 'xyz');
1 row created.
COMMIT;
Commit complete.
SELECT * FROM cloud_table_test_default ORDER BY i;

I STR2 
- ---- 
1 ABC  
2 ABC  
3 null 
4 xyz

  • You can use the CLOUD_TABLE_COMMIT_THRESHOLD initialization parameter to enable buffering for Cloud Tables. See CLOUD_TABLE_COMMIT_THRESHOLD for more information.

Parent topic: Use Cloud Tables to Store Logging and Diagnostic Information