Syntax of Oracle SQL commands



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 ... 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.