create immutable table tq84_immutable_table (
txt varchar2(20),
dat date
)
no drop until 16 days idle
no delete until 20 days after insert;
Without records, the table can be dropped:
drop table tq84_immutable_table;
Recreae the table and insert a record:
create immutable table tq84_immutable_table (
txt varchar2(20),
dat date
)
no drop until 16 days idle
no delete until 20 days after insert;
begin
insert into tq84_immutable_table values ('first record', sysdate);
commit;
end;
/
The table cannot be dropped anymore, the following statement throws ORA-05723: dropping TQ84_IMMUTABLE_TABLE, which is a non-empty blockchain or immutable table, is not allowed:
drop table tq84_immutable_table;
Hidden columns
An immutable table creates the following hidden columns:
orabctab_inst_id$
orabctab_chain_id$
orabctab_seq_num$
orabctab_creation_time$
orabctab_user_number$
orabctab_hash$
orabctab_signature$
orabctab_signature_alg$
orabctab_signature_cert$
orabctab_spare$
select
internal_column_id,
column_name,
data_type,
data_length,
hidden_column
from
user_tab_cols
where
table_name = 'TQ84_IMMUTABLE_TABLE' and
column_name not in (select column_name from user_immutable_table_columns where table_name = 'TQ84_IMMUTABLE_TABLE')
order by
internal_column_id;
select
col.col_name,
col.col_pos,
col.col_id,
col.data_type
from
dba_objects obj,
sys.orabctab_immutable_cols(obj.object_id) col
where
obj.object_name = 'TQ84_IMMUTABLE_TABLE' and
obj.owner = 'RENE'
;