Oracle Database Features in Autonomous AI Database on Dedicated Exadata Infrastructure

This article provides information on using Oracle AI Database features and options in Autonomous AI Database on Dedicated Exadata Infrastructure.

For equivalent information in Autonomous AI Database Serverless deployments, see Autonomous AI Database for Experienced Database Users.

You can provision an Autonomous AI Database with Oracle Database 19c or Oracle AI Database 26ai, depending on the database software version of its parent Autonomous Container Database (ACD). For example, to create an Autonomous AI Database with Oracle AI Database 26ai, you must choose an ACD whose Oracle database software version is 26ai. For a comprehensive list of database features supported by each of these database versions, see Oracle Database 26ai or Oracle Database 19c on Oracle Help Center.

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

Let us discuss how are some of the key features of the Oracle database handled in an Autonomous AI Database:

Data and temporary tablespaces: The default data and temporary tablespaces for the database are configured automatically. The name of the default data tablespace is DATA.
Database character set: The database character set is Unicode AL32UTF8.
Stored data encryption: Stored data is encrypted using the AES256 (Advanced Encryption Standard 256-bit cipher key) algorithm.

Note: Autonomous AI Databases created before September 2021 use AES128 algorithm by default.

If needed, you can change the encryption algorithm for a tablespace using the ALTER TABLESPACE command. For example, to change the encryption algorithm of the DATA tablespace to AES256, enter:

  ALTER TABLESPACE data ENCRYPTION ONLINE USING 'AES256' REKEY;

Data compression: For Autonomous AI Transaction Processing workloads, compression is not enabled by default. For Autonomous AI Lakehouse workloads, hybrid columnar compression is enabled by default for all the tables.

To enable or disable compression or to specify different compression methods for your tables using the table_compression clause in your CREATE TABLE or ALTER TABLE command.
Database access: You do not have direct access to the database node or the local file system, and SYSTEM or SYSAUX tablespaces.
Parallel execution: By default, parallel execution (parallelism within a SQL statement) is not enabled for the Autonomous AI Transaction Processing workloads. However, for the Autonomous AI Lakehouse workloads, parallel execution is enabled by default, and the degree of parallelism for SQL statements is set based on the number of CPUs in the system and the database service you use when connecting to the database.
- You can modify the degree of parallelism for a table or an index using the parallel_clause or by using a hint. For example, you can disable parallel DML in your session using the following SQL command:
```
ALTER SESSION DISABLE PARALLEL DML;
```
  See Oracle Database 19c VLDB and Partitioning Guide or Oracle Database 26ai VLDB and Partitioning Guide for more information on parallel DML operations.
- If you create an index manually and specify the parallel_clause, the parallel attribute remains after the index is created. In this case, SQL statements can run in parallel unknown to the end-user. Change the parallel_clause value to NOPARALLEL or set the PARALLEL attribute to 1 to specify serial execution:
```
ALTER INDEX *index_name* NOPARALLEL;
```
  or
```
ALTER INDEX *index_name* PARALLEL 1;
```

Latest Autonomous AI Database Features

Autonomous AI Database includes the latest Oracle Database features.

Autonomous AI Database includes features that:

Automate index management tasks, such as creating, rebuilding, and dropping indexes based on changes in the application workload. See Managing Auto Indexes in Oracle Database 19c Administrator’s Guide or Oracle Database 26ai Administrator’s Guide for more information.
Gather real-time statistics automatically while a conventional DML workload is running. Because statistics can go stale between DBMS_STATS jobs, online statistics gathering for conventional DML helps the optimizer generate more optimal plans. Online statistics aim to reduce the possibility of the optimizer being misled by stale statistics. You can manage and access statistics for conventional DML through PL/SQL packages, data dictionary views, and hints. See Real-Time Statistics in Oracle Database 19c SQL Tuning Guide or Oracle Database 26ai SQL Tuning Guide for more information.
Gather statistics automatically on a more frequent basis. High-frequency automatic optimizer statistics collection complements the standard statistics collection job. By default, the collection occurs every 15 minutes, meaning that statistics have less time in which to be stale. See Configuring High-Frequency Automatic Optimizer Statistics Collection in Oracle Database 19c SQL Tuning Guide or Oracle Database 26ai SQL Tuning Guide for more information.
Quarantine execution plans for SQL statements that are terminated by the Resource Manager for consuming excessive system resources in an Oracle database. You can configure quarantine settings for a SQL statement by specifying limits on its resource consumption using procedures in the DBMS_SQLQ package. When the SQL statement crosses any of these resource consumption limits, it is terminated and the execution plan is quarantined. In this way, the database prevents high-resource SQL statements from executing repeatedly. See Quarantine for Execution Plans for SQL Statements Consuming Excessive System Resources in Oracle Database 19c Administrator’s Guide or Oracle Database 26ai Administrator’s Guide for more information.
Optimize high-frequency single-row inserts for applications, such as Internet of Things (IoT) applications. See Enabling High Performance Data Streaming With the Memoptimized Rowstore in Oracle Database 19c Performance Tuning Guide or Oracle Database 26ai Performance Tuning Guide.

The ADMIN User and the SYS User

In Oracle Autonomous AI Database on Dedicated Exadata Infrastructure the predefined administrative user is ADMIN. In Oracle Database the predefined administrative user is SYS. While these two users serve the same purpose in their respective databases, they are not the same and do not have the same set of privileges.

Because Oracle Autonomous AI Database on Dedicated Exadata Infrastructure imposes security controls and performs administrative database tasks for you, the ADMIN user does not have as many privileges as the SYS user. Here is a list of the privileges that the ADMIN user does not have but that the SYS user in an Oracle Database does have:

ALTER LOCKDOWN PROFILE
BACKUP ANY TABLE
BECOME USER
CREATE ANY JOB
CREATE ANY LIBRARY
CREATE LIBRARY
CREATE LOCKDOWN PROFILE
CREATE PLUGGABLE DATABASE
DEQUEUE ANY QUEUE
DROP LOCKDOWN PROFILE
EM EXPRESS CONNECT
ENQUEUE ANY QUEUE
EXPORT FULL DATABASE
FLASHBACK ANY TABLE
FLASHBACK ARCHIVE ADMINISTER
GRANT ANY PRIVILEGE
GRANT ANY ROLE
IMPORT FULL DATABASE
INHERIT ANY PRIVILEGES
LOGMINING
MANAGE ANY FILE GROUP
MANAGE ANY QUEUE
MANAGE FILE GROUP
USE ANY JOB RESOURCE
USE ANY SQL TRANSLATION PROFILE

All system privileges with the ANY keyword (such as SELECT ANY TABLE, CREATE ANY PROCEDURE) honor the COMMON_SCHEMA_ACCESS lockdown, and an ADMIN user can not use it against the Common user schemas.

Unlike Oracle database on-premises, where the ANY keyword applies to all users except SYS, the ANY privilege only works against non-Common Users in Autonomous AI Database.

Notes

To find a list of common users, you can run the following query as an ADMIN user:
```
select username
from dba_users
where common ='YES'
order by username;
```

To view a list of privileges that can be used with the ANY keyword, you can run the following query as an ADMIN user:

select distinct(privilege)
from dba_sys_privs
where grantee like 'ADMIN' and privilege like '%ANY%'
order by privilege;

Database Initialization Parameters

Autonomous AI Database configures database initialization parameters automatically when you provision a database. You do not need to set any initialization parameters to start using your service. However, you can modify some parameters if you need to.

List of Initialization Parameters that can be Modified

Note: Click an initialization parameter in the following list to learn more about it. For the parameters that are not clickable below, see Oracle Database 19c Reference or Oracle Database 26ai Reference for more information.

ALLOW_ROWID_COLUMN_TYPE
APPROX_FOR_AGGREGATION
APPROX_FOR_COUNT_DISTINCT
APPROX_FOR_PERCENTILE
AWR_PDB_AUTOFLUSH_ENABLED
CONTAINER_DATA
CURRENT_SCHEMA (Session only by using ALTER SESSION)
CURSOR_SHARING
DB_BLOCK_CHECKING
DDL_LOCK_TIMEOUT
FIXED_DATE
GLOBAL_NAMES
HEAT_MAP
IGNORE_SESSION_SET_PARAM_ERRORS
INMEMORY_OPTIMIZED_ARITHMETIC (Allowed only with ALTER SYSTEM)
INMEMORY_QUERY (Allowed with ALTER SYSTEM and ALTER SESSION)
JOB_QUEUE_PROCESSES (You can only lower its value or bring it back to the original value)
LDAP_DIRECTORY_ACCESS
MAX_IDLE_TIME
NLS_CALENDAR
NLS_COMP
NLS_CURRENCY
NLS_DATE_FORMAT
NLS_DATE_LANGUAGE
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_LANGUAGE
NLS_LENGTH_SEMANTICS
NLS_NCHAR_CONV_EXCP
NLS_NUMERIC_CHARACTERS
NLS_SORT
NLS_TERRITORY
NLS_TIME_FORMAT
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
OPEN_CURSORS (Allowed range of values are 1000 to 4000)
OPEN_LINKS (You must set SCOPE=SPFILE and restart the Autonomous Database after modifying this parameter)
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES (Session only by using ALTER SESSION)
OPTIMIZER_IGNORE_HINTS
OPTIMIZER_IGNORE_PARALLEL_HINTS
OPTIMIZER_INMEMORY_AWARE (Allowed with ALTER SYSTEM and ALTER SESSION)
OPTIMIZER_MODE
PARALLEL_MIN_DEGREE
PARALLEL_DEGREE_LIMIT
PLSCOPE_SETTINGS
PLSQL_CCFLAGS
PLSQL_DEBUG
PLSQL_OPTIMIZE_LEVEL
PLSQL_WARNINGS
QUERY_REWRITE_INTEGRITY
RECYCLEBIN
RESULT_CACHE_MODE
SESSION_EXIT_ON_PACKAGE_STATE_ERROR
SQL_TRACE (Allowed only with ALTER SESSION)
STATISTICS_LEVEL (Session only by using ALTER SESSION)
SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous AI Database
TIME_ZONE
UNDO_RETENTION (min=300 and max=86400)

SYSDATE_AT_DBTIMEZONE Select a Time Zone for SYSDATE on Autonomous AI Database

SYSDATE_AT_DBTIMEZONE enables special handling in a session for the date and time value returned in calls to SYSDATE and SYSTIMESTAMP.

Depending on the value of SYSDATE_AT_DBTIMEZONE, you see either the date and time based on the default Autonomous AI Database time zone, Coordinated Universal Time (UTC), or based on the time zone that you set in your database.

Property	Description
Parameter type	Boolean
Default Value	`FALSE`
Modifiable	`ALTER SESSION`, `ALTER SYSTEM`
Range of values	`TRUE`	`FALSE`

Default Autonomous AI DatabaseTime Zone

The default Autonomous AI Database time zone is Coordinated Universal Time (UTC) and by default calls to SYSDATE and SYSTIMESTAMP return the date and time in UTC.

In order to change database time zone, you can run the following statement.

This example sets the database time zone to UTC-5.

ALTER DATABASE SET TIME_ZONE='-05:00';

This example sets the database time zone for the US east coast: America/New_York and the time is automatically adjusted at DST switches:

ALTER DATABASE SET TIME_ZONE='America/New_York';

Note: You must restart the Autonomous AI Database instance for the change to take effect.

After you set the database time zone, by default SYSDATE and SYSTIMESTAMP continue to return date and time in UTC (SYSDATE_AT_DBTIMEZONE is FALSE by default). If you set SYSDATE_AT_DBTIMEZONE to TRUE in a session, SYSDATE and SYSTIMESTAMP return the database time zone.

See Setting the Database Time Zone in Oracle Database 19c or Oracle Database 26ai for more information on using the SET TIME_ZONE clause with ALTER DATABASE.

Using SYSDATE_AT_DBTIMEZONE in a Session

When SYSDATE_AT_DBTIMEZONE is FALSE in a session, calls to SYSDATE and SYSTIMESTAMP return values based on the default Autonomous AI Database time zone, Coordinated Universal Time (UTC).

When SYSDATE_AT_DBTIMEZONE is TRUE in a session, calls to SYSDATE or SYSTIMESTAMP return the date and time based on the database time zone.

Note: Setting SYSDATE_AT_DBTIMEZONE to TRUE only affects the use of SYSDATE and SYSTIMESTAMP as operators in application SQL (for example, in queries, DML, and CTAS operations). When using this parameter, it is recommended that your client/session timezone matches your database timezone.

Example

The following example returns dates and times for two different time zones, based on the SYSDATE_AT_DBTIMEZONE parameter value:

SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
_____________
-05:00

SQL> ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=FALSE;

Session altered.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
______________________________________
27-JAN-22 06.59.45.708082000 PM GMT

SQL> ALTER SESSION SET SYSDATE_AT_DBTIMEZONE=TRUE;

Session altered.

SQL> SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
_________________________________________
27-JAN-22 02.14.47.578946000 PM -05:00

Note: When a SYSDATE or SYSTIMESTAMP query is executed in SQL Worksheet of Database Actions, the time and date value that is returned is in UTC (when SYSDATE_AT_DBTIMEZONE parameter is set to TRUE or FALSE). To obtain the database time zone when working in Database Actions, use TO_CHAR() as follows:

SQL> SELECT TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SS TZH":"TZM') FROM DUAL;
TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"HH24:MI:SSTZH":"TZM')
___________________________________________________________
2022-01-27T14:15:00 -05:00