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. - Create Cloud Tables
Shows the steps to create a Cloud Table on Autonomous Database. - Cloud Table Notes
Provides notes for Cloud Tables:
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.
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
andDELETE
operations both acquire an exclusive lock on a Cloud Table. Therefore,UPDATE
orDELETE
transactions block concurrentUPDATE
orDELETE
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:
|
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:
|
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:
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
, andUPDATE
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 forNOT NULL
constraints.Cloud Tables support all
NOT NULL
constraint modes including the default mode (ENABLE VALIDATE
).PRIMARY KEY
,UNIQUE
,FOREIGN KEY
, andNOT 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. TheDBMS_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 aDBMS_CLOUD.CREATE_CLOUD_TABLE
procedure call can include theDEFAULT
clause, which functions like theDEFAULT
clause inCREATE 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