Use the TTL feature

The Time To Live (TTL) feature in the SQL mode of OceanBase Database provides the capability to manage expired data. By defining a TTL strategy for a table, you can set the validity period of the data. The system then executes the TTL task to handle expired data.

For tables with a defined TTL strategy, the system processes expired data in the following two steps:

1. When a TTL task is triggered, the system synchronizes a delete marker for tables with the TTL attribute, their index tables, and auxiliary tables. Data rows that meet the deletion criteria are marked as deleted and become invisible to users.

2. During the next major compaction, the system removes the marked data from the storage layer to free up storage space.

This topic describes how to initiate a TTL task using commands or by setting up periodic tasks.

Considerations

• When the TTL task synchronously marks deleted data, it will lock the table, which is mutually exclusive with operations such as offline import, DDL, and transfer.

• When the number of partitions in the main table, index table, and auxiliary table exceeds 4000, a transaction timeout may occur, causing the task to fail repeatedly. You can increase the timeout time to solve this issue. The statement is as follows:

  obclient(root@sys)[(none)]> ALTER SYSTEM SET internal_sql_execute_timeout = 600; /*Execute this statement in the sys tenant. The unit is seconds.*/
  

• Since the transaction for marking deleted data is not mutually exclusive with ordinary DML transactions, when two transactions are executed concurrently and the transaction isolation level is Read Committed, phantom reads may occur when querying data that is about to expire in the DML transaction.

  For example, assume that there is a transaction trans_A for marking deleted data, a transaction trans_B for ordinary DML operations, and a data row rowkey_A that is about to expire. During the period from T1 to T3 (T1 < T2 < T3):

  1. At T1, the data row rowkey_A has not reached the expiration time, and the ordinary DML transaction trans_B can read the data of rowkey_A.
  2. At T2, the data row rowkey_A reaches the expiration time, and the transaction trans_A marks rowkey_A as deleted.
  3. At T3, the ordinary DML transaction trans_B reads rowkey_A again and finds that the data cannot be read.

• When the TTL task is not executed, you can query the data in the table that meets the expiration rules. If you have strict visibility requirements for expired data, you need to add a filter condition to the query SQL statement.

• After you execute offline DDL operations or create an index on the main table and its index, the table data will be rewritten, and the write time of the data will be updated, which will cause the data to expire later.

• After you execute the TTL task, expired data will be asynchronously deleted during the compaction (major compaction) process. The process of deleting expired data is performed only within OceanBase Database and will not be synchronized to other application systems.

TTL tables

Limitations

When you use the TTL feature to manage expired data, the following limitations apply to TTL tables:

• Foreign key constraints are not supported.

• Triggers are not supported.

• Vector indexes and full-text indexes are not supported.

• Only single-column TTL columns are supported. Virtual columns, generated columns, and expressions are not supported.

• The table update mode MERGE_ENGINE cannot be set to partial_update.

• In the current version, only the internal hidden column ora_rowscn (which records the timestamp of the last update) is supported as the TTL column.

• You cannot add a column named ora_rowscn to a table that has the TTL attribute defined.

Create a table with the TTL attribute

The statement for creating a TTL table is as follows:

CREATE TABLE table_name (table_definition_list) MERGE_ENGINE = {append_only | delete_insert}
   TTL [=] col_name + INTERVAL interval_num ttl_unit BY COMPACTION;

The parameters in the statement are described as follows:

• MERGE_ENGINE: specifies the update mode of the table. The table update mode MERGE_ENGINE cannot be set to partial_update.

• col_name: specifies the TTL filter column. In the current version, only the column named ora_rowscn (an internal hidden column that records the timestamp of the last update) is supported.

• interval_num: specifies the integer value of the expiration time. The value range is [0,+∞). When the value is 0, the time unit ttl_unit can be any of the supported values, indicating that the data will expire immediately after it is committed.

• ttl_unit: specifies the unit of the expiration time. Valid values: SECOND, MINUTE, HOUR, DAY, MONTH, or YEAR.

For example, create a TTL table where data expires after 7 days.

obclient> CREATE TABLE ttl_tbl1(
   id INT PRIMARY KEY,
   val VARCHAR(100)
) MERGE_ENGINE = append_only TTL ora_rowscn + INTERVAL 7 DAY BY COMPACTION;

After the TTL table is created, you can modify the TTL strategy of the table based on your business requirements. The statement is as follows:

obclient> ALTER TABLE ttl_tbl1 SET TTL ora_rowscn + INTERVAL 1 HOUR BY COMPACTION;

Similarly, if you no longer need to use the TTL feature, you can delete the TTL strategy of the table. The statement is as follows.

obclient> ALTER TABLE ttl_tbl1 REMOVE TTL;

Triggering TTL tasks

For each table with the TTL attribute defined, the system periodically schedules TTL tasks to process expired data when the TTL feature is enabled. After a TTL task is executed, expired data becomes invisible, but it is not physically deleted. Instead, it is asynchronously deleted during a major compaction (major compaction) without consuming clogs or write bandwidth. The storage space occupied by expired data is only released after the major compaction task is completed.

Enabling the TTL feature

To initiate a TTL task, the current tenant must have the TTL feature enabled. The TTL feature is controlled by the tenant-level configuration item [enable_ttl] (../../800.configuration-items-and-system-variables/100.system-configuration-items/400.tenant-level-configuration-items/3020.enable_ttl.md) (with a value of True). By default, this configuration item is set to False when a new tenant is created or a tenant is restored, and must be manually enabled.

1. Log in as an administrator to the sys tenant or a user tenant in the cluster.

   The following example shows how to connect to the database. Please adjust the connection parameters based on your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysqltenant#obdemo -p***** -A
   

2. Execute the following statement to enable the TTL feature.

   • Enable the TTL feature for a specified tenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM SET enable_ttl = True TENANT = tenant_name;
     

   • Enable the TTL feature for the current user tenant.

     obclient> ALTER SYSTEM SET enable_ttl = True;
     

Configuring the scheduled time for TTL tasks

After enabling the TTL feature, the system periodically triggers TTL tasks based on the expiration status of data in TTL tables under the tenant. The scheduled time for triggering TTL tasks is controlled by the tenant-level configuration item ttl_duty_time, with a default value of 01:00 daily.

The ttl_duty_time is based on the tenant's timezone. It is recommended to set it during off-peak hours for the tenant and before daily major compactions. For example, you can set it to 02:00.

To modify the scheduled time for triggering TTL tasks:

1. Log in as an administrator to the sys tenant or a user tenant in the cluster.

   The following example shows how to connect to the database. Please adjust the connection parameters based on your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@mysqltenant#obdemo -p***** -A
   

2. Execute the following statement to modify the scheduled time for triggering TTL tasks.

   • Modify the scheduled time for triggering TTL tasks for a specified tenant.

     obclient(root@sys)[(none)]> ALTER SYSTEM SET ttl_duty_time = '02:00' TENANT = tenant_name;
     

   • Modify the scheduled time for triggering TTL tasks for the current user tenant.

     obclient> ALTER SYSTEM SET ttl_duty_time = '02:00';
     

Manually triggering TTL tasks

After enabling the TTL feature, you can manually trigger a TTL task.

1. Log in as an administrator to the sys tenant or a user tenant in the cluster.

   The following example shows how to connect to the database. Please adjust the connection parameters based on your actual environment.

   obclient -h10.xx.xx.xx -P2883 -uroot@sys#obdemo -p***** -A
   

2. Execute the following command to manually trigger a TTL task.

   • Manually trigger a TTL task for all user tenants.

     obclient(root@sys)[(none)]> ALTER SYSTEM TRIGGER TTL TENANT = all_user;
     

     Notice

     The system tenant cannot manually trigger a TTL task for all Meta tenants using the ALTER SYSTEM TRIGGER TTL TENANT = all_meta; statement.

   • Manually trigger a TTL task for a specified tenant.

     obclient> ALTER SYSTEM TRIGGER TTL TENANT = tenant_name;
     

   • Manually trigger a TTL task for the current user tenant.

     obclient> ALTER SYSTEM TRIGGER TTL;
     

TTL task status

You can query the CDB_OB_TTL_TASKS view (sys tenant) or the DBA_OB_TTL_TASKS view (user tenant) to view the details of a TTL task in execution, including the table information, start time, modified time, and execution status.

• System tenant

  obclient(root@sys)[(none)]> SELECT * FROM oceanbase.CDB_OB_TTL_TASKS;
  

• User tenant

  obclient(root@mysqltenant)[(none)]> SELECT * FROM oceanbase.DBA_OB_TTL_TASKS; /*MySQL mode*/
  

  obclient(root@oracletenant)[SYS]> SELECT * FROM SYS.DBA_OB_TTL_TASKS; /*Oracle mode*/
  

The query result in a user tenant is as follows:

+------------+----------+---------+----------------------------+----------------------------+--------------+------------+------------+------------+
| TABLE_NAME | TABLE_ID | TASK_ID | START_TIME                 | MODIFIED_TIME              | TRIGGER_TYPE | STATUS     | RET_CODE   | TASK_TYPE  |
+------------+----------+---------+----------------------------+----------------------------+--------------+------------+------------+------------+
| NULL       |       -1 |       1 | 2026-01-28 17:31:27.624387 | 2026-01-28 17:31:27.624387 | USER         | TRIGGERING | OB_SUCCESS | COMPACTION |
+------------+----------+---------+----------------------------+----------------------------+--------------+------------+------------+------------+
1 row in set

For more information about the fields in the query result, see CDB_OB_TTL_TASKS and DBA_OB_TTL_TASKS.

After a TTL task is completed, you can query the CDB_OB_TTL_TASK_HISTORY (sys tenant) or DBA_OB_TTL_TASK_HISTORY (user tenant) view to view the history of the completed TTL task.

Manage TTL tasks

You can manually pause, resume, or cancel a TTL task during its execution.

Pause a TTL task

After a TTL task is manually or periodically triggered, you can manually pause the TTL task in execution as needed.

• Pause all TTL tasks of all user tenants in the system tenant.

  obclient(root@sys)[(none)]> ALTER SYSTEM SUSPEND TTL TENANT = all_user;
  

• Pause the TTL tasks of a specified tenant in the system tenant.

  obclient(root@sys)[(none)]> ALTER SYSTEM SUSPEND TTL TENANT = tenant_name;
  

• Pause the TTL tasks of the current tenant in the user tenant.

  obclient> ALTER SYSTEM SUSPEND TTL;
  

Resume a paused TTL task

After a TTL task is paused, you can manually resume it.

• Resume all TTL tasks of all user tenants in the system tenant.

  obclient(root@sys)[(none)]> ALTER SYSTEM RESUME TTL TENANT = all_user;
  

• Resume the TTL tasks of a specified tenant in the system tenant.

  obclient(root@sys)[(none)]> ALTER SYSTEM RESUME TTL TENANT = tenant_name;
  

• Resume the TTL tasks of the current tenant in the user tenant.

  obclient> ALTER SYSTEM RESUME TTL;
  

Cancel a TTL task

During the execution of a TTL task, you can manually cancel it as needed.

• Cancel all TTL tasks of all user tenants in the system tenant.

  obclient(root@sys)[(none)]> ALTER SYSTEM CANCEL TTL TENANT = all_user;
  

• Cancel the TTL tasks of a specified tenant in the system tenant.

  obclient(root@sys)[(none)]> ALTER SYSTEM CANCEL TTL TENANT = tenant_name;
  

• Cancel the TTL tasks of the current tenant in the user tenant.

  obclient> ALTER SYSTEM CANCEL TTL;