Blockchain Row Retention Clause-Database Tables

The blockchain row retention clause controls when rows can be removed from the table.
The syntax for the blockchain row retention clause is as follows:
NO DELETE { [ LOCKED ] | (UNTIL NUMBER DAYS AFTER INSERT [ LOCKED ]) }

If you specify NO DELETE or NO DELETE LOCKED (with no UNTIL clause), then you cannot ever delete, update, or truncate any rows inserted into this table. The only way to remove the data is to drop the table or drop the user that owns the table. The table can only be dropped if it is inactive for more than the number of days specified in the blockchain drop table clause.

The UNTIL NUMBER DAYS AFTER INSERT value specifies the number of days after a row is inserted that it can be deleted. The minimum value allowed for this is 16.

If you specify LOCKED, then you can’t change the NUMBER DAYS retention using an ALTER TABLE command. If you do not specify LOCKED in the clause UNTIL NUMBER DAYS AFTER INSERT, then you can change the retention period using an ALTER TABLE command (but only to a value higher than the previous retention period).

Blockchain Hash and Data Format Clause
This clause specifies cryptographic function used when generating the hash signature for each newly inserted row. The syntax for this clause is as follows:
HASHING USING sha2_512 VERSION v1

You must specify this clause last in the CREATE BLOCKCHAIN TABLE statement (e.g., it must be placed after all other clauses). Also, you cannot modify this clause via the ALTER TABLE statement.

Creating a Blockchain Table
Now that you have an understanding of what a blockchain table is and the clauses that control its behavior, let’s look at creating a blockchain table. Here’s an example:

$ sqlplus eoda/foo@PDB1
SQL> create blockchain table ledger(id number,username varchar2(30),value number)no drop until 0 days idleno delete lockedhashing using SHA2_512 version v1;Table created.

In the prior code, since DROP UNTIL 0 DAYS IDLE is specified, this means the table can be dropped at anytime. Normally, you wouldn’t specify zero days, but this allows you to drop this table while you’re testing this feature.

The NO DELETE LOCKED means that the data can never be deleted, modified, or truncated. The only way you can remove the data is by dropping the table or dropping the user that owns the table. For example, observe what happens after we insert data and try to remove it:

SQL> insert into ledger values(1,’HEIDI’,1); 1 row created.
SQL> delete from ledger*ERROR at line 1:
ORA-05715: operation not allowed on the blockchain table
You can verify the blockchain table characteristics as follows:
SQL> select * from user_blockchain_tables;
TABLE_NAME ROW_RETENTION ROW TABLE_INACTIVITY_RETENTION HASH_ALG
LEDGER 16 NO 0 SHA2_512

Note In addition to the restrictions on dropping the table or removing data from a blockchain table, you cannot add columns, remove columns, nor can you mark a column as unused.

Other than blocked DML and DDL (deletes and updates), a blockchain table looks and acts like a regular table. You can access this table like you would any other table (that you have privileges to access). This allows companies to identify and prevent illicit data changes.

Tip When you create a blockchain table, an entry is created in the dictionary table blockchain_table$ owned by SYS.

When you create a blockchain table, Oracle adds hidden columns to the table. These columns can be viewed as follows:
SQL> select column_name, data_type, hidden_column from user_tab_cols
where table_name =’LEDGER’order by column_id;COLUMN_NAME DATA_TYPE HID

ID NUMBER NO
USERNAME VARCHAR2 NO
VALUE NUMBER NO
ORABCTAB_SPARE$ RAW YES
ORABCTAB_USER_NUMBER$ NUMBER YES
ORABCTAB_HASH$ RAW YES
ORABCTAB_SIGNATURE$ RAW YES
ORABCTAB_SIGNATURE_ALG$ NUMBER YES
ORABCTAB_SIGNATURE_CERT$ RAW YES
ORABCTAB_SEQ_NUM$ NUMBER YES
ORABCTAB_CHAIN_ID$ NUMBER YES
ORABCTAB_INST_ID$ NUMBER YES
ORABCTAB_CREATION_TIME$ TIMESTAMP(6) WITH TIME ZONE YES

These columns are used to keep track of the data in the table to ensure that the cryptographic sequences are maintained. You wouldn’t normally need to query these columns. Rather, these are columns that Oracle uses to store information that maintains the integrity of a blockchain table.