Oracle Database Features with Limited Support

Autonomous Database 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 Limitation
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.
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 GoldenGate and XStream Only classic replicat is supported. Capture is not supported.
Oracle Label Security You cannot specify labeling functions in policies.
Oracle Spatial See Limitations on the Use of Oracle Spatial.
Oracle Text See Limitations on the Use of Oracle Text.
Oracle XML DB See Limitations on the Use of Oracle XML DB.
Oracle Flashback and Restore Points See Limitations on the Use of Oracle Flashback and Restore Points.

Limitations on the Use of SQL Commands

Autonomous Database 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. 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
  • DBMS_PIPE
  • SYS.DBMS_SHARED_POOL

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

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 TABLE

XMLType tables using XML schema-based storage are not allowed. See Limitations on the Use of 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 Limitations on the Use of 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.

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

Limitations on the Use of Certain Data Types

Autonomous Database 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 databases:

  • In dedicated deployments, the ROWID data type is not enabled by default. You can enable it by setting the ALLOW_ROWID_COLUMN_TYPE initialization parameter to true. However, if you enable it, be aware that ROWID columns are incompatible with rolling upgrade operations and other internal operations that physically move a row. At a minimum, database activities involving ROWID should be suspended during upgrades. Applications using ROWID columns should introduce correctness validation to mitigate against logical errors in the application if a row relocates.
  • 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.

Limitations on the Use of 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.

Limitations on the Use of Oracle Spatial

The following describes support for Oracle Spatial features in dedicated Autonomous Databases. To ensure the security and the performance of Autonomous Database, some Oracle Spatial features are restricted.

The following spatial features are not supported in dedicated Autonomous Database currently.

Spatial Feature More Information
Network Data Model Network Data Model
Routing Engine Routing Engine
Geocoder Geocoding Address Data
Open Geospatial Consortium Web Services (WFS, WFS-T, C-SW, WCS, OpenLS)

SDO_WFS_PROCESS Package (WFS Processing)

SDO_OLS Package (OpenLS)

For details on Oracle Spatial, 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 Databases. 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.

Limitations on the Use of Oracle XML DB

The following describes support for Oracle XML DB features in dedicated Autonomous Databases. 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 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 Oracle Flashback and Restore Points

The following describes support for Oracle Flashback and Restore Points in dedicated dedicated Autonomous Databases. 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 Dedicated Autonomous Database
DBMS_FLASHBACK Yes, except for the DBMS_FLASHBACK.TRANSACTION_BACKOUT procedure.
Flashback Data Archive No
Flashback Drop Yes
Flashback Query Yes
Flashback Table Yes
Flashback Transaction No
Flashback Transaction Query No
Flashback Version Query Yes
Restore Point No
Guaranteed Restore Point No

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