In version 19c, Oracle a very interesting feature, giving administrators the ability to create tables where only insert operations are allowed, where row deletion is prohibited or can be restricted for a set period of time, and where table deletion can also be restricted.
It is important to note that in order to use this feature, the database must be version 19.10, patch 32431413 must be applied, and the COMPATIBLE parameter of the database must be set to version 19.10.0 or higher. Starting with version 19.11, the patch is not necessary, only the change in the COMPATIBLE parameter.
If you are running tests on this new feature, be careful not to set a very long retention period, as it will not be possible to delete the tests you are running until the set period is over.
There are a number of clauses that can be added to the command that determine the final state of our blockchain tables.
CREATE BLOCKCHAIN TABLE clauses:
* NO DROP determines the time our table is protected against the "drop" deletion statement.
NO DROP [ UNTIL number DAYS IDLE ]
*NO DROP : The table cannot be deleted.
*NO DROP UNTIL number DAYS IDLE The table cannot be deleted until new rows have been inserted during the specified number of days.ificates.
* NO DELETE determines the period of time in which rows cannot be deleted.
NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }
*NO DELETE O DELETE LOCKED: Rows cannot be deleted.
*NO DELETE UNTIL number DAYS AFTER INSERTThe rows cannot be deleted until X number of days in which the rows were inserted, it is possible to change the retention with the ALTER TABLE command.
*NO DELETE UNTIL x DAYS AFTER INSERT LOCKED: Rows cannot be deleted until X number of days in which the rows were inserted, and it is not possible to change the retention with the ALTER TABLE command. The minimum number of days is 16, otherwise you will receive an error when executing the command.
*HASHING USING sha2_512 VERSION v1
Hash algorithm format clause.
Example:
We create a test table according to the specifications explained above:
In our case we have created the table "oracleconraul", we have left it so that it can be deleted since it is a test environment and we have indicated that 16 days must pass before we can delete data from the table, the minimum possible value.
We insert test values.
We check that the records have been inserted correctly:
When we try to delete data, we receive the message that it is not possible to delete data in our table.
ALTER on a BLOCKCHAIN table
In our example, we could perform thedrop on the table we have created as we will see below, since the table was created with the clause "NO DROP UNTIL 0 DAYS IDLE", which for a test environment could be the most sensible.
Execution of the command on our table with the current values:
In our case to avoid deleting the table we execute the following command:
We perform the checks to verify that everything works as established. We are going to try to delete records that should not be allowed, we are going to insert new data in the table, which should not have any problem and we will try as a final check to try to delete the table that after the alter, should not be possible.
Executions:
insert into raul.oracleconraul values(200,'RAUL');
1 row created.
commit;
SQL> delete raul.oracleconraul where test_id=200;
delete raul.oracleconraul where test_id=200
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table
SQL> drop table raul.oracleconraul;
drop table raul.oracleconraul
*
ERROR at line 1:
ORA-05723: drop blockchain or immutable table ORACLECONRAUL not allowed
To check that everything is correct, we can launch the following query:
In it we can identify which tables we have created as "blockchain" tables and their characteristics.
In short, Oracle a new feature in version 19.10 that allows blockchain applications to be implemented, or provides administrators with an additional security tool to prevent problems of deletion or distortion of audits on any of the "core" tables that cannot be altered in the database. Before applying this functionality, it is very important to consider its correct application, since, as indicated, it is possible that the table that was initially designed not to be deleted/altered, due to the evolution of the application, resources, etc., may subsequently need to be deleted/altered, making it impossible to perform the action.
