Oracle Database 12c SQL statements Review


All Statements in the column NEW have been updated in the 12.1.0.2 release.

The Oracle Database 12c SQL DML statements access and manipulate data in existing schema objects

SATEMENT PAGE NEW COMMIT PL/SQL SUPPORT
SELECT 77 SELECT DOES NOT implicitly commit the current transaction. SELECT INTO is fully supported in PL/SQL code,
but SELECT without INTO must be executed dynamically in PL/SQL code.
INSERT 17 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
UPDATE 71 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
DELETE 9 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
MERGE 5 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
LOCK TBLE 3 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
EXPLAIN PLAN 4 DOES NOT implicitly commit the current transaction. Supported in PL/SQL only when executed dynamically.
CALL 4 DOES NOT implicitly commit the current transaction. Supported in PL/SQL only when executed dynamically.

The Oracle Database 12c Transaction Control Statements manage changes made by DML statements

SATEMENT PAGE NEW COMMIT PL/SQL SUPPORT
COMMIT 14 Commits a transaction. Fully Supported in PL/SQL.
ROLLBACK 2 Rolls back a transaction. Fully Supported in PL/SQL.
SAVEPOINT 2 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
SET TRANSACTION 3 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.
SET CONSTRAINT[S] 2 DOES NOT implicitly commit the current transaction. Fully Supported in PL/SQL.

The Oracle Database 12c SQL DDL statements create, alter, and drop schema objects, grant and revoke privileges and roles, analyze information on a table, index, or cluster, establish auditing options, add comments to the data dictionary

SATEMENT PAGE NEW COMMIT PL/SQL SUPPORT
ADMINISTER KEY MANAGEMENT 19 ADMINISTER KEY MANAGEMENT Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
ALTER … 345

ALTER PLUGGABLE DATABASE

ALTER CLUSTER

ALTER TABLE

ALTER INDEX

ALTER INDEXTYPE

ALTER SEQUENCE

ALTER DATABASE

ALTER DISKGROUP

ALTER FLASHBACK ARCHIVE

ALTER MATERIALIZED VIEW

ALTER MATERIALIZED VIEW LOG

ALTER MATERIALIZED ZONEMAP

ALTER AUDIT POLICY (Unified Auditing)

Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
CREATE … 361

CREATE PLUGGABLE DATABASE

CREATE CLUSTER

CREATE TABLE

CREATE TABLESPACE

CREATE INDEX

CREATE INDEXTYPE

CREATE SEQUENCE

CREATE DISKGROUP

CREATE FLASHBACK ARCHIVE

CREATE VIEW

CREATE MATERIALIZED VIEW

CREATE MATERIALIZED VIEW LOG

CREATE MATERIALIZED ZONEMAP

Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
DROP … 56

DROP PLUGGABLE DATABASE

DROP MATERIALIZED ZONEMAP

DROP AUDIT POLICY (Unified Auditing)

Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
FLASHBACK … 9 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
ANALYZE 8 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
ASSOCIATE
STATISTICS
4 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
AUDIT … 19 CREATE AUDIT POLICY (Unified Auditing)

AUDIT (Unified Auditing)
Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
CREATE COMMENT 2 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
DROP DISASSOCIATE STATISTICS 2 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
GRANT 27 GRANT Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
NOAUDIT … 9 NOAUDIT (Unified Auditing) Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
PURGE 2 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
RENAME 2 Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
REVOKE 10 REVOKE Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.
TRUNCATE… 6 TRUNCATE TABLE Implicit COMMIT of the current transaction before and after every DDL statement. Supported by PL/SQL with the use of the DBMS_SQL package.

Be aware of the following facts:

  • The CREATE, ALTER, and DROP commands require exclusive access to the specified object. For example, an ALTER TABLE statement fails if another user has an open transaction on the specified table.
  • The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not require exclusive access to the specified object. For example, you can analyze a table while other users are updating the table.
  • Many DDL statements may cause Oracle Database to recompile or reauthorize schema
    objects.

The Oracle Database 12c Session Control Statements dynamically manage the properties of a user session

SATEMENT PAGE NEW COMMIT PL/SQL SUPPORT
ALTER SESSION … 14 DOES NOT implicitly commit the current transaction. PL/SQL DOES NOT Support session control statements
SET ROLE 3 DOES NOT implicitly commit the current transaction. To run the SET ROLE command from PL/SQL, you must use dynamic SQL, preferably the EXECUTE IMMEDIATE statement.

The Oracle Database 12c System Control Statement Dynamically manages the properties of an Oracle Database instance

SATEMENT PAGE NEW COMMIT PL/SQL SUPPORT
ALTER SYSTEM … 22 ALTER SYSTEM DOES NOT implicitly commit the current transaction. PL/SQL DOES NOT Support ALTER SYSTEM statement. This statement does not clear shared SQL and PL/SQL areas for items that are currently being executed. You can use this clause regardless of whether your instance has the database dismounted or mounted, open or closed.

Reference
Oracle® Database SQL Language Reference 12c Release 1 (12.1) E41329-09

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s