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.

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

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                            

DROP TABLE CLOUD_TABLE_TEST;

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 Clausefor 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