This preface contains:
Changes in Oracle Database Release 19c
The following are changes in Oracle Database SQL Language Reference for Oracle Database 19c.
The following features are new in Release 19c:
You can create SQL macros (SQM) to factor out common SQL expressions and statements into reusable, parameterized constructs that can be used in other SQL statements.
Starting with Oracle Database release 19c, version 19.7, SQL table macros are supported. SQL table macros are expressions, typically used in a FROM clause, to act as a sort of polymorphic (parameterized) views.
SQL table macros increase developer productivity, simplify collaborative development, and improve code quality.
Finer Granularity Supplemental Logging
Fine-grained supplemental logging provides a way for partial database replication users to disable supplemental logging for uninteresting tables even when supplemental logging is enabled at the database or schema level.
Use this feature in cases where where only some of the tables in the database require supplemental logging and thereby significantly reduce overhead of resource usage and redo generation.
To use this feature configure the new
supplemental_subset_replication_clause added to the
supplemental_db_logging clause of the
ALTER DATABASE and
ALTER PLUGGABLE DATABASE statements.
Signature-based security for LOB locators
Starting with this release, you can configure signature-based security for large object (LOB) locators. LOB signature keys can be in both multitenant PDBs or in standalone, non-multitenant databases.
You can enable the encryption of the LOB signature key credentials by executing the
ALTER DATABASE DICTIONARY ENCRYPT CREDENTIALS SQL statement. Otherwise, the credentials are stored in obfuscated format. If you choose to store the LOB signature key in encrypted format, then the database or PDB must have an open TDE keystore.
Cloud Object Store Support Using REST APIs
Oracle Data Pump can import data from files located in a supported object store.
You can specify database credentials on the command line and store default credentials in the database using the new
property_clause of the
ALTER DATABASE statement.
Multiple Table Family Support for System-Managed Sharding
This feature applies to system-managed sharded databases only. You can host different applications accessing different table families on one sharded database with the
CREATE SHARDED TABLE statement.
You can create more than one table family with system sharding, but only one table family is supported for user-defined and composite sharding.
Generation of Unique Sequence Numbers Across Shards
You can generate globally unique sequence numbers across shards for non-primary key columns with unique constraints without having to manage them yourself. The sharded database manages these sequence numbers for you.
SHARD clause of the
CREATE SEQUENCE statement or the
ALTER SEQUENCE statement to generate unique sequence numbers across shards.
Big Data and Performance Enhancements for In-Memory External Tables
inmemory_clause of the
CREATE TABLE and
ALTER TABLE statement supports supports specification of the
ORACLE_BIGDATA driver types.
You can specify
INMEMORY in the
inmemory_clause clause on non-partitioned tables to support these driver types.
Bitmap Based Count Dictinct SQL Functions
You can use five new bitvector functions for speeding up COUNT DISTINCT operations within a SQL query:
Memoptimized Rowstore - Fast Ingest
CREATE TABLE or
ALTER TABLEto enable fast ingest. Fast ingest optimizes the memory processing of high frequency single row data inserts from Internet of Things (IoT) applications by using a large buffering pool to store the inserts before writing them to disk.
This feature enables the mapping of JSON data to and from user-defined SQL object types and collections.
You can convert JSON data to an instance of a SQL object type using SQL/JSON function
json_value. In the opposite direction, you can generate JSON data from an instance of a SQL object type using SQL/JSON function
You can now update a JSON document declaratively, using new SQL function
json_mergepatch. You can apply one or more changes to multiple documents using a single statement.
This feature improves the flexibility of JSON update operations.
JSON Syntax Simplifications
Syntax simplifications are offered for SQL/JSON path expressions and SQL/JSON generation with function
json_object. A new SQL query clause,
NESTED, provides a simple alternative to using
LEFT OUTER JOIN.
JSON_SERIALIZE and JSON Data Guide Support for GeoJSON Data
You can use new SQL function
json_serialize to serialize JSON data to text or to UTF-encoded
SQL aggregate function
json_dataguide can now detect GeoJSON geographic data in your documents. You can use it to create a view that projects such data as SQL data type
Hybrid Partitioned Tables
You can create hybrid partitioned tables where some partitions reside in Oracle database segments and some partitions reside in external files and sources. Internal and external partitions can be integrated into a single partitioned table as needed.
EXTERNAL in the
table_partition_description clause of
CREATE TABLE or
Parity Protected Files
You can configure single parity for write-once files, such as archive logs and backup sets that do not require a high level of redundancy and save space.
PARITY in the
redundancy_clause of the
ALTER DISKGROUP statement.
DISTINCT Option for LISTAGG Aggregate
LISTAGG aggregate function now supports duplicate elimination by using the new
Unified Auditing Top Level Statements
ONLY TOPLEVEL clause in the
CREATE AUDIT POLICY statement (Unified Auditing) when you want to audit the SQL statements issued directly by a user.
You can now use the
by_users_with_roles_clause to enable policy for users who have been directly or indirectly granted the specified roles.
Transparent Online Conversion Support for Auto-Renaming in Non-OMF Mode
FILE_NAME_CONVERT is omitted in the
ALTER TABLESPACE ENCRYPTION statement, Oracle will internally select a name for the auxiliary file, and later rename it back to the original name.
ALTER SYSTEM clause FLUSH PASSWORDFILE_METADATA_CACHE
ALTER SYSTEM FLUSH PASSWORDFILE_METADATA_CACHE flushes the password file metadata cache stored in the SGA and informs the database that a change has occurred.
The following features are deprecated in this release, and may be desupported in a future release:
SQLNET.ENCRYPTION_WALLET_LOCATION parameter is being deprecated.