ANALYZE
AUDIT

CALL
Cluster - CREATE CLUSTER
- ALTER CLUSTER
- DROP CLUSTER
COMMENT
COMMIT
CONNECT
CONSTRAINT - Add / Enable
Context - CREATE CONTEXT
- DROP CONTEXT
Controlfile - CREATE CONTROLFILE

Database - CREATE DATABASE
- ALTER DATABASE
DELETE
DESCRIBE
Dimension - CREATE DIMENSION
- ALTER DIMENSION
- DROP DIMENSION
Directory - CREATE DIRECTORY
- DROP DIRECTORY

EXEC
EXECUTE IMMEDIATE
EXPLAIN PLAN

Function - CREATE FUNCTION
- ALTER FUNCTION
- DROP FUNCTION

GRANT

Index - CREATE INDEX
- ALTER INDEX
- DROP INDEX
Indextype - CREATE INDEXTYPE
- DROP INDEXTYPE
INSERT
INTERSECT

Java - CREATE JAVA
- ALTER JAVA
- DROP JAVA

Library - CREATE LIBRARY
- DROP LIBRARY
Link - CREATE DATABASE LINK
- DROP DATABASE LINK
LOCK TABLE

Mview - CREATE MATERIALIZED VIEW
- ALTER MATERIALIZED VIEW
- DROP MATERIALIZED VIEW
- CREATE MATERIALIZED VIEW LOG
- ALTER MATERIALIZED VIEW LOG
- DROP MATERIALIZED VIEW LOG
MINUS

NOAUDIT

Operator - CREATE OPERATOR
- DROP OPERATOR
Outline - CREATE OUTLINE
- ALTER OUTLINE
- DROP OUTLINE

Package - CREATE PACKAGE/BODY
- ALTER PACKAGE
- DROP PACKAGE
Pfile - CREATE PFILE
Procedure - CREATE PROCEDURE
- ALTER PROCEDURE
- DROP PROCEDURE
Profile - CREATE PROFILE
- ALTER PROFILE
- DROP PROFILE

RECOVER
RENAME
Resource - ALTER RESOURCE COST
REVOKE
RMAN - Recovery Manager
Role - CREATE ROLE
- SET ROLE
- ALTER ROLE
- DROP ROLE
ROLLBACK
Rollback - CREATE ROLLBACK SEGMENT
- ALTER ROLLBACK SEGMENT
- DROP ROLLBACK SEGMENT

SAVEPOINT
Schema - CREATE SCHEMA
SELECT
Sequence - CREATE SEQUENCE
- ALTER SEQUENCE
- DROP SEQUENCE
Session - ALTER SESSION
SHUTDOWN
SNAPSHOT
SPfile - CREATE SPFILE
STARTUP
Statistics - ASSOCIATE STATISTICS
- DISASSOCIATE STATISTICS
Synonym - CREATE SYNONYM
- DROP SYNONYM
System - ALTER SYSTEM

Table - CREATE TABLE
- ALTER TABLE
- DROP TABLE
Tablespace - CREATE TABLESPACE
- ALTER TABLESPACE
- DROP TABLESPACE
- CREATE TEMPORARY TABLESPACE
Transaction - SET TRANSACTION
Trigger - CREATE TRIGGER
- ALTER TRIGGER
- DROP TRIGGER
TRUNCATE
Type - CREATE TYPE
- ALTER TYPE
- DROP TYPE
- CREATE TYPE BODY
- DROP TYPE BODY

UPDATE
UNION
User - CREATE USER
- ALTER USER
- DROP USER

View - CREATE VIEW
- ALTER VIEW
- DROP VIEW

ANALYZE Statement

Update statistics.
Now superceeded by GATHER_SCHEMA_STATS

Syntax:

ANALYZE TABLE tablename COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
ANALYZE INDEX indexname COMPUTE|ESTIMATE|DELETE STATISTICS ptnOption options
ANALYZE CLUSTER clustername COMPUTE|ESTIMATE|DELETE STATISTICS options

ptnOption
PARTITION (partion)
SUBPARTITION (subpartition)

options
VALIDATE STRUCTURE [CASCADE] [INTO tablename]
LIST CHAINED ROWS [INTO tablename]
COMPUTE|ESTIMATE STATISTICS FOR TABLE
COMPUTE|ESTIMATE STATISTICS FOR ALL COLUMNS
COMPUTE|ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS
COMPUTE|ESTIMATE STATISTICS FOR COLUMNS [SIZE int] column [SIZE int]

When Estimating statistics you can optionally
specify
... ESTIMATE STATISTICS SAMPLE n ROWS
... ESTIMATE STATISTICS SAMPLE n PERCENT

Validate structure will perform an integrity check - and will therefore lock the table/index/cluster while it is running.

If the INTO clause is used to store a list of chained rows in a table - the default tablename is CHAINED_ROWS

UTLCHAIN.sql - creates the chained_rows table

The ANALYZE command is available for all versions of Oracle, however to obtain faster and better statistics use the procedures supplied - in 7.3.4 and 8.0 DBMS_UTILITY.ANALYZE_SCHEMA, and in 8i and above - DBMS_STATS.GATHER_SCHEMA_STATS

AUDIT Statement

Audit an SQL statement or access to a specific database object.

Syntax Auditing SQL:

AUDIT ALL|ALL PRIVILEGES|sql_statement|system_priv [options]

Options:
BY user
BY proxy [ON BEHALF OF ANY|user]
BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL]

Syntax for Auditing Objects:

AUDIT action on schema.object BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL]
AUDIT action on DEFAULT BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL]
AUDIT action on DIRECTORY dir_name BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL]

Where actions is any of
ALTER, AUDIT, COMMENT, DELETE, EXECUTE, GRANT,
INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE

You must first enable auditing with the init.ora parameter AUDIT_TRAIL = YES

CALL

Execute a procedure or function from within SQL (may be used for both stored rocedures/packages and standalone routines).

Syntax:

CALL [schema.] item_to_call

CALL [schema.] [package.] item_to_call [INTO :host_variable [[INDICATOR] :indicator_var] ]

CALL [schema.] [type.] item_to_call [INTO :host_variable [[INDICATOR] :indicator_var] ]

Key

item_to_call: function [@dblink] (expr,...)
procedure [@dblink] (expr,...)
method [@dblink] (expr,...)

indicator_var: The value or condition of the host variable

Example

CALL place_order(453);

CREATE CLUSTER

Create a cluster of tables.

Syntax:

CREATE CLUSTER schema.cluster(col1 type, col2 type...)
options [ROWDEPENDENCIES|NOROWDEPENDENCIES] [CACHE|NOCACHE] ;

Options:
The options used with this command can be any combination of the following

PCTUSED int
PCTFREE int
INITRANS int
MAXTRANS int
SIZE int K | M
TABLESPACE tablespace_name
STORAGE storage_clause
INDEX | [SINGLE TABLE] HASHKEYS int [HASH IS expression]
PARALLEL parallel_clause

If the PARALLEL clause is used it should be the last option.

SIZE dictates the amount of space (in bytes) reserved to store all rows with the same cluster key value or the same hash value.

Specify INDEX to create an indexed cluster.

Specify the HASHKEYS clause to create a hash cluster and specify the number of
hash values for a hash cluster.

Specify CACHE if you want the blocks retrieved for this cluster to be placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This may improve performance for small lookup tables.

The ROWDEPENDENCIES setting is primarily used to allow parallel propagation in a replicated database.

ALTER CLUSTER

Change the storage properties of a cluster.

Syntax:

ALTER CLUSTER schema.cluster options

The options used with this command can be any combination of the following:

PCTUSED int
PCTFREE int
INITRANS int
MAXTRANS int
SIZE int K | M
TABLESPACE tablespace_name
STORAGE storage_clause
CACHE int | NOCACHE
ALLOCATE EXTENT
ALLOCATE EXTENT SIZE int K | M
ALLOCATE EXTENT DATAFILE 'filename'
ALLOCATE EXTENT INSTANCE int
DEALLOCATE UNUSED
DEALLOCATE UNUSED KEEP int K | M
INSTANCE instance_no
PARALLEL parallel_clause

If the PARALLEL clause is used it should be the last option.

Specify CACHE if you want the blocks retrieved for this cluster to be placed at the most recently used end of the least recently used (LRU) list in the buffer cache when a full table scan is performed. This may improve performance for small lookup tables.

When you explicitly ALLOCATE an extent, Oracle will ignore the cluster’s storage parameters when allocating space for a new extent. Therefore, specify SIZE only if you do not want to use the default value.

DROP CLUSTER

Drop a cluster.

Syntax:

DROP CLUSTER [schema.]cluster [INCLUDING TABLES [CASCADE CONSTRAINTS]]

COMMENT

Add a comment to the data dictionary:

Syntax:

COMMENT ON TABLE [schema.]table IS 'text'
COMMENT ON TABLE [schema.]view IS 'text'
COMMENT ON TABLE [schema.]materialized_view IS 'text'

COMMENT ON COLUMN [schema.]table.column IS 'text'
COMMENT ON COLUMN [schema.]view.column IS 'text'
COMMENT ON COLUMN [schema.]materialized_view.column IS 'text'

To drop a comment from the database, set it to the empty string ' '

Example

COMMENT ON COLUMN supplies.part_no IS ’The six digit part number’;

COMMIT

Save changes (transactional).

Syntax:

COMMIT [WORK] [COMMENT 'comment_text']
COMMIT [WORK] [FORCE 'force_text' [,int] ]

FORCE - will manually commit an in-doubt distributed transaction
force_text - transaction identifier (see the DBA_2PC_PENDING view)
int - sets a specific SCN

If a network or machine failure prevents a distributed transaction from committing properly, Oracle will store any commit comment in the data dictionary along with the transaction ID.

Commit comments are likely to be depracated in a future version of Oracle.

CONNECT

Start a user session.

Syntax:

Connecting from an SQL Prompt:

CONNECT "username"
CONNECT "username/password"
CONNECT "username/password@connect_string"
CONNECT "username/password@connect_string AS SYSDBA"
CONNECT / AS SYSDBA

Starting SQL*Plus:

SQLPLUS [option] "<username>" [<start>]
SQLPLUS [option] "username/password" [<start>]
SQLPLUS [option] "username/password@connect_string" [<start>]
SQLPLUS [option] "username/password@connect_string AS SYSDBA" [<start>]
SQLPLUS -- Interactive logon
SQLPLUS /NOLOG [<start>] -- Don't login to a database
SQLPLUS -H -- Display SQL*Plus Help
SQLPLUS -V -- Display SQL*Plus version

Options
"-L" Attempt log on just once
"-M <o>" Use HTML markup options <o>
"-R <n>" Use restricted mode <n>
"-S" Use silent mode
/ Login without using TNS (i.e on the server) ORACLE_SID
/NOLOG Don't login to a database

<start> ::=
@<filename>[.<ext>] [<parameter> ...]
@<URI>

Notes
CONNECT can be abbreviated as CONN
To connect as System Operator rather than DBA, replace SYSDBA with SYSOPER in the syntax above.
CONNECT INTERNAL is no longer a valid login option (deprecated in 8.0, abolished in 9i.)

Examples
sqlplus "system/manager@test.com"
Connected
SQL> show user

USER is "SYSTEM"

sqlplus "sys/manager@test.com AS SYSDBA"
Connected
SQL> show user

USER is "SYS"

-- To connect without using TNS (on the server)

set oracle_sid=test
sqlplus "sys/manager as sysdba"