B Autonomous Data Warehouse for Experienced Oracle Database Users

This appendix provides information on using Autonomous Data Warehouse for experienced Oracle Database users.

About the Autonomous Data Warehouse Database

Autonomous Data Warehouse configures and optimizes your database for you. You do not need to perform administration operations for configuring the database. SQL commands used for database administration such as CREATE TABLESPACE are not available in this service. Similarly, other administrative interfaces and utilities such as RMAN are not available.

Major characteristics of an Autonomous Data Warehouse database are:

  • The default data and temporary tablespaces for the database are configured automatically. Adding, removing, or modifying tablespaces is not allowed.

    The default data tablespace name is data.

  • In Autonomous Data Warehouse direct access to the database node and the local file system are not allowed.

  • Database links to other databases are not allowed for enhanced security. Database links from other databases to Autonomous Data Warehouse are allowed.

  • Calling PL/SQL programs using database links is not supported.

  • Parallelism is enabled by default. Degree of parallelism for SQL statements is set based on the number of OCPUs in the system and the database service the user is connecting to. For more information on database services see Managing Concurrency and Priorities on Autonomous Data Warehouse.

  • Parallel DML is enabled by default. If you do not want to run DML operations in parallel you can disable parallel DML in your session using the following SQL command:

    ALTER SESSION DISABLE PARALLEL DML;

    See VLDB and Partitioning Guide for more information on parallel DML operations.

  • Compression is enabled by default. Autonomous Data Warehouse uses Hybrid Columnar Compression for all tables by default. You can also use different compression methods for your tables by specifying the compression clause in your CREATE TABLE and ALTER TABLE commands.

    For more information on CREATE TABLE, see CREATE TABLE.

    For more information on ALTER TABLE, see ALTER TABLE.

  • Autonomous Data Warehouse uses Unicode AL32UTF8 as the database character set.
  • Oracle Database Result Cache is enabled by default for all SQL statements. Changing the result cache mode is not allowed.

Restrictions for Database Initialization Parameters

Autonomous Data Warehouse configures the database initialization parameters based on the compute and storage capacity you provision. You do not need to set any initialization parameters to start using your service. But, you can modify some parameters if you need to.

List of Initialization Parameters that can be Modified

APPROX_FOR_AGGREGATION
APPROX_FOR_COUNT_DISTINCT
APPROX_FOR_PERCENTILE 
AWR_PDB_AUTOFLUSH_ENABLED
FIXED_DATE
NLS_CALENDAR
NLS_COMP
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_NCHAR_CONV_EXCP
NLS_LANGUAGE
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
NLS_NUMERIC_CHARACTERS 
NLS_SORT
NLS_TERRITORY
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES  Allowed only with ALTER SESSION
OPTIMIZER_IGNORE_HINTS
OPTIMIZER_IGNORE_PARALLEL_HINTS
PLSCOPE_SETTINGS
PLSQL_DEBUG
PLSQL_WARNINGS
PLSQL_OPTIMIZE_LEVEL
PLSQL_CCFLAGS
STATISTICS_LEVEL Allowed only with ALTER SESSION
TIME_ZONE: Allowed only with ALTER SESSION

For more information on initialization parameters see Oracle Database Reference. For more information on TIME_ZONE, see Database SQL Language Reference.

For more information on OPTIMIZER_IGNORE_HINTS and OPTIMIZER_IGNORE_PARALLEL_HINTS, see Manage Optimizer Statistics on Autonomous Data Warehouse.

Restrictions for Database Features

Autonomous Data Warehouse is built for data warehouse workloads. In some cases, features which are present in Oracle Database Enterprise Edition are not available in Autonomous Data Warehouse. Additionally, database features designed for administration are not available.

List of Restricted and Removed Oracle Features

The following Oracle Database features are not available in Autonomous Data Warehouse:

  • Oracle Real Application Testing

  • Oracle Database Vault

  • Database Resident Connection Pooling (DRCP)

  • Oracle OLAP

  • Oracle R capabilities of Oracle Advanced Analytics

  • Oracle Spatial and Graph

  • Oracle Industry Data Models

  • Oracle Text

  • Oracle Tuning Pack

  • Oracle Database Lifecycle Management Pack

  • Oracle Data Masking and Subsetting Pack

  • Oracle Cloud Management Pack for Oracle Database

  • Oracle Application Express

  • Oracle Multimedia

  • Java in DB

  • Oracle XML DB

  • Context

  • Oracle Workspace Manager

Restrictions for SQL Commands

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

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

The following SQL statements are not available in Autonomous Data Warehouse:

  • ADMINISTER KEY MANAGEMENT

  • ALTER PROFILE

  • ALTER TABLESPACE

  • CREATE DATABASE LINK

  • CREATE PROFILE

  • CREATE TABLESPACE

  • DROP TABLESPACE

SQL Statements with Restrictions in Autonomous Data Warehouse

The following DDL statements are available in Autonomous Data Warehouse 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

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 Restrictions for Database Initialization Parameters.

ALTER USER

The following clauses are ignored:

  • DEFAULT TABLESPACE

  • PROFILE

ALTER TABLE

For restrictions, see ALTER TABLE Restrictions.

CREATE TABLE

For restrictions, see CREATE TABLE Restrictions.

CREATE USER

The following clauses are ignored:

  • DEFAULT TABLESPACE

  • PROFILE

CREATE TABLE Restrictions

XMLType tables are not allowed.

The clauses not in this list are allowed.

Clause Comment

physical_properties

Ignored

logging_clause

Ignored

inmemory_table_clause

Ignored

ilm_clause

Ignored

organization index

Ignored

organization external

Ignored

cluster

Ignored

LOB_storage_clause

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

physical_attributes_clause

Ignored

logging_clause

Ignored

inmemory_table_clause

Ignored

ilm_clause

Ignored

allocate_extent_clause

Ignored

deallocate_unused_clause

Ignored

shrink_clause

Ignored

alter_iot_clauses

Ignored

modify_LOB_storage_clause

Ignored

Note:

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

Restrictions for Data Types

Autonomous Data Warehouse allows most of the data types available in the Oracle Database. To ensure the security and the performance of Autonomous Data Warehouse, some data types are restricted.

The following data types are not supported in Autonomous Data Warehouse:

  • LONG

  • LONG RAW

  • Media types

  • Spatial types

For a list of Oracle data types see Oracle Database SQL Language Reference.

Managing Partitions, Indexes, and Materialized Views

Autonomous Data Warehouse allows manual creation of partitioned tables, indexes, and materialized views using the partitioning clause in the CREATE TABLE statement, the CREATE INDEX statement, and the CREATE MATERIALIZED VIEW statement respectively.

Oracle recommends that you do not manually create these structures, and leave performance optimizations to the Autonomous Data Warehouse. If you're a highly skilled Oracle Database tuning expert and decide to manually create these access structures, please be advised to test the impact of your manual tuning efforts on your full workload.