This appendix provides information on using Oracle Database features and options in Autonomous Transaction Processing dedicated deployments.
For equivalent information in other kinds of Autonomous Database deployments, see:
- For Autonomous Data Warehouse shared deployments: Autonomous Data Warehouse for Experienced Oracle Database Users
- For Autonomous Data Warehouse dedicated deployments: Using Oracle Database Features in Dedicated Autonomous Data Warehouse Deployments
- For Autonomous Transaction Processing shared deployments: Autonomous Transaction Processing for Experienced Oracle Database Users
- About the Autonomous Transaction Processing Dedicated Database
- Latest Autonomous Transaction Processing Database Features
- Modifying Database Initialization Parameters
- Oracle Database Features That Are Not Supported
- Oracle Database Features with Limited Support
- Limitations on the Use of SQL Commands
- Limitations on the Use of Certain Data Types
About the Autonomous Transaction Processing Dedicated Database
Autonomous Transaction Processing
configures and optimizes your database for you. You do not need to perform administration operations
for configuring the database. SQL commands used solely for database administration are not available
in this service. Similarly, other administrative interfaces and utilities such as
RMAN are not available.
Characteristics of an Autonomous Transaction Processing dedicated database include:
The default data and temporary tablespaces for the database are configured automatically.
The name of the default data tablespace is
- The database character set is Unicode AL32UTF8.
- Compression is not enabled by default. You can specify compression for a table using the table_compression clause in your
Accessing the Autonomous Transaction Processing dedicated database:
- You do not have direct access to the database node or the local file system.
- You do not have access to the
Parallel execution (parallelism within a SQL statement) is not enabled by default. You can specify a degree of parallelism by using the parallel_clause for a table or an index or by using a hint.
If you want to run DML operations in parallel, you can enable parallel DML in your session by using the following SQL command:
ALTER SESSION ENABLE PARALLEL DML;
See Oracle Database VLDB and Partitioning Guide for more information on parallel DML operations.
- If you create an index manually and specify the parallel_clause, the parallel
attribute remains after the index is created. In this case, SQL statements can run in parallel
unbeknownst to the end user. Change the parallel_clause value to NOPARALLEL or set the
PARALLEL attribute to 1 to specify serial
ALTER INDEX index_name NOPARALLEL;
ALTER INDEX index_name PARALLEL 1;
Latest Autonomous Transaction Processing Database Features
Autonomous Transaction Processing includes the latest Oracle Database features.
Autonomous Transaction Processing includes features that:
Automate index management tasks, such as creating, rebuilding, and dropping indexes based on changes in the application workload. See Managing Auto Indexes in Oracle Database Administrator’s Guide for more information.
Gather real-time statistics automatically while a conventional DML workload is running. Because statistics can go stale between
DBMS_STATSjobs, online statistics gathering for conventional DML helps the optimizer generate more optimal plans. Online statistics aim to reduce the possibility of the optimizer being misled by stale statistics. You can manage and access statistics for conventional DML through PL/SQL packages, data dictionary views, and hints. See Real-Time Statistics in Oracle Database SQL Tuning Guide for more information.
Gather statistics automatically on a more frequent basis. High-frequency automatic optimizer statistics collection complements the standard statistics collection job. By default, the collection occurs every 15 minutes, meaning that statistics have less time in which to be stale. See Configuring High-Frequency Automatic Optimizer Statistics Collection in Oracle Database SQL Tuning Guide for more information.
Quarantine execution plans for SQL statements that are terminated by the Resource Manager for consuming excessive system resources in an Oracle database. You can configure quarantine settings for a SQL statement by specifying limits on its resource consumption using procedures in the
DBMS_SQLQpackage. When the SQL statement crosses any of these resource consumption limits, it is terminated and the execution plan is quarantined. In this way, the database prevents high-resource SQL statements from executing repeatedly. See Quarantine for Execution Plans for SQL Statements Consuming Excessive System Resources in Oracle Database Administrator’s Guide for more information.
- Optimize high-frequency single-row inserts for applications, such as Internet of Things (IoT) applications. See Enabling High Performance Data Streaming With the Memoptimized Rowstore in Oracle Database Performance Tuning Guide.
Modifying Database Initialization Parameters
Autonomous Transaction Processing configures database initialization parameters automatically when you provision a database. You do not need to set any initialization parameters to start using your service. However, you can modify some parameters if you need to.
List of Initialization Parameters that can be Modified
APPROX_FOR_AGGREGATION APPROX_FOR_COUNT_DISTINCT APPROX_FOR_PERCENTILE AWR_PDB_AUTOFLUSH_ENABLED CURSOR_SHARING DB_BLOCK_CHECKING DDL_LOCK_TIMEOUT FIXED_DATE GLOBAL_NAMES HEAT_MAP MAX_IDLE_TIME NLS_CALENDAR NLS_COMP NLS_CURRENCY NLS_DATE_FORMAT NLS_DATE_LANGUAGE NLS_DUAL_CURRENCY NLS_ISO_CURRENCY NLS_LANGUAGE NLS_LENGTH_SEMANTICS NLS_NCHAR_CONV_EXCP NLS_NUMERIC_CHARACTERS NLS_SORT NLS_TERRITORY NLS_TIME_FORMAT NLS_TIME_TZ_FORMAT NLS_TIMESTAMP_FORMAT NLS_TIMESTAMP_TZ_FORMAT OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES (Session only by using ALTER SESSION) OPTIMIZER_IGNORE_HINTS OPTIMIZER_IGNORE_PARALLEL_HINTS PLSCOPE_SETTINGS PLSQL_CCFLAGS PLSQL_DEBUG PLSQL_OPTIMIZE_LEVEL PLSQL_WARNINGS RECYCLEBIN STATISTICS_LEVEL (Session only by using ALTER SESSION) TIME_ZONE (Session only by using ALTER SESSION)
Oracle Database Features That Are Not Supported
Autonomous Transaction Processing supports most of the features present in Oracle Database Enterprise Edition. To ensure the security and the performance of Autonomous Transaction Processing, some features are not supported.
The following Oracle Database features, options and packs are not supported in Autonomous Transaction Processing databases.
- Application containers
- Application contexts
- Clusters (groups of tables)
- Common users
- Dictionary-managed tablespaces
- Index-organized tables
- Logical standby databases
- Manual segment space management
- Manual undo management
- Oracle Cloud Management Pack for Oracle Database
- Oracle Data Masking and Subsetting Pack
- Oracle Database Lifecycle Management Pack for Oracle Database
- Oracle LogMiner
- Oracle On-Line Analytical Processing (OLAP) (Use analytic views instead. See Overview of Analytic Views for more information.)
- Oracle R Enterprise
- Oracle Real Application Testing
- Oracle Sharding
- Oracle Workspace Manager
- Root container (CDB$ROOT) access
- Transportable tablespaces
- Uniform extent allocation
Oracle Database Features with Limited Support
Autonomous Transaction Processing supports most of the features present in Oracle Database Enterprise Edition. To ensure the security and the performance of Autonomous Transaction Processing, some features have only limited support.
The following Oracle Database features, options and packs have limited support in Autonomous Transaction Processing databases.
|Feature, Option or Pack||Limitation|
|Java in Oracle Database (sometimes called OJVM or Database Embedded JVM)||Database sessions that use Java in Oracle Database will experience reduced database-side performance when service maintenance operations are being performed on the database.|
|Oracle Database Advanced Queuing||Only the PL/SQL Advanced Queuing APIs are supported.|
|Oracle GoldenGate and XStream||Only classic replicat is supported. Capture is not supported.|
|Oracle Label Security||You cannot specify labeling functions in policies.|
|Oracle Spatial and Graph||See Limitations on the Use of Oracle Spatial and Graph.|
|Oracle Text||See Limitations on the Use of Oracle Text.|
|Oracle XML DB||See Limitations on the Use of Oracle XML DB.|
Limitations on the Use of Oracle Spatial and Graph
The following describes support for Oracle Spatial and Graph features in Autonomous Transaction Processing dedicated databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle Spatial and Graph features are restricted.
Note:Oracle Autonomous Database does not include Oracle Spatial and Graph 3-Dimensional geometry types and related operators, functions, or utilities.
|Spatial and Graph Feature||Supported in Autonomous Database||More Information|
|2D Geometry data types||Yes||SDO_GEOMETRY Object Type|
|2D Spatial operators||Yes||Spatial Operators|
|2D Spatial functions||Yes||SDO_GEOM Package (Geometry)|
|Coordinate Transformation||Yes||SDO_CS Package (Coordinate System Transformation)|
|Spatial Utility Functions (except GML and KML conversion functions)||Yes||SDO_UTIL Package (Utility)|
|Spatial Aggregate Functions||Yes||Spatial Aggregate Functions|
|Spatial indexing and querying features||Yes|
|2D Linear Referencing System||Yes||SDO_LRS Package (Linear Referencing System)|
|GeoJSON and JSON support for Spatial data types||Yes|
|Location Tracking Server||Yes||SDO_TRKR Package (Location Tracking)|
|Spatial Analysis and Mining features||Yes|
|Flat Model for Point Cloud data||Yes||DO_PC_PKG.CLIP_PC_FLAT|
|RDF Graph||No||RDF Semantic Graph Overview|
|Property Graph||No||Spatial and Graph Property Graph Support Overview|
|Network Data Model||No||Network Data Model|
|GeoRaster||No||SDO_GEOR Package Reference|
|Routing Engine||No||Routing Engine|
|Geocoder||No||Geocoding Address Data|
|Spatial Visualization||No||Introduction to the Map Visualization Component|
|Topology Data Model||No||Topology Data Model Overview|
|Open Geospatial Consortium Web Services (WMS, WFS-T, C-SW, WCS, OpenLS)||No|
|Point Cloud Object Type||No||SDO_PC_PKG Package (Point Clouds)|
|Triangulated Irregular Network (TIN) types||No||SDO_TIN_PKG Package (TINs)|
Note:Oracle Spatial and Graph relies on and uses Java in Oracle Database . Therefore, database sessions that use Oracle Spatial and Graph will experience reduced database-side performance when service maintenance operations are being performed on the database.
For details on Oracle Spatial and Graph, see Oracle Database 19c Spatial and Graph.
Limitations on the Use of Oracle Text
The following describes support for Oracle Text features in Autonomous Transaction Processing dedicated databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle Text features are restricted.
|Oracle Text Feature||Supported in Autonomous Database||More Information|
|All logging, and APIs which perform logging such as
|File and URL datastore||Not Supported||Datastore Type|
||Not supported by default. Foot 1||Improved Response Time Using the BIG_IO Option of CONTEXT Index|
||Not supported by default.(see Footnote 1).||OPTIMIZE_INDEX|
Footnote 1 This is supported
if you grant the privilege to create a trigger to the user (
GRANT CREATE TRIGGER).
You must also disable parallel DML at the session level (
ALTER SESSION DISABLE PARALLEL
For details on Oracle Text, see Oracle Text Application Developer's Guide.
Limitations on the Use of Oracle XML DB
The following describes support for Oracle XML DB features in Autonomous Transaction Processing dedicated databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle XML DB are restricted.
Note:If you migrate tables containing
XMLTypecolumns to Autonomous Transaction Processing using Oracle Data Pump, you need to convert to Non-Schema Binary XML prior to using Oracle Data Pump Export (expdp).
The following features are supported:
Full support for XMLQuery, XMLTable, and other SQL/XML standard functions
Indexing schema including functional indexes using SQL/XML expressions, Structured XMLIndex and XQuery Full Text Index
The following features are not supported or are only partially supported.
|Area||XML DB Feature||Supported in Autonomous Database||More Information|
|Repository||XML DB Protocol||No||Repository Access Using Protocols|
|Repository||XML DB Resources||No||Oracle XML DB Repository Resources|
|Repository||XML DB ACLs||No||Repository Access Control|
|Storage||XML Schema Registration||No||XML Schema Registration with Oracle XML DB|
|Storage||Object Relational||No||XML Schema and Object-Relational XMLType|
|Storage||Binary XML||Yes (Non schema-based only)||XMLType Storage Models|
|Index||Structured XML Index||Yes||XMLIndex Structured Component|
|Index||XQuery Full Text Index||Yes||Indexing XML Data for Full-Text Queries|
|Index||Unstructured XMLIndex||No||XMLIndex Unstructured Component|
|Packages||XML DOM package||Yes||PL/SQL DOM API for XMLType (DBMS_XMLDOM)|
|Packages||XML Parser Package||Yes||PL/SQL Parser API for XMLType (DBMS_XMLPARSER)|
|Packages||XSL Processor (DBMS_XSLPROCESSOR)||Yes||PL/SQL XSLT Processor for XMLType (DBMS_XSLPROCESSOR)|
For details on Oracle XML DB, see Oracle XML DB Developer's Guide.
Limitations on the Use of SQL Commands
Autonomous Transaction Processing allows most of the SQL commands available in Oracle Database. To ensure the security and the performance of Autonomous Transaction Processing, some SQL commands are restricted.
This section provides a list of SQL command limitations that are required to protect security and for performance integrity in Autonomous Transaction Processing. Most of the standard SQL and PL/SQL syntax and constructs used with Oracle Database work in Autonomous Transaction Processing.
If you try to use a restricted SQL command the system generates this error:
ORA-01031: insufficient privileges
This error indicates that you are not allowed to run the SQL command in Autonomous Transaction Processing.
The following SQL commands and PL/SQL packages are not supported in Autonomous Transaction Processing databases:
ADMINISTER KEY MANAGEMENT
SQL Commands with Restrictions in Autonomous Transaction Processing
The following DDL commands are available in Autonomous Transaction Processing with restrictions:
Only the following clauses are allowed:
Only the following clauses are allowed:
The following clauses are ignored:
For more information on
Database links can only be used for TCP connections because TCPS connections require a wallet.
The following clauses are ignored:
For more information on
Limitations on the Use of Certain Data Types
Autonomous Transaction Processing allows most of the data types available in Oracle Database. To ensure the security and the performance of Autonomous Transaction Processing, some data types are restricted.
The following Oracle Database data types are not supported or are only partially supported in Autonomous Transaction Processing databases:
- The ROWID data type is not enabled by default, but you can enable it by setting the
ALLOW_ROWID_COLUMN_TYPEinitialization parameter to true.
- Large object (LOB) data types: only SecureFiles LOB storage is supported.
- Multimedia data types are not supported. (Oracle Multimedia is desupported in Oracle Database.)
For a list of Oracle data types see Oracle Database SQL Language Reference.