B Autonomous Transaction Processing for Experienced Oracle Database Users

This appendix provides information on using Autonomous Transaction Processing for experienced Oracle Database users in Autonomous Database for serverless deployments.

For equivalent information about using Oracle Database features and options in Autonomous Database for dedicated deployments, see these resources:

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 for database administration such as CREATE TABLESPACE are not available. Similarly, other administrative interfaces and utilities such as RMAN are not available.

Characteristics of an Autonomous Transaction Processing database:

  • The default data and temporary tablespaces for the database are configured automatically. Adding, removing, or modifying tablespaces is not allowed. Autonomous Database creates one tablespace or multiple tablespaces automatically depending on the storage size.

  • The database character set is Unicode AL32UTF8.
  • Compression is not enabled by default but Autonomous Transaction Processing honors a compression clause if compression is specified on a table.

Accessing an Autonomous Transaction Processing database:

  • You do not have direct access to the database node. You can create and drop directories with CREATE DIRECTORY and DROP DIRECTORY, as described in Creating and Managing Directories. You can use DBMS_CLOUD procedures such as DBMS_CLOUD.DELETE_FILE, DBMS_CLOUD.GET_OBJECT, and DBMS_CLOUD.PUT_OBJECT with files and objects. You do not have direct access to the local file system.

Parallel Execution:

  • Parallelism is not enabled by default. You can specify a degree of parallelism using the parallel_clause for a table or an index or by using a hint.

  • If you do want to run DML operations in parallel you can enable parallel DML in your session using the following SQL command:

    ALTER SESSION ENABLE PARALLEL DML;

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

    To specify serial execution, change the INDEX parallel clause to NOPARALLEL or set the PARALLEL degree attribute to 1 to specify serial execution:

     ALTER INDEX index_name NOPARALLEL;

    or

     ALTER INDEX index_name PARALLEL 1; 

Restrictions for 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. But, 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
DDL_LOCK_TIMEOUT
FIXED_DATE
LDAP_DIRECTORY_ACCESS
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_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES  (Allowed only with ALTER SESSION)
OPTIMIZER_IGNORE_HINTS
OPTIMIZER_IGNORE_PARALLEL_HINTS
OPTIMIZER_MODE
PLSCOPE_SETTINGS
PLSQL_CCFLAGS
PLSQL_DEBUG
PLSQL_OPTIMIZE_LEVEL
PLSQL_WARNINGS
STATISTICS_LEVEL (Allowed only with ALTER SESSION)

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

For more information on OPTIMIZER_IGNORE_HINTS and OPTIMIZER_IGNORE_PARALLEL_HINTS, see Manage Optimizer Statistics on Autonomous Transaction Processing.

Restrictions for SQL Commands

Autonomous Transaction Processing allows most of the SQL commands available in the 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 the Oracle Database work in Autonomous Transaction Processing.

Note:

If you try to use a restricted SQL command the system reports:

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 are not available in Autonomous Transaction Processing:

  • ADMINISTER KEY MANAGEMENT

  • ALTER PROFILE

  • ALTER TABLESPACE

  • CREATE DATABASE LINK

    Note:

    Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links in Autonomous Transaction Processing. See Create Database Links from Autonomous Database to Other Databases for more information.
  • CREATE PROFILE

  • CREATE TABLESPACE

  • DROP TABLESPACE

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

SET CMU_WALLET

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

SET TIME_ZONE

ALTER SYSTEM

ALTER SYSTEM is not allowed except ALTER SYSTEM SET and ALTER SYSTEM KILL SESSION. SET can only be used to set parameters listed in Restrictions for Database Initialization Parameters.

ALTER USER

The following clauses are ignored:

  • DEFAULT TABLESPACE

  • PROFILE

IDENTIFIED with the EXTERNALLY clause is not supported.

ALTER TABLE

For restrictions, see ALTER TABLE Restrictions.

CREATE TABLE

For restrictions, see CREATE TABLE Restrictions.

CREATE USER

The following clauses are ignored:

  • DEFAULT TABLESPACE

  • PROFILE

IDENTIFIED with the EXTERNALLY clause is not supported.

CREATE TABLE Restrictions

XMLType tables are not allowed.

The clauses not in this list are allowed.

Clause Comment

physical_properties

Ignored

logging_clause

Ignored

inmemory_table_clause

Ignored

ilm_clause

Ignored

organization index

Ignored

organization external

Ignored

cluster

Ignored

LOB_storage_clause

Ignored

Note:

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

ALTER TABLE Restrictions

The clauses not in this list are allowed.

Clause Comment

physical_attributes_clause

Ignored

logging_clause

Ignored

inmemory_table_clause

Ignored

ilm_clause

Ignored

allocate_extent_clause

Ignored

deallocate_unused_clause

Ignored

shrink_clause

Ignored

alter_iot_clauses

Ignored

modify_LOB_storage_clause

Ignored

Note:

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

Restrictions for Data Types

Autonomous Transaction Processing allows most of the data types available in the Oracle Database. To ensure the security and the performance of Autonomous Transaction Processing, some data types are restricted.

The following data types are not supported or have limited support in Autonomous Transaction Processing:

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

Restrictions for Database PL/SQL Packages

Lists Oracle Database PL/SQL packages with restrictions in Autonomous Transaction Processing database.

Removed PL/SQL Packages

  • UTL_TCP

PL/SQL Packages with Restrictions

  • UTL_HTTP Restrictions

    • Connections through IP addresses are not allowed.

    • Only HTTPS connections are allowed (HTTP and HTTP_PROXY are disallowed).

    • The only allowed ports are 443 and 8443.

    • The SET_WALLET and SET_AUTHENTICATION_FROM_WALLET APIs are disallowed.

    • The WALLET_PATH and WALLET_PASSWORD arguments for the CREATE_REQUEST_CONTEXT, REQUEST, and REQUEST_PIECES APIs are ignored.

  • UTL_SMTP Restrictions

    • The only supported email provider is Oracle Cloud Infrastructure Email Delivery service. See Overview of the Email Delivery Service for more information.

    • Mail with an IP address in the host name is not allowed.

    • The only allowed ports are 25 and 587.

  • DBMS_NETWORK_ACL_ADMIN Restrictions

    • Granting ACL privileges on IP addresses is not allowed.

    • The http_proxy and use_passwords ACL privileges are not allowed.

See UTL_HTTP, UTL_SMTP, and DBMS_NETWORK_ACL_ADMIN in PL/SQL Packages and Types Reference for more information.

Restrictions for Database Features

Autonomous Transaction Processing is built for transaction processing and mixed workloads. In some cases, features which are present in Oracle Database Enterprise Edition are not available in Autonomous Transaction Processing. Additionally, database features designed for administration are not available.

Restrictions for Oracle XML DB

The following describes support for Oracle XML DB features in Autonomous Transaction Processing databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle XML DB features are restricted.

The following is supported, in addition to the features listed in Table B-1:

  • 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

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

Table B-1 XML DB Supported Features with Autonomous Database

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.

Restrictions for Oracle Text

The following describes support for Oracle Text features in Autonomous Transaction Processing databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle Text features are restricted.

Table B-2 Oracle Text Restricted Features with Autonomous Database

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.

Restrictions for Oracle Spatial and Graph

The following describes support for Oracle Spatial and Graph features in Autonomous Transaction Processing databases. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle Spatial and Graph features are restricted.

Note:

Autonomous Database does not include Oracle Spatial and Graph 3-Dimensional geometry types and related operators, functions, or utilities.

Table B-3 Oracle Spatial and Graph Supported Features with Autonomous Database

Spatial 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 Spatial and Graph Developer's Guide.

Restrictions for Oracle Application Express

Autonomous Transaction Processing supports Oracle Application Express. To ensure the security and the performance of Autonomous Transaction Processing, some Oracle Application Express features are restricted.

Restrictions for Oracle Flashback

Oracle Flashback Technology is a group of Oracle Database features that let you view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.

To restore and recover your database to a point in time, see Restore and Recover your Autonomous Transaction Processing Database.

Table B-4 Oracle Flashback Supported Features with Autonomous Database

Oracle Flashback Feature Supported in Autonomous Database

DBMS_FLASHBACK

Yes except the procedure:

DBMS_FLASHBACK.TRANSACTION_BACKOUT

Flashback Data Archive

No

Flashback Drop

Yes

Flashback Query

Yes

Flashback Table

Yes

Flashback Transaction

No

Flashback Transaction Query

Yes

Flashback Version Query

Yes

See About Oracle Flashback Technology for information on using Flashback features.

List of Restricted and Removed Oracle Features

Lists the Oracle Database features that are not available in Autonomous Transaction Processing. Additionally, database features designed for administration are not available.

List of Removed Oracle Features

  • Oracle Real Application Testing

  • Oracle Database Vault

  • Database Resident Connection Pooling (DRCP)

  • Oracle OLAP

  • Oracle R capabilities of Oracle Advanced Analytics

  • Oracle Industry Data Models

  • Oracle Tuning Pack

  • Oracle Database Lifecycle Management Pack

  • Oracle Data Masking and Subsetting Pack

  • Oracle Cloud Management Pack for Oracle Database

  • Oracle Multimedia

  • Java in DB

  • Oracle Workspace Manager