Oracle Cloud Infrastructure Documentation

SQL Commands

Autonomous AI Database allows most of the SQL commands available in Oracle 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 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 <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 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.