What's New in SQL

This section lists new features for Release 11.2.1 that are documented in this reference and provides cross-references to additional information.

Access Control

• New system privileges and object privileges. See Chapter 6, "Privileges". Also see "Required privileges" for each SQL statement in Chapter 5, "SQL Statements".

• New system views:

  • SYS.DBA_SYS_PRIVS and SYS.USER_SYS_PRIVS

  • SYS.ALL_TAB_PRIVS, SYS.DBA_TAB_PRIVS and SYS.USER_TAB_PRIVS

  • SYS.ALL_USERS, SYS.DBA_USERS and SYS.USER_USERS

  • SYS.ALL_COL_PRIVS, SYS.DBA_COL_PRIVS and SYS.USER_COL_PRIVS

  • SYS.SESSION_ROLES

• New syntax for ALTER USER, CREATE USER, DROP USER, GRANT and REVOKE statements

• Revised reserved words. See Chapter 8, "Reserved Words".

PL/SQL support

The ALTER SESSION statement has been enhanced.

These statements are new:

• ALTER FUNCTION

• ALTER PACKAGE

• ALTER PROCEDURE

• CREATE FUNCTION

• CREATE PACKAGE

• CREATE PACKAGE BODY

• CREATE PROCEDURE

• DROP FUNCTION

• DROP PACKAGE [BODY]

• DROP PROCEDURE

These system tables and system views are new:

• SYS.ALL_ARGUMENTS, SYS.DBA_ARGUMENTS and SYS.USER_ARGUMENTS

• SYS.ALL_DEPENDENCIES, SYS.DBA_DEPENDENCIES and SYS.USER_DEPENDENCIES

• SYS.ALL_ERRORS, SYS.DBA_ERRORS and SYS.USER_ERRORS

• SYS.ALL_IDENTIFIERS, SYS.DBA_IDENTIFIERS and SYS.USER_IDENTIFIERS

• SYS.ALL_OBJECTS, SYS.DBA_OBJECTS and SYS.USER_OBJECTS

• SYS.ALL_PLSQL_OBJECT_SETTINGS, SYS.DBA_PLSQL_OBJECT_SETTINGS and SYS.USER_PLSQL_OBJECT_SETTINGS

• SYS.ALL_PROCEDURES, SYS.DBA_PROCEDURES and SYS.USER_PROCEDURES

• SYS.ALL_SOURCE, SYS.DBA_SOURCE and SYS.USER_SOURCE

• SYS.ALL_STORED_SETTINGS, SYS.DBA_STORED_SETTINGS and SYS.USER_STORED_SETTINGS

• SYS.DBA_OBJECT_SIZE and SYS.USER_OBJECT_SIZE

• SYS.PUBLIC_DEPENDENCY

These SQL functions are new:

• SYS_CONTEXT

• UID

Oracle In-Memory Database Cache (IMDB Cache)

These IMDB Cache features are new:

• Dynamic cache groups - In a dynamic cache group, new cache instances are loaded manually into the TimesTen cache tables using a load operation, or on demand using a dynamic load operation. See:

  • "CREATE CACHE GROUP"

  • "LOAD CACHE GROUP"

  • "REFRESH CACHE GROUP"

  • "UNLOAD CACHE GROUP"

• Cache grid - A cache grid is a collection of TimesTen databases that collectively manage the application data using the relational data model. A cache grid consists of one or more grid members each backed by a TimesTen database. See "CREATE CACHE GROUP".

• Global cache groups - In a global cache group, data in the cache tables are shared among TimesTen databases within a cache grid. See "CREATE CACHE GROUP".

Asynchronous materialized views

Materialized views can be refreshed asynchronously. The CREATE MATERIALIZED VIEW statement has been enhanced. These statements are new:

• CREATE MATERIALIZED VIEW LOG

• DROP MATERIALIZED VIEW LOG

• REFRESH MATERIALIZED VIEW

RETURNING ... INTO clause

The DELETE, INSERT and UPDATE statements have been enhanced with the RETURNING...INTO clause.

ROWID data type

The ROWID data type has been implemented. See "ROWID data type".

Duplicate parameter names

TimesTen offers Oracle-style behavior for duplicated parameter names. See "Duplicate parameter names".

Bitmap indexes

You can create bitmap indexes. See "CREATE INDEX".

Set operators in subqueries

Set operators are allowed in subqueries. See "Subqueries".

Enhanced ":" parameter markers

":" parameter markers have been enhanced. See "Dynamic parameters".

Multiline C-style comments

You can use multiline C-style comments in SQL statements. See "Comments within SQL statements".

View and sequence names

A view and a sequence cannot have the same name. See "CREATE VIEW" and "CREATE SEQUENCE".