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.
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
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;
/
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.
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.