Halimdba: Oracle Syntax Master

Oracle Syntax Master

SELECT Syntax statement

SELECT [hint][DISTINCT] select_list

FROM table_list

[WHERE conditions]

[GROUP BY group_by_list]

[HAVING search_conditions]

[ORDER BY order_list [ASC | DESC] ]

[FOR UPDATE for_update_options]

select_list
column1, column2, column3

table.column1, table.column2

table.column1 C_1_Alias, table.column2 C_2_Alias

schema.table.column1 Col_1_Alias, schema.table.column2 c_2_Alias

schema.table.*

expr1, expr2

(subquery [WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint]])

In the select_lists above, 'table' may be replaced with view or snapshot.
Using the * expression will return all columns. If a Column_Alias is specified this will appear at the top of any column headings in the query output.

FROM table_list
Contains a list of the tables from which the result set data is retrieved.

[schema.]{table | view | snapshot}[@dblink] [t_alias]

When selecting from a table you can also specify Partition and/or Sample clauses e.g. [schema.]table [PARTITION (partition)] [SAMPLE (sample_percent)]

WHERE search_conditions
A filter that defines the conditions each row in the source table(s) must meet to qualify for the SELECT. Only rows that meet the conditions will be included in the result set. The WHERE clause can also contain inner and outer join specifications (SQL1989 standard). e.g.

WHERE tableA.column = tableB.column

WHERE tableA.column = tableB.column(+)

WHERE tableA.column(+) = tableB.column

GROUP BY group_by_list
The GROUP BY clause partitions the result set into groups.
The group_by_list may be one or more columns or expressions and may optionally include the CUBE / ROLLUP keywords for creating crosstab results.

Heirarchical Queries
Any query that does *not* include a GROUP BY clause may include a CONNECT BY heirarchy clause:

[START WITH condition] CONNECT BY condition

HAVING search_conditions
An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause.

ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ]
The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1...9 a...z) or descending (9...1 z...a).

You can sort by any column even if that column is not actually in the main SELECT clause. If you do not include an ORDER BY clause then the order of the result set rows will be unpredictable (random or quasi random).

FOR UPDATE options - this locks the selected rows (Oracle will normally wait for a lock unless you spacify NOWAIT)

FOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT]

Writing a SELECT statement

The clauses (SELECT ... FROM ... WHERE ...GROUP BY... HAVING ... ORDER BY ... ) must be in this order.

SELECT command { UNION | UNION ALL | INTERSECT | MINUS } SELECT command ]

INSERT Syntax Statement

INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...)

VALUES (expression)

INSERT [hint] INTO [schema.] table

[[SUB]PARTITION (ptn_name)] [t_alias] (column, column,...)

VALUES (expression)

INSERT [hint] INTO subquery

WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ]

[t_alias] (column, column,...)

VALUES (expression)

VALUES(expression) can be expanded to

VALUES ([expr, expr...] [subquery])

[RETURNING expr, expr... INTO host_variable|plsql_variable]

UPDATE Syntax Statement

UPDATE [hint] [schema.]table [@dblink] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s)

[WHERE condition]

[ RETURNING (expr,...) INTO (data_item,...) ]

UPDATE [hint] [schema.]table [[SUB]PARTITION partition] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s)

[WHERE condition]

[ RETURNING (expr,...) INTO (data_item,...) ]

UPDATE [hint] [schema.]view [@dblink] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s) [WHERE condition]

UPDATE [hint] [schema.]snapshot [@dblink] [alias]

[ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ]

SET col_expr(s) [WHERE condition]

col_expr:

column = expression

column = (subquery)

(column, column,...) = (subquery)

VALUE (table_alias) = expression

VALUE (table_alias) = (subquery)

To update multiple columns, separate col_expr with commas. The terms "snapshot" and "materialized view" are synonymous.

DELETE Syntax Statement

DELETE [FROM] [schema.] table [@dblink] [alias]

WHERE (condition)

[RETURNING expr INTO DATA_ITEM]

DELETE [FROM] [schema.] table [SUB]PARTITION partition [alias]

WHERE (condition)

[RETURNING expr INTO DATA_ITEM]

DELETE [FROM] [schema.] view [@dblink] [alias]

WHERE (condition)

[RETURNING expr INTO DATA_ITEM]

DELETE [FROM] subquery [WITH READ ONLY] [alias]

WHERE (condition)

[RETURNING expr INTO DATA_ITEM]

DELETE [FROM] subquery [WITH CHECK OPTION] [CONSTRAINT constraint] [alias]

WHERE (condition)

[RETURNING expr INTO DATA_ITEM]

Example:

The following example returns column es_salary from the deleted rows and stores the result in bind array :1

DELETE FROM employee

WHERE emp_no = 1075 AND commission = 50

RETURNING salary INTO :1;

ALTER DATABASE Syntax Statement

ALTER DATABASE

Open an existing database, and /or modify associated files.

ALTER DATABASE database_name options

options can be any combination of the following:

open / mount options:

MOUNT

MOUNT STANDBY DATABASE

MOUNT CLONE DATABASE

MOUNT PARALLEL

MOUNT STANDBY DATABASE

CONVERT

OPEN [READ ONLY]

OPEN [READ WRITE] RESETLOGS|NORESETLOGS

ACTIVATE STANDBY DATABASE

[NATIONAL] CHARACTER SET char_set

archivelog options:

ARCHIVELOG

NOARCHIVELOG

backup and recovery options:

BACKUP CONTROLFILE TO 'filename' [REUSE]

BACKUP CONTROLFILE TO TRACE

BACKUP CONTROLFILE TO TRACE RESETLOGS

CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]

RENAME FILE 'data_file_name' TO 'data_file_name'

RENAME FILE 'redolog_file_name' TO 'redolog_file_name'

RECOVER recover_clause

DATAFILE 'filename' END BACKUP

Datafile options:

CREATE DATAFILE 'filename' AS filespec

DATAFILE 'filename' ONLINE

DATAFILE 'filename' OFFLINE [DROP]

DATAFILE 'filename' RESIZE int K | M

DATAFILE 'filename' AUTOEXTEND OFF

DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]

DATAFILE 'filename' END BACKUP

TEMPFILE 'filename' ONLINE

TEMPFILE 'filename' OFFLINE

TEMPFILE 'filename' DROP

TEMPFILE 'filename' RESIZE int K | M

TEMPFILE 'filename' AUTOEXTEND OFF

TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]

redo log options:

ADD LOGFILE [THREAD int] [GROUP int] filespec

ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int

ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename'

DROP LOGFILE GROUP int

DROP LOGFILE ('filename')

DROP LOGFILE MEMBER 'filename'

CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE]

CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE]

Parallel server options:

CREATE STANDBY CONTROLFILE AS 'filename' [REUSE]

SET DBLOW = 'text'

SET DBHIGH = 'text'

SET DBMAC = ON | OFF

ENABLE [PUBLIC] THREAD int

DISABLE THREAD int

Backwards compatibility options:

RENAME GLOBAL_NAME TO database [domain]

RESET COMPATIBILITY

database_name is defined when the database is created - it is normally set to the same as the database SID.

Some of the commands above can only be used when the database is in a particular state:

MOUNT, CONVERT - Require that the db is Not Mounted.

ARCHIVELOG, NOARCHIVLOG, RECOVER - Require that the db is Mounted but not open (must be mount exclusive - not mount parallel).

ENABLE, DISABLE, RENAME GLOBAL_NAME, RESET, SET - Require that the db is Open.

All other options will work with the db mounted, open or closed as long as none of the files involved are 'in use'

ALTER RESOURCE COST Syntax Statement

ALTER RESOURCE COST option(s)

options are any combination of

CPU_PER_SESSION int

CONNECT_TIME int

LOGICAL_READS_PER_SESSION int

PRIVATE_SGA int

int is the integer weight applied to each option

The units being costed are
CPU = 1/100 sec
connect_time = 1/100 sec
SGA = bytes

ALTER SESSION Syntax Statement

ALTER SESSION ADVISE {COMMIT | ROLLBACK | NOTHING}

ALTER SESSION CLOSE DATABASE LINK link_name

ALTER SESSION {ENABLE | DISABLE} COMMIT IN PROCEDURE

ALTER SESSION {ENABLE | DISABLE | FORCE} PARALLEL {DML|DDL|QUERY} [PARALLEL int]

ALTER SESSION SET option(s)

options:

CONSTRAINT[S] {IMMEDIATE|DEFERRED|DEFAULT}

CREATE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' }

CURRENT_SCHEMA = schema

CURSOR_SHARING = {FORCE | EXACT}

DB_BLOCK_CHECKING = {TRUE | FALSE}

DB_FILE_MULTIBLOCK_READ_COUNT = int

FAST_START_IO_TARGET = int

FLAGGER = {ENTRY | INTERMEDIATE | FULL | OFF}

GLOBAL_NAMES = {TRUE | FALSE}

HASH_AREA_SIZE = int

HASH_JOIN_ENABLED = {TRUE | FALSE}

HASH_MULTIBLOCK_IO_COUNT = int

INSTANCE = int

ISOLATION_LEVEL = {SERIALIZABLE | READ_COMMITTED}

LABEL = {'text' | DBLOW | DBHIGH | OSLABEL}

LOG_ARCHIVE_DEST_n (read the Oracle8i Reference for more on this)

LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}

LOG_ARCHIVE_MIN_SUCCEED_DEST = int

MAX_DUMP_FILE_SIZE = { size | UNLIMITED }

NLS_CALENDAR = 'text'

NLS_COMP = 'text'

NLS_CURRENCY = 'text'

NLS_DATE_FORMAT = 'fmt'

NLS_DATE_LANGUAGE = language

NLS_DUAL_CURRENCY = 'text'

NLS_ISO_CURRENCY = territory

NLS_LANGUAGE = language

NLS_NUMERIC_CHARACTERS = 'text'

NLS_SORT = {sort | BINARY}

NLS_TERRITORY = territory

OBJECT_CACHE_MAX_SIZE_PERCENT = int

OBJECT_CACHE_OPTIMAL_SIZE = int

OPTIMIZER_INDEX_CACHING = int

OPTIMIZER_INDEX_COST_ADJ = int

OPTIMIZER_MAX_PERMUTATIONS = int

OPTIMIZER_MODE = {ALL_ROWS | FIRST_ROWS | RULE | CHOOSE}

OPTIMIZER_PERCENT_PARALLEL = int

PARALLEL_BROADCAST_ENABLED = {TRUE | FALSE}

PARALLEL_INSTANCE_GROUP = ' text '

PARALLEL_MIN_PERCENT = int

PARTITION_VIEW_ENABLED = {TRUE | FALSE}

PLSQL_V2_COMPATIBILITY = {TRUE | FALSE}

QUERY_REWRITE_ENABLED = {TRUE | FALSE}

QUERY_REWRITE_INTEGRITY = {enforced | trusted | stale_tolerated}

REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}

SESSION_CACHED_CURSORS = int

SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}

SORT_AREA_RETAINED_SIZE = int

SORT_AREA_SIZE = integer

SORT_MULTIBLOCK_READ_COUNT = int

SQL_TRACE = {TRUE | FALSE}

STAR_TRANSFORMATION_ENABLED = {TRUE | FALSE}

TIMED_STATISTICS = {TRUE | FALSE}

USE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' }

Version 7 Options - now obsolete in v8

CLOSE_CACHED_OPEN_CURSORS = {TRUE | FALSE}

NLS_ISO_CURRENCY = territory

MLS_LABEL_FORMAT = 'fmt'

OPTIMISER_GOAL - is now OPTIMISER_MODE

SCHEMA=schema_name

ALTER SYSTEM Syntax Statement

ALTER SYSTEM ARCHIVE LOG archive_log_clause

ALTER SYSTEM CHECKPOINT [GLOBAL | LOCAL]

ALTER SYSTEM CHECK DATAFILES [GLOBAL | LOCAL]

ALTER SYSTEM FLUSH SHARED POOL

ALTER SYSTEM SWITCH LOGFILE

ALTER SYSTEM {ENABLE | DISABLE} DISTRIBUTED RECOVERY

ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION

ALTER SYSTEM {SUSPEND | RESUME}

ALTER SYSTEM SHUTDOWN [IMMEDIATE] dispatcher_name

ALTER SYSTEM KILL SESSION 'int1, int2' [POST TRANSACTION] [IMMEDIATE]

ALTER SYSTEM DISCONNECT SESSION 'int1, int2' [IMMEDIATE]

ALTER SYSTEM SET option(s)

options:

AQ_TM_PROCESSES = int

BACKGROUND_DUMP_DEST = 'text'

BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED

CONTROL_FILE_RECORD_KEEP_TIME = int

CORE_DUMP_DEST = 'text'

CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name' } [NOOVERRIDE]

CURSOR_SHARING = {force|exact}

DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED

DB_BLOCK_CHECKSUM = {TRUE | FALSE}

DB_BLOCK_MAX_DIRTY_TARGET = int

DB_FILE_DIRECT_IO_COUNT = int DEFERRED

DB_FILE_MULTIBLOCK_READ_COUNT = int

FAST_START_IO_TARGET = int

FAST_START_PARALLEL_ROLLBACK = {FALSE | LOW | HIGH}

FIXED_DATE = { 'DD_MM_YY' | 'YYYY_MI_DD_HH24_MI-SS' }

GC_DEFER_TIME = int

GLOBAL_NAMES = {TRUE | FALSE}

HASH_MULTIBLOCK_IO_COUNT = int

HS_AUTOREGISTER = {TRUE | FALSE}

JOB_QUEUE_PROCESSES = int

LICENSE_MAX_SESSIONS = int

LICENSE_MAX_USERS = int

LICENSE_SESSIONS_WARNING = int

LOG_ARCHIVE_DEST = string

LOG_ARCHIVE_DEST_n = {null_string |

{LOCATION=local_pathname | SERVICE=tnsnames_service}

[MANDATORY | OPTIONAL] [REOPEN[=integer]]}

LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER}

LOG_ARCHIVE_DUPLEX_DEST = string

LOG_ARCHIVE_MAX_PROCESSES = int

LOG_ARCHIVE_MIN_SUCCEED_DEST = int

LOG_ARCHIVE_TRACE = int

LOG_CHECKPOINT_INTERVAL = int

LOG_CHECKPOINT_TIMEOUT = int

MAX_DUMP_FILE_SIZE = { size | 'unlimited'} [DEFERRED]

MTS_DISPATCHERS = dispatcher_clause

MTS_SERVERS = int

OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED

OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED

OPTIMIZER_MAX_PERMUTATIONS = int NOOVERRIDE

PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE}

PARALLEL_INSTANCE_GROUP = 'text'

PARALLEL_THREADS_PER_CPU = int

PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED]

QUERY_REWRITE_ENABLED = {TRUE | FALSE} [DEFERRED | NOOVERRIDE]

QUERY_REWRITE_INTEGRITY = {ENFORCED | TRUSTED | STALE_TOLERATED}

REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE}

RESOURCE_LIMIT = {TRUE | FALSE}

RESOURCE_MANAGER_PLAN = plan_name

SORT_AREA_RETAINED_SIZE = int DEFERRED

SORT_AREA_SIZE = int DEFERRED

SORT_MULTIBLOCK_READ_COUNT = int DEFERRED

STANDBY_ARCHIVE_DEST = string

TIMED_STATISTICS = {TRUE | FALSE}

TIMED_OS_STATISTICS = int

TRANSACTION_AUDITING = {TRUE | FALSE} DEFERRED

USE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' } [NOOVERRIDE]

USER _DUMP_DEST = 'directory_name'

Oracle 7 options now obsolete in v8

CACHE_INSTANCES = int

SCAN_INSTANCES = int

ALTER TABLE Syntax Statement

Change the properties of an existing table.

ALTER TABLE [schema.]table RENAME TO new_table_name

ALTER TABLE [schema.]table

[ [NO]MINIMISE RECORDS PER BLOCK ]

[PARALLEL parallel_clause]

[ENABLE enable_clause | DISABLE disable_clause]

[{ENABLE|DISABLE} TABLE LOCK]

[{ENABLE|DISABLE} ALL TRIGGERS]

ALTER TABLE [schema.]table

iot_overflow_clause

[PARALLEL parallel_clause]

[ENABLE enable_clause | DISABLE disable_clause]

[{ENABLE|DISABLE} TABLE LOCK]

[{ENABLE|DISABLE} ALL TRIGGERS]

ALTER TABLE [schema.]table

partitioning_clause

[PARALLEL parallel_clause]

[ENABLE enable_clause | DISABLE disable_clause]

[{ENABLE|DISABLE} TABLE LOCK]

[{ENABLE|DISABLE} ALL TRIGGERS]

ALTER TABLE [schema.]table

tbl_defs,...

[PARALLEL parallel_clause]

[ENABLE enable_clause | DISABLE disable_clause]

[{ENABLE|DISABLE} TABLE LOCK]

[{ENABLE|DISABLE} ALL TRIGGERS]

tbl_defs:

ADD [column datatype] [DEFAULT expr] [column_constraint(s)]

[table_constraint] [table_ref_constraint]

MODIFY [column datatype] [DEFAULT expr] [column_constraint(s)]

MODIFY [table_constraint]

drop_column_clause

DROP drop_constraint_clause

[PCTFREE int][PCTUSED int][INITTRANS int]

[MAXTRANS int][STORAGE storage_clause]

extent_options

MOVE [ONLINE] storage_options INDEX index_organized_tbl_clause

[LOB_storage_clause][varray_clause]

LOGGING|NOLOGGING

MODIFY NESTED TABLE collection_item RETURN AS {LOCATOR|VALUE }

MODIFY LOB [LOB_storage_clause]

MODIFY VARRAY [varray_clause]

CACHE | NOCACHE

MONITORING | NOMONITORING

storage_options:

PCTFREE int

PCTUSED int

INITTRANS int

MAXTRANS int

STORAGE storage_clause

TABLESPACE tablespace

[LOGGING|NOLOGGING]

extent_options:

ALLOCATE EXTENT [( [size int K | M ]

[DATAFILE 'filename' ] [INSTANCE int] )]

DEALLOCATE UNUSED [KEEP int K | M ]

index_organized_tbl_clause:

storage_option(s) [PCTTHRESHOLD int]

[COMPRESS int|NOCOMPRESS]

[ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

iot_overflow_clause:

{PCTTHRESHOLD int | INCLUDING column} |

OVERFLOW overflow_storage_clause

ADD OVERFLOW [storage_options] [(PARTITION storage_options)]

overflow_storage_clause:

PCTFREE int

PCTUSED int

INITTRANS int

MAXTRANS int

extent_options

STORAGE storage_clause

[LOGGING|NOLOGGING]

nested_storage_clause:

NESTED TABLE nested_item STORE AS storage_table

[RETURN AS {LOCATOR|VALUE }]

drop_column_clause:

SET UNUSED (column,...)

[CASCADE CONSTRAINTS][INVALIDATE]

DROP COLUMN (column,...)

[CASCADE CONSTRAINTS][INVALIDATE] CHECKPOINT int

DROP {UNUSED COLUMNS|COLUMNS CONTINUE} [CHECKPOINT int]

ANALYZE syntax Statement

Update CBO (Cost Based Optimiser) statistics.

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

ASSOCIATE STATISTICS

ASSOCIATE STATISTICS WITH

COLUMNS [schema.]table_column,...

USING [schema.]statistics_type;

ASSOCIATE STATISTICS WITH

object [schema.]object_name,...

cost_usage_clause;

Where object is any of

FUNCTIONS

PACKAGES

TYPES

INDEXES

INDEXTYPES

cost_usage_clauses:

USING [schema.]statistics_type

DEFAULT COST (cpu_cost, io_cost, network_cost)

DEFAULT SELECTIVITY default_selectivity

AUDIT syntax Statement

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

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]

Auditing Objects

To audit an object use the modified syntax

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

CREATE INDEX Syntax Statement

CREATE [UNIQUE|BITMAP] INDEX [schema.]index

ON [schema.]TABLE [tbl_alias]

(col [ASC | DESC]) index_clause index_attribs

CREATE [UNIQUE|BITMAP] INDEX [schema.]index

ON [schema.]TABLE [tbl_alias]

(col_expression [ASC | DESC]) index_clause index_attribs

CREATE [UNIQUE|BITMAP] INDEX [schema.]index

ON CLUSTER [schema.]cluster index_attribs

index_clauses:

INDEXTYPE IS indextype[PARAMETERS ('string')]

LOCAL STORE IN {tablespace_name|DEFAULT}

LOCAL (PARTITION [partition

[LOGGING|NOLOGGING]

[TABLESPACE {tablespace_name|DEFAULT}]

[PCTFREE int]

[PCTUSED int]

[INITRANS int]

[MAXTRANS int]

[STORAGE storage_clause]

[STORE IN {tablespace_name|DEFAULT]

[SUBPARTITION [subpartition [TABLESPACE tablespace_name]]]])

GLOBAL PARTITION BY RANGE (col_list)

( PARTITION partition VALUES LESS THAN (value_list)

[LOGGING|NOLOGGING]

[TABLESPACE {tablespace_name|DEFAULT}]

[PCTFREE int]

[PCTUSED int]

[INITRANS int]

[MAXTRANS int]

[STORAGE storage_clause] )

index_attribs:
any combination of the following

NOSORT|REVERSE

COMPRESS int

NOCOMPRESS

COMPUTE STATISTICS

[NO]LOGGING

ONLINE

TABLESPACE {tablespace_name|DEFAULT}

PCTFREE int

PCTUSED int

INITRANS int

MAXTRANS int

STORAGE storage_clause

PARALLEL parallel_clause

CREATE DATABASE LINK

CREATE [SHARED][PUBLIC] DATABASE LINK link_name

[CONNECT TO CURRENT_USER ]

[USING 'connect_string']

CREATE [SHARED][PUBLIC] DATABASE LINK link_name

[CONNECT TO user IDENTIFIED BY password]

[AUTHENTICATED BY user IDENTIFIED BY password]

[USING 'connect_string']

CREATE MATERIALIZED VIEW Syntax Statement

CREATE MATERIALIZED VIEW [schema.]materialized_view options

[USING INDEX index_options]

[REFRESH [refresh_options]]

[FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]

CREATE MATERIALIZED VIEW [schema.]materialized_view

ON PREBUILT TABLE [{WITH | WITHOUT} REDUCED PRECISION]

[USING INDEX index_options]

[REFRESH [refresh_options]]

[FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE]

options:

CLUSTER cluster (column,...) [Partitioning clause]

[PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}]

storage_options [LOB/Modify LOB Storage clause] [CACHE | NOCACHE]

[Partitioning clause] [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}]

Where storage_options can be any of:

PCTFREE int

PCTUSED int

INITRANS int

MAXTRANS int

STORAGE storage_clause

TABLESPACE tablespace

LOGGING | NOLOGGING

index_options

INITRANS int

MAXTRANS int

STORAGE storage_clause

TABLESPACE tablespace

refresh_options:

FAST | COMPLETE | FORCE

ON [DEMAND | COMMIT]

{NEXT | START WITH} date

WITH {PRIMARY KEY | rowid}

USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT

USING {MASTER|LOCAL} ROLLBACK SEGMENT rb_segment

CREATE MATERIALIZED VIEW LOG

CREATE MATERIALIZED VIEW LOG ON [schema.]table options

[PARALLEL int | NOPARALLEL]

Partitioning_options

WITH filter_option(s)

[{INCLUDING|EXCLUDING} NEW VALUES];

options:

PCTFREE int

PCTUSED int

INITRANS int

MAXTRANS int

STORAGE storage_clause

TABLESPACE tablespace

LOGGING | NOLOGGING

[CACHE | NOCACHE]

filter_options:

[{PRIMARY KEY | rowid}] (filter_column,...)

multiple filter_options can be separated with commas

CREATE SYNONYM Syntax Statement

CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object [@dblink]

You should be aware of the performance hit when accessing data through a synony

CREATE TABLE Syntax Statement

CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (tbl_defs,...)

[ON COMMIT {DELETE|PRESERVE} ROWS]

[storage_options | CLUSTER cluster_name (col1, col2,... )

| ORGANIZATION {HEAP [storage_options] | INDEX idx_organized_tbl_clause}]

[LOB_storage_clause][varray_clause][nested_storage_clause]

partitioning_options

[[NO]CACHE] [[NO]MONITORING] [PARALLEL parallel_clause]

[ENABLE enable_clause | DISABLE disable_clause]

[AS subquery]

tbl_defs:

column datatype [DEFAULT expr] [column_constraint(s)]

table_constraint

table_ref_constraint

storage_options:

PCTFREE int

PCTUSED int

INITTRANS int

MAXTRANS int

STORAGE storage_clause

TABLESPACE tablespace

[LOGGING|NOLOGGING]

idx_organized_tbl_clause:

storage_option(s) [PCTTHRESHOLD int]

[COMPRESS int|NOCOMPRESS]

[ [INCLUDING column_name] OVERFLOW [storage_option(s)] ]

nested_storage_clause:

NESTED TABLE nested_item STORE AS storage_table

[RETURN AS {LOCATOR|VALUE } ]

CREATE TABLESPACE Syntax Statement

CREATE TABLESPACE tablespace_name

DATAFILE Datafile_Options Storage_Options ;

Datafile_Options:

'filespec' [AUTOEXTEND OFF]

'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]]

The Autoextend Maxsize clause will default to UNLIMITED if no value is specified.

Storage_Options:

DEFAULT STORAGE storage_clause

MINIMUM EXTENT int {K|M}

LOGGING | NOLOGGING

ONLINE | OFFLINE

PERMANENT | TEMPORARY

EXTENT MANAGEMENT {DICTIONARY |

LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }

CREATE TRIGGER Syntax Statement

CREATE [OR REPLACE] TRIGGER [schema.]trigger

{BEFORE event | AFTER event | INSTEAD OF event}

referencing_clause WHEN (condition) pl_sql_block

event can be one or more of the following (separate multiple events with OR)

DELETE event_ref

INSERT event_ref

UPDATE event_ref

UPDATE OF column, column... event_ref

ddl_statement ON [schema.] {table|view}

ddl_statement ON DATABASE

SERVERERROR

LOGON

LOGOFF

STARTUP

SHUTDOWN

event_ref:

ON [schema.]table

ON [schema.]view

ON [NESTED TABLE nested_table_column OF] [schema.]view

referencing_clause:

FOR EACH ROW

REFERENCING OLD [AS] old [FOR EACH ROW]

REFERENCING NEW [AS] new [FOR EACH ROW]

REFERENCING PARENT [AS] parent [FOR EACH ROW]

CREATE VIEW Syntax Statement

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW

[schema.]view [(alias,...)]

AS subquery options

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW

[OF [schema.] type_name

[WITH OBJECT IDENTIFIER {DEFAULT|(attribute,...)}]

AS subquery options

options:

WITH READ ONLY

WITH CHECK OPTION [CONSTRAINT constraint]

CREATE FUNCTION Syntax Statement

CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]

RETURN datatype [invoke_clause]

AS plsql_function_body

CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]

RETURN datatype [invoke_clause]

AS LANGUAGE JAVA NAME 'string'

CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause]

RETURN datatype [invoke_clause]

AS LANGUAGE C [NAME name] LIBRARY lib_name [WITH CONTEXT][PARAMETERS params]

arguments_clause:

(argument [IN|OUT|IN OUT] [NOCOPY datatype])

invoke_clause:

any combination of...

AUTHID CURRENT_USER

AUTHID DEFINER

DETERMINISTIC

PARALLEL_ENABLE

CREATE PROCEDURE Syntax Statement

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options)

invoker_rights AS plsql_sub_program_body

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options)

invoker_rights AS LANGUAGE JAVA NAME ('string')

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options)

invoker_rights AS LANGUAGE C NAME name LIBRARY lib_name [WITH CONTEXT][PARAMETERS (parameters)]

options:

argument IN [NOCOPY] datatype

argument OUT [NOCOPY] datatype

argument IN OUT [NOCOPY] datatype

(The procedure can have several arguments separated with commas)

invoker_rights:

AUTHID CURRENT_USER

AUTHID DEFINER

AUTHID DEFINER will execute with the privileges of the procedure schema/owner.

NOCOPY will instruct Oracle to pass the argument as fast as possible. This can significantly enhance performance when passing a large value.

CREATE ROLE Syntax Statement

CREATE ROLE role_name [NOT IDENTIFIED]

CREATE ROLE role_name [IDENTIFIED BY password]

CREATE ROLE role_name [IDENTIFIED EXTERNALLY]

CREATE ROLE role_name [IDENTIFIED GLOBALLY]

Example

--Create the role

CREATE ROLE MY_ORACLE_ROLE

--Assign all object rights from the current user schema (user_objects)

spool GrantRights.sql

SELECT DECODE (object_type,

'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON'||&OWNER||'.', 'VIEW','GRANT SELECT ON '||&OWNER||'.',

'SEQUENCE','GRANT SELECT ON '||&OWNER||'.',

'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.',

'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.',

'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO MY_ORACLE_ROLE ;' from user_objects

WHERE OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE','FUNCTION') ORDER BY OBJECT_TYPE

spool off

@GrantRights.sql

CREATE CONTROLFILE Syntax Statement

CREATE CONTROLFILE [REUSE] [SET] DATABASE database

LOGFILE [GROUP int] filespec

[RESETLOGS | NORESETLOGS]

DATAFILE filespec options

options

MAXDATAFILES int

MAXLOGFILES int

MAXLOGMEMBERS int

MAXLOGHISTORY int

MAXINSTANCES int

ARCHIVELOG | NOARCHIVELOG

Several LOGFILE or DATAFILEs can be specified at once if separated with commas.

CREATE DATABASE Syntax Statement

CREATE DATABASE database_name options

options can be any combination of the following:

DATAFILE filespec AUTOEXTEND OFF

DATAFILE filespec AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]

LOGFILE [GROUP int] filespec

MAXDATAFILES int

MAXLOGFILES int

MAXLOGMEMBERS int

MAXLOGHISTORY int

MAXINSTANCES int

CONTROLFILE REUSE

CHARACTER SET charset

Example

-- Create a database with the SID of TEST and char set WE8ISO8859P1

CREATE DATABASE TEST

LOGFILE 'E:\Oracle\TEST\LOGS\LOG1TEST.ORA' SIZE 2M,

'E:\Oracle\TEST\LOGS\LOG2TEST.ORA' SIZE 200M,

'E:\Oracle\TEST\LOGS\LOG3TEST.ORA' SIZE 200M,

'E:\Oracle\TEST\LOGS\LOG4TEST.ORA' SIZE 200M,

'E:\Oracle\TEST\LOGS\LOG5TEST.ORA' SIZE 200M

MAXDATAFILES 100

DATAFILE 'E:\Oracle\TEST\DATA\SYS1TEST.ORA' SIZE 500 M

NOARCHIVELOG

CHARACTER SET WE8ISO8859P1;

CREATE DIMENSION Syntax Statement

CREATE DIMENSION [schema.]dimension level_clause(s)

[heirarchy_clause(s) attribute_clause(s)];

The command should include at least one heirarchy clause or attribute clause.

level_clause:

LEVEL level IS (table.column,...)

heirarchy_clause:

HEIRARCHY heirarchy (child_level CHILD OF parent_level,... [join_clause])

attribute_clause:

ATTRIBUTE level DETERMINES (dependent_column,...)

join_clause:

JOIN KEY (child_key_column,...) REFERENCES parent_level

CREATE DIRECTORY Syntax Statement

CREATE [OR REPLACE] DIRECTORY directory AS 'pathname';

CREATE JAVA Syntax Statement

CREATE [OR REPLACE] [AND {RESOLVE|COMPILE}] [NOFORCE]

JAVA [RE]SOURCE NAMED [schema.]primary_name

[AUTHID {CURRENT_USER |DEFINER}]

[RESOLVER (( match_string, schema_name )...)]

source_option;

CREATE [OR REPLACE] [AND {RESOLVE|COMPILE}] [NOFORCE]

JAVA CLASS [SCHEMA schema]

[AUTHID {CURRENT_USER |DEFINER}]

[RESOLVER (( match_string, schema_name )...)]

source_option;

source_options:

USING BFILE (directory, 'class_filename')

USING {CLOB|BLOB|BFILE} subquery

USING 'key_for_blob'

AS source_text

CREATE LIBRARY Syntax Statement

CREATE [OR REPLACE] LIBRARY [schema.]library_name AS 'filespec';

CREATE OUTLINE Syntax Statement

CREATE [OR REPLACE] OUTLINE [schema.]operator

[FOR CATEGORY category]

ON statement;

CREATE PACKAGE Syntax Statement

CREATE [OR REPLACE] PACKAGE [schema.]package_name [invoker_rights] AS package

invoker_rights:

AUTHID CURRENT_USER

AUTHID DEFINER

AUTHID DEFINER will execute with the privileges of the package schema/owner.

CREATE PACKAGE BODY Syntax Statement

CREATE [OR REPLACE] PACKAGE BODY [schema.]package_name IS package_body

CREATE [OR REPLACE] PACKAGE BODY [schema.]package_name AS package_body

CREATE ROLLBACK SEGMENT Syntax Statement

CREATE [PUBLIC] ROLLBACK SEGMENT rbs_name option(s)

options:

TABLESPACE tablespace_name

STORAGE storage_clause

A public RBS is available for use by more than one instance

CREATE SCHEMA Syntax Statement

CREATE SCHEMA AUTHORISATION schema options

options

CREATE TABLE

CREATE VIEW

GRANT

The schema name must be an existing Oracle username.

CREATE SEQUENCE Syntax Statement

CREATE SEQUENCE [schema.]sequence_name option(s)

options:

INCREMENT BY int

START WITH int

MAXVALUE int | NOMAXVALUE

MINVALUE int | NOMINVALUE

CYCLE | NOCYCLE

CACHE int | NOCACHE

ORDER | NOORDER

CREATE TEMPORARY TABLESPACE Syntax Statement

CREATE TEMPORARY TABLESPACE tablespace_name

TEMPFILE Tempfile_Options

[EXTENT MANAGEMENT LOCAL]

[UNIFORM [SIZE int K | M] ];

Tempfile_Options:

'filespec' [AUTOEXTEND OFF]

'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]]

To create a locally managed tablespace specify 'EXTENT MANAGEMENT LOCAL'. All extents of temporary tablespaces are the same size - if UNIFORM is not defined it will default to 1 MB.

CREATE USER Syntax Statement

CREATE USER username

IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS external_name}

options

options:

DEFAULT TABLESPACE tablespace_name

TEMPORARY TABLESPACE tablespace_name

QUOTA int {K | M} ON tablespace_name

QUOTA UNLIMITED ON tablespace_name

PROFILE profile_name

PASSWORD EXPIRE

ACCOUNT {LOCK|UNLOCK}

COMMENT Syntax Statement

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

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

COMMENT ON TABLE [schema.]snapshot IS 'text'

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

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

COMMENT ON COLUMN [schema.]snapshot.column IS 'text'

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

COMMIT Syntax Statement

COMMIT [WORK] [COMMENT 'comment_text']

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

FORCE - will manually commit an in-doubt distributed transaction.

DESCRIBE Syntax Statement

DESC table

DESC view

DESC synonym

DESC function

DESC package.procedure

DESC package*

It is also possible to describe objects in another schema or via a database link
e.g.
DESCRIBE user.table@db_link

The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command.

DISASSOCIATE STATISTICS Syntax Statement

DISSASSOCIATE STATISTICS FROM object [schema.]object_name [FORCE]

Where object is any of

COLUMNS

FUNCTIONS

PACKAGES

TYPES

INDEXES

INDEXTYPES

EXEC Syntax Statement

EXEC statement

EXEC [:bind_variable :=] package.procedure;

EXEC [:bind_variable :=] package.function(parameters);

The length of the EXEC command cannot exceed the length defined by SET LINESIZE. If the EXEC command is too long to fit on one line, use the SQL*Plus continuation character (a hyphen) -

Example
EXEC :answer := EMP_PAY.BONUS('SMITH')

EXECUTE IMMEDIATE Syntax Statement

EXECUTE IMMEDIATE dynamic_sql_string

[INTO {define_variable,... | INTO record_name}]

[USING

[IN|OUT|IN OUT] bind_argument,...]

[RETURN[ING] INTO

bind_argument,...];

dynamic_sql_string : The SQL statement string or PL/SQL block

define_variable : One variable receives each column

value returned by the query.

record_name : A record based on a user-defined TYPE

or %ROWTYPE that receives an entire row

returned by a query

bind_argument : An expression whose value is passed to the

SQL statement or PL/SQL block INTO clause

Use for single-row queries; for each column value

returned by the query, you must supply an

individual variable or field in a record of

compatible type.

USING clause : Allows you to supply bind arguments for the

SQL string. This clause is used for both

dynamic SQL and PL/SQL,

which is why you can specify a parameter mode.

This usage is only relevant for PL/SQL,

however; the default is IN, which is the only

kind of bind argument you would have for

SQL statements.

You cannot use EXECUTE IMMEDIATE for multiple-row queries.

If "dynamic_sql_string" ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language--SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE).

The "dynamic_sql_string" may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names.

When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position).

You can pass numeric, date, and string expressions.

You cannot, pass a Boolean, or a NULL literal value, you can however pass a variable of the correct type that has a value of NULL.

EXPLAIN PLAN Syntax Statement

EXPLAIN PLAN [SET STATEMENT_ID = 'text']

FOR statement

EXPLAIN PLAN [SET STATEMENT_ID = 'text']

INTO [schema.]table@dblink

FOR statement

GRANT Syntax Statement

Roles:

GRANT role TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION]

System Privs:

GRANT system_priv(s) TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION]

GRANT ALL TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION]

Objects:

GRANT object_priv [(column, column,...)]

ON [schema.]object

TO [user], [role], [PUBLIC] [WITH GRANT OPTION]

GRANT ALL [(column, column,...)]

ON [schema.]object

TO [user], [role], [PUBLIC] [WITH GRANT OPTION]

GRANT object_priv [(column, column,...)]

ON DIRECTORY directory_name

TO [user], [role], [PUBLIC] [WITH GRANT OPTION]

GRANT object_priv [(column, column,...)]

ON JAVA [RE]SOURCE [schema.]object

TO [user], [role], [PUBLIC] [WITH GRANT OPTION]

key:

object_privs

ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, UPDATE

system_privs

ALTER ANY INDEX, BECOME USER , CREATE TABLE, DROP ANY VIEW RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE plus too many others to list here

roles

Standard Oracle roles - EXP_FULL_DATABASE, IMP_FULL_DATABASE, OSOPER, OSDBA plus any user defined roles you have available notes:

LOCK TABLE Syntax Statement

LOCK TABLE [schema.] table [options] IN lockmode MODE [NOWAIT]

LOCK TABLE [schema.] view [options] IN lockmode MODE [NOWAIT]

options:

PARTITION partition

SUBPARTITION subpartition

@dblink

lockmodes:

EXCLUSIVE

ROW EXCLUSIVE

SHARE ROW EXCLUSIVE

ROW SHARE* | SHARE UPDATE*

NOAUDIT Syntax Statement

NOAUDIT {ALL|ALL PRIVILEGES|sql_statement|system_priv} [options]

[WHENEVER [NOT] SUCCESSFUL]

options:

BY user

BY proxy [ON BEHALF OF ANY|user]

Schema Objects

To noaudit an object use the modified syntax

NOAUDIT {ALL|action} on [schema.]object

[WHENEVER [NOT] SUCCESSFUL]

NOAUDIT {ALL|action} on DIRECTORY directory_name

[WHENEVER [NOT] SUCCESSFUL]

NOAUDIT {ALL|action} on DEFAULT

[WHENEVER [NOT] SUCCESSFUL]

actions

ALTER, AUDIT, COMMENT, DELETE, EXECUTE, GRANT,

INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE

You can disable all auditing with the init.ora parameter AUDIT_TRAIL = NO