Hi Guys,
Recently I started the ‘Top 5’ series where I share my top 5 features in any particular tool or product. Last time I did for SQL Developer command line (SQLcl) & TOP utility, this time it will be about top 5 features in Oracle 21c database.
So, without any particular order, below are my top 5 Oracle 21c features …
1. Immutable Tables :
Native Blockchain Tables provide in-database immutable, insert-only tables. This type of tamper resistance helps protect against hacks and illegal changes and is a great feature added into Oracle 21c database. Even an account with DBA role cannot modify there tables. Immutable tables intended for use in an environment where it is required that an audit trail could potentially be tampered with my insertion but that once a record was inserted it would not be possible to alter or delete it except within the date constraints imposed as part of the NO DROP and NO DELETE clauses.
Let me do a demo to explain!
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 21.0.0.0.0 - Production on Thu Sep 2 12:03:22 2021
Version 21.3.0.0.0
Copyright (c) 1982, 2021, Oracle. All rights reserved.
Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.3.0.0.0
SQL>
SQL>
--- You will get an error if you try to create it in the roo container.
SQL> create immutable table testimmu (
id number,
testname varchar2(20),
class number,
created_date date,
constraint testimmu_pk primary key (id)
)
no drop until 1 days idle
no delete until 20 days after insert;
create immutable table testimmu (
*
ERROR at line 1:
ORA-05729: blockchain or immutable table cannot be created in root container
SQL> alter session set container = ORCLPDB1;
Session altered.
SQL> show con_name
CON_NAME
------------------------------
ORCLPDB1
SQL>
SQL> show user
USER is "SYS"
SQL>
SQL> create immutable table testimmu (
id number,
testname varchar2(20),
class number,
created_date date,
constraint testimmu_pk primary key (id))
no drop until 1 days idle
no delete until 20 days after insert;
Table created.
SQL>
SQL>
SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';
Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
20 NO 1
SQL>
-- lets try to alter the NO DELETE clause.
SQL> alter table testimmu no delete until 60 days after insert;
Table altered.
SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';
Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
60 NO 1
-- What happens when anyone tries to lower down that ?
SQL> alter table testimmu no delete until 59 days after insert;
alter table testimmu no delete until 59 days after insert
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered
-- Lets insert some data.
SQL> insert into testimmu (id, testname, class, created_date) values (10,'Elisa',50,sysdate-1);
1 row created.
SQL>
SQL> select * from testimmu;
ID TESTNAME CLASS CREATED_D
---------- -------------------- ---------- ---------
10 Elisa 50 01-SEP-21
-- Now try to UPDATE the table record.
SQL> update testimmu set CLASS=40 where TESTNAME='Elisa';
update testimmu set CLASS=40 where TESTNAME='Elisa'
*
ERROR at line 1:
ORA-05715: operation not allowed on the blockchain or immutable table
SQL> alter table testimmu no drop;
Table altered.
SQL>
SQL> SELECT row_retention "Row Retention Period", row_retention_locked "Row Retention Lock", table_inactivity_retention "Table Retention Period" FROM dba_immutable_tables WHERE table_name = 'TESTIMMU';
Row Retention Period Row Table Retention Period
-------------------- --- ----------------------
60 NO 365000
SQL>
-- Now will try to drop it and will see what will happen.
SQL> drop table testimmu;
drop table testimmu
*
ERROR at line 1:
ORA-05723: drop blockchain or immutable table TESTIMMU not allowed
SQL> alter table testimmu no drop until 10 days idle;
alter table testimmu no drop until 10 days idle
*
ERROR at line 1:
ORA-05732: retention value cannot be lowered
2. Compare EXECUTION PLANS:
Starting from 21c, now you can compare your execution plans. This is a great in-build feature which helps you to identify the differences between any two plans. Maybe a demo can help explaining how …
SQL> explain plan
2 set statement_id = 'd1'
3 for select /*+ full(bigtab) */ * from bigtab where id=840;
Explained.
SQL>
SQL> explain plan
2 set statement_id = 'd2'
3 for select /*+ index(bigtab) */ * from bigtab where id=840;
Explained.
SQL>
SQL> VARIABLE d varchar2(5000)
SQL> exec :d := dbms_xplan.compare_explain('d1','d2')
PL/SQL procedure successfully completed.
SQL>
SQL> print d
D
----------------------------------------------------------------------------------------------------------------
COMPARE PLANS REPORT
---------------------------------------------------------------------------------------------
Current user : SYS
Total number of plans : 2
Number of findings : 1
---------------------------------------------------------------------------------------------
COMPARISON DETAILS
---------------------------------------------------------------------------------------------
Plan Number : 1 (Reference Plan)
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SYS
Plan Table Name : PLAN_TABLE
Statement ID : d1
Plan ID : 1
Plan Database Version : 21.0.0.0
Parsing Schema : "SYS"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 441133017
-----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74 | 2590 | 71 | 00:00:01 |
| * 1 | TABLE ACCESS FULL | BIGTAB | 74 | 2590 | 71 | 00:00:01 |
-----------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=840)
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
---------------------------------------------------------------------------------------------
Plan Number : 2
Plan Found : Yes
Plan Source : Plan Table
Plan Table Owner : SYS
Plan Table Name : PLAN_TABLE
Statement ID : d2
Plan ID : 2
Plan Database Version : 21.0.0.0
Parsing Schema : "SYS"
SQL Text : No SQL Text
Plan
-----------------------------
Plan Hash Value : 3941851520
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74 | 2590 | 87 | 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED | BIGTAB | 74 | 2590 | 87 | 00:00:01 |
| * 2 | INDEX RANGE SCAN | IDX_TESTA | 74 | | 1 | 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=840)
Notes
-----
- Dynamic sampling used for this statement ( level = 2 )
Comparison Results (1):
-----------------------------
1. Query block SEL$1, Alias "BIGTAB"@"SEL$1": Access path is different -
reference plan: FULL (line: 1), current plan: INDEX_RS_ASC (lines: 1, 2).
---------------------------------------------------------------------------------------------
3. CHECKSUM with export dumps
This is again a great feature and this is to avoid any data tampering or modifications in export dumps. So, you generate the checksum at the time of export backup and if someone modifies any data into the dump (via any editing tool), it will be highlighted during import time.
-- To create a checksum at the time of exporting the data in to dump.
[oracle@localhost admin]$ expdp system/*****@*** DIRECTORY=testdirectory1 DUMPFILE=bigtabtestbkp.dmp tables=bigtab CHECKSUM=YES
-- To verify checksum during import
[oracle@localhost admin]$ impdp system/*****@*** DUMPFILE=bigtabtestbkp.dmp verify_checksum=yes
4. Zero Down Time Timezone Upgrade :
One problem with this is that DST patch required “startup upgrade”, I mean it is not a RAC-rolling patch and standby databases have to be in MOUNT mode. With this feature now these patches are RAC-rolling patches and standby databases can be OPEN-ed. This is really cool! Who knows they will even be part of RU & RUR’s 🙂
5. MULTIVALUE INDEX on JSON data.
I’ve recently starting using JSON data for one of our data and I know how difficult it was when you are doing searches using JSON_EXISTS or JSON_QUERY operators in your query.
Now in 21c, a new create index syntax CREATE MULTIVALUE INDEX allows you to create a functional index on arrays of strings or numbers within a JSON type column. Each unique value within the array will become a searchable index entry. This avoids the need for full JSON scans to find values within arrays in JSON columns, when searched using the JSON_EXISTS or JSON_QUERY operators.
CREATE MULTIVALUE INDEX idx_jsndatest ON mytable tempjsdata (temp.jcol.item_grade.numberOnly());There are few other good features like automatic materialized views, SQL Macros, Expressions in Initialization Parameters (i.e. alter system set pga_aggregate_target=’sga_target/2‘ now possible) etc. available with 21c.
Hope It Helped
Prashant Dixit
Tales From A Lazy Fat DBA 