Oracle Database Features with Limited Support in Autonomous Database on Dedicated Exadata Infrastructure

Autonomous Database on Dedicated Exadata Infrastructure supports most of the features present in Oracle Database Enterprise Edition. To ensure the security and the performance of Autonomous Database, some features have only limited support.

The following Oracle Database features, options and packs have limited support in Autonomous Database.

Feature, Option or Pack Limitations
Database In-Memory See Database In-Memory.
Fast Ingest See Fast Ingest.
Index-organized tables (IOTs) Index-organized tables are not supported, but attempting to create one does not generate an error. Instead, a heap-organized table with a primary key index is created. Therefore, if you use index-organized tables, you should test the applications that use index-organized tables to confirm that they work using heap-organized tables with a primary key indexes.
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.
LogMiner See Oracle LogMiner.
Oracle Data Pump Transportable tablespace mode (the TRANSPORT_TABLESPACES parameter) is not supported.
Oracle Database Advanced Queuing

Only the PL/SQL Advanced Queuing APIs are supported.

Oracle Call Interface (OCI) notifications for Advanced Queuing are not supported.
Oracle Flashback and Restore Points See Oracle Flashback and Restore Points.
Oracle Label Security You cannot specify labeling functions in policies.
Oracle Machine Learning (OML)
Supports:
  • OML4SQL on all database versions.
  • Oracle Data Miner.
For OML product documentation, refer to Oracle Machine Learning on Oracle Help Center.
Oracle Real Application Testing
SQL Performance Analyzer and Database Replay are the key components of Oracle Real Application Testing (RAT). Depending on the nature and impact of the system change being tested and the type of test system, you can use either or both components to perform your testing.
Oracle Spatial See Limitations on the Use of Oracle Spatial.
Oracle Text See Oracle Text.
Oracle XML DB See Oracle XML DB.

Related Topics

SQL Commands

Autonomous Database on Dedicated Exadata Infrastructure allows most of the SQL commands available in Oracle Database. To ensure the security and the performance of Autonomous Database, 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 Database on Dedicated Exadata Infrastructure. Most of the standard SQL and PL/SQL syntax and constructs used with Oracle Database work in Autonomous Database.

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

The following SQL commands and PL/SQL packages are not supported in Autonomous Databases:

  • ADMINISTER KEY MANAGEMENT
  • SYS.DBMS_SHARED_POOL
  • CREATE_LIBRARY

SQL Commands with Restrictions in Autonomous Database

The following DDL commands are available in Autonomous Database 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 PROFILE

 Using ALTER PROFILE:

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 init-param = value

For a list of initialization parameters you can set in Oracle Autonomous Database on Dedicated Exadata Infrastructure, see Modifying Database Initialization Parameters. Note that you cannot set some of these parameters at the session level. To find out whether a given parameter can be altered using ALTER SESSION SET, query the ISSES_MODIFIABLE column of the V$PARAMETER dynamic performance view.

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 TABLESPACE

The use of SMALLFILE tablespaces is not supported by Autonomous Database.

The Autonomous Database block size is fixed at 8K. A BIGFILE tablespace contains only one data file or temp file, and the maximum size of the file for a tablespace with 8K blocks is 32 terabytes (TB).

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.

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 PROFILE

Refer to Manage Password Complexity on Autonomous Database for the Password Verify Function requirements.

CREATE TABLE

XMLType tables using XML schema-based storage are not allowed. See Oracle XML DB.

The following clauses are ignored:

  • cluster

  • ilm_clause

  • inmemory_table_clause

  • LOB_storage_clause

  • logging_clause

  • organization external: Use of this clause has certain limitations, as described in Manually Created External Tables.

  • organization index: Using this clause does not create an index-organized table. It creates a regular table with a primary key. You should test and verify the performance of the generated table for your application.

  • physical_properties

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

CREATE TABLESPACE

The use of SMALLFILE tablespaces is not supported by Autonomous Database.

The Autonomous Database block size is fixed at 8K. A BIGFILE tablespace contains only one data file or temp file, and the maximum size of the file for a tablespace with 8K blocks is 32 terabytes (TB).

CREATE USER

 The following clauses can not be used with CREATE USER statement:
  • IDENTIFIED EXTERNALLY
  • IDENTIFIED BY VALUES

DROP TABLESPACE

This command is supported only when used in the following format:

DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;

If you execute this command without INCLUDING CONTENTS AND DATAFILES in the syntax, you will get the following error:

ORA-01031: insufficient privileges

Data Types

Autonomous Database on Dedicated Exadata Infrastructure allows most of the data types available in Oracle Database. To ensure the security and the performance of Autonomous Database, some data types are restricted.

The following Oracle Database data types are not supported or are only partially supported in Autonomous Database:

  • By default, the ROWID and UROWID data types are not enabled in Dedicated Exadata Infrastructure deployments. You cannot specify ROWID or UROWID columns when creating tables, nor can you add ROWID or UROWID columns to existing tables.

    However, you can enable ROWID or UROWID by setting the ALLOW_ROWID_COLUMN_TYPE initialization parameter to true. See Database Reference for more information on enabling it.

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

Database In-Memory

Oracle Database In-Memory (Database In-Memory) is a suite of features that significantly improves performance for real-time analytics and mixed workloads.

The In-Memory Column Store (IM column store) is the key feature of Database In-Memory. The IM column store maintains copies of tables, partitions, and individual columns in a special compressed columnar format optimized for rapid scans. The IM column store resides in the In-Memory Area, which is an optional portion of the system global area (SGA). For more information about this feature, refer to Database In-Memory Guide.

Autonomous Database on Dedicated Exadata Infrastructure lets you enable Database In-memory for your Autonomous Database by allocating a percentage of its System Global Area (SGA) to the In-Memory column store (IM column store) either while provisioning the database or later. You can also enable or disable Database In-Memory for an existing Autonomous Database from its Details page on the Oracle Cloud Infrastructure (OCI) console.

The following describes notes to consider before and after enabling Database In-Memory for your Autonomous Database:

  • Turning Database In-Memory on or off is supported only on Autonomous Databases with:
    • Version is 19.20 or later.
    • At least 4 OCPUs enabled.
  • Scaling down an Autonomous Database to less than 4 OCPUs automatically disables Database In-Memory for that database.
  • You can also enable Database In-Memory on databases cloned from a database instance or backup, irrespective of whether the clone source has Database In-memory enabled. As the settings are not inherited from the clone, you can enable Database In-Memory manually while cloning or later.
  • You may experience performance degradation in the following cases:
    • Any patching activity that results in rebooting the database with Database In-Memory enabled.
    • Restarting an Autonomous Container Database with Database In-Memory enabled Autonomous Databases.
  • If you enable Database In-Memory for a primary Autonomous Databases in an Autonomous Data Guard setup, the standby database also carries the same settings. However, you can not change these settings on the standby database.
  • For better management and sharing of the underlying SGA/memory resources, Oracle recommends that all the Autonomous Databases configured for Database In-Memory be in the same Autonomous Container Database.

Refer to Enable or Disable Database In-Memory or Create an Autonomous Database on Dedicated Exadata Infrastructure for detailed instructions.

Manually Created External Tables

Instead of using the DBMS_CLOUD.CREATE_EXTERNAL_TABLE procedure to create an external table, you can use the SQL CREATE TABLE...ORGANIZATION EXTERNAL statement to manually create one. When you do so, certain limitations apply.

  • The ORACLE_LOADER and ORACLE_BIGDATA access drivers are supported. The ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE access drivers are not supported.

  • Partitioned external tables and partitioned hybrid tables are not currently supported.

  • If you are specifying files stored in Cloud Object Storage as the data source location, you must use one of the URI formats described in Cloud Object Storage URI Formats.

Oracle LogMiner

LogMiner, which is part of Oracle Database, enables you to query online and archived redo log files through a SQL interface. Redo log files contain information about the history of activity on a database.

All changes made to user data or the database dictionary are recorded in the Oracle redo log files to perform database recovery operations. Because LogMiner provides a well-defined, easy-to-use, and comprehensive relational interface to redo log files, it can be used as a powerful data auditing tool and sophisticated data analysis tool. For key capabilities of LogMiner, refer to LogMiner Benefits in Oracle Database Utilities.

For information about LogMiner configuration and requirements, see Introduction to LogMiner in Oracle Database Utilities.

Autonomous Database on Dedicated Exadata Infrastructure supports LogMiner, with the following limitations:

  • The DBMS_LOGMNR.ADD_LOGFILE and DBS_LOGMNR.REMOVE_LOGFILE procedures can not be used from an Autonomous Database.

  • The dbms_logmnr.start_logmnr procedure can only be invoked with SCN or time range parameters. Querying the V$LOGMNR_CONTENTS view subsequently returns the data from the specified SCN or time range for the connected Autonomous Database.

  • Only one LogMiner session is allowed per Autonomous Database.

  • By default, Autonomous Database archived log files are kept for up to:
    • 3 days for databases without GoldenGate capture and Autonomous Data Guard setup.
    • 7 days for databases with Autonomous Data Guard that do not have GoldenGate capture enabled.
    • 15 days for databases with GoldenGate capture enabled.
    Therefore, LogMiner can only access up to 3, 7, or 15 days of archived log files as per the above list.

    The log files retention period depends on the database transaction rate and is not always guaranteed up to the number of days specified above. There can be instances when you attempt to mine unavailable log files. In such cases, LogMiner reports ORA-1285: "error reading file".

Limitations on the Use of Oracle Spatial

Autonomous Database on Dedicated Exadata Infrastructure includes Oracle Spatial, with the following limitations.

The following Oracle Spatial components require that you download applications from Oracle Cloud Marketplace:
The following are not available in Autonomous Database:
Spatial Feature More Information
Routing Engine Routing Engine
Geocoder (mid-tier) Geocoding Address Data

Oracle Text

The following describes support for Oracle Text features in Autonomous Database on Dedicated Exadata Infrastructure. To ensure the security and the performance of Autonomous Database, 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.

Oracle XML DB

The following describes support for Oracle XML DB features in Autonomous Database on Dedicated Exadata Infrastructure. To ensure the security and the performance of Autonomous Database, some Oracle XML DB are restricted.

Note:

If you migrate tables containing XMLType columns to Autonomous Database on Dedicated Exadata Infrastructure 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.

Oracle Flashback and Restore Points

The following describes support for Oracle Flashback and Restore Points in Autonomous Database on Dedicated Exadata Infrastructure. To ensure the security and the performance of Autonomous Database, some Oracle Flashback and Restore Point features are restricted.

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

Flashback and Restore Points Feature Supported in Autonomous Database on Dedicated Exadata Infrastructure
DBMS_FLASHBACK Yes, except for the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure.
Flashback Time Travel

Yes, except for specifying a default Flashback Data Archive using the SET DEFAULT clause with the CREATE FLASHBACK ARCHIVE and ALTER FLASHBACK ARCHIVE statements.

Flashback Drop Yes
Flashback Query Yes
Flashback Table Yes
Flashback Transaction Yes
Flashback Transaction Query Yes
Flashback Version Query Yes
Restore Point No
Guaranteed Restore Point No

For information on using Flashback features, see About Oracle Flashback Technology.

Database Replay

You can use Database Replay to capture a workload on the production system and replay it on a test system with the original workload's exact timing, concurrency, and transaction characteristics. This enables you to test the effects of a system change without affecting the production system.

Capturing the production workload eliminates the need to develop simulation workloads or scripts, resulting in significant cost reduction and time savings. Realistic testing of complex applications that took months using load simulation tools can be completed within days with the help of Database Replay. This enables you to rapidly test changes and adopt new technologies with higher confidence and at lower risk. For more information about Database Replay, see Introduction to Database Replay in Oracle Testing Guide.

Using the Database Replay component of Real Application Testing (RAT) in the Oracle Autonomous Database on Dedicated Exadata Infrastructure is the same as its implementation in the Oracle Database. See Part II Database Replay for the process involved in using Database Replay with your database.

There are certain limitations and recommendations to use Database Replay with Autonomous Database on Dedicated Exadata Infrastructure:
  • Oracle recommends that you use a Network File System (NFS) or File Storage System (FSS) that is attached to your Autonomous Database to store the capture and replay files. Refer to Attach Network File System and Attach External File System for guidance.
  • Database Replay can be used with Autonomous Database versions 19.21 and above only.
  • Replay client must be run with Patch 35341947 from 19.19 DB Home or 19.19 Instant Client.
  • Autonomous Database does not support dbms_workload_capture.import_awr and dbms_workload_replay.import_awr. That is, you can not import Automatic Workload Repository (AWR) data with Database Replay. Hence, it is recommended that you generate the AWR comparison reports on a non-autonomous database, preferably Oracle Database 23c instance for better compatibility.

Database PL/SQL Packages

The following describes notes to consider before, during, and after using PL/SQL packages in Autonomous Database on Dedicated Exadata Infrastructure.

Lists Oracle Database PL/SQL packages with restrictions and notes in Autonomous Database on Dedicated Exadata Infrastructure.

Removed PL/SQL Packages
  • DBMS_DEBUG_JDWP
  • DBMS_DEBUG_JDWP_CUSTOM
  • UTL_TCP
PL/SQL Packages Notes
  • UTL_HTTP Notes
    • Connections through IP addresses are allowed.
    • Egress ports 80 and 443 are enabled by default.
    • HTTP, HTTPS, and HTTP_PROXY connections are allowed.
    • The APEX_WEB_SERVICE PL/SQL package is layered on UTL_HTTP.
    • Before invoking the UTL_HTTP subprograms, you must first set the wallet location using the SET_WALLET subprogram. As this is a session level setting, you must set it before each session. For more information, see SET_WALLET Procedure and UTL_HTTP Session Settings in PL/SQL Packages and Types Reference.
      BEGIN
  UTL_HTTP.SET_WALLET(path => 'file:/var/opt/oracle/dbaas_acfs/grid/tcps_wallets');
END;
/
  • UTL_SMTP Notes
    • Connections through IP addresses are allowed.
    • Egress ports 25, 465, 587, and 2525 are enabled by default.
    • Both simple and secure (SSL) SMTP endpoints are accessible.
    • Oracle Cloud Infrastructure (OCI) Email Delivery Service and third-party SMTP servers are both supported as email providers. See Oracle Cloud Infrastructure - Networking and Connectivity for details on establishing connectivity between the third-party server and your Autonomous Database tenant.
    • However, unlike Oracle Enterprise Edition on-premises database, you cannot set up localhost as the SMTP server. The sendmail() service is also disabled on the platform.
    • The APEX_MAIL PL/SQL package is layered on UTL_SMTP.