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:
|
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. |
- 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. - 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. - 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. - Manually Created External Tables
Instead of using the DBMS_CLOUD.CREATE_EXTERNAL_TABLE procedure to create an external table, you can use the SQLCREATE TABLE...ORGANIZATION EXTERNAL
statement to manually create one. When you do so, certain limitations apply. - 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. - Limitations on the Use of Oracle Spatial
Autonomous Database on Dedicated Exadata Infrastructure includes Oracle Spatial, with the following limitations. - 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 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. - 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. - 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. - 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.
Parent topic: Features for Experienced Database Users
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.
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 |
---|---|
|
Only the following clauses are allowed:
|
|
Using ALTER PROFILE :
|
|
Only the following clauses are allowed:
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 |
|
Only |
|
The use of The Autonomous Database block size is fixed at 8K. A |
|
The following clauses are ignored:
For more information on |
|
In the Database links can only be used for TCP connections because TCPS connections require a wallet. |
|
Refer to Manage Password Complexity on Autonomous Database for the |
|
The following clauses are ignored:
For more information on |
|
The use of The Autonomous Database block size is fixed at 8K. A |
|
The following clauses can not be used with CREATE USER statement:
|
|
This command is supported only when used in the following format:
If you execute this command without
|
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
andUROWID
data types are not enabled in Dedicated Exadata Infrastructure deployments. You cannot specifyROWID
orUROWID
columns when creating tables, nor can you addROWID
orUROWID
columns to existing tables.However, you can enable
ROWID
orUROWID
by setting theALLOW_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
andORACLE_BIGDATA
access drivers are supported. TheORACLE_DATAPUMP
,ORACLE_HDFS
andORACLE_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.
-
The
DBMS_LOGMNR.ADD_LOGFILE
andDBS_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 theV$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.
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.
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.
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 |
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.
- 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
anddbms_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.
DBMS_DEBUG_JDWP
DBMS_DEBUG_JDWP_CUSTOM
UTL_TCP
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 onUTL_HTTP
. - Before invoking the
UTL_HTTP
subprograms, you must first set the wallet location using theSET_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 onUTL_SMTP
.
DBMS_AQADM
NotesDBMS_AQADM.MOVE_QUEUE_TABLE
can only be used inONLINE
mode to move Advanced Queuing (AQ) tables from one tablespace to another or within the same tablespace. See My Oracle Support (MOS) Note: Doc ID 2867445.1 for guidance on moving AQ tables across tablespaces usingDBMS_AQADM.MOVE_QUEUE_TABLE
on 19c and above.DBMS_AQADM.MOVE_QUEUE_TABLE
can not be used to move AQ tables even inONLINE
mode when table or schema names are case-sensitive and include special identifiers.