Always Free Autonomous Database – Oracle Database 21c
When you provision Always Free Autonomous Database you can select either Oracle Database 19c or Oracle Database 23ai.
- Always Free Autonomous Database Oracle Database 21c Features
When you provision Always Free Autonomous Database you can select either Oracle Database 19c or Oracle Database 21c. - Always Free Autonomous Database Oracle Database 21c Notes
If you are using Always Free Autonomous Database with Oracle Database 21c, the following Oracle Database 21c functionality is not supported:
Always Free Autonomous Database Oracle Database 21c Features
When you provision Always Free Autonomous Database you can select either Oracle Database 19c or Oracle Database 21c.
Always Free Autonomous Database running with Oracle Database 21c offers many new innovative autonomous and developer-oriented functionality, including but not limited to the following:
Performance Features
-
Automatic Zone Maps
Automatic zone maps are created and maintained for any user table without any customer intervention. Zone maps allow the pruning of block ranges and partitions based on the predicates in the queries. Automatic zone maps are maintained for direct loads, and are maintained and refreshed for any other DML operation incrementally and periodically in the background.
The feature is enabled as follows:
exec dbms_auto_zonemap.configure('AUTO_ZONEMAP_MODE','ON');
The feature is disabled as follows:
exec dbms_auto_zonemap.configure('AUTO_ZONEMAP_MODE','OFF');
See Summary of DBMS_AUTO_ZONEMAP Subprograms for more information.
-
Object Activity Tracking System
Object Activity Tracking System (OATS) tracks the usage of various types of database objects. Usage includes operations such as access, data manipulation, or refresh.
No manual intervention is required to enable OATS, and zero or minimal configuration is required. See PL/SQL procedure DBMS_ACTIVITY.CONFIGURE and database dictionary views DBA_ACTIVITY_CONFIG for details.
Application Development: Advanced Analytical SQL Capabilities
-
SQL Macros
SQL Macros, the capability to factor out common SQL constructs supports scalar expressions, increasing developer productivity, simplify collaborative code development, and improve code quality. See SQL Macros for more information.
-
Enhanced Analytic Functions
Window functions support the full ANSI Standard, including the support of EXCLUDE options and the WINDOW clause. Supporting the full ANSI standard enables easier migration of applications that were developed with other standard-compliant database systems. See Windowing Functions for more information.
-
New Analytical and Statistical Aggregate Functions
Several new analytical and statistical aggregate functions are available in SQL in Oracle Database 21c. With these additional SQL aggregation functions, you can write more efficient code and benefit from faster in-database processing.
-
CHECKSUM
computes the checksum of the input values or expression.Supports the keywords
ALL
andDISTINCT
. -
KURTOSIS
functionsKURTOSIS_POP
andKURTOSIS_SAMP
measure the tailedness of a data set where a higher value means more of the variance within the data set is the result of infrequent extreme deviations as opposed to frequent modestly sized deviations. Note that a normal distribution has a kurtosis of zero.Supports the keywords
ALL
,DISTINCT
, andUNIQUE
. -
SKEWNESS
functionsSKEWNESS_POP
andSKEWNESS_SAMP
are measures of asymmetry in data. A positive skewness is means the data skews to the right of the center point. A negative skewness means the data skews to the left.Supports the keywords
ALL
,DISTINCT
, andUNIQUE
. -
ANY_VALUE
, a function to simplify and optimize the performance ofGROUP BY
statements, returns a random value in a group and is optimized to return the first value in the group. It ensures that there are no comparisons for any incoming row and eliminates the necessity to specify every column as part of theGROUP BY
clause.
See Oracle Database 21c SQL Language Reference Guide for more information.
-
-
Bitwise Aggregate Functions
With the new bitwise type processing functions
BIT_AND_AGG
,BIT_OR_AGG
, andBIT_XOR_AGG
, native bitwise type processing is provided by Oracle Database 21c. These functions enable a type of processing inside the database for new types of application processing, improving the overall performance, avoiding unnecessary data movement, and natively taking advantage of core database functionality such as parallel processing. See Oracle Database 21c SQL Language Reference Guide. for more information.
JavaScript Execution using DBMS_MLE
The DBMS_MLE
package allows users to execute JavaScript code inside the
Oracle Database and exchange data seamlessly between PL/SQL and JavaScript. The JavaScript
code itself can execute PL/SQL and SQL through built-in JavaScript modules. JavaScript data
types are automatically mapped to Oracle Database data types and vice versa.
With the DBMS_MLE
package, developers can write their data processing
logic in JavaScript. JavaScript is a widely-used and popular programming language that can
now also be used for writing programs that need to execute close to the data.
See DBMS_MLE for more information.
Blockchain Table
Blockchain tables are append-only tables in which only insert operations are allowed. Deleting rows is either prohibited or restricted based on time. Rows in a blockchain table are made tamper-resistant by special sequencing and chaining algorithms. Users can verify that rows have not been tampered. A hash value that is part of the row metadata is used to chain and validate rows.
Blockchain tables enable you to implement a centralized ledger model where all participants in the blockchain network have access to the same tamper-resistant ledger.
A centralized ledger model reduces administrative overheads of setting up a decentralized ledger network, leads to a relatively lower latency compared to decentralized ledgers, enhances developer productivity, reduces the time to market, and leads to significant savings for the organization. Database users can continue to use the same tools and practices that they would use for other database application development.
See Managing Blockchain Tables for more information.
JSON Document Store Enhancements
-
Enhancements to Data Guide
Enhances development flexibility and allows for materialized views, which may improve query performance with a trade-off against DML performance.
-
JSON_DATAGUIDE
now gathers statistic information if you specifyDBMS_JSON.GATHER_STATS
in the third argument. They are computed dynamically (up-to-date) at the time of the function call. -
DBMS_JSON.CREATE_VIEW
now gives you the option to create a materialized view instead of a standard view. It also gives you the option to specify a particular path so the view can be created on a subset of the data. BothCREATE_VIEW
andADD_VIRTUAL_COLUMN
are enhanced to allow automatic resolution of column naming conflicts, to provide a prefix to be applied to column names, and to specify the case-sensitivity of column names.
See JSON Data Guide for more information.
-
-
Multivalue Index for JSON DataType
A new create index syntax
CREATE MULTIVALUE INDEX
allows you to create a functional index on arrays of strings or numbers within a JSON datatype 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 theJSON_EXISTS
orJSON_VALUE
operators. It provides similar benefits to conventional functional indexes when searching JSON, but conventional functional indexes are limited to a single indexed value per row.See Creating Multivalue Function-Based Indexes for JSON_EXISTS and Using a Multivalue Function-Based Index for more information.
-
New JSON Data Type
JSON is a new SQL and PL/SQL data type for JSON data. Using this type provides a substantial increase in query and update performance. JSON data type uses binary format OSON that is optimized for SQL/JSON query and DML processing. Using the binary format can yield database performance improvements for processing JSON data.
You can use JSON data type and its instances in most places where a SQL data type is allowed, including:
- As the column type for table or view DDL
- With SQL/JSON functions and conditions, and with PL/SQL procedures and functions
- In Oracle dot-notation query syntax
- For creation of functional and search indexes
Oracle Call Interface and Java Database Connectivity (JDBC) clients now provide APIs that can work directly with binary JSON datatype OSON format, significantly saving network costs and server CPU cycles. Going forward, Oracle recommends using JSON datatype to store and process JSON data.
The Oracle Autonomous JSON Database uses OSON format to store and process JSON data.
See Creating a Table With a JSON Column for more information.
-
New Oracle SQL Function JSON_TRANSFORM
You can use SQL function
JSON_TRANSFORM
to update parts of a JSON document. You specify which parts to modify, the modifications, and any new values.JSON_TRANSFORM
is optimized by doing partial updates at OSON format level to achieve better JSON datatype update performance.JSON_TRANSFORM
makes it easier for an application to modify a JSON document, without having to parse and rebuild it. In most cases, it also avoids a round-trip between the server and client for the whole document.See Oracle SQL Function JSON_TRANSFORM for more information.
-
SQL/JSON Syntax Improvements
You can now express more complex SQL/JSON queries and express some queries more succinctly:
-
New SQL function
JSON_SCALAR
accepts a scalar instance of a SQL data type and returns a scalar JSON value as an instance of JSON data type. -
New JSON path-language item methods support
JSON_SCALAR: float()
,double()
,binary()
,ymInterval()
, anddsInterval()
. -
The JSON path-language and dot-notation syntax support the aggregate item methods:
avg()
,count()
,minNumber()
,maxNumber()
,minString()
,maxString()
,sum()
.See Simple Dot-Notation Access to JSON Data and SQL/JSON Path Expression Item Methods for more information.
-
SODA Enhancements: New JSON Data Type
The default collection storage changes to the JSON data type. See Creating a Document Collection with SODA for PL/SQL for more information.
PL/SQL Enhancements
-
PL/SQL is enhanced to help you program iteration controls using new iterators in loops and in qualified expressions.
The new iterator constructs are clear, simple, understandable, and efficient.
See PL/SQL Extended Iterators for more information.
Gradual Database Password Rollover for Applications
An application can change its database passwords without an administrator having to schedule downtime.
To accomplish this, a database administrator can associate a profile having a
non-zero limit for the PASSWORD_ROLLOVER_TIME
password profile parameter,
with an application schema. This allows the database password of the application user to be
altered while allowing the older password to remain valid for the time specified by the
PASSWORD_ROLLOVER_TIME
limit. During the rollover period of time, the
application instance can use either the old password or the new password to connect to the
database server. When the rollover time expires, only the new password is allowed.
In addition to the clause PASSWORD_ROLLOVER_TIME
in the
CREATE PROFILE
and ALTER PROFILE
statements, the
ALTER USER
statement has a clause, EXPIRE PASSWORD ROLLOVER
PERIOD
. The ACCOUNT_STATUS
column of the
DBA_USERS
and USER_USERS
data dictionary views have
several statuses indicating values to indicate rollover status.
See Managing Gradual Database Password Rollover for Applications for more information.
Parent topic: Always Free Autonomous Database – Oracle Database 21c
Always Free Autonomous Database Oracle Database 21c Notes
If you are using Always Free Autonomous Database with Oracle Database 21c, the following Oracle Database 21c functionality is not supported:
- Automatic Materialized Views
-
Autonomous Database only supports Cloud Links when your database version is Oracle Database 19c. Cloud Links are not supported with database version Oracle Database 21c. See Use Cloud Links for Read Only Data Access on Autonomous Database for more information.
Parent topic: Always Free Autonomous Database – Oracle Database 21c