Blockchain tables
A blockchain-
table is an
insert-only and
tamperproof table.
(Compare with immutable tables that exist since Oracle 19.11).
Block chain tables were backported to 19.10 with patch 32431413 (requires compatible to be set to at least 19.10.0).
19.11 does not require this patch.
Analytical functions
The window clause allows to name over(partition by … order by …) definition once and then be used multiple times:
select
first_value(col) over wnd as …,
last_value (col) over wnd as …
from
tab
WINDOW wnd as (partition by … order by …)
Besides rows between … and range between …, there is also a groups between:
select
count(col) over wnd_row as cnt_row,
count(col) over wnd_rng as cnt_rng,
count(col) over wnd_grp as cnt_grp
from
tab
window
wnd_row as (order by col rows between 1 preceding and current row),
wnd_rng as (order by col range between 1 preceding and current row),
wnd_grp as (order by col groups between 1 preceding and current row)
;
The exclude clause:
… over (… exclude no other ) …
… over (… exclude current row) …
… over (… exclude group ) …
… over (… exclude ties ) …
CHECKSUM
checksum is a new analytical function that calculates the checksum of the input values or expressions.
JSON
21c introduced a proper («native»)
datatype to store JSON documents. The name of this data type happens to be
json.
With 21c, JSON is also supported in Advanced Queuing.
Misc
The set operator
minus is improved with the
minus all variant,
intersect with
intersect all.
Optimizer features:
-
WITH clause filter predicate pushdown
- Number of distinct value (NDV) modeling for real time statistics
The word database now refers to either a CDB or a PDB.
21c introduces Transactional Event Queues (TEQ): Partitioned message queues that combine messaging, streaming, direct messages, publish/subcribe.
TEQ is an optimized implementation of AQ (previously known as AQ Sharded Queues).
Sample schemas are not installed by default anymore, they must be cloned from github.
Also: AutoML
In-memory enhancements
Multitenant enhancements
Easier sharding
Gradual passwort rollover
Peristent memory
Better graph analytics
Oracle Wallet Manager (OWM) is deprecated.
ACFS (formerly an abbreviation for Oracle Automatic Storage Management Cluster File System ) now stands for Oracle Advanced Cluster File System.
dbnest_enable | database Nest enable |
dbnest_pdb_fs_conf | PDB Filesystem configuration |
diagnostics_control | controls response when 'enabling diagnostics' privilege is absent |
enable_per_pdb_drcp | Turn on/off per PDB DRCP |
heartbeat_batch_size | Number of heartbeats to be sent in a batch |
inmemory_deep_vectorization | In-Memory Deep Vectorization Enabled |
mandatory_user_profile | Enforce Mandatory Password Profile for multitenant database |
optimizer_capture_sql_quarantine | enable automatic creation/update of SQL quarantine configuration |
optimizer_use_sql_quarantine | enable use of SQL quarantine |
pmem_filestore | Persistent Memory Filestore list |
result_cache_execution_threshold | Minimum executions before a PL/SQL function is cached |
result_cache_max_temp_result | maximum temp per result as percent of total temp for result cache |
result_cache_max_temp_size | maximum amount of temp space to be used |
tablespace_encryption_default_algorithm | default tablespace encryption block cipher mode |
tde_key_cache | Enable caching of TDE intermediate key |
timezone_version_upgrade_integrity | perform DST upgrade leveraging constraints with desired integrity (really new with 21c ???) |
dba_activity_config | Configuration parameters that control the Object Activity Tracking System (OATS). |
dba_activity_mview | Materialized view activity snapshots that were recently taken by the Object Activity Tracking System (OATS). Compare with v$activity_mview |
dba_activity_snapshot_meta | Information about activity snapshots taken by the Object Activity Tracking System (OATS). |
dba_activity_table | Table activity snapshots that were recently taken by the Object Activity Tracking System (OATS). |
dba_app_errors_history | Errors raised when an application PDB synchronizes with an application in the application root. |
dba_autotask_settings | Settings for automated tasks |
dba_auto_mv_analysis_actions | Information about analysis and tuning activities associated with automatic materialized views. |
dba_auto_mv_analysis_executions | Information about analysis and tuning executions associated with automatic materialized views. |
dba_auto_mv_analysis_recommendations | |
dba_auto_mv_analysis_report | |
dba_auto_mv_analysis_task | |
dba_auto_mv_config | |
dba_auto_mv_maint_report | |
dba_auto_mv_refresh_history | |
dba_auto_mv_verification_report | |
dba_auto_mv_verification_status | |
dba_auto_zonemap_config | |
dba_logstdby_support_mode | Information if tables in the database are supported for logical standby. |
dba_ogg_auto_captured_tables | !23c Tables in the database that are enabled for Oracle GoldenGate automatic capture. |
dba_queue_event_streams | |
dba_rolling_support_mode | |
dba_zonemap_auto_actions | |
dba_zonemap_auto_findings | |
V$VIEWS:
v$activity_mview | |
v$activity_table | |
v$authpool_stats | |
v$eq_cached_partitions | |
v$eq_cross_instance_jobs | |
v$eq_inactive_partitions | |
v$eq_message_cache | |
v$eq_message_cache_advice | |
v$eq_message_cache_stat | |
v$eq_nondur_subscriber | |
v$eq_nondur_subscriber_lwm | |
v$eq_partition_stats | |
v$eq_remote_dequeue_affinity | |
v$eq_subscriber_load | |
v$eq_subscriber_stat | |
v$eq_uncached_partitions | |
v$lcr_cache | |
v$pkcs11_path | All processes that are using a PKCS#11 library for Transparent Data Encryption (TDE). |
v$pmem_filestore | |
v$result_subcache_statistics | |
DBMS_* packages
dbms_activity | |
dbms_app_cont_report | |
dbms_auto_zonemap | |
dbms_avtune | |
dbms_cube | |
dbms_cube_advise | |
dbms_hcheck | |
dbms_mle | |