iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Complete Syntax  |  Oracle Functions  |  PL/SQL Syntax  |  SQL Syntax  |  Tables

 

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.

 

 

 

Google
 
Web web site