Time-Based Aging

Time-based aging removes data from a table based on the specified data lifetime and frequency of the aging process.

You can specify a time-based aging policy for a new table with the AGING USE clause of the CREATE TABLE statement. Add a time-based aging policy to an existing table with the ADD AGING USE clause of the ALTER TABLE statement.

The AGING USE clause has a ColumnName argument. ColumnName is the name of the column that is used for time-based aging, also called the timestamp column. The timestamp column must be defined as follows:

  • ORA_TIMESTAMP, TT_TIMESTAMP, ORA_DATE or TT_DATE data type

  • NOT NULL

Your application updates the values of the timestamp column. If the value of this column is unknown for some rows and you do not want the rows to be aged, then define the column with a large default value. You can create an index on the timestamp column for better performance of the aging process.

Note:

You cannot add or modify a column in an existing table and then use that column as a timestamp column because you cannot add or modify a column and define it to be NOT NULL.

You cannot drop the timestamp column from a table that has a time-based aging policy.

If the data type of the timestamp column is ORA_TIMESTAMP, TT_TIMESTAMP, or ORA_DATE, you can specify the lifetime in days, hours, or minutes in the LIFETIME clause of the CREATE TABLE statement. If the data type of the timestamp column is TT_DATE, specify the lifetime in days.

The value in the timestamp column is subtracted from SYSDATE. The result is truncated the result using the specified unit (minute, hour, day) and compared with the specified LIFETIME value. If the result is greater than the LIFETIME value, then the row is a candidate for aging.

Use the CYCLE clause to indicate how often the system should examine the rows to remove data that has exceeded the specified lifetime. If you do not specify CYCLE, aging occurs every five minutes. If you specify 0 for the cycle, then aging is continuous. Aging begins automatically if the state is ON.

Use the ALTER TABLE statement to perform the following tasks:

  • Enable or disable the aging state on a table with a time-based aging policy by using the SET AGING {ON|OFF} clause.

  • Change the aging cycle on a table with a time-based aging policy by using the SET AGING CYCLE clause.

  • Change the lifetime by using the SET AGING LIFETIME clause.

  • Add time-based aging to an existing table with no aging policy by using the ADD AGING USE clause.

  • Drop aging on a table by using the DROP AGING clause.

Call the ttAgingScheduleNow built-in procedure to schedule when aging starts. See Scheduling When Aging Starts.

To change the aging policy from time-based aging to LRU aging on a table, first drop time-based aging on the table. Then add LRU aging by using the ALTER TABLE statement with the ADD AGING LRU clause.

There are two ways to define an LRU aging policy for your table: