8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Immutable Table Enhancements in Oracle Database 23c
Immutable tables were introduced to Oracle 21.3 and 19.11 at the same time, so it could be considered a 19c and 21c new feature. This article demonstrates the enhancements to immutable tables in Oracle 23c. There are some fundamental changes to immutable tables in Oracle 23c, so some of the previous article will be replicated here.
An immutable table is a tamper-proof, insert-only table with an associated table-level and row-level retention period. They are similar to blockchain tables, but the rows are not chained using cryptographic hashes.
When learning about immutable tables, be careful not to set excessively long retention periods, or you will have to wait a long time to drop your test tables.
- Create an Immutable Table
- Alter an Immutable Table
- Blocked DML and DDL Operations
- Add/Drop Columns
- Control Long Idle Retention Times
- DBMS_IMMUTABLE_TABLE Package
- Considerations
Related articles.
- Immutable Tables in Oracle Database 19c and 21c
- Blockchain Tables in Oracle Database 21c
- Immutable Tables in Oracle Database 21c
- Blockchain Table Enhancements in Oracle Database 23c
Create an Immutable Table
In addition to adding the IMMUTABLE
keyword to the CREATE TABLE
command, there are two immutable clauses.
The NO DROP
clause determines how long the table is protected from being dropped. If the table has no rows it can still be dropped. Unlike the initial releases of blockchain tables, the NO DROP
clause also prevents the table being dropped via a DROP USER ... CASCADE
command.
NO DROP [ UNTIL number DAYS IDLE ]
NO DROP
: The table can't be dropped. Be careful about using this setting during testing.NO DROP UNTIL number DAYS IDLE
: The table can't dropped until there have been no new rows inserted for the specified number of days. You may prefer to use 0 or 1 as the number of days during testing this functionality.
The NO DELETE
clause determines the retention period. How long each row will be protected from deletion.
NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }
NO DELETE
: Each row is retained forever. The absence of theLOCKED
keyword implies the setting can be changed with theALTER TABLE
command, but it can't. Retention periods can only be increased.NO DELETE LOCKED
: Same asNO DELETE
.NO DELETE UNTIL number DAYS AFTER INSERT
: Each row is protected from deletion for the specified number of days, but this setting can be increased using theALTER TABLE
command. Minimum 16 days.NO DELETE UNTIL number DAYS AFTER INSERT LOCKED
: Each row is protected from deletion for the specified number of days, and this setting can't be changed using theALTER TABLE
command. Minimum 16 days.
In Oracle 23c we have the option of using the original "V1" version, which is the default, or the new "V2" version, which supports additional functionality. The following example creates two tables. One of each version.
drop table if exists it_t1 purge; create immutable table it_t1 ( id number, fruit varchar2(20), quantity number, created_date date, constraint it_t1_pk primary key (id) ) no drop until 0 days idle no delete until 16 days after insert; drop table if exists it_t2 purge; create immutable table it_t2 ( id number, fruit varchar2(20), quantity number, created_date date, constraint it_t2_pk primary key (id) ) no drop until 0 days idle no delete until 16 days after insert version "v2";
Checking the USER_TAB_COLS
view shows us several invisible columns have been added to our column list. The hidden columns are the same as those of a blockchain table, but unlike blockchain tables, only the ORABCTAB_CREATION_TIME$
and ORABCTAB_USER_NUMBER$
columns are populated. The rest of the columns are set to null. The hidden columns are described here. Notice the "V2" table has twice the number of hidden columns compared to the "V1" table.
set linesize 120 pagesize 50 column column_name format a45 column data_type format a27 column hidden_column format a13 select internal_column_id, column_name, data_type, data_length, hidden_column FROM user_tab_cols WHERE table_name = 'IT_T1' ORDER BY internal_column_id; INTERNAL_COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN ------------------ --------------------------------------------- --------------------------- ----------- ------------- 1 ID NUMBER 22 NO 2 FRUIT VARCHAR2 20 NO 3 QUANTITY NUMBER 22 NO 4 CREATED_DATE DATE 7 NO 5 ORABCTAB_INST_ID$ NUMBER 22 YES 6 ORABCTAB_CHAIN_ID$ NUMBER 22 YES 7 ORABCTAB_SEQ_NUM$ NUMBER 22 YES 8 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13 YES 9 ORABCTAB_USER_NUMBER$ NUMBER 22 YES 10 ORABCTAB_HASH$ RAW 2000 YES 11 ORABCTAB_SIGNATURE$ RAW 2000 YES 12 ORABCTAB_SIGNATURE_ALG$ NUMBER 22 YES 13 ORABCTAB_SIGNATURE_CERT$ RAW 16 YES 14 ORABCTAB_SPARE$ RAW 2000 YES 14 rows selected. SQL> select internal_column_id, column_name, data_type, data_length, hidden_column FROM user_tab_cols WHERE table_name = 'IT_T2' ORDER BY internal_column_id; INTERNAL_COLUMN_ID COLUMN_NAME DATA_TYPE DATA_LENGTH HIDDEN_COLUMN ------------------ --------------------------------------------- --------------------------- ----------- ------------- 1 ID NUMBER 22 NO 2 FRUIT VARCHAR2 20 NO 3 QUANTITY NUMBER 22 NO 4 CREATED_DATE DATE 7 NO 5 ORABCTAB_INST_ID$ NUMBER 22 YES 6 ORABCTAB_CHAIN_ID$ NUMBER 22 YES 7 ORABCTAB_SEQ_NUM$ NUMBER 22 YES 8 ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE 13 YES 9 ORABCTAB_USER_NUMBER$ NUMBER 22 YES 10 ORABCTAB_HASH$ RAW 2000 YES 11 ORABCTAB_SIGNATURE$ RAW 2000 YES 12 ORABCTAB_SIGNATURE_ALG$ NUMBER 22 YES 13 ORABCTAB_SIGNATURE_CERT$ RAW 1000 YES 14 ORABCTAB_SPARE$ RAW 2000 YES 15 ORABCTAB_PDB_GUID$ RAW 2000 YES 16 ORABCTAB_ROW_VERSION$ NUMBER 22 YES 17 ORABCTAB_LAST_ROW_VERSION_NUMBER$ RAW 1 YES 18 ORABCTAB_USER_CHAIN_HASH$ RAW 2000 YES 19 ORABCTAB_DELEGATE_SIGNATURE$ RAW 2000 YES 20 ORABCTAB_DELEGATE_SIGNATURE_ALG$ NUMBER 22 YES 21 ORABCTAB_DELEGATE_SIGNATURE_CERT$ RAW 1000 YES 22 ORABCTAB_DELEGATE_USER_NUMBER$ NUMBER 22 YES 23 ORABCTAB_COUNTERSIGNATURE$ RAW 2000 YES 24 ORABCTAB_COUNTERSIGNATURE_ALG$ NUMBER 22 YES 25 ORABCTAB_COUNTERSIGNATURE_CERT$ RAW 1000 YES 26 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_VERSION$ VARCHAR2 4000 YES 27 ORABCTAB_COUNTERSIGNATURE_ROW_FORMAT_FLAG$ NUMBER 22 YES 28 ORABCTAB_TS$ TIMESTAMP(6) 13 YES 28 rows selected. SQL>
The {CDB|DBA|ALL|USER}_IMMUTABLE_TABLES
views display information about immutable tables. It's a view over the SYS.IMMUTABLE_TABLE$
table.
column table_name format a10 column row_retention format 9999999999999 column row_retention_locked format a20 column table_inactivity_retention format 99999999999999999999999999 SELECT table_name, row_retention, row_retention_locked, table_inactivity_retention FROM user_immutable_tables order by 1; TABLE_NAME ROW_RETENTION ROW_RETENTION_LOCKED TABLE_INACTIVITY_RETENTION ---------- -------------- -------------------- --------------------------- IT_T1 16 NO 0 IT_T2 16 NO 0 SQL>
Alter an Immutable Table
The following operations perform the same on "V1" and "V2" blockchain tables.
The NO DROP
clause can be altered using the ALTER TABLE
command, as long as the retention period is not reduced. Be careful not to set it too large when testing.
alter table it_t1 no drop until 1 days idle;
Regardless of the current drop delay setting, an attempt to switch to the maximum value of NO DROP
causes an error. This is because of the BLOCKCHAIN_TABLE_RETENTION_THRESHOLD
parameter discussed below.
alter table it_t1 no drop; * ERROR at line 1: ORA-05807: Blockchain or immutable table "TESTUSER1"."IT_T1" cannot have idle retention greater than 16 days. SQL>
Assuming it was not defined as locked, the NO DELETE
clause can be modified using the ALTER TABLE
command, as long as the retention period is not reduced. We currently have a row retention period of 16 days. In the example below we increase that value to 32. When we subsequently attempt to lower the value to 16 it gives an error.
-- Increase to 32 days. alter table it_t1 no delete until 32 days after insert; Table IT_T1 altered. SQL> -- Decrease to 16 days (fail). alter table it_t1 no delete until 16 days after insert; Error report - ORA-05732: retention value cannot be lowered SQL>
We can set the row retention to NO DELETE
, which means rows will be kept forever. Remember, we can't reduce this value once is it set, so take care using such a long retention period.
alter table it_t1 no delete;
We'll drop and recreate the "V1" immutable table. We've not inserted any rows into it yet, so despite the retention times the following command works.
drop table if exists it_t1 purge; create immutable table it_t1 ( id number, fruit varchar2(20), quantity number, created_date date, constraint it_t1_pk primary key (id) ) no drop until 0 days idle no delete until 16 days after insert;
Blocked DML and DDL Operations
As you would expect for an insert-only table, all DML and DDL operations that would result in row data being amended or deleted are prevented for an immutable table.
The following example shows a successful insert, then some unsuccessful DML statements.
-- INSERT insert into it_t2 (id, fruit, quantity, created_date ) values (1, 'apple', 20, sysdate); 1 row inserted. SQL> commit; Commit complete. SQL> -- UPDATE update it_t2 set quantity = 10 where id = 1; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL> -- DELETE delete from it_t2 where id = 1; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL>
Some DDL statements that could alter the contents of the data are also prevented. Here is an example of the TRUNCATE
statement.
truncate table it_t1; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL>
Extending existing columns is fine.
alter table it_t2 modify (fruit varchar2(25)); Table altered. SQL>
Add/Drop Columns
In a "V1" immutable table adding new columns or dropping existing columns is not allowed.
-- Add column alter table it_t1 add (additional_info varchar2(50)); * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL> -- Drop column. alter table it_t1 drop column quantity; * ERROR at line 1: ORA-05715: operation not allowed on the blockchain or immutable table SQL>
In a "V2" immutable table we can add new columns and drop existing columns. The dropped columns are marked as hidden, rather than actually being dropped.
-- Add column alter table it_t2 add (additional_info varchar2(50)); Table altered. SQL> -- Drop column. alter table it_t2 drop column additional_info; Table altered. SQL>
Control Long Idle Retention Times
In Oracle 23c the maximum idle retention time for the table is controlled by the BLOCKCHAIN_TABLE_RETENTION_THRESHOLD
parameter.
show parameter blockchain_table_retention_threshold NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ blockchain_table_retention_threshold integer 16 SQL>
This means we can't set the NO DROP
clause to a value longer than 16 days.
drop table if exists it_t3 purge; create immutable table it_t3 ( id number, fruit varchar2(20), quantity number, created_date date, constraint it_t3_pk primary key (id) ) no drop until 32 days idle no delete until 16 days after insert; * ERROR at line 1: ORA-05807: Blockchain or immutable table "TESTUSER1"."IT_T3" cannot have idle retention greater than 16 days. SQL>
We can either increase this setting, or grant the user the TABLE RETENTION
privilege, which allows the user to ignore this limit.
conn sys/SysPassword1@//localhost:1521/freepdb1 as sysdba grant table retention to testuser1; conn testuser1/testuser1@//localhost:1521/freepdb1
Now we can ignore the limit.
drop table if exists itt_t3 purge; create immutable table it_t3 ( id number, fruit varchar2(20), quantity number, created_date date, constraint it_t3_pk primary key (id) ) no drop until 32 days idle no delete until 16 days after insert; Table created. SQL>
DBMS_IMMUTABLE_TABLE Package
The DBMS_IMMUTABLE_TABLE
package is used for maintenance of immutable tables.
The DELETE_EXPIRED_ROWS
procedure removes any rows that are beyond the retention period. They can't be removed using a normal DELETE
statement.
set serveroutput on declare l_rows number; begin dbms_immutable_table.delete_expired_rows( schema_name => 'testuser1', table_name => 'it_t1', before_timestamp => null, number_of_rows_deleted => l_rows); dbms_output.put_line('Rows Deleted=' || l_rows); end; / Rows Deleted=0 PL/SQL procedure successfully completed. SQL>
Alternatively, we can limit the deletion by date. The rows will only be deleted if they are outside the retention period, and match the date criteria.
set serveroutput on declare l_rows number; begin dbms_immutable_table.delete_expired_rows( schema_name => 'testuser1', table_name => 'it_t1', before_timestamp => systimestamp - 60, number_of_rows_deleted => l_rows); dbms_output.put_line('Rows Deleted=' || l_rows); end; / Rows Deleted=0 PL/SQL procedure successfully completed. SQL>
Considerations
There are a number of things to consider when using immutable tables.
- Immutable tables can be indexed and partitioned in the normal manner.
- There are a number of general restrictions associated with blockchain tables, described here.
I guess the main question should be, why would you use an immutable table?
- If you need an insert-only tamper proof table in your application generally, this could be the solution.
- If you want the added security of cryptographic hashes, you might want to consider blockchain tables.
For more information see:
- Managing Immutable Tables
- DBMS_IMMUTABLE_TABLE
- ALL_IMMUTABLE_TABLES
- CREATE TABLE
- Immutable Tables in Oracle Database 19c and 21c
- Blockchain Tables in Oracle Database 21c
- Immutable Tables in Oracle Database 21c
- Blockchain Table Enhancements in Oracle Database 23c
Hope this helps. Regards Tim...