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