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.

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.

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

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');
  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.

Cloud Table Notes

Provides notes for 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