Check the block chain table in Oracle

Oracle introduce the block-chain table which only allow the insert operations.
Delete operation is restricted based on time.
Blockchain tables are insert-only tables that organize rows into a number of chains.
Each row is linked to previous row, expect first row.
Rows in block-chain table are tamper-proof.

Check the block chain table present in schema:

Column schema_name for a10
column table_name for a10
column row_retention for a15
column row_retention_locked for a20
column table_inactivity_retention for a25
column hash_algorithm for a15

SELECT schema_name, table_name, row_retention,row_retention_locked, 
table_inactivity_retention, hash_algorithm  
FROM   dba_blockchain_tables 
WHERE  table_name = 'BANK_EXAMPLE';

Note:
Row_retention: Specify minimum no of days rows must be retained, not deleted. IF value is NULL, row can never be deleted from table.
row_retention_locked: Y means locked(cannot change row retention period) N means you can alter but value higher then current value.
example:  ALTER TABLE … NO DELETE UNTIL n DAYS AFTER INSERT
Table_inactivity_Retention: Number of days for which the blockchain table must be inactive before it can be dropped.
Hash Algorithm: Algorithm used for computing the hash value for each table row.

Syntax of create Block-chain table:

CREATE blockchain table tn( column_name datatype....)
NO DROP [ UNTIL number DAYS IDLE ]
NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }
HASHING USING sha2_512 VERSION v1;


Use carefully clause of Blockchain table:
NO DROP means table cannot be dropped.
NO DROP UNTIL 10 DAYS IDLE means no activity for 10 days then you can drop table.
NO DELETE means rows never deleted from table.
NO DELTE UNTIL number DAYS AFTER INSERT means row can be deleted after specified number of days.
[locked] means setting can be changed with alter command. retention only increased by alter command.

Example of Creating BlockChain table:

CREATE BLOCKCHAIN TABLE bank_Example ( id number,deposit date, amount number)
No drop until 30 days idle
NO delete until 25 days after insert 
hashing using "SHA2_512" version "V1";

ALTER BLOCKCHAIN table RETENTION for rows

-- Increase to 30 days.
alter table bank_Example no delete until 30 days after insert;


-- Decrease to 15 days.
alter table bank_Example no delete until 15 days after insert;
ORA-05732: retention value cannot be lowered

INSERT, UPDATE or DELETE Operation in Block-chain table:

Note: we only performed insert operation on block chain table. No update or delete operation performed.

Insert into bank_example values (1,'25-JUN-2021',10000);
1 row inserted.

-- Update or delete operation give error:
Error: ORA-05715: operation not allowed on the blockchain table

DBMS_BLOCKCHAIN_TABLE package used the delete procedure “delete_expired_rows” in blockchain table

DELETE_EXPIRED_ROWS: For removes row above the retention period we need to use package instead of normal delete operation on blockchain table. Check retention period with dba_blockchain_tables view. you can also specify condition as change input parameter value “before_timestamp” to package “dbms_blockchain_table”
Example:
Delete 3 month later data and above retention period rows with specified: before_timestamp => systimestamp – 90

--Following example used to remove rows present beyond the retention period specified for blockchain table:

set serveroutput on
declare
  v_rows  number;
begin
  dbms_blockchain_table.delete_expired_rows(
    schema_name            => 'hr',
    table_name             => 'bank_example',
    before_timestamp       => null,   -- Delete all rows above retention period
	-- before_timestamp => systimestamp - 90  -- delete all rows above retention period and 90 days
    number_of_rows_deleted => v_rows);

  dbms_output.put_line('Deleted number of rows=' || v_rows);
end;
/

VERIFY_ROWS procedure verify the rows are having consistent hash and signature value. Note: Both row or procedure return same value if no issue find in consistent rows or signature.

set serveroutput on
declare
  n_total_rows      number;
  n_check  number;
begin
  select count(*) into n_total_rows from hr.bank_example;

  dbms_blockchain_table.verify_rows(
    schema_name             => 'hr',
    table_name              => 'bank_example',
    number_of_rows_verified => n_check);

  dbms_output.put_line('Total rows in table=' || n_total_rows || '  checked Rows=' || n_check);
end;
/