Oracle Syntax Master

Oracle Syntax Master

SELECT Syntax statement

SELECT [hint][DISTINCT] select_list FROM table_list [WHERE conditions] [GROUP BY group_by_list] [HAVING search_conditions] [ORDER BY order_list [ASC | DESC] ] [FOR UPDATE for_update_options] select_list column1, column2, column3 table.column1, table.column2 table.column1 C_1_Alias, table.column2 C_2_Alias schema.table.column1 Col_1_Alias, schema.table.column2 c_2_Alias schema.table.* * expr1, expr2 (subquery [WITH READ ONLY | WITH CHECK OPTION [CONSTRAINT constraint]]) In the select_lists above, 'table' may be replaced with view or snapshot. Using the * expression will return all columns. If a Column_Alias is specified this will appear at the top of any column headings in the query output. FROM table_list Contains a list of the tables from which the result set data is retrieved. [schema.]{table | view | snapshot}[@dblink] [t_alias] When selecting from a table you can also specify Partition and/or Sample clauses e.g. [schema.]table [PARTITION (partition)] [SAMPLE (sample_percent)] WHERE search_conditions A filter that defines the conditions each row in the source table(s) must meet to qualify for the SELECT. Only rows that meet the conditions will be included in the result set. The WHERE clause can also contain inner and outer join specifications (SQL1989 standard). e.g. WHERE tableA.column = tableB.column WHERE tableA.column = tableB.column(+) WHERE tableA.column(+) = tableB.column GROUP BY group_by_list The GROUP BY clause partitions the result set into groups. The group_by_list may be one or more columns or expressions and may optionally include the CUBE / ROLLUP keywords for creating crosstab results. Heirarchical Queries Any query that does *not* include a GROUP BY clause may include a CONNECT BY heirarchy clause: [START WITH condition] CONNECT BY condition HAVING search_conditions An additional filter - the HAVING clause acts as an additional filter to the grouped result rows - as opposed to the WHERE clause that applies to individual rows. The HAVING clause is most commonly used in conjunction with a GROUP BY clause. ORDER BY order_list [ ASC | DESC ] [ NULLS { FIRST | LAST } ] The ORDER BY clause defines the order in which the rows in the result set are sorted. order_list specifies the result set columns that make up the sort list. The ASC and DESC keywords are used to specify if the rows are sorted ascending (1...9 a...z) or descending (9...1 z...a). You can sort by any column even if that column is not actually in the main SELECT clause. If you do not include an ORDER BY clause then the order of the result set rows will be unpredictable (random or quasi random). FOR UPDATE options - this locks the selected rows (Oracle will normally wait for a lock unless you spacify NOWAIT) FOR UPDATE [OF [ [schema.]{table|view}.] column] [NOWAIT] Writing a SELECT statement The clauses (SELECT ... FROM ... WHERE ...GROUP BY... HAVING ... ORDER BY ... ) must be in this order. SELECT command { UNION | UNION ALL | INTERSECT | MINUS } SELECT command ]

INSERT Syntax Statement

INSERT [hint] INTO [schema.] table [@dblink] [t_alias] (column, column,...) VALUES (expression) INSERT [hint] INTO [schema.] table [[SUB]PARTITION (ptn_name)] [t_alias] (column, column,...) VALUES (expression) INSERT [hint] INTO subquery WITH [READ ONLY | CHECK OPTION [CONSTRAINT constraint] ] [t_alias] (column, column,...) VALUES (expression) VALUES(expression) can be expanded to VALUES ([expr, expr...] [subquery]) [RETURNING expr, expr... INTO host_variable|plsql_variable]

UPDATE Syntax Statement

UPDATE [hint] [schema.]table [@dblink] [alias] [ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ] SET col_expr(s) [WHERE condition] [ RETURNING (expr,...) INTO (data_item,...) ] UPDATE [hint] [schema.]table [[SUB]PARTITION partition] [alias] [ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ] SET col_expr(s) [WHERE condition] [ RETURNING (expr,...) INTO (data_item,...) ] UPDATE [hint] [schema.]view [@dblink] [alias] [ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ] SET col_expr(s) [WHERE condition] UPDATE [hint] [schema.]snapshot [@dblink] [alias] [ WITH {READ ONLY | CHECK OPTION [CONSTRAINT constraint]} ] SET col_expr(s) [WHERE condition] col_expr: column = expression column = (subquery) (column, column,...) = (subquery) VALUE (table_alias) = expression VALUE (table_alias) = (subquery) To update multiple columns, separate col_expr with commas. The terms "snapshot" and "materialized view" are synonymous.

DELETE Syntax Statement

DELETE [FROM] [schema.] table [@dblink] [alias] WHERE (condition) [RETURNING expr INTO DATA_ITEM] DELETE [FROM] [schema.] table [SUB]PARTITION partition [alias] WHERE (condition) [RETURNING expr INTO DATA_ITEM] DELETE [FROM] [schema.] view [@dblink] [alias] WHERE (condition) [RETURNING expr INTO DATA_ITEM] DELETE [FROM] subquery [WITH READ ONLY] [alias] WHERE (condition) [RETURNING expr INTO DATA_ITEM] DELETE [FROM] subquery [WITH CHECK OPTION] [CONSTRAINT constraint] [alias] WHERE (condition) [RETURNING expr INTO DATA_ITEM] Example: The following example returns column es_salary from the deleted rows and stores the result in bind array :1 DELETE FROM employee WHERE emp_no = 1075 AND commission = 50 RETURNING salary INTO :1;

ALTER DATABASE Syntax Statement

ALTER DATABASE Open an existing database, and /or modify associated files. ALTER DATABASE database_name options options can be any combination of the following: open / mount options: MOUNT MOUNT STANDBY DATABASE MOUNT CLONE DATABASE MOUNT PARALLEL MOUNT STANDBY DATABASE CONVERT OPEN [READ ONLY] OPEN [READ WRITE] RESETLOGS|NORESETLOGS ACTIVATE STANDBY DATABASE [NATIONAL] CHARACTER SET char_set archivelog options: ARCHIVELOG NOARCHIVELOG backup and recovery options: BACKUP CONTROLFILE TO 'filename' [REUSE] BACKUP CONTROLFILE TO TRACE BACKUP CONTROLFILE TO TRACE RESETLOGS CREATE STANDBY CONTROLFILE AS 'filename' [REUSE] RENAME FILE 'data_file_name' TO 'data_file_name' RENAME FILE 'redolog_file_name' TO 'redolog_file_name' RECOVER recover_clause DATAFILE 'filename' END BACKUP Datafile options: CREATE DATAFILE 'filename' AS filespec DATAFILE 'filename' ONLINE DATAFILE 'filename' OFFLINE [DROP] DATAFILE 'filename' RESIZE int K | M DATAFILE 'filename' AUTOEXTEND OFF DATAFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M] DATAFILE 'filename' END BACKUP TEMPFILE 'filename' ONLINE TEMPFILE 'filename' OFFLINE TEMPFILE 'filename' DROP TEMPFILE 'filename' RESIZE int K | M TEMPFILE 'filename' AUTOEXTEND OFF TEMPFILE 'filename' AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M] redo log options: ADD LOGFILE [THREAD int] [GROUP int] filespec ADD LOGFILE MEMBER 'filename' [REUSE] TO GROUP int ADD LOGFILE MEMBER 'filename' [REUSE] TO 'filename' DROP LOGFILE GROUP int DROP LOGFILE ('filename') DROP LOGFILE MEMBER 'filename' CLEAR [UNARCHIVED] LOGFILE GROUP int [UNRECOVERABLE DATAFILE] CLEAR [UNARCHIVED] LOGFILE ('filename') [UNRECOVERABLE DATAFILE] Parallel server options: CREATE STANDBY CONTROLFILE AS 'filename' [REUSE] SET DBLOW = 'text' SET DBHIGH = 'text' SET DBMAC = ON | OFF ENABLE [PUBLIC] THREAD int DISABLE THREAD int Backwards compatibility options: RENAME GLOBAL_NAME TO database [domain] RESET COMPATIBILITY database_name is defined when the database is created - it is normally set to the same as the database SID. Some of the commands above can only be used when the database is in a particular state: MOUNT, CONVERT - Require that the db is Not Mounted. ARCHIVELOG, NOARCHIVLOG, RECOVER - Require that the db is Mounted but not open (must be mount exclusive - not mount parallel). ENABLE, DISABLE, RENAME GLOBAL_NAME, RESET, SET - Require that the db is Open. All other options will work with the db mounted, open or closed as long as none of the files involved are 'in use'

ALTER RESOURCE COST Syntax Statement

ALTER RESOURCE COST option(s) options are any combination of CPU_PER_SESSION int CONNECT_TIME int LOGICAL_READS_PER_SESSION int PRIVATE_SGA int int is the integer weight applied to each option The units being costed are CPU = 1/100 sec connect_time = 1/100 sec SGA = bytes

ALTER SESSION Syntax Statement

ALTER SESSION ADVISE {COMMIT | ROLLBACK | NOTHING} ALTER SESSION CLOSE DATABASE LINK link_name ALTER SESSION {ENABLE | DISABLE} COMMIT IN PROCEDURE ALTER SESSION {ENABLE | DISABLE | FORCE} PARALLEL {DML|DDL|QUERY} [PARALLEL int] ALTER SESSION SET option(s) options: CONSTRAINT[S] {IMMEDIATE|DEFERRED|DEFAULT} CREATE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' } CURRENT_SCHEMA = schema CURSOR_SHARING = {FORCE | EXACT} DB_BLOCK_CHECKING = {TRUE | FALSE} DB_FILE_MULTIBLOCK_READ_COUNT = int FAST_START_IO_TARGET = int FLAGGER = {ENTRY | INTERMEDIATE | FULL | OFF} GLOBAL_NAMES = {TRUE | FALSE} HASH_AREA_SIZE = int HASH_JOIN_ENABLED = {TRUE | FALSE} HASH_MULTIBLOCK_IO_COUNT = int INSTANCE = int ISOLATION_LEVEL = {SERIALIZABLE | READ_COMMITTED} LABEL = {'text' | DBLOW | DBHIGH | OSLABEL} LOG_ARCHIVE_DEST_n (read the Oracle8i Reference for more on this) LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER} LOG_ARCHIVE_MIN_SUCCEED_DEST = int MAX_DUMP_FILE_SIZE = { size | UNLIMITED } NLS_CALENDAR = 'text' NLS_COMP = 'text' NLS_CURRENCY = 'text' NLS_DATE_FORMAT = 'fmt' NLS_DATE_LANGUAGE = language NLS_DUAL_CURRENCY = 'text' NLS_ISO_CURRENCY = territory NLS_LANGUAGE = language NLS_NUMERIC_CHARACTERS = 'text' NLS_SORT = {sort | BINARY} NLS_TERRITORY = territory OBJECT_CACHE_MAX_SIZE_PERCENT = int OBJECT_CACHE_OPTIMAL_SIZE = int OPTIMIZER_INDEX_CACHING = int OPTIMIZER_INDEX_COST_ADJ = int OPTIMIZER_MAX_PERMUTATIONS = int OPTIMIZER_MODE = {ALL_ROWS | FIRST_ROWS | RULE | CHOOSE} OPTIMIZER_PERCENT_PARALLEL = int PARALLEL_BROADCAST_ENABLED = {TRUE | FALSE} PARALLEL_INSTANCE_GROUP = ' text ' PARALLEL_MIN_PERCENT = int PARTITION_VIEW_ENABLED = {TRUE | FALSE} PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} QUERY_REWRITE_ENABLED = {TRUE | FALSE} QUERY_REWRITE_INTEGRITY = {enforced | trusted | stale_tolerated} REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE} SESSION_CACHED_CURSORS = int SKIP_UNUSABLE_INDEXES = {TRUE | FALSE} SORT_AREA_RETAINED_SIZE = int SORT_AREA_SIZE = integer SORT_MULTIBLOCK_READ_COUNT = int SQL_TRACE = {TRUE | FALSE} STAR_TRANSFORMATION_ENABLED = {TRUE | FALSE} TIMED_STATISTICS = {TRUE | FALSE} USE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' } Version 7 Options - now obsolete in v8 CLOSE_CACHED_OPEN_CURSORS = {TRUE | FALSE} NLS_ISO_CURRENCY = territory MLS_LABEL_FORMAT = 'fmt' OPTIMISER_GOAL - is now OPTIMISER_MODE SCHEMA=schema_name

ALTER SYSTEM Syntax Statement

ALTER SYSTEM ARCHIVE LOG archive_log_clause ALTER SYSTEM CHECKPOINT [GLOBAL | LOCAL] ALTER SYSTEM CHECK DATAFILES [GLOBAL | LOCAL] ALTER SYSTEM FLUSH SHARED POOL ALTER SYSTEM SWITCH LOGFILE ALTER SYSTEM {ENABLE | DISABLE} DISTRIBUTED RECOVERY ALTER SYSTEM {ENABLE | DISABLE} RESTRICTED SESSION ALTER SYSTEM {SUSPEND | RESUME} ALTER SYSTEM SHUTDOWN [IMMEDIATE] dispatcher_name ALTER SYSTEM KILL SESSION 'int1, int2' [POST TRANSACTION] [IMMEDIATE] ALTER SYSTEM DISCONNECT SESSION 'int1, int2' [IMMEDIATE] ALTER SYSTEM SET option(s) options: AQ_TM_PROCESSES = int BACKGROUND_DUMP_DEST = 'text' BACKUP_TAPE_IO_SLAVES = {TRUE | FALSE} DEFERRED CONTROL_FILE_RECORD_KEEP_TIME = int CORE_DUMP_DEST = 'text' CREATE_STORED_OUTLINES = {TRUE | FALSE | 'category_name' } [NOOVERRIDE] CURSOR_SHARING = {force|exact} DB_BLOCK_CHECKING = {TRUE | FALSE} DEFERRED DB_BLOCK_CHECKSUM = {TRUE | FALSE} DB_BLOCK_MAX_DIRTY_TARGET = int DB_FILE_DIRECT_IO_COUNT = int DEFERRED DB_FILE_MULTIBLOCK_READ_COUNT = int FAST_START_IO_TARGET = int FAST_START_PARALLEL_ROLLBACK = {FALSE | LOW | HIGH} FIXED_DATE = { 'DD_MM_YY' | 'YYYY_MI_DD_HH24_MI-SS' } GC_DEFER_TIME = int GLOBAL_NAMES = {TRUE | FALSE} HASH_MULTIBLOCK_IO_COUNT = int HS_AUTOREGISTER = {TRUE | FALSE} JOB_QUEUE_PROCESSES = int LICENSE_MAX_SESSIONS = int LICENSE_MAX_USERS = int LICENSE_SESSIONS_WARNING = int LOG_ARCHIVE_DEST = string LOG_ARCHIVE_DEST_n = {null_string | {LOCATION=local_pathname | SERVICE=tnsnames_service} [MANDATORY | OPTIONAL] [REOPEN[=integer]]} LOG_ARCHIVE_DEST_STATE_n = {ENABLE | DEFER} LOG_ARCHIVE_DUPLEX_DEST = string LOG_ARCHIVE_MAX_PROCESSES = int LOG_ARCHIVE_MIN_SUCCEED_DEST = int LOG_ARCHIVE_TRACE = int LOG_CHECKPOINT_INTERVAL = int LOG_CHECKPOINT_TIMEOUT = int MAX_DUMP_FILE_SIZE = { size | 'unlimited'} [DEFERRED] MTS_DISPATCHERS = dispatcher_clause MTS_SERVERS = int OBJECT_CACHE_MAX_SIZE_PERCENT = int DEFERRED OBJECT_CACHE_OPTIMAL_SIZE = int DEFERRED OPTIMIZER_MAX_PERMUTATIONS = int NOOVERRIDE PARALLEL_ADAPTIVE_MULTI_USER = {TRUE | FALSE} PARALLEL_INSTANCE_GROUP = 'text' PARALLEL_THREADS_PER_CPU = int PLSQL_V2_COMPATIBILITY = {TRUE | FALSE} [DEFERRED] QUERY_REWRITE_ENABLED = {TRUE | FALSE} [DEFERRED | NOOVERRIDE] QUERY_REWRITE_INTEGRITY = {ENFORCED | TRUSTED | STALE_TOLERATED} REMOTE_DEPENDENCIES_MODE = {TIMESTAMP | SIGNATURE} RESOURCE_LIMIT = {TRUE | FALSE} RESOURCE_MANAGER_PLAN = plan_name SORT_AREA_RETAINED_SIZE = int DEFERRED SORT_AREA_SIZE = int DEFERRED SORT_MULTIBLOCK_READ_COUNT = int DEFERRED STANDBY_ARCHIVE_DEST = string TIMED_STATISTICS = {TRUE | FALSE} TIMED_OS_STATISTICS = int TRANSACTION_AUDITING = {TRUE | FALSE} DEFERRED USE_STORED_OUTLINES = {TRUE | FALSE| 'category_name' } [NOOVERRIDE] USER _DUMP_DEST = 'directory_name' Oracle 7 options now obsolete in v8 CACHE_INSTANCES = int SCAN_INSTANCES = int

ALTER TABLE Syntax Statement

Change the properties of an existing table. ALTER TABLE [schema.]table RENAME TO new_table_name ALTER TABLE [schema.]table [ [NO]MINIMISE RECORDS PER BLOCK ] [PARALLEL parallel_clause] [ENABLE enable_clause | DISABLE disable_clause] [{ENABLE|DISABLE} TABLE LOCK] [{ENABLE|DISABLE} ALL TRIGGERS] ALTER TABLE [schema.]table iot_overflow_clause [PARALLEL parallel_clause] [ENABLE enable_clause | DISABLE disable_clause] [{ENABLE|DISABLE} TABLE LOCK] [{ENABLE|DISABLE} ALL TRIGGERS] ALTER TABLE [schema.]table partitioning_clause [PARALLEL parallel_clause] [ENABLE enable_clause | DISABLE disable_clause] [{ENABLE|DISABLE} TABLE LOCK] [{ENABLE|DISABLE} ALL TRIGGERS] ALTER TABLE [schema.]table tbl_defs,... [PARALLEL parallel_clause] [ENABLE enable_clause | DISABLE disable_clause] [{ENABLE|DISABLE} TABLE LOCK] [{ENABLE|DISABLE} ALL TRIGGERS] tbl_defs: ADD [column datatype] [DEFAULT expr] [column_constraint(s)] [table_constraint] [table_ref_constraint] MODIFY [column datatype] [DEFAULT expr] [column_constraint(s)] MODIFY [table_constraint] drop_column_clause DROP drop_constraint_clause [PCTFREE int][PCTUSED int][INITTRANS int] [MAXTRANS int][STORAGE storage_clause] extent_options MOVE [ONLINE] storage_options INDEX index_organized_tbl_clause [LOB_storage_clause][varray_clause] LOGGING|NOLOGGING MODIFY NESTED TABLE collection_item RETURN AS {LOCATOR|VALUE } MODIFY LOB [LOB_storage_clause] MODIFY VARRAY [varray_clause] CACHE | NOCACHE MONITORING | NOMONITORING storage_options: PCTFREE int PCTUSED int INITTRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace [LOGGING|NOLOGGING] extent_options: ALLOCATE EXTENT [( [size int K | M ] [DATAFILE 'filename' ] [INSTANCE int] )] DEALLOCATE UNUSED [KEEP int K | M ] index_organized_tbl_clause: storage_option(s) [PCTTHRESHOLD int] [COMPRESS int|NOCOMPRESS] [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ] iot_overflow_clause: {PCTTHRESHOLD int | INCLUDING column} | OVERFLOW overflow_storage_clause ADD OVERFLOW [storage_options] [(PARTITION storage_options)] overflow_storage_clause: PCTFREE int PCTUSED int INITTRANS int MAXTRANS int extent_options STORAGE storage_clause [LOGGING|NOLOGGING] nested_storage_clause: NESTED TABLE nested_item STORE AS storage_table [RETURN AS {LOCATOR|VALUE }] drop_column_clause: SET UNUSED (column,...) [CASCADE CONSTRAINTS][INVALIDATE] DROP COLUMN (column,...) [CASCADE CONSTRAINTS][INVALIDATE] CHECKPOINT int DROP {UNUSED COLUMNS|COLUMNS CONTINUE} [CHECKPOINT int]

ANALYZE syntax Statement

Update CBO (Cost Based Optimiser) statistics. ANALYZE TABLE tablename COMPUTE | ESTIMATE | DELETE STATISTICS ptnOption options ANALYZE INDEX indexname COMPUTE | ESTIMATE | DELETE STATISTICS ptnOption options ANALYZE CLUSTER clustername COMPUTE | ESTIMATE | DELETE STATISTICS options ptnOption PARTITION (partion) SUBPARTITION (subpartition) options VALIDATE STRUCTURE [CASCADE] [INTO tablename] LIST CHAINED ROWS [INTO tablename] COMPUTE|ESTIMATE STATISTICS FOR TABLE COMPUTE|ESTIMATE STATISTICS FOR ALL COLUMNS COMPUTE|ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS COMPUTE|ESTIMATE STATISTICS FOR COLUMNS [SIZE int] column [SIZE int] When Estimating statistics you can optionally specify ... ESTIMATE STATISTICS SAMPLE n ROWS ... ESTIMATE STATISTICS SAMPLE n PERCENT Validate structure will perform an integrity check - and will therefore lock the table/index/cluster while it is running. If the INTO clause is used to store a list of chained rows in a table - the default tablename is CHAINED_ROWS

ASSOCIATE STATISTICS

ASSOCIATE STATISTICS WITH COLUMNS [schema.]table_column,... USING [schema.]statistics_type; ASSOCIATE STATISTICS WITH object [schema.]object_name,... cost_usage_clause; Where object is any of FUNCTIONS PACKAGES TYPES INDEXES INDEXTYPES cost_usage_clauses: USING [schema.]statistics_type DEFAULT COST (cpu_cost, io_cost, network_cost) DEFAULT SELECTIVITY default_selectivity

AUDIT syntax Statement

Audit an SQL statement or accesss to a specific database object. AUDIT ALL | ALL PRIVILEGES | sql_statement | system_priv [options] options BY user BY proxy [ON BEHALF OF ANY|user] BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL] Auditing Objects To audit an object use the modified syntax AUDIT action on schema.object BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL] AUDIT action on DEFAULT BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL] AUDIT action on DIRECTORY dir_name BY ACCESS|SESSION [WHENEVER [NOT] SUCCESSFUL] Where actions is any of ALTER, AUDIT, COMMENT, DELETE, EXECUTE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE You must first enable auditing with the init.ora parameter AUDIT_TRAIL = YES

CREATE INDEX Syntax Statement

CREATE [UNIQUE|BITMAP] INDEX [schema.]index ON [schema.]TABLE [tbl_alias] (col [ASC | DESC]) index_clause index_attribs CREATE [UNIQUE|BITMAP] INDEX [schema.]index ON [schema.]TABLE [tbl_alias] (col_expression [ASC | DESC]) index_clause index_attribs CREATE [UNIQUE|BITMAP] INDEX [schema.]index ON CLUSTER [schema.]cluster index_attribs index_clauses: INDEXTYPE IS indextype[PARAMETERS ('string')] LOCAL STORE IN {tablespace_name|DEFAULT} LOCAL (PARTITION [partition [LOGGING|NOLOGGING] [TABLESPACE {tablespace_name|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] [STORE IN {tablespace_name|DEFAULT] [SUBPARTITION [subpartition [TABLESPACE tablespace_name]]]]) GLOBAL PARTITION BY RANGE (col_list) ( PARTITION partition VALUES LESS THAN (value_list) [LOGGING|NOLOGGING] [TABLESPACE {tablespace_name|DEFAULT}] [PCTFREE int] [PCTUSED int] [INITRANS int] [MAXTRANS int] [STORAGE storage_clause] ) index_attribs: any combination of the following NOSORT|REVERSE COMPRESS int NOCOMPRESS COMPUTE STATISTICS [NO]LOGGING ONLINE TABLESPACE {tablespace_name|DEFAULT} PCTFREE int PCTUSED int INITRANS int MAXTRANS int STORAGE storage_clause PARALLEL parallel_clause

CREATE DATABASE LINK

CREATE [SHARED][PUBLIC] DATABASE LINK link_name [CONNECT TO CURRENT_USER ] [USING 'connect_string'] CREATE [SHARED][PUBLIC] DATABASE LINK link_name [CONNECT TO user IDENTIFIED BY password] [AUTHENTICATED BY user IDENTIFIED BY password] [USING 'connect_string']

CREATE MATERIALIZED VIEW Syntax Statement

CREATE MATERIALIZED VIEW [schema.]materialized_view options [USING INDEX index_options] [REFRESH [refresh_options]] [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE] CREATE MATERIALIZED VIEW [schema.]materialized_view ON PREBUILT TABLE [{WITH | WITHOUT} REDUCED PRECISION] [USING INDEX index_options] [REFRESH [refresh_options]] [FOR UPDATE] [{ENABLE|DISABLE} QUERY REWRITE] options: CLUSTER cluster (column,...) [Partitioning clause] [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}] or storage_options [LOB/Modify LOB Storage clause] [CACHE | NOCACHE] [Partitioning clause] [PARALLEL int | NOPARALLEL] [BUILD {IMMEDIATE|DEFERRED}] Where storage_options can be any of: PCTFREE int PCTUSED int INITRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace LOGGING | NOLOGGING index_options INITRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace refresh_options: FAST | COMPLETE | FORCE ON [DEMAND | COMMIT] {NEXT | START WITH} date WITH {PRIMARY KEY | rowid} USING DEFAULT {MASTER|LOCAL} ROLLBACK SEGMENT USING {MASTER|LOCAL} ROLLBACK SEGMENT rb_segment

CREATE MATERIALIZED VIEW LOG

CREATE MATERIALIZED VIEW LOG ON [schema.]table options [PARALLEL int | NOPARALLEL] Partitioning_options WITH filter_option(s) [{INCLUDING|EXCLUDING} NEW VALUES]; options: PCTFREE int PCTUSED int INITRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace LOGGING | NOLOGGING [CACHE | NOCACHE] filter_options: [{PRIMARY KEY | rowid}] (filter_column,...) multiple filter_options can be separated with commas

CREATE SYNONYM Syntax Statement

CREATE [PUBLIC] SYNONYM [schema.]synonym FOR [schema.]object [@dblink] You should be aware of the performance hit when accessing data through a synony

CREATE TABLE Syntax Statement

CREATE [GLOBAL TEMPORARY] TABLE [schema.]table (tbl_defs,...) [ON COMMIT {DELETE|PRESERVE} ROWS] [storage_options | CLUSTER cluster_name (col1, col2,... ) | ORGANIZATION {HEAP [storage_options] | INDEX idx_organized_tbl_clause}] [LOB_storage_clause][varray_clause][nested_storage_clause] partitioning_options [[NO]CACHE] [[NO]MONITORING] [PARALLEL parallel_clause] [ENABLE enable_clause | DISABLE disable_clause] [AS subquery] tbl_defs: column datatype [DEFAULT expr] [column_constraint(s)] table_constraint table_ref_constraint storage_options: PCTFREE int PCTUSED int INITTRANS int MAXTRANS int STORAGE storage_clause TABLESPACE tablespace [LOGGING|NOLOGGING] idx_organized_tbl_clause: storage_option(s) [PCTTHRESHOLD int] [COMPRESS int|NOCOMPRESS] [ [INCLUDING column_name] OVERFLOW [storage_option(s)] ] nested_storage_clause: NESTED TABLE nested_item STORE AS storage_table [RETURN AS {LOCATOR|VALUE } ]

CREATE TABLESPACE Syntax Statement

CREATE TABLESPACE tablespace_name DATAFILE Datafile_Options Storage_Options ; Datafile_Options: 'filespec' [AUTOEXTEND OFF] 'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]] The Autoextend Maxsize clause will default to UNLIMITED if no value is specified. Storage_Options: DEFAULT STORAGE storage_clause MINIMUM EXTENT int {K|M} LOGGING | NOLOGGING ONLINE | OFFLINE PERMANENT | TEMPORARY EXTENT MANAGEMENT {DICTIONARY | LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }

CREATE TRIGGER Syntax Statement

CREATE [OR REPLACE] TRIGGER [schema.]trigger {BEFORE event | AFTER event | INSTEAD OF event} referencing_clause WHEN (condition) pl_sql_block event can be one or more of the following (separate multiple events with OR) DELETE event_ref INSERT event_ref UPDATE event_ref UPDATE OF column, column... event_ref ddl_statement ON [schema.] {table|view} ddl_statement ON DATABASE SERVERERROR LOGON LOGOFF STARTUP SHUTDOWN event_ref: ON [schema.]table ON [schema.]view ON [NESTED TABLE nested_table_column OF] [schema.]view referencing_clause: FOR EACH ROW REFERENCING OLD [AS] old [FOR EACH ROW] REFERENCING NEW [AS] new [FOR EACH ROW] REFERENCING PARENT [AS] parent [FOR EACH ROW]

CREATE VIEW Syntax Statement

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [schema.]view [(alias,...)] AS subquery options CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW [OF [schema.] type_name [WITH OBJECT IDENTIFIER {DEFAULT|(attribute,...)}] AS subquery options options: WITH READ ONLY WITH CHECK OPTION [CONSTRAINT constraint]

CREATE FUNCTION Syntax Statement

CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause] RETURN datatype [invoke_clause] AS plsql_function_body CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause] RETURN datatype [invoke_clause] AS LANGUAGE JAVA NAME 'string' CREATE [OR REPLACE] FUNCTION [schema.]function [arguments_clause] RETURN datatype [invoke_clause] AS LANGUAGE C [NAME name] LIBRARY lib_name [WITH CONTEXT][PARAMETERS params] arguments_clause: (argument [IN|OUT|IN OUT] [NOCOPY datatype]) invoke_clause: any combination of... AUTHID CURRENT_USER AUTHID DEFINER DETERMINISTIC PARALLEL_ENABLE

CREATE PROCEDURE Syntax Statement

CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options) invoker_rights AS plsql_sub_program_body CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options) invoker_rights AS LANGUAGE JAVA NAME ('string') CREATE [OR REPLACE] PROCEDURE [schema.]procedure_name (options) invoker_rights AS LANGUAGE C NAME name LIBRARY lib_name [WITH CONTEXT][PARAMETERS (parameters)] options: argument IN [NOCOPY] datatype argument OUT [NOCOPY] datatype argument IN OUT [NOCOPY] datatype (The procedure can have several arguments separated with commas) invoker_rights: AUTHID CURRENT_USER AUTHID DEFINER AUTHID DEFINER will execute with the privileges of the procedure schema/owner. NOCOPY will instruct Oracle to pass the argument as fast as possible. This can significantly enhance performance when passing a large value.

CREATE ROLE Syntax Statement

CREATE ROLE role_name [NOT IDENTIFIED] CREATE ROLE role_name [IDENTIFIED BY password] CREATE ROLE role_name [IDENTIFIED EXTERNALLY] CREATE ROLE role_name [IDENTIFIED GLOBALLY] Example --Create the role CREATE ROLE MY_ORACLE_ROLE --Assign all object rights from the current user schema (user_objects) spool GrantRights.sql SELECT DECODE (object_type, 'TABLE','GRANT SELECT, INSERT, UPDATE, DELETE , REFERENCES ON'||&OWNER||'.', 'VIEW','GRANT SELECT ON '||&OWNER||'.', 'SEQUENCE','GRANT SELECT ON '||&OWNER||'.', 'PROCEDURE','GRANT EXECUTE ON '||&OWNER||'.', 'PACKAGE','GRANT EXECUTE ON '||&OWNER||'.', 'FUNCTION','GRANT EXECUTE ON'||&OWNER||'.' )||object_name||' TO MY_ORACLE_ROLE ;' from user_objects WHERE OBJECT_TYPE IN ( 'TABLE', 'VIEW', 'SEQUENCE', 'PROCEDURE', 'PACKAGE','FUNCTION') ORDER BY OBJECT_TYPE / spool off @GrantRights.sql

CREATE CONTROLFILE Syntax Statement

CREATE CONTROLFILE [REUSE] [SET] DATABASE database LOGFILE [GROUP int] filespec [RESETLOGS | NORESETLOGS] DATAFILE filespec options options MAXDATAFILES int MAXLOGFILES int MAXLOGMEMBERS int MAXLOGHISTORY int MAXINSTANCES int ARCHIVELOG | NOARCHIVELOG Several LOGFILE or DATAFILEs can be specified at once if separated with commas.

CREATE DATABASE Syntax Statement

CREATE DATABASE database_name options options can be any combination of the following: DATAFILE filespec AUTOEXTEND OFF DATAFILE filespec AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M] LOGFILE [GROUP int] filespec MAXDATAFILES int MAXLOGFILES int MAXLOGMEMBERS int MAXLOGHISTORY int MAXINSTANCES int CONTROLFILE REUSE CHARACTER SET charset Example -- Create a database with the SID of TEST and char set WE8ISO8859P1 CREATE DATABASE TEST LOGFILE 'E:\Oracle\TEST\LOGS\LOG1TEST.ORA' SIZE 2M, 'E:\Oracle\TEST\LOGS\LOG2TEST.ORA' SIZE 200M, 'E:\Oracle\TEST\LOGS\LOG3TEST.ORA' SIZE 200M, 'E:\Oracle\TEST\LOGS\LOG4TEST.ORA' SIZE 200M, 'E:\Oracle\TEST\LOGS\LOG5TEST.ORA' SIZE 200M MAXDATAFILES 100 DATAFILE 'E:\Oracle\TEST\DATA\SYS1TEST.ORA' SIZE 500 M NOARCHIVELOG CHARACTER SET WE8ISO8859P1;

CREATE DIMENSION Syntax Statement

CREATE DIMENSION [schema.]dimension level_clause(s) [heirarchy_clause(s) attribute_clause(s)]; The command should include at least one heirarchy clause or attribute clause. level_clause: LEVEL level IS (table.column,...) heirarchy_clause: HEIRARCHY heirarchy (child_level CHILD OF parent_level,... [join_clause]) attribute_clause: ATTRIBUTE level DETERMINES (dependent_column,...) join_clause: JOIN KEY (child_key_column,...) REFERENCES parent_level

CREATE DIRECTORY Syntax Statement

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

CREATE JAVA Syntax Statement

CREATE [OR REPLACE] [AND {RESOLVE|COMPILE}] [NOFORCE] JAVA [RE]SOURCE NAMED [schema.]primary_name [AUTHID {CURRENT_USER |DEFINER}] [RESOLVER (( match_string, schema_name )...)] source_option; CREATE [OR REPLACE] [AND {RESOLVE|COMPILE}] [NOFORCE] JAVA CLASS [SCHEMA schema] [AUTHID {CURRENT_USER |DEFINER}] [RESOLVER (( match_string, schema_name )...)] source_option; source_options: USING BFILE (directory, 'class_filename') USING {CLOB|BLOB|BFILE} subquery USING 'key_for_blob' AS source_text

CREATE LIBRARY Syntax Statement

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

CREATE OUTLINE Syntax Statement

CREATE [OR REPLACE] OUTLINE [schema.]operator [FOR CATEGORY category] ON statement;

CREATE PACKAGE Syntax Statement

CREATE [OR REPLACE] PACKAGE [schema.]package_name [invoker_rights] AS package invoker_rights: AUTHID CURRENT_USER AUTHID DEFINER AUTHID DEFINER will execute with the privileges of the package schema/owner.

CREATE PACKAGE BODY Syntax Statement

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

CREATE ROLLBACK SEGMENT Syntax Statement

CREATE [PUBLIC] ROLLBACK SEGMENT rbs_name option(s) options: TABLESPACE tablespace_name STORAGE storage_clause A public RBS is available for use by more than one instance

CREATE SCHEMA Syntax Statement

CREATE SCHEMA AUTHORISATION schema options options CREATE TABLE CREATE VIEW GRANT The schema name must be an existing Oracle username.

CREATE SEQUENCE Syntax Statement

CREATE SEQUENCE [schema.]sequence_name option(s) options: INCREMENT BY int START WITH int MAXVALUE int | NOMAXVALUE MINVALUE int | NOMINVALUE CYCLE | NOCYCLE CACHE int | NOCACHE ORDER | NOORDER

CREATE TEMPORARY TABLESPACE Syntax Statement

CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE Tempfile_Options [EXTENT MANAGEMENT LOCAL] [UNIFORM [SIZE int K | M] ]; Tempfile_Options: 'filespec' [AUTOEXTEND OFF] 'filespec' [AUTOEXTEND ON [NEXT int K | M] [MAXSIZE int K | M]] To create a locally managed tablespace specify 'EXTENT MANAGEMENT LOCAL'. All extents of temporary tablespaces are the same size - if UNIFORM is not defined it will default to 1 MB.

CREATE USER Syntax Statement

CREATE USER username IDENTIFIED {BY password | EXTERNALLY | GLOBALLY AS external_name} options options: DEFAULT TABLESPACE tablespace_name TEMPORARY TABLESPACE tablespace_name QUOTA int {K | M} ON tablespace_name QUOTA UNLIMITED ON tablespace_name PROFILE profile_name PASSWORD EXPIRE ACCOUNT {LOCK|UNLOCK}

COMMENT Syntax Statement

COMMENT ON TABLE [schema.]table IS 'text' COMMENT ON TABLE [schema.]view IS 'text' COMMENT ON TABLE [schema.]snapshot IS 'text' COMMENT ON COLUMN [schema.]table.column IS 'text' COMMENT ON COLUMN [schema.]view.column IS 'text' COMMENT ON COLUMN [schema.]snapshot.column IS 'text' To drop a comment from the database, set it to the empty string ' '.

COMMIT Syntax Statement

COMMIT [WORK] [COMMENT 'comment_text'] COMMIT [WORK] [FORCE 'force_text' [,int] ] FORCE - will manually commit an in-doubt distributed transaction.

DESCRIBE Syntax Statement

DESC table DESC view DESC synonym DESC function DESC package.procedure DESC package* It is also possible to describe objects in another schema or via a database link e.g. DESCRIBE user.table@db_link The DESCRIBE command allows you to describe objects recursively to the depth level set in the SET DESCRIBE command.

DISASSOCIATE STATISTICS Syntax Statement

DISSASSOCIATE STATISTICS FROM object [schema.]object_name [FORCE] Where object is any of COLUMNS FUNCTIONS PACKAGES TYPES INDEXES INDEXTYPES

EXEC Syntax Statement

EXEC statement EXEC [:bind_variable :=] package.procedure; EXEC [:bind_variable :=] package.function(parameters); The length of the EXEC command cannot exceed the length defined by SET LINESIZE. If the EXEC command is too long to fit on one line, use the SQL*Plus continuation character (a hyphen) - Example EXEC :answer := EMP_PAY.BONUS('SMITH')

EXECUTE IMMEDIATE Syntax Statement

EXECUTE IMMEDIATE dynamic_sql_string [INTO {define_variable,... | INTO record_name}] [USING [IN|OUT|IN OUT] bind_argument,...] [RETURN[ING] INTO bind_argument,...]; dynamic_sql_string : The SQL statement string or PL/SQL block define_variable : One variable receives each column value returned by the query. record_name : A record based on a user-defined TYPE or %ROWTYPE that receives an entire row returned by a query bind_argument : An expression whose value is passed to the SQL statement or PL/SQL block INTO clause Use for single-row queries; for each column value returned by the query, you must supply an individual variable or field in a record of compatible type. USING clause : Allows you to supply bind arguments for the SQL string. This clause is used for both dynamic SQL and PL/SQL, which is why you can specify a parameter mode. This usage is only relevant for PL/SQL, however; the default is IN, which is the only kind of bind argument you would have for SQL statements. You cannot use EXECUTE IMMEDIATE for multiple-row queries. If "dynamic_sql_string" ends with a semicolon, it will be treated as a PL/SQL block; otherwise, it will be treated as either DML (Data Manipulation Language--SELECT, INSERT, UPDATE, or DELETE) or DDL (Data Definition Language, such as CREATE TABLE). The "dynamic_sql_string" may contain placeholders for bind arguments, but you cannot use bind values to pass in the names of schema objects, such as table names or column names. When the statement is executed, the runtime engine replaces each placeholder (an identifier with a colon in front of it, such as :salary_value) in the SQL string with its corresponding bind argument (by position). You can pass numeric, date, and string expressions. You cannot, pass a Boolean, or a NULL literal value, you can however pass a variable of the correct type that has a value of NULL.

EXPLAIN PLAN Syntax Statement

EXPLAIN PLAN [SET STATEMENT_ID = 'text'] FOR statement EXPLAIN PLAN [SET STATEMENT_ID = 'text'] INTO [schema.]table@dblink FOR statement

GRANT Syntax Statement

Roles: GRANT role TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION] System Privs: GRANT system_priv(s) TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION] GRANT ALL TO [user,] [role,] [PUBLIC] [WITH ADMIN OPTION] Objects: GRANT object_priv [(column, column,...)] ON [schema.]object TO [user], [role], [PUBLIC] [WITH GRANT OPTION] GRANT ALL [(column, column,...)] ON [schema.]object TO [user], [role], [PUBLIC] [WITH GRANT OPTION] GRANT object_priv [(column, column,...)] ON DIRECTORY directory_name TO [user], [role], [PUBLIC] [WITH GRANT OPTION] GRANT object_priv [(column, column,...)] ON JAVA [RE]SOURCE [schema.]object TO [user], [role], [PUBLIC] [WITH GRANT OPTION] key: object_privs ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, UPDATE system_privs ALTER ANY INDEX, BECOME USER , CREATE TABLE, DROP ANY VIEW RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE plus too many others to list here roles Standard Oracle roles - EXP_FULL_DATABASE, IMP_FULL_DATABASE, OSOPER, OSDBA plus any user defined roles you have available notes:

LOCK TABLE Syntax Statement

LOCK TABLE [schema.] table [options] IN lockmode MODE [NOWAIT] LOCK TABLE [schema.] view [options] IN lockmode MODE [NOWAIT] options: PARTITION partition SUBPARTITION subpartition @dblink lockmodes: EXCLUSIVE SHARE ROW EXCLUSIVE SHARE ROW EXCLUSIVE ROW SHARE* | SHARE UPDATE*

NOAUDIT Syntax Statement

NOAUDIT {ALL|ALL PRIVILEGES|sql_statement|system_priv} [options] [WHENEVER [NOT] SUCCESSFUL] options: BY user BY proxy [ON BEHALF OF ANY|user] Schema Objects To noaudit an object use the modified syntax NOAUDIT {ALL|action} on [schema.]object [WHENEVER [NOT] SUCCESSFUL] NOAUDIT {ALL|action} on DIRECTORY directory_name [WHENEVER [NOT] SUCCESSFUL] NOAUDIT {ALL|action} on DEFAULT [WHENEVER [NOT] SUCCESSFUL] actions ALTER, AUDIT, COMMENT, DELETE, EXECUTE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE You can disable all auditing with the init.ora parameter AUDIT_TRAIL = NO

RECOVER Syntax Statement

RECOVER [AUTOMATIC] [FROM 'location'] [STANDBY] DATABASE [UNTIL CANCEL] [UNTIL TIME date] [UNTIL CHANGE int] [USING BACKUP CONTROLFILE] RECOVER [AUTOMATIC] [FROM 'location'] TABLESPACE tablespace [, tablespace2...] RECOVER [AUTOMATIC] [FROM 'location'] STANDBY TABLESPACE tablespace [, tablespace2...] UNTIL [CONSISTENT] [WITH] CONTROLFILE RECOVER [AUTOMATIC] [FROM 'location'] DATAFILE 'filename' [, filename2...] RECOVER [AUTOMATIC] [FROM 'location'] STANDBY DATAFILE 'filename' [, filename2,...] UNTIL [CONSISTENT] [WITH] CONTROLFILE RECOVER [AUTOMATIC] [FROM 'location'] LOGFILE 'filename' RECOVER [AUTOMATIC] [FROM 'location'] CONTINUE [DEFAULT] RECOVER [AUTOMATIC] [FROM 'location'] CANCEL RECOVER MANAGED STANDBY DATABASE TIMEOUT integer RECOVER MANAGED STANDBY DATABASE CANCEL [IMMEDIATE] On a parallel server you can add to any of the above: PARALLEL Parallel Clause Key: AUTOMATIC Automatically generate the name of the next archived redo log file needed to continue the recovery operation. FROM location The location from which the archived redo log file group is read. STANDBY Recover the standby database using the control file and archived redo log files copied from the primary database. The standby database must be mounted but not open. UNTIL CANCEL Partial recovery. Recovery proceeds by prompting you with the suggested filenames of archived redo log files, and recovery completes when you specify CANCEL instead of a filename. UNTIL CHANGE integer An incomplete, change-based recovery. integer is the System Change Number (SCN) following the last change you wish to recover. UNTIL TIME date Partial time-based recovery. Use the format: 'YYYY-MM-DD:HH24:MI:SS' UNTIL [CONSISTENT] [WITH] CONTROLFILE Recover an old standby datafile or tablespace using the current standby database control file. TABLESPACE tablespace Recover a tablespace. You may recover up to 16 tablespaces in one statement. CONTINUE [DEFAULT] Continues multi-instance recovery after it has been interrupted to disable a thread. Continues recovery using the redo log file that Oracle would automatically generate if no other logfile were specified. This option is equivalent to specifying AUTOMATIC, except that Oracle does not prompt for a filename. CANCEL Terminates cancel-based recovery. MANAGED STANDBY DATABASE Sustained standby recovery mode. TIMEOUT integer The number of MINUTES = the wait period of a sustained recovery operation. The RECOVER command is available in Server Manager/SQL*Plus, this is recommended for media recovery in preference to the syntax ALTER DATABASE RECOVER... (provided for backwards compatibility with older versions)

RENAME Syntax Statement

RENAME old TO new

REVOKE Syntax Statement

REVOKE role FROM {user, | role, |PUBLIC} System Privs: REVOKE system_priv(s) FROM {user, | role, |PUBLIC} Objects: REVOKE ALL [(columns)] ON [schema.]object FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE] REVOKE object_priv [(columns)] ON [schema.]object FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE] REVOKE object_priv [(columns)] ON DIRECTORY directory_name FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE] REVOKE object_priv [(columns)] ON JAVA [RE]SOURCE [schema.]object FROM {user, | role, |PUBLIC} [CASCADE CONSTRAINTS] [FORCE] key: object_privs ALTER, DELETE, EXECUTE, INDEX, INSERT, REFERENCES, SELECT, UPDATE, ALL PRIVILEGES system_privs ALTER ANY INDEX, BECOME USER , CREATE TABLE, DROP ANY VIEW RESTRICTED SESSION, UNLIMITED TABLESPACE, UPDATE ANY TABLE plus too many others to list here roles Standard Oracle roles - CONNECT, RESOURCE, DBA, EXP_FULL_DATABASE, IMP_FULL_DATABASE plus any user defined roles you have available FORCE, will revoke all privileges from a user-defined-type and mark it's dependent objects INVALID.

ROLLBACK Syntax Statement

ROLLBACK [WORK] [TO 'savepoint_text_identifier'] ROLLBACK [WORK] [FORCE 'force_text']

SET TRANSACTION Syntax Statement

SET TRANSACTION READ ONLY SET TRANSACTION READ WRITE SET TRANSACTION ISOLATION LEVEL {SERIALIZABLE | READ COMMITTED} SET TRANSACTION USE ROLLBACK SEGMENT rb_segment

SHUTDOWN Syntax Statement

SHUTDOWN ABORT SHUTDOWN IMMEDIATE SHUTDOWN TRANSACTIONAL [LOCAL] SHUTDOWN NORMAL key: ABORT The fastest possible shutdown of the database without waiting for calls to complete or users to disconnect. Uncommitted transactions are not rolled back. Client SQL statements currently being processed are terminated. All users currently connected to the database are implicitly disconnected and the next database startup will require instance recovery. You must use this option if a background process terminates abnormally. IMMEDIATE Does not wait for current calls to complete or users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup. NORMAL NORMAL is the default option which waits for users to disconnect from the database. Further connects are prohibited. The database is closed and dismounted. The instance is shutdown and no instance recovery is required on the next database startup. TRANSACTIONAL [LOCAL] A planned shutdown of an instance, allowing active transactions to complete first. It prevents clients from losing work without requiring all users to log off. No client can start a new transaction on this instance. Attempting to start a new transaction results in disconnection. After completion of all transactions, any client still connected to the instance is disconnected. Now the instance shuts down (SHUTDOWN IMMEDIATE). The next startup of the database will not require any instance recovery procedures. The LOCAL mode specifies a transactional shutdown on the local instance only, so that it only waits on local transactions to complete, not all transactions. This is useful, for example, for scheduled outage maintenance.

STARTUP Syntax Statement

STARTUP [FORCE] [RESTRICT] [PFILE=filename] NOMOUNT STARTUP [FORCE] [RESTRICT] [PFILE=filename] MOUNT [dbname] STARTUP [FORCE] [RESTRICT] [PFILE=filename] OPEN [open_options] [dbname] open_options: READ {ONLY | WRITE [RECOVER]} | RECOVER key: FORCE Shut down the current Oracle instance (if it is running) with SHUTDOWN mode ABORT, before restarting it. If the current instance is running and FORCE is not specified, an error results. FORCE is useful while debugging and under abnormal circumstances. It should not normally be used. RESTRICT Only allow Oracle users with the RESTRICTED SESSION system privilege to connect to the database. Later, you can use the ALTER SYSTEM command to disable the restricted session feature. PFILE=filename The init.ora parameter file to be used while starting up the instance. If PFILE is not specified, then the default STARTUP parameter file is used. The default file used is platform specific. . MOUNT dbname Mount a database but do not open it. dbname is the name of the database to mount or open. If no database name is specified, the database name is taken from the initialization parameter DB_NAME. OPEN Mount and open the specified database. NOMOUNT Don't mount the database upon instance startup. Cannot be used with MOUNT, or OPEN. RECOVER Specifies that media recovery should be performed, if necessary, before starting the instance. STARTUP RECOVER has the same effect as issuing the RECOVER DATABASE command and starting an instance. Only 'complete recovery' is possible with the RECOVER option. Recovery proceeds, if necessary, as if AUTORECOVERY is set to ON, regardless of whether or not AUTORECOVERY is enabled. If a redo log file is not found in the expected location, recovery will continue by prompting you with the suggested location and name of the subsequent log files that need to be applied.

TRUNCATE TABLE Syntax Statement

TRUNCATE TABLE [schema.]table [{PRESERVE|PURGE} SNAPSHOT LOG] [{DROP | REUSE} STORAGE] TRUNCATE CLUSTER [schema.]cluster [{PRESERVE|PURGE} SNAPSHOT LOG] [{DROP | REUSE} STORAGE] The terms "snapshot" and "materialized view" are synonymous.

Date Format (fmt)

When a date format is used by TO_CHAR or TO_DATE they return part of the date/time. When used by TRUNC they will return the first day of the period. When used by ROUND the values will round up at mid year/mid month (July 1 or 16th day) CC Century SCC Century BC prefixed with - YYYY Year 2001 SYYY Year BC prefixed with - IYYY ISO Year 2001 YY Year 01 RR Year 01 rollover for Y2K compatibility * YEAR Year spelled out SYEAR Year spelled out BC prefixed with - BC BC/AD Indicator * Q Quarter : Jan-Mar=1, Apr-Jun=2 MM Month of year 01, 02...12 RM Roman Month I, II...XII * MONTH In full [January ]...[December ] FMMONTH In full [January]...[December] MON JAN, FEB WW Week of year 1-52 W Week of month 1-5 IW ISO std week of year DDD Day of year 1-366 * DD Day of month 1-31 D Day of week 1-7 DAY In full [Monday ]...[Sunday ] FMDAY In full [Monday]...[Sunday] DY MON...SUN DDTH Ordinal Day 7TH DDSPTH Spell out ordinal SEVENTH J Julian Day (days since 31/12/4713) HH Hours of day (1-12) HH12 Hours of day (1-12) HH24 Hours of day (1-24) SPHH Spell out SEVEN AM am or pm * PM am or pm * A.M. a.m. or p.m. * P.M. a.m. or p.m. * MI Minutes 0-59 SS Seconds 0-59 * SSSS Seconds past midnight (0-86399) * The following punctuation -/,.;: can be included in any date format any other chars can be included "in quotes" * Formats marked with * can only be used with TO_CHAR or TO_DATE not TRUNC () or ROUND() Date formats that are spelled out in characters will adopt the capitalisation of the format e.g. 'MONTH' =JANUARY 'Month' = January

NLS Formats (Territory)

Specifying an NLS parameter for an SQL function means that any user session NLS parameters (or the lack of appropriate NLS parameters) will not affect evaluation of the function. This feature may be important for SQL statements that contain numbers and dates as string literals. For example, the following query is evaluated correctly only if the language specified for dates is American: SELECT ENAME FROM EMP WHERE HIREDATE > '1-JAN-01' This can be made independent of the current date language by specifying NLS_DATE_LANGUAGE: SELECT ENAME FROM EMP WHERE HIREDATE > TO_DATE ('1-JAN-01','DD-MON-YY', 'NLS_DATE_LANGUAGE = AMERICAN') Of course a simpler way of making this language-independent is SELECT ENAME FROM EMP WHERE HIREDATE > TO_DATE ('1-01-01','DD-MM-YY') NLS settings include Character set, Language and territory The most common Character Sets are: WE8ISO8859P15 European English includes euro character US7ASCII American English Oracle Languages e.g. NLS_LANGUAGE = ENGLISH us AMERICAN ar ARABIC … The NLS_LANGUAGE above implicitly defines several other parameters: NLS_DATE_LANGUAGE, NLS_SORT Oracle Territories e.g. NLS_TERRITORY = "UNITED KINGDOM" AMERICA AUSTRALIA … The NLS_TERRITORY implicitly defines several other parameters: NLS_NUMERIC_CHARACTERS, NLS_CURRENCY, NLS_ISO_CURRENCY, NLS_DATE_FORMAT, NLS_MONETARY_CHARACTERS, NLS_CREDIT, NLS_DEBIT If necessary these can be explicitly defined e.g. NLS_NUMERIC_CHARACTERS = ",." NLS_COMP This provides a simple alternative to specifying NLS_SORT in an SQL WHERE clause NLS formats will affect SQL statements in views, CHECK constraints, and triggers.