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 the performance integrity of Autonomous Databases. Most of the standard SQL and PL/SQL syntax and constructs available with Oracle Database work in Autonomous Databases.

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

The following SQL statements are not available in Autonomous Database:

• ADMINISTER KEY MANAGEMENT: By default Autonomous Database uses Oracle-managed encryption keys. Using Oracle-managed keys, Autonomous Database creates and manages the encryption keys that protect your data and Oracle handles rotation of the TDE master key.

  If you want customer-managed keys, a master encryption key in the Oracle Cloud Infrastructure Vault is used to generate the TDE master key on Autonomous Database. See Managing Encryption Keys on Autonomous Database for more information.

• CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE: Autonomous Database automatically configures default data and temporary tablespaces for the database. Adding, removing, or modifying tablespaces is not allowed. Autonomous Database creates one tablespace or multiple tablespaces automatically depending on the storage size.

• CREATE DATABASE LINK

  Use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK to create database links in Autonomous Database. See Use Database Links with Autonomous Database for more information.

• CREATE LIBRARY

SQL Statements with Restrictions in Autonomous Database

The following DDL statements are available in Autonomous Database with some restrictions:

SQL Command	Restrictions
ALTER PLUGGABLE DATABASE and ALTER DATABASE	Only the following clauses are allowed: DATAFILE AUTOEXTEND ON DATAFILE AUTOEXTEND OFF DEFAULT EDITION SET TIME_ZONE SET CMU_WALLET
ALTER PROFILE	Using ALTER PROFILE, there are restrictions for a user defined PASSWORD_VERIFY_FUNCTION. See Manage Password Complexity on Autonomous Database for more information. Using ALTER PROFILE, the optional CONTAINER clause is ignored if specified. See Create Users on Autonomous Database for information on the password parameter values defined in the default 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 CONSTRAINTS SET CURRENT_SCHEMA SET DEFAULT_COLLATION SET EDITION SET ISOLATION_LEVEL SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES SET ROW ARCHIVAL VISIBILITY SET STATISTICS_LEVEL 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 Initialization Parameters.
ALTER USER	The following clause is ignored: DEFAULT TABLESPACE The IDENTIFIED with the EXTERNALLY clause is not supported. The IDENTIFIED BY VALUES clause is not allowed.
ALTER TABLE	For restrictions, see ALTER TABLE Restrictions.
CREATE PROFILE	PASSWORD_VERIFY_FUNCTION See Manage Password Complexity on Autonomous Database for more information. Using ALTER PROFILE, the optional CONTAINER clause is ignored if specified. See Create Users on Autonomous Database for information on the password parameter values defined in the default profile.
CREATE TABLE	For restrictions, see CREATE TABLE Restrictions.
CREATE USER	The following clause is ignored: DEFAULT TABLESPACE IDENTIFIED with the EXTERNALLY clause is not supported. The IDENTIFIED BY VALUES clause is not allowed.

CREATE TABLE Restrictions

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

The clauses not in this list are allowed.

Clause	Comment
cluster	Ignored
ilm_clause	Ignored
inmemory_table_clause	Ignored
LOB_storage_clause	The LOB_compression_clause is recognized. Other LOB_storage_clause parameters are ignored. See LOB_compression_clause for more information.
logging_clause	Ignored
organization external	Ignored
organization index	Creates a regular table with a primary key. Using the organization index clause does not create an index-organized table. You should test and verify the performance of the generated table for your application.
physical_properties	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
allocate_extent_clause	Ignored
alter_iot_clauses	Ignored
deallocate_unused_clause	Ignored
ilm_clause	Ignored
inmemory_table_clause	Ignored
logging_clause	Ignored
modify_LOB_storage_clause	Ignored
physical_attributes_clause	Ignored
shrink_clause	Ignored

Note:

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