In version 19c Oracle introduced a very interesting feature, giving the administrator the possibility to create tables where only insert operations are allowed, where the deletion of rows is forbidden or can be restricted for a set time, as well as it is also possible to restrict the deletion of a table.
It is important to add that in order to enjoy this feature it is necessary that the database is in version 19.10, the patch 32431413 applied and the COMPATIBLE parameter of the database is set to version 19.10.0 or higher. From version 19.11 onwards the patch is not necessary, if the COMPATIBLE parameter is changed.
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 IDLEThe table cannot be deleted until new rows have been inserted during the specified number of days.ificates.
* NO DELETE determines the time period in which rows cannot be deleted.
NO DELETE { [ LOCKED ] | (UNTIL number DAYS AFTER INSERT [ LOCKED ]) }
NO DELETEO DELETE LOCKED: Rows cannot be deleted.NO DELETE UNTIL number DAYS AFTER INSERTRows 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 offers from version 19.10 a new functionality that allows implementing blockchain applications, or allows the administrator to have an additional security tool to avoid problems of deletion or distortion of audits on any of the "cores" tables that cannot be altered in the database. It is very important before applying this functionality, to think about its correct application, since, as indicated, it is possible that the table that was initially conceived not to be deleted/altered, due to the evolution of the application, resources, etc., may have to be deleted/altered later on and it may be impossible to perform the action.