IMMUTABLE

9.11 IMMUTABLE_TABLE

This command uses an extension of the DBMS_IMMUTABLE_TABLE PL/SQL package.

Immutable tables are read-only tables that protect data against unauthorized modification. These tables also prevent against accidental data modifications that may be caused by human errors. The DBMS_IMMUTABLE_TABLE PL/SQL package enables you to delete the expired rows in an immutable table and add interval partitioning.

The sub-commands are:

9.11.1 Add Interval Partitioning

Adds interval partitioning to an existing, non-partitioned, V1 or V2 immutable table.

Syntax

immutable_table|im add_interval_partitioning|addintpartition {OPTIONS}

Options

Option	Description
Required
`-table_name\|-tab <table_name>`	Specifies a name for the immutable table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes. Note: Case sensitive, embedded white space table and schema name should be entered as `"""Alp_ha"".""Be $a"""`. The parser parses these values as `"Alp_ha"."Be $a".`
`-interval_number\|-intnum <interval_number>`	Specifies the interval number that determines how often the database creates partitions for the immutable table.
`-interval_frequency\|-intfreq <interval_frequency>`	Specifies the interval frequency that determines how frequently the database creates partitions for immutable tables within a specified interval set by the `interval_number` setting. Supported values are `YEAR, MONTH, DAY, HOUR, and MINUTE`.
`-first_high_timestamp\|-firsthigh <first_high_timestamp>`	Specifies a timestamp that determines the upper boundary of the first partition in the immutable table.

9.11.2 Delete Expired Rows

Deletes some or all of the expired rows from the immutable table. This command does not commit if the database release is 19c or 21c. This command commits if the database release is at least 23ai.

Syntax

immutable_table|im delete_expired_rows|del {OPTIONS}

Options

Option	Description
Required
`-table_name\|-tab <table_name>`	Specifies a name for the immutable table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes. Note: Case sensitive, embedded white space table and schema name should be entered as `"""Alp_ha"".""Be $a"""`. The parser parses these values as `"Alp_ha"."Be $a".`
Optional
`-before_timestamp\|-before <before_timestamp>`	Specifies a timestamp to determine deletion of expired rows. Set this parameter according to the NLS_TIMESTAMP_TZ_FORMAT or NLS_TIMESTAMP_FORMAT. The default value for this parameter is NULL. If the parameter value is NULL, all expired rows in the table are deleted. If the parameter value is not NULL and is older than the timestamp calculated based on the current time and the row retention period, then rows with timestamps earlier than the parameter value are deleted. If the parameter value is not NULL and is more recent than the timestamp calculated based on the current time and row retention period, the calculated timestamp is used, resulting in the deletion of all expired rows.
`-rowcount <rowcount>`	Requests the number of rows deleted. This is an OUT parameter option, used for storing the output value of the command in a bind variable.

9.11.3 Describe Table

Describes the details of the specified immutable table.

Syntax

immutable_table|im desc {OPTIONS}

Option	Description
Required
`-table_name\|-tab <table_name>`	Specifies a name for the immutable table. The name can be preceded by the respective schema name. To specify a case-sensitive schema or table name, enclose the entire name in double quotes and then enclose the individual names in double, double quotes.