Sunday, May 17, 2009

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}]

or

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

SHARE

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

RECOVER Syntax Statement

RECOVER [AUTOMATIC] [FROM 'location']

[STANDBY] DATABASE

[UNTIL CANCEL] [UNTIL TIME date] [UNTIL CHANGE int]

[USING BACKUP CONTROLFILE]

RECOVER [AUTOMATIC] [FROM 'location']

TABLESPACE tablespace [, tablespace2...]

RECOVER [AUTOMATIC] [FROM 'location']

STANDBY TABLESPACE tablespace [, tablespace2...]

UNTIL [CONSISTENT] [WITH] CONTROLFILE

RECOVER [AUTOMATIC] [FROM 'location']

DATAFILE 'filename' [, filename2...]

RECOVER [AUTOMATIC] [FROM 'location']

STANDBY DATAFILE 'filename' [, filename2,...]

UNTIL [CONSISTENT] [WITH] CONTROLFILE

RECOVER [AUTOMATIC] [FROM 'location']

LOGFILE 'filename'

RECOVER [AUTOMATIC] [FROM 'location']

CONTINUE [DEFAULT]

RECOVER [AUTOMATIC] [FROM 'location']

CANCEL

RECOVER MANAGED STANDBY DATABASE TIMEOUT integer

RECOVER MANAGED STANDBY DATABASE CANCEL [IMMEDIATE]

On a parallel server you can add to any of the above:
PARALLEL Parallel Clause

Key:

AUTOMATIC Automatically generate the name of the next archived

redo log file needed to continue the recovery operation.

FROM location

The location from which the archived redo log file group is read.

STANDBY

Recover the standby database using the control file and archived

redo log files copied from the primary database.

The standby database must be mounted but not open.

UNTIL CANCEL

Partial recovery. Recovery proceeds by prompting you with the

suggested filenames of archived redo log files, and recovery completes

when you specify CANCEL instead of a filename.

UNTIL CHANGE integer

An incomplete, change-based recovery. integer is the System Change Number

(SCN) following the last change you wish to recover.

UNTIL TIME date

Partial time-based recovery. Use the format:

'YYYY-MM-DD:HH24:MI:SS'

UNTIL [CONSISTENT] [WITH] CONTROLFILE

Recover an old standby datafile or tablespace using the current

standby database control file.

TABLESPACE tablespace

Recover a tablespace.

You may recover up to 16 tablespaces in one statement.

CONTINUE [DEFAULT]

Continues multi-instance recovery after it has been

interrupted to disable a thread.

Continues recovery using the redo log file that Oracle

would automatically generate if no other logfile were specified.

This option is equivalent to specifying AUTOMATIC, except that Oracle

does not prompt for a filename.

CANCEL

Terminates cancel-based recovery.

MANAGED STANDBY DATABASE

Sustained standby recovery mode.

TIMEOUT integer

The number of MINUTES = the wait period of a sustained recovery operation.

The RECOVER command is available in Server Manager/SQL*Plus, this is recommended for media recovery in preference to the syntax ALTER DATABASE RECOVER... (provided for backwards compatibility with older versions)

RENAME Syntax Statement

RENAME old TO new

REVOKE Syntax Statement

REVOKE role FROM {user, | role, |PUBLIC}

System Privs:

REVOKE system_priv(s) FROM {user, | role, |PUBLIC}

Objects:

REVOKE ALL [(columns)] ON [schema.]object

FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

REVOKE object_priv [(columns)] ON [schema.]object

FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

REVOKE object_priv [(columns)] ON DIRECTORY directory_name

FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

REVOKE object_priv [(columns)] ON JAVA [RE]SOURCE [schema.]object

FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE]

key:

object_privs

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

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 - CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE plus any user defined roles you have available FORCE, will revoke all privileges from a user-defined-type and mark it's dependent objects INVALID.

ROLLBACK Syntax Statement

ROLLBACK [WORK] [TO 'savepoint_text_identifier']

ROLLBACK [WORK] [FORCE 'force_text']

SET TRANSACTION Syntax Statement

SET TRANSACTION READ ONLY

SET TRANSACTION READ WRITE

SET TRANSACTION ISOLATION LEVEL

{SERIALIZABLE | READ COMMITTED}

SET TRANSACTION USE ROLLBACK SEGMENT rb_segment

SHUTDOWN Syntax Statement

SHUTDOWN ABORT

SHUTDOWN IMMEDIATE

SHUTDOWN TRANSACTIONAL [LOCAL]

SHUTDOWN NORMAL

key:

ABORT

The fastest possible shutdown of the database without waiting for calls to complete or users to disconnect. Uncommitted transactions are not rolled back. Client SQL statements currently being processed are terminated. All users currently connected to the database are implicitly disconnected and the next database startup will require instance recovery. You must use this option if a background process terminates abnormally.

IMMEDIATE

Does not wait for current calls to complete or users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.

NORMAL

NORMAL is the default option which waits for users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup.

TRANSACTIONAL [LOCAL]

A planned shutdown of an instance, allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off. No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down (SHUTDOWN IMMEDIATE). The next startup of the database will not require any instance recovery procedures. The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.

STARTUP Syntax Statement

STARTUP [FORCE] [RESTRICT] [PFILE=filename] NOMOUNT

STARTUP [FORCE] [RESTRICT] [PFILE=filename] MOUNT [dbname]

STARTUP [FORCE] [RESTRICT] [PFILE=filename] OPEN [open_options] [dbname]

open_options:

READ {ONLY | WRITE [RECOVER]} | RECOVER

key:

FORCE

Shut down the current Oracle instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified, an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used.

RESTRICT

Only allow Oracle users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature.

PFILE=filename

The init.ora parameter file to be used while starting up the instance. If PFILE is not specified, then the default STARTUP parameter file is used. The default file used is platform specific.

.

MOUNT dbname

Mount a database but do not open it. dbname is the name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME.

OPEN

Mount and open the specified database.

NOMOUNT

Don't mount the database upon instance startup. Cannot be used with MOUNT, or OPEN.

RECOVER

Specifies that media recovery should be performed, if necessary, before starting the instance. STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE command and starting an instance. Only 'complete recovery' is possible with the RECOVER option. Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON, regardless of whether or not AUTORECOVERY is enabled. If a redo log file is not found in the expected location, recovery will continue by prompting you with the suggested location and name of the subsequent log files that need to be applied.

TRUNCATE TABLE Syntax Statement

TRUNCATE TABLE [schema.]table

[{PRESERVE|PURGE} SNAPSHOT LOG]

[{DROP | REUSE} STORAGE]

TRUNCATE CLUSTER [schema.]cluster

[{PRESERVE|PURGE} SNAPSHOT LOG]

[{DROP | REUSE} STORAGE]

The terms "snapshot" and "materialized view" are synonymous.

Date Format (fmt)

When a date format is used by TO_CHAR or TO_DATE they return part of the date/time. When used by TRUNC they will return the first day of the period. When used by ROUND the values will round up at mid year/mid month (July 1 or 16th day)

CC Century

SCC Century BC prefixed with -

YYYY Year 2001

SYYY Year BC prefixed with -

IYYY ISO Year 2001

YY Year 01

RR Year 01 rollover for Y2K compatibility *

YEAR Year spelled out

SYEAR Year spelled out BC prefixed with -

BC BC/AD Indicator *

Q Quarter : Jan-Mar=1, Apr-Jun=2

MM Month of year 01, 02...12

RM Roman Month I, II...XII *

MONTH In full [January ]...[December ]

FMMONTH In full [January]...[December]

MON JAN, FEB

WW Week of year 1-52

W Week of month 1-5

IW ISO std week of year

DDD Day of year 1-366 *

DD Day of month 1-31

D Day of week 1-7

DAY In full [Monday ]...[Sunday ]

FMDAY In full [Monday]...[Sunday]

DY MON...SUN

DDTH Ordinal Day 7TH

DDSPTH Spell out ordinal SEVENTH

J Julian Day (days since 31/12/4713)

HH Hours of day (1-12)

HH12 Hours of day (1-12)

HH24 Hours of day (1-24)

SPHH Spell out SEVEN

AM am or pm *

PM am or pm *

A.M. a.m. or p.m. *

P.M. a.m. or p.m. *

MI Minutes 0-59

SS Seconds 0-59 *

SSSS Seconds past midnight (0-86399) *

The following punctuation -/,.;: can be included in any date format

any other chars can be included "in quotes"

* Formats marked with * can only be used with TO_CHAR or TO_DATE not TRUNC () or ROUND()

Date formats that are spelled out in characters will adopt the capitalisation of the format
e.g.
'MONTH' =JANUARY
'Month' = January

NLS Formats (Territory)

Specifying an NLS parameter for an SQL function means that any user session NLS parameters (or the lack of appropriate NLS parameters) will not affect evaluation of the function.

This feature may be important for SQL statements that contain numbers and dates as string literals. For example, the following query is evaluated correctly only if the language specified for dates is American:

SELECT ENAME FROM EMP

WHERE HIREDATE > '1-JAN-01'

This can be made independent of the current date language

by specifying NLS_DATE_LANGUAGE:

SELECT ENAME FROM EMP

WHERE HIREDATE > TO_DATE ('1-JAN-01','DD-MON-YY',

'NLS_DATE_LANGUAGE = AMERICAN')

Of course a simpler way of making this language-independent is

SELECT ENAME FROM EMP

WHERE HIREDATE > TO_DATE ('1-01-01','DD-MM-YY')

NLS settings include Character set, Language and territory

The most common Character Sets are:

WE8ISO8859P15 European English includes euro character

US7ASCII American English

Oracle Languages

e.g. NLS_LANGUAGE = ENGLISH

us AMERICAN

ar ARABIC

The NLS_LANGUAGE above implicitly defines several other parameters:

NLS_DATE_LANGUAGE, NLS_SORT

Oracle Territories

e.g. NLS_TERRITORY = "UNITED KINGDOM"

AMERICA

AUSTRALIA

The NLS_TERRITORY implicitly defines several other parameters:

NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_MONETARY_CHARACTERS, NLS_CREDIT, NLS_DEBIT

If necessary these can be explicitly defined

e.g. NLS_NUMERIC_CHARACTERS = ",."

NLS_COMP

This provides a simple alternative to specifying NLS_SORT in

an SQL WHERE clause

NLS formats will affect SQL statements in views, CHECK constraints, and triggers.

No comments: