98 DBMS_IMMUTABLE_TABLE
Immutable tables are read-only tables that protect data against unauthorized modification. They also prevent against accidental data modifications that may be caused by human errors. The DBMS_IMMUTABLE_TABLE
package allows you to delete the expired rows in an immutable table and add interval partitioning.
This chapter contains the following topics:
98.1 DBMS_IMMUTABLE_TABLE Overview
Immutable tables are read-only
tables that protect data against unauthorized modification. Immutable tables also prevent against accidental data modifications that may be caused by human errors. You must specify a retention period for the immutable table and for rows within the immutable table. An immutable table can be dropped if it contains no rows or if the specified retention period for the table has elapsed. Rows can be deleted from an immutable table only after the specified retention period for rows in the table has elapsed. Except for increasing the retention periods and adding interval partitioning, you cannot modify the definition of an immutable table with this package.
98.2 DBMS_IMMUTABLE_TABLE Security Model
The DBMS_IMMUTABLE_TABLE
package is owned by SYS
and is installed as part of database installation.
The routines in the package are run with invoker's rights and hence run with the privileges
of the current user.
98.3 Summary of DBMS_IMMUTABLE_TABLE Subprograms
This table lists the DBMS_IMMUTABLE_TABLE
subprograms in
alphabetical order and briefly describes them.
Table 98-1 DBMS_IMMUTABLE_TABLE Package Subprograms
Subprogram | Description |
---|---|
ADD_INTERVAL_PARTITIONING Procedure | This procedure adds interval partitioning to an existing, non-partitioned, V1 or V2 immutable table. |
DELETE_EXPIRED_ROWS Procedure | This procedure deletes the expired rows. |
98.3.1 ADD_INTERVAL_PARTITIONING Procedure
This procedure adds interval partitioning to an existing, non-partitioned, V1 or V2 immutable table.
Syntax
DBMS_IMMUTABLE_TABLE.ADD_INTERVAL_PARTITIONING(
schema_name IN VARCHAR2,
table_name IN VARCHAR2,
interval_number IN NUMBER,
interval_frequency IN VARCHAR2,
first_high_timestamp IN TIMESTAMP);
Parameters
Table 98-2 ADD_INTERVAL_PARTITIONING Parameters
Parameter | Description |
---|---|
|
The name of the schema. |
table_name |
The name of the immutable table. |
interval_number |
Sets how often the database creates partitions for the immutable table. |
interval_frequency |
Sets the frequency for the value that was set in the |
first_high_timestamp |
A timestamp that determines the upper boundary of the first partition in the immutable table. |
Usage Notes
- Composite partitioning (that is, sub-partitioning) is not supported with the above interval partitioning.
98.3.2 DELETE_EXPIRED_ROWS Procedure
This procedure deletes some or all of the expired rows from the immutable table. This procedure commits before deleting any expired rows and commits after deleting any expired rows.
Syntax
DBMS_IMMUTABLE_TABLE.DELETE_EXPIRED_ROWS( schema_name IN VARCHAR2, table_name IN VARCHAR2, before_timestamp IN TIMESTAMP WITH TIME ZONE DEFAULT NULL, number_of_rows_deleted OUT NUMBER);
Parameters
Table 98-3 DELETE_EXPIRED_ROWS Procedure Parameters
Parameter | Description |
---|---|
schema_name |
The name of the schema. |
table_name |
The name of the immutable table. |
before_timestamp |
If the parameter is The default value is |
number_of_rows_deleted |
The number of rows deleted. |