A Using Oracle Database Features in Autonomous Transaction Processing

This appendix provides information on using Oracle Database features and options in Autonomous Transaction Processing.

About the Autonomous Transaction Processing 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 a dedicated Autonomous Transaction Processing database include:

  • The default data and temporary tablespaces for the database are configured automatically.

    The name of the default data tablespace is DATA.

  • 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.

Accessing the Autonomous Transaction Processing database:

  • You do not have direct access to the database node or the local file system.
  • You do not have access to the SYSTEM or SYSAUX tablespaces.

Parallel Execution:

  • 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 execution:
     ALTER INDEX index_name NOPARALLEL;

    or

     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_STATS jobs, 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_SQLQ package. 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
FIXED_DATE
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_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
STATISTICS_LEVEL (Session only by using ALTER SESSION)
TIME_ZONE (Session only by using ALTER SESSION)

For more information on initialization parameters see Oracle Database Reference. For more information on TIME_ZONE, see Oracle Database SQL Language Reference.

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
  • Java in Oracle Database
  • 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.

Limitations on the Use of Oracle Spatial and Graph

The following describes support for Oracle Spatial and Graph features in dedicated Autonomous Transaction Processing 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

SQL Statements for Indexing Spatial Data

Spatial Query

2D Linear Referencing System Yes SDO_LRS Package (Linear Referencing System)
GeoJSON and JSON support for Spatial data types Yes

JSON and GeoJSON Support in Oracle Spatial and Graph

JSSDO_UTIL.TO_GEOJSON

SDO_UTIL.TO_JSON

SDO_UTIL.TO_JSON_VARCHAR

Location Tracking Server Yes SDO_TRKR Package (Location Tracking)
Spatial Analysis and Mining features Yes

Spatial Analysis and Mining

SDO_SAM Package (Spatial Analysis and Mining)

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

SDO_WFS_PROCESS Package (WFS Processing)

SDO_OLS Package (OpenLS)

GML No SDO_UTIL.TO_GMLGEOMETRY
Point Cloud Object Type No SDO_PC_PKG Package (Point Clouds)
Triangulated Irregular Network (TIN) types No SDO_TIN_PKG Package (TINs)

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 dedicated Autonomous Transaction Processing 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 ctx_report.query_log_summary Not Supported QUERY_LOG_SUMMARY
File and URL datastore Not Supported Datastore Type
CREATE INDEX with BIG_IO option Not supported by default. Foot 1 Improved Response Time Using the BIG_IO Option of CONTEXT Index
OPTIMIZE_INDEX in rebuild mode 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 DML).

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 dedicated Autonomous Transaction Processing databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle XML DB are restricted.

Note:

If you migrate tables containing XMLType columns 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 CLOB No Deprecated
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.

Note:

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 statements and PL/SQL packages are not supported in Autonomous Transaction Processing databases:

  • ADMINISTER KEY MANAGEMENT
  • ALTER TABLESPACE
  • DBMS_PIPE

SQL Statements with Restrictions in Autonomous Transaction Processing

The following DDL statements are available in Autonomous Transaction Processing with restrictions:

SQL Command Restrictions

ALTER PLUGGABLE DATABASE and ALTER DATABASE

Only the following clauses are allowed:

DATAFILE AUTOEXTEND ON

DATAFILE AUTOEXTEND OFF

DATAFILE RESIZE

DEFAULT EDITION

SET TIME_ZONE

ALTER SESSION

Only the following clauses are allowed:

ADVISE COMMIT, ADVISE ROLLBACK, ADVISE NOTHING

CLOSE DATABASE LINK

ENABLE COMMIT IN PROCEDURE, DISABLE COMMIT IN PROCEDURE

ENABLE PARALLEL <QUERY|DDL|DML>, DISABLE PARALLEL <QUERY|DDL|DML>, FORCE PARALLEL <QUERY|DDL|DML>

ENABLE RESUMABLE, DISABLE RESUMABLE

SET DEFAULT_COLLATION

SET EDITION

SET ISOLATION_LEVEL

SET ROW ARCHIVAL VISIBILITY

ALTER SYSTEM

Only ALTER SYSTEM SET and ALTER SYSTEM KILL SESSION are allowed. SET can only be used to set parameters listed in Modifying Database Initialization Parameters.

ALTER TABLE

The following clauses are ignored:

allocate_extent

alter_iot

deallocate_unused

ilm

inmemory_table

logging

modify_LOB_storage

physical_attributes

shrink

For more information on ALTER TABLE, see Oracle Database SQL Language Reference.

ALTER USER

The PROFILE clause is ignored.

CREATE DATABASE LINK

In the USING 'connect string' clause, you must use the Easy Connect syntax or provide a complete descriptor. You cannot use a network service name because the tnsnames.ora file is not available for lookup.

Database links can only be used for TCP connections because TCPS connections require a wallet.

CREATE TABLE

XMLType tables are not allowed.

The following clauses are ignored:

cluster

ilm

inmemory_table

LOB_storage

logging

organization external

organization index

physical_properties

For more information on CREATE TABLE, see Oracle Database SQL Language Reference.

CREATE USER

The PROFILE clause is ignored.

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:

  • Large object (LOB) data types (Only SecureFiles LOB storage is supported.)
  • Multimedia (Oracle Multimedia is desupported.)

For a list of Oracle data types see Oracle Database SQL Language Reference.