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

• For a TTL table, if the value in the TTL column is NULL, the row data never expires. If a new column is designated as the TTL column, data in rows with a default NULL value will not expire.

• 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.

• To avoid data loss caused by concurrent writes, for tables that use user-defined temporal columns as TTL columns, the TTL task only cleans up data that has expired before the task starts and will not clean up data that expires after the task starts.

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.

• In addition to supporting the internal hidden column ora_rowscn (which records the timestamp of the last update), the current version also supports user-defined time columns as TTL columns.

• For tables with the partial_update table update mode, only the primary key column or the internal hidden column ora_rowscn is supported as the TTL column, and the table must not have indexes.

• For tables where the TTL column is the internal hidden column ora_rowscn, you cannot add another column named ora_rowscn to this table.

• For indexed tables, the index must include the TTL column (either as an original column or a Storing column). If the existing index does not contain the TTL column, you must first create a new index containing a Storing column, and then drop the old index.

• For tables with LOB columns:

  • You can define the TTL attribute for a table using a user column when creating the table. After the table is created, you cannot change the TTL column to another user column.
  • You cannot add the TTL attribute to a table that does not already have it.
  • You can switch the TTL column from a user-defined time column to the internal hidden column ora_rowscn.
  • You can drop the TTL attribute.

• DDL operation restrictions:

  • When adding the TTL attribute, all indexed tables must contain the TTL column.
  • When changing the TTL column, all indexed tables must contain the new TTL column.
  • When dropping the TTL column, you must first drop the TTL attribute.

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 | partial_update}
   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 current version supports the table update modes MERGE_ENGINE set to delete_insert, append-only, or partial_update.

• col_name: specifies the TTL filter column. You can specify the internal hidden column ora_rowscn (which records the timestamp of the last update) or a user-defined time column.

  When the TTL column is a user-defined time column, the supported column types are as follows:

  • MySQL-compatible mode: DATETIME, TIMESTAMP, DATE

  • Oracle-compatible mode: DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE

• 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.

Examples are as follows.

• Example 1: Create a TTL table where data expires after 7 days. In this example, the TTL column is the internal hidden column ora_rowscn.

  In MySQL-compatible mode or Oracle-compatible mode, execute the following statement to create the TTL table.

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

• Example 2: Create a TTL table where data expires after 7 days. In this example, the TTL column is a user-defined time column.

  • MySQL-compatible mode

    obclient(root@mysql001)[infotest]> CREATE TABLE ttl_tbl2(
        order_id INT PRIMARY KEY,
        order_time DATETIME NOT NULL,
        payment_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP) 
        MERGE_ENGINE = delete_insert TTL order_time + INTERVAL 7 DAY BY COMPACTION;
    

  • Oracle-compatible mode

    obclient(SYS@oracle001)[SYS]> CREATE TABLE TTL_TBL2(
        ORDER_ID INT PRIMARY KEY,
        ORDER_TIME DATE NOT NULL,
        PAYMENT_TIME DATE NOT NULL DEFAULT sysdate) 
        MERGE_ENGINE = delete_insert TTL ORDER_TIME + INTERVAL 7 DAY BY COMPACTION;
    

Modify the TTL attribute of a table

After a table is created, for a non-TTL table, you can add the TTL attribute; for a TTL table, you can modify the TTL strategy, change the TTL column, or drop the TTL attribute.

Add the TTL attribute

The syntax for adding the TTL attribute to an existing table is as follows:

ALTER TABLE table_name TTL [=] col_name + INTERVAL interval_num ttl_unit BY COMPACTION;

Here, col_name is used to specify the TTL column, which can be the internal hidden column ora_rowscn or a user-defined time column.

obclient(root@mysql001)[infotest]> CREATE TABLE tbl3(
      order_id INT,
      order_time DATETIME NOT NULL,
      payment_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY(order_id, order_time));

obclient(root@mysql001)[infotest]> ALTER TABLE tbl3 TTL order_time + INTERVAL 7 DAY BY COMPACTION;

obclient(root@mysql001)[infotest]> ALTER TABLE tbl3 TTL ora_rowscn + INTERVAL 7 DAY BY COMPACTION;

obclient(SYS@oracle001)[SYS]> CREATE TABLE TBL3(
        ORDER_ID INT,
        ORDER_TIME DATE NOT NULL,
        PAYMENT_TIME DATE NOT NULL DEFAULT sysdate,
        PRIMARY KEY(ORDER_ID, ORDER_TIME));

obclient(SYS@oracle001)[SYS]> ALTER TABLE TBL3 TTL ORDER_TIME + INTERVAL 7 DAY BY COMPACTION;

obclient(SYS@oracle001)[SYS]> ALTER TABLE TBL3 TTL ora_rowscn + INTERVAL 7 DAY BY COMPACTION;

Modify the TTL strategy

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

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

Change the TTL column

The syntax for changing the TTL column is as follows:

ALTER TABLE table_name TTL [=] new_ttl_col + INTERVAL interval_num ttl_unit BY COMPACTION;

Here, new_ttl_col is used to specify the new TTL column, following the same requirements as when creating a TTL table. For an indexed table, the index must include the new TTL column.

obclient(root@mysql001)[infotest]> CREATE TABLE ttl_tbl4(
        order_id INT,
        order_time DATETIME NOT NULL,
        payment_time DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY(order_id, order_time, payment_time)) 
        MERGE_ENGINE = delete_insert TTL order_time + INTERVAL 7 DAY BY COMPACTION;

obclient(root@mysql001)[infotest]> ALTER TABLE ttl_tbl4 TTL payment_time + INTERVAL 7 DAY BY COMPACTION;

    obclient(SYS@oracle001)[SYS]> CREATE TABLE TTL_TBL4(
        ORDER_ID INT,
        ORDER_TIME DATE NOT NULL,
        PAYMENT_TIME DATE NOT NULL DEFAULT sysdate,
        PRIMARY KEY(ORDER_ID, ORDER_TIME, PAYMENT_TIME)) 
        MERGE_ENGINE = delete_insert TTL ORDER_TIME + INTERVAL 7 DAY BY COMPACTION;

obclient(SYS@oracle001)[SYS]> ALTER TABLE TTL_TBL4 TTL PAYMENT_TIME + INTERVAL 7 DAY BY COMPACTION;

Drop the TTL attribute

For a TTL table, if you no longer need to use the TTL feature, you can drop the TTL attribute. An example 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 (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-compatible mode*/
  

  obclient(root@oracletenant)[SYS]> SELECT * FROM SYS.DBA_OB_TTL_TASKS; /*Oracle-compatible 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;