Managing the default retention time interval for the system and viewing retention time intervals

Before you set retention time interval for all tables in a schema or database, consider the cost of storage for temporal tables, which could be significant. See Managing time travel space usage.

Setting the retention time interval for the system

To set the default DATA_VERSION_RETENTION_TIME to a specific value for the system, run the SET SYSTEM DEFAULT command.

Before you set DATA_VERSION_RETENTION_TIME for all tables in a schema or database, consider the cost of storage for temporal tables, which could be significant. See Managing time travel space usage.

SET SYSTEM DEFAULT DATA_VERSION_RETENTION_TIME TO <NUMBER OF DAYS>

Example:

SET SYSTEM DEFAULT DATA_VERSION_RETENTION_TIME TO 30

The value of the property at the system level determines the default value inherited by a subsequent CREATE DATABASE statement that does not explicitly specify this property.

To set DATA_VERSION_RETENTION_TIME for a specific object, you can run the ALTER or CREATE command.

Viewing the retention time interval with the command-line

Viewing the default retention time interval for the system with the command-line

To view DATA_VERSION_RETENTION_TIME for the system, run the SHOW SYSTEM DEFAULT command.

SHOW SYSTEM DEFAULT DATA_VERSION_RETENTION_TIME

If you did not set the retention time previously, the default is 0.

Viewing the retention time interval for tables, schemas, and databases with the command-line

To view DATA_VERSION_RETENTION_TIME for a specific object, run one of these commands. The commands display DATA_VERSION_RETENTION_TIME only if it is a nonzero value.

\d <table_name>

SHOW SCHEMA <schema_name>

\l+ <all databases, with detail>

Retention time interval and retention lower bound for an object are available in the dataverretntime and dataverretnlowerbound columns of the following system views:

_v_table
_v_schema
_v_database

Viewing the retention time interval with the web console

Viewing the default retention time interval for the system with the web console

Log in to the web console as described in Getting started with the web console.
Go to Databases. The retention time interval for the system is displayed in the Retention time interval section at the top of the page.

Viewing the retention time interval for tables, schemas, and databases with the web console

Log in to the web console as described in Getting started with the web console.
View the retention time interval:
- For a table:
  1. Go to Databases.
  2. Select the database and schema in which the table that you want to view the retention interval is located.
  3. Ensure that you are in the DB Objects > Tables tab.
  4. Identify the table for which you want to view the retention interval.
    
    The retention interval is displayed in the Retention time interval (days) column.
- For a schema:
  1. Go to Databases.
  2. Select the database in which the schema that you want to view the retention interval is located.
  3. Identify the schema for which you want to view the retention interval.
    
    The retention interval is displayed in the Retention time interval (days) column.
- For a database:
  1. Go to Databases.
  2. Identify the database for which you want to view the retention interval.
    
    The retention interval is displayed in the Retention time interval (days) column.