SQL Commands

Autonomous AI Database allows most of the SQL commands available in Oracle AI Database. To ensure the security and the performance of Autonomous AI 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 AI Databases. Most of the standard SQL and PL/SQL syntax and constructs available with Oracle AI Database work in Autonomous AI 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 AI Lakehouse.

The following SQL statements are not available in Autonomous AI Database:

ADMINISTER KEY MANAGEMENT: By default Autonomous AI Database uses Oracle-managed encryption keys. Using Oracle-managed keys, Autonomous AI 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 AI Database. See Manage Encryption Keys on Autonomous AI Database for more information.
CREATE TABLESPACE, ALTER TABLESPACE, and DROP TABLESPACE: Autonomous AI Database automatically configures default data and temporary tablespaces for the database. Adding, removing, or modifying tablespaces is not allowed. Autonomous AI 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 AI Database. See Use Database Links with Autonomous AI Database for more information.
CREATE LIBRARY
DROP DATABASE LINK

Use DBMS_CLOUD_ADMIN.DROP_DATABASE_LINK to drop database links in Autonomous AI Database. See Use Database Links with Autonomous AI Database for more information.

Roles and Views Restrictions for Data Dictionary

Granting SELECT ANY DICTIONARY does not provide access to the SYS/SYSTEM schemas. You can grant SELECT_CATALOG_ROLE to allow SELECT privileges on all data dictionary views, if needed.

SQL Statements with Restrictions in Autonomous AI Database

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

SQL Command	Restrictions
`ALTER PLUGGABLE DATABASE` and `ALTER DATABASE`	Only the following clauses are allowed: `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 AI Database for more information. Using `ALTER PROFILE`, the optional `CONTAINER` clause is ignored if specified. See Create Users on Autonomous AI Database - Connecting with a Client Tool 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` , `DISABLE PARALLEL` , `FORCE PARALLEL` `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`, `ALTER SYSTEM KILL SESSION`, and `ALTER SYSTEM CANCEL SQL` `SET` can only be used to set parameters listed in Initialization Parameters. The ADMIN user cannot run `CANCEL SQL` on system schemas, such as `SYS`.
`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 AI Database for more information. Using `ALTER PROFILE`, the optional `CONTAINER` clause is ignored if specified. See Create Users on Autonomous AI Database - Connecting with a Client Tool for information on the password parameter values defined in the default profile.
`CREATE TABLE`	For restrictions, see `CREATE TABLE` Restrictions.
`CREATE OR REPLACE TRIGGER`	The `AFTER STARTUP` and `BEFORE SHUTDOWN` events are not supported with `CREATE OR REPLACE TRIGGER`.
`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.