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
Fast Ingest See Limitations on the Use of 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.
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 Limitations on the Use of Oracle Flashback and Restore Points.
Oracle Label Security You cannot specify labeling functions in policies.
Oracle Real Application Testing Database Replay is not supported and SQL Performance Analyzer (SPA) is supported. See Testing Guide for more information on SPA.

Tip:

You can use the SPA features from Enterprise Manager (EM). For more details, see Monitoring and Administration Tasks in Administrator's Guide for Oracle Autonomous Databases
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.

Related Topics

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

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

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

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:

  • 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 Fast Ingest

Fast ingest optimizes the processing of high-frequency, single-row data inserts into a database. Fast ingest uses the large pool for buffering the inserts before writing them to disk, so as to improve data insert performance.

For an overview of fast ingest and the steps involved in using this feature, refer to Using Fast Ingest in Database Performance Tuning Guide.

To use fast ingest with your dedicated Autonomous Database, you must enable the optimizer to use hints by setting the optimizer_ignore_hints parameter to FALSE at the session or system level, as appropriate.

Dedicated Autonomous Database supports fast ingest, with the following limitations.

  • Tables with the following characteristics can not use fast ingest:
    • disk compression
    • in-memory compression
    • function-based indexes
    • domain indexes
    • bitmap indexes
    • bitmap join indexes
    • ref types
    • varray types
    • OID$ types
    • unused columns
    • LOBs that are stored out-of-line in a separate LOB segment
    • Triggers
    • binary columns
    • foreign keys
    • row archival
    • invisible columns
  • Following objects can not use fast ingest:
    • Temporary tables
    • Nested tables
    • Index organized tables
    • External tables
    • Materialized views with on-demand refresh
  • Sub-partitioning is not supported with fast ingest.
  • The following partitioning types are not supported with fast ingest:
    • REFERENCE
    • SYSTEM
    • INTERVAL
    • AUTOLIST

Limitations on the Use of Oracle Spatial

Autonomous Database 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 dedicated Autonomous Database:
Spatial Feature More Information
Routing Engine Routing Engine
Geocoder (mid-tier) 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)

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 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 Yes
Flashback Version Query Yes
Restore Point No
Guaranteed Restore Point No

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

Notes on the Use of Database PL/SQL Packages

The following describes notes to consider before, during, and after using PL/SQL packages in dedicated Autonomous Databases.

Lists Oracle Database PL/SQL packages with restrictions and notes in Autonomous Database.

Removed PL/SQL Packages
  • DBMS_LDAP
  • DBMS_DEBUG
  • 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 dedicated 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.