Oracle Cloud Infrastructure Documentation

Manage Tablespaces in Autonomous AI Database

Autonomous AI Database manages tablespace creation and object placement automatically. For every 32 TB of provisioned storage, or three times the provisioned storage when storage auto scaling is enabled, Autonomous AI Database creates one tablespace for user objects, such as tables and indexes.

For example, if you provision or scale up to 45 TB of storage, Autonomous AI Database creates two data tablespaces. If you provision or scale up to 32 TB and enable storage auto scaling, Autonomous AI Database creates three data tablespaces because storage auto scaling allows the database to consume up to three times the base storage. Each tablespace starts with a small initial size and extends as needed, up to 32 TB.

When you create an object, such as a table, index, partition, or subpartition, Autonomous AI Database ignores the tablespace clause if you specify it. The database determines where to place the object based on the number of tablespaces and the amount of free space in each tablespace.

Best Practices for Managing Tablespaces

The maximum size of an individual segment, such as a non-partitioned table, non-partitioned index, table partition, or subpartition, depends on the maximum tablespace size and the available space in that tablespace. The size of an object is not the same as the size of the uncompressed data; it is the size of the physical segment stored on disk. The physical size of an object depends on various factors, such as compression.

For example, the maximum size of a non-partitioned table is 32 TB, assuming there are no other segments in the same tablespace. Oracle recommends that you use partitioning to split large tables into smaller segments so that a single table can use more storage than the tablespace size. See VLDB and Partitioning Guide for more information on partitioning.

If the space allocated to all tablespaces reaches your provisioned storage size, or three times the provisioned storage size when storage auto scaling is enabled, DML statements can start to fail if any tablespace needs more free space. To prevent this issue, monitor the StorageUtilization metric and set alarms on that metric. See Available Metrics: oci_autonomous_database for more information. In this case, scale up your storage or enable storage auto scaling if it is not already enabled. See Add CPU or Storage Resources or Enable Auto Scaling for more information.

The recycle bin is enabled by default. It enables you to recover tables that you accidentally drop. Over time, if you drop many tables, the recycle bin can grow and cause the tablespaces to extend and allocate more storage. If you do not need the recycle bin to protect against accidental table drops, you can disable it.

Monitor and Move Objects When a Tablespace is Full

If a tablespace’s used space reaches its maximum possible size, existing segments in that tablespace cannot grow anymore and DML statements on these segments can start to fail. To prevent this issue, monitor the used space for each tablespace using the following query:

SELECT tablespace_name, used_percent
FROM DBA_TABLESPACE_USAGE_METRICS
WHERE tablespace_name LIKE 'DATA%';

If all tablespaces are approaching 100% used space, scale up your storage. See Add CPU or Storage Resources or Enable Auto Scaling for more information. If some tablespaces are approaching 100% and some are not, you can move objects from the tablespaces approaching the limit to other tablespaces.

For example, if the DATA tablespace is approaching 100% and other data tablespaces, such as DATA_2 and DATA_3, have free space, use the ALTER TABLE command to move objects online without downtime. Moving objects frees up space in DATA so that its objects can grow.

For tables, use this command:

ALTER TABLE table_name MOVE ONLINE;

For partitioned tables, use this command for the partitions you want to move:

ALTER TABLE table_name MOVE PARTITION partition_name ONLINE;

For subpartitioned tables, use this command for the subpartitions you want to move:

ALTER TABLE table_name MOVE SUBPARTITION subpartition_name ONLINE;

When you run these move commands, Autonomous AI Database chooses a target tablespace that has free space and moves the objects online, freeing space in the tablespace that was approaching its maximum size. The target tablespace is based on the number of tablespaces and the amount of free space in them. Autonomous AI Database also moves the indexes on these tables to the same tablespace, except for global indexes on partitioned tables, which stay in their existing tablespace.