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:
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:
The feature is disabled as follows:
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, 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.
CHECKSUMcomputes the checksum of the input values or expression.
Supports the keywords
KURTOSIS_SAMPmeasure 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
SKEWNESS_SAMPare 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
ANY_VALUE, a function to simplify and optimize the performance of
GROUP BYstatements, 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 the
See Oracle Database 21c SQL Language Reference Guide for more information.
Bitwise Aggregate Functions
With the new bitwise type processing functions
BIT_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.
types are automatically mapped to Oracle Database data types and vice versa.
DBMS_MLE package, developers can write their data processing
now also be used for writing programs that need to execute close to the data.
See DBMS_MLE for more information.
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_DATAGUIDEnow gathers statistic information if you specify
DBMS_JSON.GATHER_STATSin the third argument. They are computed dynamically (up-to-date) at the time of the function call.
DBMS_JSON.CREATE_VIEWnow 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. Both
ADD_VIRTUAL_COLUMNare 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 INDEXallows 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 the
JSON_VALUEoperators. 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.
See Creating a Table With a JSON Column for more information.
New Oracle SQL Function JSON_TRANSFORM
You can use SQL function
JSON_TRANSFORMto update parts of a JSON document. You specify which parts to modify, the modifications, and any new values.
JSON_TRANSFORMis optimized by doing partial updates at OSON format level to achieve better JSON datatype update performance.
JSON_TRANSFORMmakes 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_SCALARaccepts 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
The JSON path-language and dot-notation syntax support the aggregate item methods:
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 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
ACCOUNT_STATUS column of the
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.