A condition specifies a combination of one or more expressions and logical (Boolean) operators and returns a value of TRUE, FALSE, or unknown.
Conditions have several forms. The sections that follow show the syntax for each form of condition. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.
|
See Also: Conditions in Oracle Database SQL Language Reference for detailed information about SQL conditions |
expr1 [ NOT ] BETWEEN expr2 AND expr3
{ (condition)
| NOT condition
| condition { AND | OR } condition
}
EQUALS_PATH
(column, path_string [, correlation_integer ])
EXISTS (subquery)
expr IS [ NOT ] { NAN | INFINITE }
{ expr
{ = | != | ^= | <> | > | < | >= | <= }
{ ANY | SOME | ALL }
({ expression_list | subquery })
| ( expr [, expr ]... )
{ = | != | ^= | <> }
{ ANY | SOME | ALL }
({ expression_list
[, expression_list ]...
| subquery
}
)
}
where !=, ^=, and <> test for inequality
{ expr [ NOT ] IN ({ expression_list | subquery })
| ( expr [, expr ]... )
[ NOT ] IN ({ expression_list [, expression_list ]...
| subquery
}
)
}
nested_table IS [ NOT ] A SET
[ dimension_column IS ] ANY
nested_table IS [ NOT ] EMPTY
expr IS [ NOT ] OF [ TYPE ]
([ ONLY ] [ schema. ] type
[, [ ONLY ] [ schema. ] type ]...
)
cell_reference IS PRESENT
char1 [ NOT ] { LIKE | LIKEC | LIKE2 | LIKE4 }
char2 [ ESCAPE esc_char ]
{ NOT | AND | OR }
expr [ NOT ] MEMBER [ OF ] nested_table
expr IS [ NOT ] NULL
REGEXP_LIKE(source_char, pattern
[, match_param ]
)
{ expr
{ = | != | ^= | <> | > | < | >= | <= }
expr
| (expr [, expr ]...)
{ = | != | ^= | <> }
(subquery)
}
where !=, ^=, and <> test for inequality
nested_table1 [ NOT ] SUBMULTISET [ OF ] nested_table2
UNDER_PATH (column [, levels ], path_string
[, correlation_integer ]
)
This chapter presents the syntax for SQL functions.
This chapter includes the following section:
SQL Language Quick Reference
11g Release 2 (11.2)
E17119-06
July 2012
Oracle Database SQL Language Quick Reference, 11g Release 2 (11.2)
E17119-06
Copyright © 2003, 2012, Oracle and/or its affiliates. All rights reserved.
Primary Authors: Diana Lorentz, Mary Beth Roeser, Simon Watt
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT END USERS: Oracle programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, delivered to U.S. Government end users are "commercial computer software" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, use, duplication, disclosure, modification, and adaptation of the programs, including any operating system, integrated software, any programs installed on the hardware, and/or documentation, shall be subject to license terms and license restrictions applicable to the programs. No other rights are granted to the U.S. Government.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
This section describes the kinds of Oracle built-in data types.
{ CHAR [ (size [ BYTE | CHAR ]) ]
| VARCHAR2 (size [ BYTE | CHAR ])
| NCHAR [ (size) ]
| NVARCHAR2 (size)
}
{ DATE
| TIMESTAMP [ (fractional_seconds_precision) ]
[ WITH [ LOCAL ] TIME ZONE ])
| INTERVAL YEAR [ (year_precision) ] TO MONTH
| INTERVAL DAY [ (day_precision) ] TO SECOND
[ (fractional_seconds_precision) ]
}
{ BLOB | CLOB | NCLOB | BFILE }
{ LONG | LONG RAW | RAW (size) }
{ NUMBER [ (precision [, scale ]) ]
| FLOAT [ (precision) ]
| BINARY_FLOAT
| BINARY_DOUBLE
}
{ ROWID | UROWID [ (size) ] }
The codes listed for the data types are used internally by Oracle Database. The data type code of a column or object attribute is returned by the DUMP function.
Table 6-1 Built-in Data Type Summary
| Code | Data Type | Description |
|---|---|---|
|
1 |
|
Variable-length character string having maximum length
|
|
1 |
|
Variable-length Unicode character string having maximum length |
|
2 |
|
Number having precision |
|
2 |
|
A subtype of the |
|
8 |
|
Character data of variable length up to 2 gigabytes, or 231 -1 bytes. Provided for backward compatibility. |
|
12 |
|
Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the |
|
21 |
|
32-bit floating point number. This data type requires 4 bytes. |
|
22 |
|
64-bit floating point number. This data type requires 8 bytes. |
|
180 |
|
Year, month, and day values of date, as well as hour, minute, and second values of time, where |
|
181 |
|
All values of |
|
231 |
|
All values of
The default format is determined explicitly by the |
|
182 |
|
Stores a period of time in years and months, where |
|
183 |
|
Stores a period of time in days, hours, minutes, and seconds, where
The size is fixed at 11 bytes. |
|
23 |
|
Raw binary data of length |
|
24 |
|
Raw binary data of variable length up to 2 gigabytes. |
|
69 |
|
Base 64 string representing the unique address of a row in its table. This data type is primarily for values returned by the |
|
208 |
|
Base 64 string representing the logical address of a row of an index-organized table. The optional |
|
96 |
|
Fixed-length character data of length
|
|
96 |
|
Fixed-length character data of length |
|
112 |
|
A character large object containing single-byte or multibyte characters. Both fixed-width and variable-width character sets are supported, both using the database character set. Maximum size is (4 gigabytes - 1) * (database block size). |
|
112 |
|
A character large object containing Unicode characters. Both fixed-width and variable-width character sets are supported, both using the database national character set. Maximum size is (4 gigabytes - 1) * (database block size). Stores national character set data. |
|
113 |
|
A binary large object. Maximum size is (4 gigabytes - 1) * (database block size). |
|
114 |
|
Contains a locator to a large binary file stored outside the database. Enables byte stream I/O access to external LOBs residing on the database server. Maximum size is 4 gigabytes. |
A format model is a character literal that describes the format of DATETIME or NUMBER data stored in a character string. When you convert a character string into a datetime or number, a format model tells Oracle how to interpret the string.
You can use number format models:
In the TO_CHAR function to translate a value of NUMBER data type to VARCHAR2 data type
In the TO_NUMBER function to translate a value of CHAR or VARCHAR2 data type to NUMBER data type
A number format model is composed of one or more number format elements. The following table lists the elements of a number format model.
Table 7-1 Number Format Elements
| Element | Example | Description |
|---|---|---|
|
, (comma) |
|
Returns a comma in the specified position. You can specify multiple commas in a number format model. Restrictions:
|
|
. (period) |
|
Returns a decimal point, which is a period (.) in the specified position. Restriction: You can specify only one period in a number format model. |
|
$ |
|
Returns value with a leading dollar sign. |
|
0 |
|
Returns leading zeros. Returns trailing zeros. |
|
9 |
|
Returns value with the specified number of digits with a leading space if positive or with a leading minus if negative. Leading zeros are blank, except for a zero value, which returns a zero for the integer part of the fixed-point number. |
|
B |
|
Returns blanks for the integer part of a fixed-point number when the integer part is zero (regardless of zeros in the format model). |
|
|
Returns in the specified position the ISO currency symbol (the current value of the | |
|
|
Returns in the specified position the decimal character, which is the current value of the Restriction: You can specify only one decimal character in a number format model. | |
|
|
Returns a value using in scientific notation. | |
|
|
Returns in the specified position the group separator (the current value of the Restriction: A group separator cannot appear to the right of a decimal character or period in a number format model. | |
|
|
Returns in the specified position the local currency symbol (the current value of the | |
|
MI |
|
Returns negative value with a trailing minus sign (-). Returns positive value with a trailing blank. Restriction: The MI format element can appear only in the last position of a number format model. |
|
PR |
|
Returns negative value in <angle brackets>. Returns positive value with a leading and trailing blank. Restriction: The PR format element can appear only in the last position of a number format model. |
|
RN rn |
|
Returns a value as Roman numerals in uppercase. Returns a value as Roman numerals in lowercase. Value can be an integer between 1 and 3999. |
|
S |
|
Returns negative value with a leading minus sign (-). Returns positive value with a leading plus sign (+). Returns negative value with a trailing minus sign (-). Returns positive value with a trailing plus sign (+). Restriction: The S format element can appear only in the first or last position of a number format model. |
|
TM |
|
The text minimum number format model returns (in decimal output) the smallest number of characters possible. This element is case insensitive. The default is TM9, which returns the number in fixed notation unless the output exceeds 64 characters. If the output exceeds 64 characters, then Oracle Database automatically returns the number in scientific notation. Restrictions:
|
|
|
Returns in the specified position the Euro (or other) dual currency symbol, determined by the current value of the | |
|
V |
|
Returns a value multiplied by 10n (and if necessary, round it up), where |
|
|
Returns the hexadecimal value of the specified number of digits. If the specified number is not an integer, then Oracle Database rounds it to an integer. Restrictions:
|
You can use datetime format models:
In the TO_CHAR, TO_DATE, TO_TIMESTAMP, TO_TIMESTAMP_TZ, TO_YMINTERVAL, and TO_DSINTERVAL datetime functions to translate a character string that is in a format other than the default datetime format into a DATETIME value
In the TO_CHAR function to translate a DATETIME value that is in a format other than the default datetime format into a character string
A datetime format model is composed of one or more datetime format elements. The following table lists the elements of a date format model.
Table 7-2 Datetime Format Elements
| Element | TO_* datetime functions? | Description |
|---|---|---|
- / , . ; : "text" |
Yes |
Punctuation and quoted text is reproduced in the result. |
AD A.D. |
Yes |
AD indicator with or without periods. |
AM A.M. |
Yes |
Meridian indicator with or without periods. |
BC B.C. |
Yes |
BC indicator with or without periods. |
CC SCC |
Century.
For example, 2002 returns 21; 2000 returns 20. | |
D |
Yes |
Day of week (1-7). This element depends on the NLS territory of the session. |
DAY |
Yes |
Name of day. |
DD |
Yes |
Day of month (1-31). |
DDD |
Yes |
Day of year (1-366). |
DL |
Yes |
Returns a value in the long date format, which is an extension of Oracle Database's Restriction: You can specify this format only with the |
DS |
Yes |
Returns a value in the short date format. Makes the appearance of the date components (day name, month number, and so forth) depend on the Restriction: You can specify this format only with the |
DY |
Yes |
Abbreviated name of day. |
E |
Yes |
Abbreviated era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
EE |
Yes |
Full era name (Japanese Imperial, ROC Official, and Thai Buddha calendars). |
|
Yes |
Fractional seconds; no radix character is printed. Use the X format element to add the radix character. Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle Database uses the precision specified for the datetime data type or the data type's default precision. Valid in timestamp and interval formats, but not in Examples:
|
|
Yes |
Returns a value with no leading or trailing blanks. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference |
|
Yes |
Requires exact matching between the character data and the format model. See Also: Additional discussion on this format model modifier in the Oracle Database SQL Language Reference |
HH HH12 |
Yes |
Hour of day (1-12). |
HH24 |
Yes |
Hour of day (0-23). |
IW |
Week of year (1-52 or 1-53) based on the ISO standard. | |
IYY IY I |
Last 3, 2, or 1 digit(s) of ISO year. | |
IYYY |
4-digit year based on the ISO standard. | |
J |
Yes |
Julian day; the number of days since January 1, 4712 BC. Number specified with J must be integers. |
MI |
Yes |
Minute (0-59). |
MM |
Yes |
Month (01-12; January = 01). |
MON |
Yes |
Abbreviated name of month. |
MONTH |
Yes |
Name of month. |
PM P.M. |
Yes |
Meridian indicator with or without periods. |
Q |
Quarter of year (1, 2, 3, 4; January - March = 1). | |
RM |
Yes |
Roman numeral month (I-XII; January = I). |
RR |
Yes |
Lets you store 20th century dates in the 21st century using only two digits. See Also: Additional discussion on |
RRRR |
Yes |
Round year. Accepts either 4-digit or 2-digit input. If 2-digit, provides the same return as RR. If you do not want this functionality, then enter the 4-digit year. |
SS |
Yes |
Second (0-59). |
SSSSS |
Yes |
Seconds past midnight (0-86399). |
TS |
Yes |
Returns a value in the short time format. Makes the appearance of the time components (hour, minutes, and so forth) depend on the Restriction: You can specify this format only with the |
TZD |
Yes |
Daylight saving information. The TZD value is an abbreviated time zone string with daylight saving information. It must correspond with the region specified in TZR. Valid in timestamp and interval formats, but not in Example: |
TZH |
Yes |
Time zone hour. (See Example: |
TZM |
Yes |
Time zone minute. (See Example: |
TZR |
Yes |
Time zone region information. The value must be one of the time zone regions supported in the database. Valid in timestamp and interval formats, but not in Example: US/Pacific |
WW |
Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year. | |
W |
Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh. | |
X |
Yes |
Local radix character. Example: |
Y,YYY |
Yes |
Year with comma in this position. |
YEAR SYEAR |
Year, spelled out; | |
YYYY SYYYY |
Yes |
4-digit year; |
YYY YY Y |
Yes |
Last 3, 2, or 1 digit(s) of year. |
SQL statements that create tables and clusters can also use ANSI data types and data types from the IBM products SQL/DS and DB2. Oracle recognizes the ANSI or IBM data type name that differs from the Oracle data type name, records it as the name of the data type of the column, and then stores the column data in an Oracle data type based on the conversions shown in the following table.
Table 6-2 ANSI Data Types Converted to Oracle Data Types
| ANSI SQL Data Type | Oracle Data Type |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Notes:
The NUMERIC and DECIMAL data types can specify only fixed-point numbers. For those data types, the scale (s) defaults to 0.
The FLOAT data type is a floating-point number with a binary precision b. The default precision for this data type is 126 binary, or 38 decimal.
The DOUBLE PRECISION data type is a floating-point number with binary precision 126.
The REAL data type is a floating-point number with a binary precision of 63, or 18 decimal.
Do not define columns with the following SQL/DS and DB2 data types, because they have no corresponding Oracle data type:
GRAPHIC
LONG VARGRAPHIC
VARGRAPHIC
TIME
Note that data of type TIME can also be expressed as Oracle datetime data.
A function is a command that manipulates data items and returns a single value.
The sections that follow show each SQL function and its related syntax. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.
|
See Also: Functions in Oracle Database SQL Language Reference for detailed information about SQL functions |
ABS(n)
ACOS(n)
ADD_MONTHS(date, integer)
Aggregate functions return a single result row based on groups of rows, rather than on single rows.
analytic_function([ arguments ]) OVER (analytic_clause)
APPENDCHILDXML ( XMLType_instance, XPath_string, value_expr [, namespace_string ])
ASCII(char)
ASCIISTR(char)
ASIN(n)
ATAN(n)
ATAN2(n1 , n2)
AVG([ DISTINCT | ALL ] expr) [ OVER(analytic_clause) ]
BFILENAME('directory', 'filename')
BIN_TO_NUM(expr [, expr ]... )
BITAND(expr1, expr2)
CARDINALITY(nested_table)
CAST({ expr | MULTISET (subquery) } AS type_name)
CEIL(n)
CHARTOROWID(char)
CHR(n [ USING NCHAR_CS ])
CLUSTER_ID ( [ schema . ] model mining_attribute_clause )
CLUSTER_PROBABILITY ( [ schema . ] model [ , cluster_id ] mining_attribute_clause )
CLUSTER_SET ( [ schema . ] model [ , topN [ , cutoff ] ] mining_attribute_clause )
COALESCE(expr [, expr ]...)
COLLECT( [ DISTINCT | UNIQUE ] column [ ORDER BY expr ] )
COMPOSE(char)
CONCAT(char1, char2)
CONVERT(char, dest_char_set[, source_char_set ])
CORR(expr1, expr2) [ OVER (analytic_clause) ]
{ CORR_K | CORR_S }
(expr1, expr2
[, { COEFFICIENT
| ONE_SIDED_SIG
| ONE_SIDED_SIG_POS
| ONE_SIDED_SIG_NEG
| TWO_SIDED_SIG
}
]
)
COS(n)
COSH(n)
COUNT({ * | [ DISTINCT | ALL ] expr }) [ OVER (analytic_clause) ]
COVAR_POP(expr1, expr2) [ OVER (analytic_clause) ]
COVAR_SAMP(expr1, expr2) [ OVER (analytic_clause) ]
CUBE_TABLE
( ' { schema.cube [ {HIERARCHY | HRR} dimension hierarchy ]...
| schema.dimension [ {HIERARCHY | HRR} [dimension] hierarchy ]
}
'
)
CUME_DIST(expr[,expr ]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
CUME_DIST() OVER ([ query_partition_clause ] order_by_clause)
CURRENT_DATE
CURRENT_TIMESTAMP [ (precision) ]
CV([ dimension_column ])
DATAOBJ_TO_PARTITION( table, partition_id )
DBTIMEZONE
DECODE(expr, search, result [, search, result ]... [, default ])
DECOMPOSE( string [ CANONICAL | COMPATIBILITY ] )
DELETEXML( XMLType_instance, XPath_string [, namespace_string ])
DENSE_RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[,expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
DENSE_RANK( ) OVER([ query_partition_clause ] order_by_clause)
DEPTH(correlation_integer)
DEREF(expr)
DUMP(expr[, return_fmt [, start_position [, length ] ]])
{ EMPTY_BLOB | EMPTY_CLOB }( )
EXISTSNODE
(XMLType_instance, XPath_string
[, namespace_string ]
)
EXP(n)
EXTRACT( { YEAR
| MONTH
| DAY
| HOUR
| MINUTE
| SECOND
| TIMEZONE_HOUR
| TIMEZONE_MINUTE
| TIMEZONE_REGION
| TIMEZONE_ABBR
}
FROM { expr }
)
EXTRACT(XMLType_instance, XPath_string [, namespace_string ])
EXTRACTVALUE(XMLType_instance, XPath_string [, namespace_string ])
FEATURE_ID( [ schema . ] model mining_attribute_clause )
FEATURE_SET( [ schema . ] model [, topN [, cutoff ]] mining_attribute_clause )
FEATURE_VALUE( [ schema . ] model [, feature_id ] mining_attribute_clause )
aggregate_function
KEEP
(DENSE_RANK FIRST ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
[ OVER ( [query_partition_clause] ) ]
FIRST_VALUE
{ (expr) [ {RESPECT | IGNORE} NULLS ]
| (expr [ {RESPECT | IGNORE} NULLS ])
}
OVER (analytic_clause)
FLOOR(n)
FROM_TZ (timestamp_value, time_zone_value)
GREATEST(expr [, expr ]...)
GROUP_ID( )
GROUPING(expr)
GROUPING_ID(expr [, expr ]...)
HEXTORAW(char)
INITCAP(char)
INSERTCHILDXML ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTCHILDXMLAFTER ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTCHILDXMLBEFORE ( XMLType_instance, XPath_string, child_expr, value_expr [, namespace_string ] )
INSERTXMLAFTER ( XMLType_instance, XPath_string, value_expr [, namespace_string ] )
INSERTXMLBEFORE ( XMLType_instance, XPath_string, value_expr [, namespace_string ] )
{ INSTR
| INSTRB
| INSTRC
| INSTR2
| INSTR4
}
(string , substring [, position [, occurrence ] ])
ITERATION_NUMBER
LAG
{ ( value_expr [, offset [, default]]) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)
aggregate_function KEEP
(DENSE_RANK LAST ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
[ OVER ( [query_partition_clause] ) ]
LAST_DAY(date)
LAST_VALUE
{ (expr) [ { RESPECT | IGNORE } NULLS ]
| (expr [ { RESPECT | IGNORE } NULLS ])
OVER (analytic_clause)
LEAD
{ ( value_expr [, offset [, default]] ) [ { RESPECT | IGNORE } NULLS ]
| ( value_expr [ { RESPECT | IGNORE } NULLS ] [, offset [, default]] )
}
OVER ([ query_partition_clause ] order_by_clause)
LEAST(expr [, expr ]...)
{ LENGTH
| LENGTHB
| LENGTHC
| LENGTH2
| LENGTH4
}
(char)
LISTAGG(measure_expr [, 'delimiter']) WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
LN(n)
LNNVL(condition)
LOCALTIMESTAMP [ (timestamp_precision) ]
LOG(n2, n1)
LOWER(char)
LPAD(expr1, n [, expr2 ])
LTRIM(char [, set ])
MAKE_REF({ table | view } , key [, key ]...)
MAX([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MEDIAN(expr) [ OVER (query_partition_clause) ]
MIN([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
MOD(n2, n1)
MONTHS_BETWEEN(date1, date2)
NANVL(n2, n1)
NCHR(number)
NEW_TIME(date, timezone1, timezone2)
NEXT_DAY(date, char)
NLS_CHARSET_DECL_LEN(byte_count, 'char_set_id')
NLS_CHARSET_ID(string)
NLS_CHARSET_NAME(number)
NLS_INITCAP(char [, 'nlsparam' ])
NLS_LOWER(char [, 'nlsparam' ])
NLS_UPPER(char [, 'nlsparam' ])
NLSSORT(char [, 'nlsparam' ])
NTH_VALUE(measure_expr, n)
[ FROM { FIRST | LAST } ][ { RESPECT | IGNORE } NULLS ]
OVER (analytic_clause)
NTILE(expr) OVER ([ query_partition_clause ] order_by_clau €ÿse)
NULLIF(expr1, expr2)
NUMTODSINTERVAL(n, 'interval_unit')
NUMTOYMINTERVAL(n, 'interval_unit')
NVL(expr1, expr2)
NVL2(expr1, expr2, expr3)
ORA_DST_AFFECTED(datetime_expr)
ORA_DST_CONVERT(datetime_expr [, integer [, integer ]])
ORA_DST_ERROR(datetime_expr)
ORA_HASH(expr [, max_bucket [, seed_value ] ])
PATH(correlation_integer)
PERCENT_RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY
expr [ DESC | ASC ]
[NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[NULLS { FIRST | LAST } ]
]...
)
PERCENT_RANK( ) OVER ([ query_partition_clause ] order_by_clause)
PERCENTILE_CONT(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
PERCENTILE_DISC(expr) WITHIN GROUP (ORDER BY expr [ DESC | ASC ]) [ OVER (query_partition_clause) ]
POWER(n2, n1)
POWERMULTISET(expr)
POWERMULTISET_BY_CARDINALITY(expr, cardinality)
PREDICTION ( [ schema . ] model [ cost_matrix_clause ] mining_attribute_clause )
PREDICTION_BOUNDS ( [schema.] model [, confidence_level [, class_value]] mining_attribute_clause )
PREDICTION_COST ( [ schema . ] model [ , class ] cost_matrix_clause mining_attribute_clause )
PREDICTION_DETAILS ( [ schema . ] model mining_attribute_clause )
PREDICTION_PROBABILITY ( [ schema . ] model [ , class ] mining_attribute_clause )
PREDICTION_SET ( [ schema . ] model [ , bestN [ , cutoff ] ] [ cost_matrix_clause ] mining_attribute_clause )
PRESENTNNV(cell_reference, expr1, expr2)
PRESENTV(cell_reference, expr1, expr2)
PREVIOUS(cell_reference)
RANK(expr [, expr ]...) WITHIN GROUP
(ORDER BY
expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
[, expr [ DESC | ASC ]
[ NULLS { FIRST | LAST } ]
]...
)
RANK( ) OVER ([ query_partition_clause ] order_by_clause)
RATIO_TO_REPORT(expr) OVER ([ query_partition_clause ])
RAWTOHEX(raw)
RAWTONHEX(raw)
REF (correlation_variable)
REFTOHEX (expr)
REGEXP_COUNT (source_char, pattern [, position [, match_param]])
REGEXP_INSTR (source_char, pattern
[, position
[, occurrence
[, return_opt
[, match_param
[, subexpr]
]
]
]
]
REGEXP_REPLACE(source_char, pattern
[, replace_string
[, position
[, occurrence
[, match_param ]
]
]
]
)
REGEXP_SUBSTR(source_char, pattern
[, position
[, occurrence
[, match_param
[, subexpr
]
]
]
]
)
REGR_AVGX, REGR_AVGY, REGR_COUNT, REGR_INTERCEPT, REGR_R2, REGR_SLOPE, REGR_SXX, REGR_SXY, REGR_SYY
{ REGR_SLOPE
| REGR_INTERCEPT
| REGR_COUNT
| REGR_R2
| REGR_AVGX
| REGR_AVGY
| REGR_SXX
| REGR_SYY
| REGR_SXY
}
(expr1 , expr2)
[ OVER (analytic_clause) ]
REMAINDER(n2, n1)
REPLACE(char, search_string
[, replacement_string ]
)
ROUND(date [, fmt ])
ROUND(n [, integer ])
ROW_NUMBER( ) OVER ([ query_partition_clause ] order_by_clause)
ROWIDTOCHAR(rowid)
ROWIDTONCHAR(rowid)
RPAD(expr1 , n [, expr2 ])
RTRIM(char [, set ])
SCN_TO_TIMESTAMP(number)
SESSIONTIMEZONE
SET (nested_table)
SIGN(n)
SIN(n)
SINH(n)
SOUNDEX(char)
SQRT(n)
STATS_BINOMIAL_TEST(expr1, expr2, p
[, { TWO_SIDED_PROB
| EXACT_PROB
| ONE_SIDED_PROB_OR_MORE
| ONE_SIDED_PROB_OR_LESS
}
]
)
STATS_CROSSTAB(expr1, expr2
[, { CHISQ_OBS
| CHISQ_SIG
| CHISQ_DF
| PHI_COEFFICIENT
| CRAMERS_V
| CONT_COEFFICIENT
| COHENS_K
}
]
)
STATS_F_TEST(expr1, expr2
[, { { STATISTIC
| DF_NUM
| DF_DEN
| ONE_SIDED_SIG
} , expr3
| TWO_SIDED_SIG
}
]
)
STATS_KS_TEST(expr1, expr2
[, { STATISTIC | SIG } ]
)
STATS_MODE(expr)
STATS_MW_TEST(expr1, expr2
[, { STATISTIC
| U_STATISTIC
| ONE_SIDED_SIG , expr3
| TWO_SIDED_SIG
}
]
)
STATS_ONE_WAY_ANOVA(expr1, expr2
[, { SUM_SQUARES_BETWEEN
| SUM_SQUARES_WITHIN
| DF_BETWEEN
| DF_WITHIN
| MEAN_SQUARES_BETWEEN
| MEAN_SQUARES_WITHIN
| F_RATIO
| SIG
}
]
)
STATS_T_TEST_INDEP, STATS_T_TEST_INDEPU, STATS_T_TEST_ONE, STATS_T_TEST_PAIRED
{
STATS_T_TEST_ONE ( expr1 [, expr2 ]
|
{ { STATS_T_TEST_PAIRED
| STATS_T_TEST_INDEP
| STATS_T_TEST_INDEPU
} ( expr1, expr2
}
}
[, { { STATISTIC | ONE_SIDED_SIG } , expr3 | TWO_SIDED_SIG | DF } ] )
STATS_WSR_TEST(expr1, expr2
[, { STATISTIC
| ONE_SIDED_SIG
| TWO_SIDED_SIG
}
]
)
STDDEV([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
STDDEV_POP(expr) [ OVER (analytic_clause) ]
STDDEV_SAMP(expr) [ OVER (analytic_clause) ]
{ SUBSTR
| SUBSTRB
| SUBSTRC
| SUBSTR2
| SUBSTR4
}
(char, position [, substring_length ])
SUM([ DISTINCT | ALL ] expr) [ OVER (analytic_clause) ]
SYS_CONNECT_BY_PATH(column, char)
SYS_CONTEXT('namespace', 'parameter' [, length ])
SYS_DBURIGEN({ column | attribute }
[ rowid ]
[, { column | attribute }
[ rowid ]
]...
[, 'text ( )' ]
)
SYS_EXTRACT_UTC(datetime_with_timezone)
SYS_GUID( )
SYS_TYPEID(object_type_value)
SYS_XMLAGG(expr [, fmt ])
SYS_XMLGEN(expr [, fmt ])
SYSDATE
SYSTIMESTAMP
TAN(n)
TANH(n)
TIMESTAMP_TO_SCN(timestamp)
TO_BINARY_DOUBLE(expr [, fmt [, 'nlsparam' ] ])
TO_BINARY_FLOAT(expr [, fmt [, 'nlsparam' ] ])
TO_BLOB ( raw_value )
TO_CHAR(nchar | clob | nclob)
TO_CHAR({ datetime | interval } [, fmt [, 'nlsparam' ] ])
TO_CHAR(n [, fmt [, 'nlsparam' ] ])
TO_CLOB(lob_column | char)
TO_DATE(char [, fmt [, 'nlsparam' ] ])
TO_DSINTERVAL ( ' { sql_format | ds_iso_format } ' )
TO_LOB(long_column)
TO_MULTI_BYTE(char)
TO_NCHAR({char | clob | nclob})
TO_NCHAR({ datetime | interval }
[, fmt [, 'nlsparam' ] ]
)
TO_NCHAR(n [, fmt [, 'nlsparam' ] ])
TO_NCLOB(lob_column | char)
TO_NUMBER(expr [, fmt [, 'nlsparam' ] ])
TO_SINGLE_BYTE(char)
TO_TIMESTAMP(char [, fmt [, 'nlsparam' ] ])
TO_TIMESTAMP_TZ(char [, fmt [, 'nlsparam' ] ])
TO_YMINTERVAL
( ' { [+|-] years - months
| ym_iso_format
} ' )
TRANSLATE(expr, from_string, to_string)
TRANSLATE ( char USING
{ CHAR_CS | NCHAR_CS }
)
TREAT(expr AS [ REF ] [ schema. ]type)
TRIM([ { { LEADING | TRAILING | BOTH }
[ trim_character ]
| trim_character
}
FROM
]
trim_source
)
TRUNC(date [, fmt ])
TRUNC(n1 [, n2 ])
TZ_OFFSET({ 'time_zone_name'
| '{ + | - } hh : mi'
| SESSIONTIMEZONE
| DBTMEZONE
}
)
UID
UNISTR( string )
UPDATEXML
(XMLType_instance,
XPath_string, value_expr
[, XPath_string, value_expr ]...
[, namespace_string ]
)
UPPER(char)
USER
[ schema. ]
{ [ package. ]function | user_defined_operator }
[ @ dblink. ]
[ ( [ [ DISTINCT | ALL ] expr [, expr ]... ] ) ]
USERENV('parameter')
VALUE(correlation_variable)
VAR_POP(expr) [ OVER (analytic_clause) ]
VAR_SAMP(expr) [ OVER (analytic_clause) ]
VARIANCE([ DISTINCT | ALL ] expr)
[ OVER (analytic_clause) ]
VSIZE(expr)
WIDTH_BUCKET (expr, min_value, max_value, num_buckets)
XMLAGG(XMLType_instance [ order_by_clause ])
XMLCAST ( value_expression AS datatype )
XMLCDATA ( value_expr )
XMLCOLATTVAL
(value_expr [ AS { c_alias | EVALNAME value_expr } ]
[, value_expr [ AS { c_alias | EVALNAME value_expr } ]
]...
)
XMLCOMMENT ( value_expr )
XMLCONCAT(XMLType_instance [, XMLType_instance ]...)
XMLDIFF ( XMLType_document, XMLType_document [ , integer, string ] )
XMLELEMENT
( [ ENTITYESCAPING | NOENTITYESCAPING ]
[ NAME ]
{ identifier
| EVALNAME value_expr
}
[, XML_attributes_clause ]
[, value_expr [ [AS] c_alias ]]...
)
XMLEXISTS ( XQuery_string [ XML_passing_clause ] )
XMLFOREST
( value_expr [ AS { c_alias | EVALNAME value_expr } ]
[, value_expr [ AS { c_alias | EVALNAME value_expr } ]
]...
)
XMLISVALID ( XMLType_instance [, XMLSchema_URL [, element ]] )
XMLPARSE
({ DOCUMENT | CONTENT } value_expr [ WELLFORMED ]
)
XMLPATCH ( XMLType_document, XMLType_document )
XMLPI
( { [ NAME ] identifier
| EVALNAME value_expr
} [, value_expr ]
)
XMLQUERY ( XQuery_string [ XML_passing_clause ] RETURNING CONTENT [NULL ON EMPTY] )
XMLROOT
( value_expr, VERSION
{ value_expr | NO VALUE }
[, STANDALONE { YES | NO | NO VALUE } ]
)
XMLSEQUENCE( XMLType_instance
| sys_refcursor_instance [, fmt ]
)
XMLSERIALIZE
( { DOCUMENT | CONTENT } value_expr [ AS datatype ]
[ ENCODING xml_encoding_spec ]
[ VERSION string_literal ]
[ NO INDENT | { INDENT [SIZE = number] } ]
[ { HIDE | SHOW } DEFAULTS ]
)
XMLTABLE ( [ XMLnamespaces_clause , ] XQuery_string XMLTABLE_options )
XMLTRANSFORM(XMLType_instance, { XMLType_instance
| string
}
)
This reference contains a complete description of the Structured Query Language (SQL) used to manage information in an Oracle Database. Oracle SQL is a superset of the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO) SQL:1999 standard.
This Preface contains these topics:
The Oracle Database SQL Language Quick Reference is intended for all users of Oracle SQL.
For information about Oracle's commitment to accessibility, visit the Oracle Accessibility Program website at http://www.oracle.com/pls/topic/lookup?ctx=acc&id=docacc.
Access to Oracle Support
Oracle customers have access to electronic support through My Oracle Support. For information, visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info or visit http://www.oracle.com/pls/topic/lookup?ctx=acc&id=trs if you are hearing impaired.
For more information, see these Oracle resources:
Oracle Database PL/SQL Language Reference for information on PL/SQL, the procedural language extension to Oracle SQL
Pro*C/C++ Programmer's Guide, Oracle SQL*Module for Ada Programmer's Guide, and the Pro*COBOL Programmer's Guide for detailed descriptions of Oracle embedded SQL
Many of the examples in this book use the sample schemas, which are installed by default when you select the Basic Installation option with an Oracle Database installation. Refer to Oracle Database Sample Schemas for information on how these schemas were created and how you can use them yourself.
The following text conventions are used in this document:
| Convention | Meaning |
|---|---|
| boldface | Boldface type indicates graphical user interface elements associated with an action, or terms defined in text or the glossary. |
| italic | Italic type indicates book titles, emphasis, or placeholder variables for which you supply particular values. |
monospace | Monospace type indicates commands within a paragraph, URLs, code in examples, text that appears on the screen, or text that you enter. |
This appendix presents many of the SQL*Plus commands.
This appendix includes the following section:
An expression is a combination of one or more values, operators, and SQL functions that evaluate to a value. An expression generally assumes the data type of its components.
Expressions have several forms. The sections that follow show the syntax for each form of expression. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses.
|
See Also: Expressions in Oracle Database SQL Language Reference for detailed information about SQL expressions |
CASE { simple_case_expression
| searched_case_expression
}
[ else_clause ]
END
A column expression can be a simple expression, compound expression, function expression, or expression list, containing only columns of the subject table, constants, and deterministic functions.
{ (expr)
| { + | - | PRIOR } expr
| expr { * | / | + | - | || } expr
}
Note: The double vertical bars are part of the syntax
(indicating concatenation) rather than BNF notation.
CURSOR (subquery)
expr AT
{ LOCAL
| TIME ZONE { ' [ + | - ] hh:mm'
| DBTIMEZONE
| 'time_zone_name'
| expr
}
}
You can use any built-in SQL function or user-defined function as an expression.
( expr1 - expr2 )
{ DAY [ (leading_field_precision) ] TO
SECOND [ (fractional_second_precision) ]
| YEAR [ (leading_field_precision) ] TO
MONTH
}
{ measure_column [ { condition | expr }[ , { condition | expr } ...] ]
| aggregate_function
{ [ { condition | expr }[ , { condition | expr } ...] ]
| [ single_column_for_loop [, single_column_for_loop] ... ]
| [ multi_column_for_loop ]
}
| analytic_function
}
|
Note: The outside square brackets shown in boldface type are part of the syntax. In this case, they do not represent optionality. |
{ table_alias.column.
| object_table_alias.
| (expr).
}
{ attribute [.attribute ]...
[.method ([ argument [, argument ]... ]) ]
| method ([ argument [, argument ]... ])
}
:host_variable
[ [ INDICATOR ]
:indicator_variable
]
A scalar subquery expression is a subquery that returns exactly one column value from one row.
{ [ query_name.
| [schema.]
{ table. | view. | materialized view. }
] { column | ROWID }
| ROWNUM
| string
| number
| sequence. { CURRVAL | NEXTVAL }
| NULL
}
[ NEW ] [ schema. ]type_name ([ expr [, expr ]... ])
This chapter presents the syntax for Oracle SQL statements.
This chapter includes the following section:
This chapter presents the syntax for combining one or more expressions and logical (Boolean) operators to specify a condition.
This chapter includes the following section:
This chapter presents the format models for datetime and number data stored in character strings.
This chapter includes the following sections:
The sections that follow show the syntax for each subclause found in:
ACTIVATE
[ PHYSICAL | LOGICAL ]
STANDBY DATABASE
[ FINISH APPLY ]
ADD BINDING (parameter_type [, parameter_type ]...) RETURN (return_type) [ implementation_clause ] using_function_clause
ADD
{column_definition | virtual_column_definition
[, column_definition | virtual_column_definition] ...
}
[ column_properties ]
[ out_of_line_part_storage [, out_of_line_part_storage]...]
ADD
{ [ QUORUM | REGULAR ] [ FAILGROUP failgroup_name ]
DISK qualified_disk_clause [, qualified_disk_clause ]...
}...
ADD PARTITION [ partition_name ] [ TABLESPACE tablespace_name ] [ key_compression ] [ parallel_clause ]
ADD PARTITION [ partition ] partitioning_storage_clause [ update_index_clauses ] [ parallel_clause ]
ADD hash_subpartition_desc [ dependent_tables_clause ] [ update_index_clauses ] [ parallel_clause ]
ADD PARTITION [ partition ]
list_values_clause
[ table_partition_description ]
[ { range_subpartition_desc
| list_subpartition_desc
| hash_subpartition_desc
}
]
[ update_index_clauses ]
ADD list_subpartition_desc [ dependent_tables_clause ] [ update_index_clauses ]
ADD [ STANDBY ] LOGFILE
{
{ [ INSTANCE 'instance_name' ] | [ THREAD 'integer' ] }
[ GROUP integer ] redo_log_file_spec
[, [ GROUP integer ] redo_log_file_spec ]...
| MEMBER 'filename' [ REUSE ] [, 'filename' [ REUSE ] ]...
TO logfile_descriptor [, logfile_descriptor ]...
}
ADD (column)
ADD OVERFLOW [ segment_attributes_clause ]
[ ( PARTITION [ segment_attributes_clause ]
[, PARTITION [ segment_attributes_clause ] ]...
)
]
ADD PARTITION [ partition ]
range_values_clause
[ table_partition_description ]
[ ( { range_subpartition_desc
| list_subpartition_desc
| hash_subpartition_desc
}
)
]
[ update_index_clauses ]
ADD range_subpartition_desc [ dependent_tables_clause ] [ update_index_clauses ]
[BEFORE { partition_name | partition_number }]
[table_partition_description]
[update_index_clauses]
{ add_range_partition_clause
| add_hash_partition_clause
| add_list_partition_clause
} [ dependent_tables_clause ]
ADD VOLUME asm_volume SIZE size_clause [redundancy_clause]
[ STRIPE_WIDTH integer {K | M} ]
[ STRIPE_COLUMNS integer ]
[ ATTRIBUTE disk_region_clause ]
+diskgroup_name [ (template_name) ] /alias_name
ALLOCATE EXTENT
[ ( { SIZE size_clause
| DATAFILE 'filename'
| INSTANCE integer
} ...
)
]
DATAFILE
{ 'filename' | filenumber }
[, 'filename' | filenumber ]...
}
{ ONLINE
| OFFLINE [ FOR DROP ]
| RESIZE size_clause
| autoextend_clause
| END BACKUP
}
{ add_column_clause
| modify_column_clauses
| drop_column_clause
| parallel_clause
| external_data_properties
| REJECT LIMIT { integer | UNLIMITED }
| PROJECT COLUMN { ALL | REFERENCED }
}
[ add_column_clause
| modify_column_clauses
| drop_column_clause
| parallel_clause
| external_data_properties
| REJECT LIMIT { integer | UNLIMITED }
| PROJECT COLUMN { ALL | REFERENCED }
]...
{ modify_index_default_attrs
| add_hash_index_partition
| modify_index_partition
| rename_index_partition
| drop_index_partition
| split_index_partition
| coalesce_index_partition
| modify_index_subpartition
}
{ SET INTERVAL ( [ expr ] )
| SET STORE IN ( tablespace [, tablespace]... )
}
{ index_org_table_clause
| alter_overflow_clause
| alter_mapping_table_clauses
| COALESCE
}
MAPPING TABLE
{ allocate_extent_clause
| deallocate_unused_clause
}
REFRESH
{ { FAST | COMPLETE | FORCE }
| ON { DEMAND | COMMIT }
| { START WITH | NEXT } date
| WITH PRIMARY KEY
| USING
{ DEFAULT MASTER ROLLBACK SEGMENT
| MASTER ROLLBACK SEGMENT rollback_segment
}
| USING { ENFORCED | TRUSTED } CONSTRAINTS
}
{ add_overflow_clause
| OVERFLOW
{ segment_attributes_clause
| allocate_extent_clause
| shrink_clause
| deallocate_unused_clause
}...
}
SET { { parameter_name = parameter_value }...
| EDITION = edition_name
}
parameter_name
[ { SCOPE = SPFILE
| SID = { 'sid' | '*' }
}...
]
{ set_parameter_clause
| USE_STORED_OUTLINES = (TRUE | FALSE | category_name)
| GLOBAL_TOPIC_ENABLED = (TRUE | FALSE)
}
{ modify_table_default_attrs
| alter_interval_partitioning
| set_subpartition_template
| modify_table_partition
| modify_table_subpartition
| move_table_partition
| move_table_subpartition
| add_table_partition
| coalesce_table_partition
| coalesce_table_subpartition
| drop_table_partition
| drop_table_subpartition
| rename_partition_subpart
| truncate_partition_subpart
| split_table_partition
| split_table_subpartition
| merge_table_partitions
| merge_table_subpartitions
| exchange_partition_subpart
}
{ { { physical_attributes_clause
| logging_clause
| table_compression
| supplemental_table_logging
| allocate_extent_clause
| deallocate_unused_clause
| { CACHE | NOCACHE }
| RESULT_CACHE ( MODE {DEFAULT | FORCE} )
| upgrade_table_clause
| records_per_block_clause
| parallel_clause
| row_movement_clause
| flashback_archive_clause
}...
| RENAME TO new_table_name
} [ alter_iot_clauses ] [ alter_XMLSchema_clause ]
| { shrink_clause
| READ ONLY
| READ WRITE
| REKEY encryption_spec
}
}
TEMPFILE
{ 'filename' [, 'filename' ]...
| filenumber [, filenumber ]...
}
{ RESIZE size_clause
| autoextend_clause
| DROP [ INCLUDING DATAFILES ]
| ONLINE
| OFFLINE
}
MODIFY VARRAY varray_item ( modify_LOB_parameters )
{ ALLOW ANYSCHEMA
| ALLOW NONSCHEMA
| DISALLOW NONSCHEMA
}
[ query_partition_clause ] [ order_by_clause [ windowing_clause ] ]
ARCHIVE LOG
[ INSTANCE 'instance_name' ]
{ { SEQUENCE integer
| CHANGE integer
| CURRENT [ NOSWITCH ]
| GROUP integer
| LOGFILE 'filename'
[ USING BACKUP CONTROLFILE ]
| NEXT
| ALL
}
[ TO 'location' ]
}
[ WITH | WITHOUT ]
ARRAY DML
[ ([ schema. ]type
[, [ schema. ]varray_type ])
[, ([ schema. ]type
[, [ schema. ]varray_type ])...
]
{ fully_qualified_file_name
| numeric_file_name
| incomplete_file_name
| alias_file_name
}
ATTRIBUTE level DETERMINES
{ dependent_column
| ( dependent_column
[, dependent_column ]... )
}
{ { sql_statement_shortcut
| ALL
| ALL STATEMENTS
} [, { sql_statement_shortcut
| ALL
}
]
| { system_privilege
| ALL PRIVILEGES
} [, { system_privilege
| ALL PRIVILEGES
}
]
}
{ sql_operation [, object_option]
| ALL
} auditing_on_clause
BY user [, user ]...
ON { [ schema. ] object
| DIRECTORY directory_name
| MINING MODEL [ schema. ] model
| DEFAULT
}
AUTOEXTEND
{ OFF
| ON [ NEXT size_clause ]
[ maxsize_clause ]
}
BINDING
(parameter_type [, parameter_type ]...)
RETURN return_type
[ implementation_clause ]
using_function_clause
[, (parameter_type [, parameter_type ]...)
RETURN return_type
[ implementation_clause ]
using_function_clause
]...
[ schema.]table
( [ [ schema. ]table. | t_alias. ]column
[ ASC | DESC ]
[, [ [ schema. ]table. | t_alias. ]column
[ ASC | DESC ]
]...
)
FROM [ schema. ]table [ t_alias ]
[, [ schema. ]table [ t_alias ]
]...
WHERE condition
[ local_partitioned_index ] index_attributes
BUILD { IMMEDIATE | DEFERRED }
measure_column [ { { condition
| expr
| single_column_for_loop
}
[, { condition
| expr
| single_column_for_loop
}
]...
| multi_column_for_loop
}
]
Note: The outer square brackets are part of the syntax.
In this case, they do not indicate optionality.
[ { IGNORE | KEEP } NAV ]
[ UNIQUE { DIMENSION | SINGLE REFERENCE } ]
CHARACTER SET character_set
CHECK DATAFILES [ GLOBAL | LOCAL ]
CHECK [ REPAIR | NOREPAIR ]
CHECKPOINT [ GLOBAL | LOCAL ]
CLUSTER [ schema. ] cluster index_attributes
COALESCE PARTITION [ parallel_clause ]
COALESCE PARTITION [ update_index_clauses ] [ parallel_clause ]
COALESCE SUBPARTITION subpartition [update_index_clauses] [parallel_clause]
COLUMNS [ schema. ]table.column
[, [ schema. ]table.column ]...
using_statistics_type
{ { add_column_clause
| modify_column_clause
| drop_column_clause
}...
| rename_column_clause
| { modify_collection_retrieval }...
| { modify_LOB_storage_clause }...
| { alter_varray_col_properties }...
}
column datatype [ SORT ]
[ DEFAULT expr ]
[ ENCRYPT encryption_spec ]
[ ( { inline_constraint }... )
| inline_ref_constraint
]
{ object_type_col_properties
| nested_table_col_properties
| { varray_col_properties | LOB_storage_clause }
[ (LOB_partition_storage [, LOB_partition_storage ]...) ]
| XMLType_column_properties
}...
{ PREPARE | COMMIT } TO SWITCHOVER
[ TO { { [ PHYSICAL | LOGICAL ] PRIMARY
| [ PHYSICAL ] STANDBY
} [ { WITH | WITHOUT } SESSION SHUTDOWN
{ WAIT | NOWAIT }
]
| LOGICAL STANDBY
}
| CANCEL
]
PARTITION BY HASH (column [, column ] ...)
{ subpartition_by_range
| subpartition_by_hash
| subpartition_by_list
}
{ individual_hash_partitions
| hash_partitions_by_quantity
}
PARTITION BY LIST ( column )
{ subpartition_by_range
| subpartition_by_hash
| subpartition_by_list
}
( { PARTITION [partition] list_partition_desc }... )
PARTITION BY RANGE ( column [, column]... )
[ INTERVAL ( expr ) [ STORE IN ( tablespace [, tablespace]... ) ]]
{ subpartition_by_range
| subpartition_by_list
| subpartition_by_hash
}
( { PARTITION [partition] range_partition_desc }... )
[ ALL | FIRST ]
WHEN condition
THEN insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
[ WHEN condition
THEN insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
]...
[ ELSE insert_into_clause
[ values_clause ]
[ error_logging_clause ]
[ insert_into_clause [ values_clause ] [ error_logging_clause ] ]...
]
{ inline_constraint
| out_of_line_constraint
| inline_ref_constraint
| out_of_line_ref_constraint
}
{ ADD { { out_of_line_constraint }...
| out_of_line_REF_constraint
}
| MODIFY { CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE (column [, column ]...)
} constraint_state
| RENAME CONSTRAINT old_name TO new_name
| drop_constraint_clause
}
[ [ [ NOT ] DEFERRABLE ]
[ INITIALLY { IMMEDIATE | DEFERRED } ]
| [ INITIALLY { IMMEDIATE | DEFERRED } ]
[ [ NOT ] DEFERRABLE ]
]
[ RELY | NORELY ]
[ using_index_clause ]
[ ENABLE | DISABLE ]
[ VALIDATE | NOVALIDATE ]
[ exceptions_clause ]
[ WITH INDEX CONTEXT, SCAN CONTEXT implementation_type [ COMPUTE ANCILLARY DATA ] ] [ WITH COLUMN CONTEXT ]
{ CREATE [ LOGICAL | PHYSICAL ]
STANDBY CONTROLFILE AS
'filename' [ REUSE ]
| BACKUP CONTROLFILE TO
{ 'filename' [ REUSE ]
| trace_file_clause
}
}
CONVERT TO ( PHYSICAL | SNAPSHOT ) STANDBY
COST
{ MODEL [AUTO]
| ( class_value [, class_value]... )
VALUES ( ( cost_value [, cost_value]...)
[ , (cost_value [, cost_value]... ) ]...
)
}
CREATE DATAFILE
{ 'filename' | filenumber }
[, 'filename' | filenumber ]...
}
[ AS { file_specification
[, file_specification ]...
| NEW
}
]
{ REFRESH
{ { FAST | COMPLETE | FORCE }
| { ON DEMAND
| ON COMMIT
}
| { START WITH date |
NEXT date
}...
| WITH { PRIMARY KEY | ROWID }
| USING
{ DEFAULT [ MASTER | LOCAL ] ROLLBACK SEGMENT
| [ MASTER | LOCAL ] ROLLBACK SEGMENT rollback_segment
}...
| USING
{ ENFORCED | TRUSTED } CONSTRAINTS
}...
| NEVER REFRESH
}
{CYCLE c_alias [, c_alias]...
SET cycle_mark_c_alias TO cycle_value
DEFAULT no_cycle_value
}
{ RENAME FILE 'filename' [, 'filename' ]...
TO 'filename'
| create_datafile_clause
| alter_datafile_clause
| alter_tempfile_clause
}
{ LOGFILE
[ GROUP integer ] file_specification
[, [ GROUP integer ] file_specification ]...
| MAXLOGFILES integer
| MAXLOGMEMBERS integer
| MAXLOGHISTORY integer
| { ARCHIVELOG | NOARCHIVELOG }
| FORCE LOGGING
}
{ ADD { DATAFILE | TEMPFILE }
[ file_specification [, file_specification ]... ]
| DROP {DATAFILE | TEMPFILE } { 'filename' | file_number }
| SHRINK TEMPFILE { 'filename' | file_number } [KEEP size_clause]
| RENAME DATAFILE 'filename' [, 'filename' ]...
TO 'filename' [, 'filename' ]...
| { DATAFILE | TEMPFILE } { ONLINE | OFFLINE }
}
[ 'filename' | 'ASM_filename' ] [ SIZE size_clause ] [ REUSE ] [ autoextend_clause ]
[ WITH
{ ROLE { role_name [, role_name]...
| ALL EXCEPT role_name [, role_name]...
}
| NO ROLES
}
]
[ AUTHENTICATION REQUIRED ]
database[.domain [.domain ]... ] [ @ connection_qualifier ]
AUTHENTICATED BY user IDENTIFIED BY password
DEALLOCATE UNUSED [ KEEP size_clause ]
DEFAULT COST (cpu_cost, io_cost, network_cost)
DEFAULT SELECTIVITY default_selectivity
{ DEFAULT EDITION = edition_name
| SET DEFAULT
{ BIGFILE | SMALLFILE } TABLESPACE
| DEFAULT TABLESPACE tablespace
| DEFAULT TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| RENAME GLOBAL_NAME TO
database.domain [.domain ]...
| { ENABLE BLOCK CHANGE TRACKING
[ USING FILE 'filename' [ REUSE ] ]
| DISABLE BLOCK CHANGE TRACKING
}
| flashback_mode_clause
| set_time_zone_clause
}
DEFAULT TABLESPACE tablespace [ DATAFILE datafile_tempfile_spec ] extent_management_clause
[ BIGFILE | SMALLFILE ] DEFAULT TEMPORARY TABLESPACE tablespace [ TEMPFILE file_specification [, file_specification ]...] extent_management_clause
SEGMENT CREATION { IMMEDIATE | DEFERRED }
DEPENDENT TABLES
( table ( partition_spec [, partition_spec]...
[, table ( partition_spec [, partition_spec]... ]
)
)
{ JOIN KEY
{ child_key_column
| (child_key_column [, child_key_column ]...)
}
REFERENCES parent_level
}...
OFFLINE
{ [QUORUM | REGULAR] DISK disk_name [, disk_name ] ...
| DISKS IN [QUORUM | REGULAR] FAILGROUP failgroup_name [, failgroup_name ]...
} ... [timeout_clause]
ONLINE
{ { [QUORUM | REGULAR] DISK disk_name [, disk_name]...
| DISKS IN [QUORUM | REGULAR] FAILGROUP failgroup_name [, failgroup_name]...
} ...
| ALL
} [ WAIT | NOWAIT ]
[ HOT | COLD ] [ MIRRORHOT | MIRRORCOLD ]
{ ADD ALIAS
'alias_name' FOR 'filename'
[, 'alias_name' FOR 'filename' ]...
| DROP ALIAS 'alias_name' [, 'alias_name' ]...
| RENAME ALIAS
'old_alias_name' TO 'new_alias_name'
[, 'old_alias_name' TO 'new_alias_name' ]...
}
SET ATTRIBUTE 'attribute_name' = 'attribute_value'
{ MOUNT [ RESTRICTED | NORMAL ]
[ FORCE | NOFORCE ]
| DISMOUNT [ FORCE | NOFORCE ]
}
{ ADD DIRECTORY 'filename' [, 'filename' ]...
| DROP DIRECTORY
'filename' [ FORCE | NOFORCE ]
[, 'filename' [ FORCE | NOFORCE ] ]...
| RENAME DIRECTORY
'old_dir_name' TO 'new_dir_name'
[, 'old_dir_name' TO 'new_dir_name' ]...
}
{ { ADD | MODIFY } TEMPLATE template_name qualified_template_clause
[, template_name qualified_template_clause ]...
| DROP €ÿ TEMPLATE template_name [, template_name ]...
}
{ add_volume_clause
| modify_volume_clause
| RESIZE VOLUME asm_volume SIZE size_clause
| DROP VOLUME asm_volume
}
{ ENABLE | DISABLE } DISTRIBUTED RECOVERY
{ [ schema. ]
{ table
[ partition_extension_clause
| @ dblink
]
| { view | materialized view } [ @ dblink ]
}
| ( subquery [ subquery_restriction_clause ] )
| table_collection_expression
}
indextype
[ local_domain_index_clause ]
[ parallel_clause ]
[ PARAMETERS ('ODCI_parameters') ]
DROP BINDING (parameter_type [, parameter_type ]...) [ FORCE ]
{ SET UNUSED { COLUMN column
| (column [, column ]...)
}
[ { CASCADE CONSTRAINTS | INVALIDATE }... ]
| DROP { COLUMN column
| (column [, column ]...)
}
[ { CASCADE CONSTRAINTS | INVALIDATE }... ]
[ CHECKPOINT integer ]
| DROP { UNUSED COLUMNS
| COLUMNS CONTINUE
}
[ CHECKPOINT integer ]
}
DROP
{ { PRIMARY KEY
| UNIQUE (column [, column ]...)
}
[ CASCADE ]
[ { KEEP | DROP } INDEX ]
| CONSTRAINT constraint
[ CASCADE ]
}
DROP
{ [QUORUM | REGULAR] DISK
disk_name [ FORCE | NOFORCE ]
[, disk_name [ FORCE | NOFORCE ] ]...
| DISKS IN [QUORUM | REGULAR] FAILGROUP
failgroup_name [ FORCE | NOFORCE ]
[, failgroup_name [ FORCE | NOFORCE ] ]...
}
DROP FILE 'filename' [, 'filename' ]...
DROP PARTITION partition_name
DROP [ STANDBY ] LOGFILE
{ logfile_descriptor
[, logfile_descriptor ]...
| MEMBER 'filename'
[, 'filename' ]...
}
DROP partition_extended_name [ update_index_clauses [ parallel_clause ] ]
DROP subpartition_extended_name [ update_index_clauses [ parallel_clause ] ]
[-] P [days D] [T [hours H] [minutes M] [seconds [. frac_secs] S ] ]
ELSE else_expr
{ ENABLE | DISABLE }
[ VALIDATE | NOVALIDATE ]
{ UNIQUE (column [, column ]...)
| PRIMARY KEY
| CONSTRAINT constraint
}
[ using_index_clause ]
[ exceptions_clause ]
[ CASCADE ]
[ { KEEP | DROP } INDEX ]
{ ENABLE | DISABLE } VOLUME
{ asm_volume [, asm_volume]...
| ALL
}
[ USING 'encrypt_algorithm' ] [ IDENTIFIED BY password ] [ 'integrity_algorithm' ] [ [ NO ] SALT ]
{ DISCONNECT SESSION 'integer1, integer2'
[ POST_TRANSACTION ]
| KILL SESSION 'integer1, integer2 [, @integer3]'
}
[ IMMEDIATE ]
LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED } ]
EXCEPTIONS INTO [ schema. ] table
EXCHANGE { partition_extended_name
| subpartition_extended_name
}
WITH TABLE [ schema. ] table
[ { INCLUDING | EXCLUDING } INDEXES ]
[ { WITH | WITHOUT } VALIDATION ]
[ exceptions_clause ]
[ update_index_clauses [ parallel_clause ] ]
{ simple_expression
| compound_expression
| case_expression
| cursor_expression
| datetime_expression
| function_expression
| interval_expression
| object_access_expression
| scalar_subquery_expression
| model_expression
| type_constructor_expression
| variable_expression
}
{ expr [, expr ]...
| ( [expr [, expr ]] ...)
}
ATTRIBUTE attribute
{ LEVEL level
DETERMINES { dependent_column
| (dependent_column [, dependent_column ]... )
}
}...
EXTENT MANAGEMENT LOCAL [ AUTOALLOCATE | UNIFORM [ SIZE size_clause ] ]
DEFAULT DIRECTORY directory
[ ACCESS PARAMETERS
{ (opaque_format_spec)
| USING CLOB subquery
}
]
LOCATION
([ directory: ] 'location_specifier'
[, [ directory: ] 'location_specifier' ]...
)
([ TYPE access_driver_type ]
external_data_properties
)
[ REJECT LIMIT { integer | UNLIMITED } ]
SET OWNERSHIP { OWNER = user | GROUP = usergroup
[, OWNER = user | GROUP = usergroup ]...
} FOR FILE 'filename' [, 'filename']...
SET PERMISSION { OWNER | GROUP | OTHER }
= { NONE | READ ONLY | READ WRITE }
[, { OWNER | GROUP | OTHER | ALL }
= { NONE | READ ONLY | READ WRITE } ]...
FOR FILE 'filename' [, 'filename']...
{ datafile_tempfile_spec
| redo_log_file_spec
}
FLASHBACK ARCHIVE [flashback_archive] | NO FLASHBACK ARCHIVE
QUOTA integer { M | G | T | P | E }
RETENTION integer {YEAR | MONTH | DAY}
FLASHBACK { ON | OFF }
{ VERSIONS BETWEEN
{ SCN | TIMESTAMP }
{ expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
}
FOR UPDATE
[ OF [ [ schema. ] { table | view } . ] column
[, [ [ schema. ] { table | view } . ] column
]...
]
[ { NOWAIT | WAIT integer
| SKIP LOCKED
}
]
[ STANDBY ] DATABASE
[ { UNTIL { CANCEL
| TIME date
| CHANGE integer
| CONSISTENT
}
| USING BACKUP CONTROLFILE
}...
]
+diskgroup_name/db_name/file_type/ file_type_tag.filenumber.incarnation_number
{ FUNCTIONS
[ schema. ]function [, [ schema. ]function ]...
| PACKAGES
[ schema. ]package [, [ schema. ]package ]...
| TYPES
[ schema. ]type [, [ schema. ]type ]...
| INDEXES
[ schema. ]index [, [ schema. ]index ]...
| INDEXTYPES
[ schema. ]indextype [, [ schema. ]indextype ]...
}
{ using_statistics_type
| { default_cost_clause [, default_selectivity_clause ]
| default_selectivity_clause [, default_cost_clause ]
}
}
RECOVER
[ AUTOMATIC ]
[ FROM 'location' ]
{ { full_database_recovery
| partial_database_recovery
| LOGFILE 'filename'
}
[ { TEST
| ALLOW integer CORRUPTION
| parallel_clause
}...
]
| CONTINUE [ DEFAULT ]
| CANCEL
}
GLOBAL PARTITION BY
{ RANGE (column_list)
(index_partitioning_clause)
| HASH (column_list)
{ individual_hash_partitions
| hash_partitions_by_quantity
}
}
{ object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
[, { object_privilege | ALL [ PRIVILEGES ] }
[ (column [, column ]...) ]
]...
on_object_clause
TO grantee_clause
[ WITH HIERARCHY OPTION ]
[ WITH GRANT OPTION ]
{ system_privilege
| role
| ALL PRIVILEGES
}
[, { system_privilege
| role
| ALL PRIVILEGES
}
]...
TO grantee_clause
[ WITH ADMIN OPTION ]
{ user [ IDENTIFIED BY password ]
| role
| PUBLIC
}
[, { user [ IDENTIFIED BY password ]
| role
| PUBLIC
}
]...
GROUP BY
{ expr
| rollup_cube_clause
| grouping_sets_clause
}
[, { expr
| rollup_cube_clause
| grouping_sets_clause
}
]...
[ HAVING condition ]
expression_list [, expression_list ]...
GROUPING SETS
({ rollup_cube_clause | grouping_expression_list })
PARTITION BY HASH (column [, column ] ...)
{ individual_hash_partitions
| hash_partitions_by_quantity
}
PARTITIONS hash_partition_quantity [ STORE IN (tablespace [, tablespace ]...) ] [ key_compression | table_compression ] [ OVERFLOW STORE IN (tablespace [, tablespace ]...) ]
SUBPARTITIONS integer [STORE IN ( tablespace [, tablespace]... )]
{ CONNECT BY [ NOCYCLE ] condition [AND condition]... [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition [AND condition]...
}
HIERARCHY hierarchy
(child_level { CHILD OF parent_level }...
[ dimension_join_clause ]
)
{ ANCILLARY TO primary_operator
( parameter_type [, parameter_type ]...)
[, primary_operator
( parameter_type [, parameter_type ]...)
]...
| context_clause
}
+diskgroup_name [ (template_name) ]
[ { physical_attributes_clause
| logging_clause
| ONLINE
| TABLESPACE { tablespace | DEFAULT }
| key_compression
| { SORT | NOSORT }
| REVERSE
| VISIBLE | INVISIBLE
| parallel_clause
}...
]
{ column | column_expression }
[ INCLUDING column_name ] OVERFLOW [ segment_attributes_clause ]
[ { mapping_table_clause
| PCTTHRESHOLD integer
| key_compression
}...
]
[ index_org_overflow_clause ]
PARTITION
[ partition
[ { segment_attributes_clause
| key_compression
}...
| PARAMETERS ( 'ODCI_parameters' )
] [ UNUSABLE ]
]
PARTITION [ partition ] VALUES LESS THAN (literal[, literal]... ) [ segment_attributes_clause ]
[ { { global_partitioned_index
| local_partitioned_index
}
| index_attributes
}...
| INDEXTYPE IS { domain_index_clause
| XMLTable_index_clause
| XMLIndex_clause
}
]
{ STORE IN (tablespace[, tablespace ]...)
| (SUBPARTITION
[ subpartition ][ TABLESPACE tablespace ] [ key_compression ] [ UNUSABLE ]
[, SUBPARTITION
[ subpartition ][ TABLESPACE tablespace ] [ key_compression ] [ UNUSABLE ]
]...
)
}
PARTITION [partition] [partitioning_storage_clause] [, PARTITION [partition] [partitioning_storage_clause]]...
SUBPARTITION [subpartition] [partitioning_storage_clause]
[ CONSTRAINT constraint_name ]
{ [ NOT ] NULL
| UNIQUE
| PRIMARY KEY
| references_clause
| CHECK (condition)
}
[ constraint_state ]
{ SCOPE IS [ schema. ] scope_table
| WITH ROWID
| [ CONSTRAINT constraint_name ]
references_clause
[ constraint_state ]
}
{ [ INNER ] JOIN table_reference
{ ON condition
| USING (column [, column ]...)
}
| { CROSS
| NATURAL [ INNER ]
}
JOIN table_reference
}
INTO dml_table_expression_clause [ t_alias ] [ (column [, column ]...) ]
{ ENABLE | DISABLE } INSTANCE 'instance_name'
[ + | - ] digit [ digit ]...
INTERVAL '{ integer | integer time_expr | time_expr }'
{ { DAY | HOUR | MINUTE } [ (leading_precision) ]
| SECOND [ (leading_precision [, fractional_seconds_precision ]) ]
}
[ TO { DAY | HOUR | MINUTE | SECOND [ (fractional_seconds_precision) ] } ]
INTERVAL 'integer [- integer ]'
{ YEAR | MONTH } [ (precision) ] [ TO { YEAR | MONTH } ]
INTO [ schema. ] table
AUTHID { CURRENT_USER | DEFINER }
table_reference
{ inner_cross_join_clause | outer_join_clause }...
{ COMPRESS [ integer ]
| NOCOMPRESS
}
LEVEL level IS
{ level_table.level_column
| (level_table.level_column
[, level_table.level_column ]...
)
}
list_values_clause
table_partition_description
[ ( "(" ( ("range_subpartition_desc"/",")
| ("list_subpartition_desc"/",")
| ("individual_hash_subparts"/",")
)
")"
) | "hash_subparts_by_quantity"
]
PARTITION BY LIST (column)
(PARTITION [ partition ]
list_values_clause table_partition_description
[, PARTITION [ partition ]
list_values_clause table_partition_description
]...
)
SUBPARTITION [subpartition] list_values_clause [partitioning_storage_clause]
VALUES ({ literal | NULL }
[, { literal | NULL }]...
| DEFAULT
)
{ COMPRESS [HIGH | MEDIUM | LOW ]
| NOCOMPRESS
}
{ DEDUPLICATE
| KEEP_DUPLICATES
}
{ { ENABLE | DISABLE } STORAGE IN ROW
| CHUNK integer
| PCTVERSION integer
| FREEPOOLS integer
| LOB_retention_clause
| LOB_deduplicate_clause
| LOB_compression_clause
| { ENCRYPT encryption_spec | DECRYPT }
| { CACHE | NOCACHE | CACHE READS } [ logging_clause ]
}...
PARTITION partition
{ LOB_storage_clause | varray_col_properties }...
[ (SUBPARTITION subpartition
{ LOB_partitioning_storage | varray_col_properties }...
)
]
LOB (LOB_item) STORE AS [BASICFILE | SECUREFILE] [ LOB_segname [ (TABLESPACE tablespace) ] | (TABLESPACE tablespace) ]
RETENTION [ MAX | MIN integer | AUTO | NONE ]
LOB
{ (LOB_item [, LOB_item ]...)
STORE AS { {SECUREFILE | BASICFILE}
| (LOB_storage_parameters)
}...
| (LOB_item)
STORE AS { {SECUREFILE | BASICFILE}
| LOB_segname
| (LOB_storage_parameters)
}...
}
{ { TABLESPACE tablespace
| LOB_parameters [storage_clause]
}...
| storage_clauase
}
LOCAL
[ ( PARTITION partition [ PARAMETERS ( 'ODCI_parameters' ) ]
[, PARTITION partition [ PARAMETERS ('ODCI_parameters') ]]...
)
]
LOCAL [ on_range_partitioned_table | on_list_partitioned_table | on_hash_partitioned_table | on_comp_partitioned_table ]
LOCAL
[ ( PARTITION partition [ XMLIndex_parameters_clause ]
[, PARTITION partition [ XMLIndex_parameters)clause ]]...
)
]
LOGFILE [ GROUP integer ] file_specification [, [ GROUP integer ] file_specification ]...
{ { ARCHIVELOG [ MANUAL ]
| NOARCHIVELOG
}
| [ NO ] FORCE LOGGING
| RENAME FILE 'filename' [, 'filename' ]...
TO 'filename'
| CLEAR [ UNARCHIVED ]
LOGFILE logfile_descriptor [, logfile_descriptor ]...
[ UNRECOVERABLE DATAFILE ]
| add_logfile_clauses
| drop_logfile_clauses
| switch_logfile_clause
| supplemental_db_logging
}
{ GROUP integer
| ('filename' [, 'filename' ]...)
| 'filename'
}
{ LOGGING | NOLOGGING | FILESYSTEM_LIKE_LOGGING }
[ MAIN main_model_name ] model_column_clauses [ cell_reference_options ] model_rules_clause
RECOVER
{ MANAGED STANDBY DATABASE
[ { USING CURRENT LOGFILE
| DISCONNECT [FROM SESSION]
| NODELAY
| UNTIL CHANGE integer
| UNTIL CONSISTENT
| parallel_clause
}...
| FINISH
| CANCEL
]
| TO LOGICAL STANDBY { db_name | KEEP IDENTITY }
}
{ MAPPING TABLE | NOMAPPING }
[ column_properties ] [ table_partitioning_clauses ] [ CACHE | NOCACHE ] [ parallel_clause ] [ build_clause ]
SET STANDBY DATABASE TO MAXIMIZE
{ PROTECTION | AVAILABILITY | PERFORMANCE }
MAXSIZE { UNLIMITED | size_clause }
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr | DEFAULT }
[, { expr | DEFAULT } ]...
)
[ where_clause ]
MERGE PARTITIONS partition_extended_name, partition_extended_name [ INTO partition_spec ] [ dependent_tables_clause ] [ update_index_clauses ] [ parallel_clause ]
MERGE SUBPARTITIONS partition_extended_name, partition_extended_name
[ INTO { range_subpartition_desc
| list_subpartition_desc
}
]
[ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
[, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
USING
{ *
| { [ schema . ] table . *
| expr [ AS alias ]
}
[, { [ schema . ] table . *
| expr [ AS alias ]
}
]...
}
MODEL [ cell_reference_options ] [ return_rows_clause ] [ reference_model ]... main_model
expr [ [ AS ] c_alias ]
[ PARTITION BY expr [ c_alias ] [, expr [c_alias] ]... DIMENSION BY (expr [c_alias] [, expr [c_alias] ]...) MEASURES (expr [c_alias] [, expr [c_alias] ]...)
ITERATE ( number ) [ UNTIL ( condition ) ]
[ RULES
[ { UPDATE | UPSERT [ ALL ] } ]
[ { AUTOMATIC | SEQUENTIAL } ORDER ]
[ model_iterate_clause ]
]
( [ { UPDATE | UPSERT [ ALL ] } ]
cell_assignment [ order_by_clause ] = expr
[, [ { UPDATE | UPSERT [ ALL ] } ]
cell_assignment [ order_by_clause ] = expr
]...
)
column [ datatype ]
[ DEFAULT expr ]
[ { ENCRYPT encryption_spec | DECRYPT }
[ inline_constraint ...
[ LOB_storage_clause ]
[ alter_XMLSchema_clause ]
COLUMN column [ NOT ] SUBSTITUTABLE AT ALL LEVELS [ FORCE ]
MODIFY NESTED TABLE collection_item
RETURN AS { LOCATOR | VALUE }
MODIFY { (modify_col_properties [, modify_col_properties] ...)
| modify_col_substitutable
}
MODIFY FILE 'filename' ATTRIBUTE ( disk_region_clause ) [, 'filename' ATTRIBUT €ÿE ( disk_region_clause ) ]...
MODIFY partition_extended_name
{ partition_attributes
| alter_mapping_table_clause
| [ REBUILD ] UNUSABLE LOCAL INDEXES
}
MODIFY DEFAULT ATTRIBUTES
[ FOR PARTITION partition ]
{ physical_attributes_clause
| TABLESPACE { tablespace | DEFAULT }
| logging_clause
}...
MODIFY PARTITION partition
{ { deallocate_unused_clause
| allocate_extent_clause
| physical_attributes_clause
| logging_clause
| key_compression
}...
| PARAMETERS ('ODCI_parameters')
| COALESCE
| UPDATE BLOCK REFERENCES
| UNUSABLE
}
MODIFY SUBPARTITION subpartition
{ UNUSABLE
| allocate_extent_clause
| deallocate_unused_clause
}
MODIFY partition_extended_name
{ partition_attributes
| { ADD | DROP } VALUES (literal[ , literal ]...)
| { add_range_subpartition
| add_list_subpartition
| add_hash_subpartition
}
| COALESCE SUBPARTITION [ update_index_clauses ][ parallel_clause ]
| [ REBUILD ] UNUSABLE LOCAL INDEXES
}
{ storage_clause
| PCTVERSION integer
| FREEPOOLS integer
| REBUILD FREEPOOLS
| LOB_retention_clause
| LOB_deduplicate_clause
| LOB_compression_clause
| { ENCRYPT encryption_spec | DECRYPT }
| { CACHE
| { NOCACHE | CACHE READS } [ logging_clause ]
}
| allocate_extent_clause
| shrink_clause
| deallocate_unused_clause
} ...
MODIFY LOB (LOB_item) (modify_LOB_parameters)
MODIFY ( column [ ENCRYPT encryption_spec
| DECRYPT ]
)
MODIFY partition_extended_name
{ partition_attributes
| { add_range_subpartition
| add_hash_subpartition
| add_list_subpartition
}
| COALESCE SUBPARTITION
[ update_index_clauses ]
[ parallel_clause ]
| alter_mapping_table_clause
| [ REBUILD ] UNUSABLE LOCAL INDEXES
}
MODIFY DEFAULT ATTRIBUTES
[ FOR partition_extended_name ]
[ deferred_segment_creation ]
[ segment_attributes_clause ]
[ table_compression ]
[ PCTTHRESHOLD integer ]
[ key_compression ]
[ alter_overflow_clause ]
[ { LOB (LOB_item) | VARRAY varray } (LOB_parameters) ]...
|
Note: You can specifydeferred_segment_creation in this clause starting with Oracle Database 11g Release 2 (11.2.0.2). |
{ modify_range_partition
| modify_hash_partition
| modify_list_partition
}
MODIFY subpartition_extended_name
{ allocate_extent_clause
| deallocate_unused_cluse
| shrink_clause
| { { LOB LOB_item | VARRAY varray } (modify_LOB_parameters) }...
| [ REBUILD ] UNUSABLE LOCAL INDEXES
| { ADD | DROP } VALUES ( literal [, literal]... )
}
MODIFY VOLUME asm_volume [ ATTRIBUTE disk_region_clause ] [ MOUNTPATH 'mountpath_name' ] [ USAGE 'usage_name' ]
MOVE segment_attributes_clause [parallel_clause]
MOVE [ ONLINE ]
[ segment_attributes_clause ]
[ table_compression ]
[ index_org_table_clause ]
[ { LOB_storage_clause | varray_col_properties }... ]
[ parallel_clause ]
MOVE partition_extended_name [ MAPPING TABLE ] [ table_partition_description ] [ update_index_clauses ] [ parallel_clause ]
MOVE SUBPARTITION
{ range_subpartition_desc
| list_subpartition_desc
| hash_subpartition_desc
} [ update_index_clauses ] [ parallel_clause ]
FOR (dimension_column
[, dimension_column ]...)
IN ( { (literal [, literal ]...)
[ (literal [, literal ]...) ]...
| subquery
}
)
{ ALL
{ insert_into_clause [ values_clause ] [error_logging_clause] }...
| conditional_insert_clause
} subquery
nested_table1 MULTISET EXCEPT [ ALL | DISTINCT ] nested_table2
nested_table1 MULTISET INTERSECT [ ALL | DISTINCT ] nested_table2
nested_table1 MULTISET UNION [ ALL | DISTINCT ] nested_table2
ADD { { OBJECT ID
| PRIMARY KEY
| ROWID
| SEQUENCE
} [ (column [, column ]...) ]
| (column [, column ]... )
} [, { { OBJECT ID
| PRIMARY KEY
| ROWID
| SEQUENCE
}
[ (column [, column ]...) ]
| (column [, column ]...)
}
]...
[ new_values_clause ]
PURGE { IMMEDIATE [ SYNCHRONOUS | ASYNCHRONOUS ] )
| START WITH datetime_expr
[ NEXT datetime_expr
| REPEAT INTERVAL interval_expr
]
| [ START WITH datetime_expr ] { NEXT datetime_expr
| REPEAT INTERVAL interval_expr
}
}
NESTED TABLE
{ nested_item | COLUMN_VALUE }
[ substitutable_column_clause ]
[ LOCAL | GLOBAL ]
STORE AS storage_table
[ ( { (object_properties)
| [ physical_properties ]
| [ column_properties ]
}...
)
]
[ RETURN [ AS ] { LOCATOR | VALUE } ]
PARTITION partition [segment_attributes_clause]
{ INCLUDING | EXCLUDING } NEW VALUES
[ + | - ]
{ digit [ digit ]... [ . ] [ digit [ digit ]... ]
| . digit [ digit ]...
}
[ [ e | E ] [ + | - ] digit [ digit ]... ] [ f | F | d | D ]
+diskgroup_name.filenumber.incarnation_number
{ { column | attribute }
[ DEFAULT expr ]
[ { inline_constraint }... | inline_ref_constraint ]
| { out_of_line_constraint
| out_of_line_ref_constraint
| supplemental_logging_props
}
}
OF
[ schema. ] object_type
[ object_table_substitution ]
[ (object_properties) ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ OID_clause ]
[ OID_index_clause ]
[ physical_properties ]
[ table_properties ]
;
[ NOT ] SUBSTITUTABLE AT ALL LEVELS
COLUMN column substitutable_column_clause
OF [ schema. ] type_name
{ WITH OBJECT IDENTIFIER
{ DEFAULT | ( attribute [, attribute ]... ) }
| UNDER [ schema. ] superview
}
[ ( { out_of_line_constraint
| attribute { inline_constraint }...
} [, { out_of_line_constraint
| attribute { inline_constraint }...
}
]...
)
]
OBJECT IDENTIFIER IS
{ SYSTEM GENERATED | PRIMARY KEY }
OIDINDEX [ index ]
({ physical_attributes_clause
| TABLESPACE tablespace
}...
)
[ STORE IN ( tablespace [, tablespace ]... ) ]
( PARTITION
[ partition ]
[ { segment_attributes_clause
| key_compression
}...
] [ UNUSABLE ] [ index_subpartition_clause ]
[, PARTITION
[ partition ]
[ { segment_attributes_clause
| key_compression
}...
] [ UNUSABLE ] [ index_subpartition_clause ]
]...
)
{ STORE IN (tablespace[, tablespace ]...)
| (PARTITION [ partition ] [ TABLESPACE tablespace ] [ key_compression ] [ UNUSABLE ]
[, PARTITION [ partition ] [ TABLESPACE tablespace ] [ key_compression ] [ UNUSABLE ]] ...
)
}
( PARTITION
[ partition ]
[ { segment_attributes_clause
| key_compression
}...
] [ UNUSABLE ]
[, PARTITION
[ partition ]
[ { segment_attributes_clause
| key_compression
}...
] [ UNUSABLE ]
]...
)
ON { [ schema. ] object
| DIRECTORY directory_name
| EDITION edition_name
| MINING MODEL [schema.] mining_model_name
| JAVA { SOURCE | RESOURCE } [ schema. ] object
}
( PARTITION
[ partition ]
[ { segment_attributes_clause
| key_compression
}...
] [ UNUSABLE ]
[, PARTITION
[ partition ]
[ { segment_attributes_clause
| key_compression
}...
] [ UNUSABLE ]
]...
)
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]...
[ CONSTRAINT constraint_name ]
{ UNIQUE (column [, column ]...)
| PRIMARY KEY (column [, column ]...)
| FOREIGN KEY (column [, column ]...) references_clause
| CHECK (condition)
} [ constraint_state ]
PARTITION partition
{ nested_table_col_properties | LOB_storage_clause | varray_col_properties }
[ nested_table_col_properties | LOB_storage_clause | varray_col_properties ]...
[ (SUBPARTITION subpartition
{ nested_table_col_properties | LOB_storage_clause | varray_col_properties }
[ nested_table_col_properties | LOB_storage_clause | varray_col_properties
]...
)
]
{ SCOPE FOR ({ ref_col | ref_attr })
IS [ schema. ] scope_table
| REF ({ ref_col | ref_attr }) WITH ROWID
| [ CONSTRAINT constraint_name ] FOREIGN KEY
( { ref_col [, ref_col ] | ref_attr [, ref_attr ] } ) references_clause
[ constraint_state ]
}
[ query_partition_clause ]
{ outer_join_type JOIN
| NATURAL [ outer_join_type ] JOIN
}
table_reference [ query_partition_clause ]
[ ON condition
| USING ( column [, column ]...)
]
{ FULL | LEFT | RIGHT } [ OUTER ]
{ NOPARALLEL | PARALLEL [ integer ] }
{ TABLESPACE tablespace [, tablespace ]...
| DATAFILE { 'filename' | filenumber }
[, 'filename' | filenumber ]...
}
[ { physical_attributes_clause
| logging_clause
| allocate_extent_clause
| deallocate_unused_clause
| shrink_clause
}...
]
[ OVERFLOW
{ physical_attributes_clause
| logging_clause
| allocate_extent_clause
| deallocate_unused_clause
}...
]
[ table_compression ]
[ { { LOB LOB_item | VARRAY varray } (modify_LOB_parameters) }...]
PARTITION partition | PARTITION FOR ( partition_key_value [, partition_key_value]... )
{ PARTITION (partition)
| PARTITION FOR (partition_key_value [, partition_key_value]...)
| SUBPARTITION (subpartition)
| SUBPARTITION FOR (subpartition_key_value [, subpartition_key_value]...)
}
PARTITION [ partition ] [ table_partition_description ]
[ { TABLESPACE tablespace
| OVERFLOW [TABLESPACE tablespace]
| table_compression
| key_compression
| LOB_partitioning_storage
| VARRAY varray_item STORE AS [SECUREFILE | BASICFILE] LOB LOB_segname
}...
]
{ { FAILED_LOGIN_ATTEMPTS
| PASSWORD_LIFE_TIME
| PASSWORD_REUSE_TIME
| PASSWORD_REUSE_MAX
| PASSWORD_LOCK_TIME
| PASSWORD_GRACE_TIME
}
{ expr | UNLIMITED | DEFAULT }
| PASSWORD_VERIFY_FUNCTION
{ function | NULL | DEFAULT }
}
TABLESPACE tablespace
[ DATAFILE file_specification [, file_specification ]... ]
{ MINIMUM EXTENT size_clause
| BLOCKSIZE integer [ K ]
| logging_clause
| FORCE LOGGING
| ENCRYPTION tablespace_encryption_spec
| DEFAULT [ table_compression ]
storage_clause
| { ONLINE | OFFLINE }
| extent_management_clause
| segment_management_clause
| flashback_mode_clause
}...
;
[ { PCTFREE integer
| PCTUSED integer
| INITRANS integer
| storage_clause
}...
]
{ [deferred_segment_creation] segment_attributes_clause [ table_compression ]
| [deferred_segment_creation] ORGANIZATION
{ HEAP [ segment_attributes_clause ] [ table_compression ]
| INDEX [ segment_attributes_clause ] index_org_table_clause
| EXTERNAL external_table_clause
}
| CLUSTER cluster (column [, column ]...)
}
table_reference PIVOT [ XML ]
( aggregate_function ( expr ) [[AS] alias ]
[, aggregate_function ( expr ) [[AS] alias ] ]...
pivot_for_clause
pivot_in_clause
)
FOR { column
| ( column [, column]... )
}
IN ( { { { expr
| ( expr [, expr]... )
} [ [ AS] alias]
}...
| subquery
| ANY [, ANY]...
}
)
{ GRANT CONNECT THROUGH { ENTERPRISE USERS | db_user_proxy db_user_proxy_clauses }
| REVOKE CONNECT THROUGH { ENTERPRISE USERS | db_user_proxy }}
search_string [ NAME disk_name ] [ SIZE size_clause ] [ FORCE | NOFORCE ]
ATTRIBUTE ( redundancy_clause striping_clause disk_region_clause )
SELECT
[ hint ]
[ { { DISTINCT | UNIQUE } | ALL } ]
select_list
FROM { table_reference | join_clause | ( join_clause ) }
[ , { table_reference | join_clause | (join_clause) } ] ...
[ where_clause ]
[ hierarchical_query_clause ]
[ group_by_clause ]
[ model_clause ]
PARTITION BY
{ value_expr[, value_expr ]...
| ( value_expr[, value_expr ]... )
}
{ query_name
| [ schema. ]
{ table [ partition_extension_clause
| @ dblink
]
| { view | materialized view } [ @ dblink ]
} ["sample_clause"]
| (subquery [ subquery_restriction_clause ])
| table_collection_expression
}
QUIESCE RESTRICTED | UNQUIESCE
range_values_clause
table_partition_description
[ ( { range_subpartition_desc [, range_subpartition_desc] ...
| list_subpartition_desc [, list_subpartition_desc] ...
| individual_hash_subparts [, individual_hash_subparts] ...
}
) | hash_subparts_by_quantity ]
PARTITION BY RANGE (column[, column ]...)
[ INTERVAL expr [ STORE IN ( tablespace [, tablespace]...) ]]
( PARTITION [ partition ]
range_values_clause table_partition_description
[, PARTITION [ partition ]
range_values_clause table_partition_description
]...
)
SUBPARTITION [subpartition] range_values_clause [partitioning_storage_clause]
VALUES LESS THAN
({ literal | MAXVALUE }
[, { literal | MAXVALUE } ]...
)
REBALANCE [POWER integer] [WAIT | NOWAIT]
REBUILD
[ { PARTITION partition
| SUBPARTITION subpartition
}
| { REVERSE | NOREVERSE }
]
[ parallel_clause
| TABLESPACE tablespace
| PARAMETERS ( 'ODCI_parameters' )
| XMLIndex_parameters_clause
| ONLINE
| physical_attributes_clause
| key_compression
| logging_clause
]...
{ MINIMIZE | NOMINIMIZE } RECORDS_PER_BLOCK
{ general_recovery
| managed_standby_recovery
| BEGIN BACKUP
| END BACKUP
}
[ 'filename | ASM_filename'
| ('filename | ASM_filename'
[, 'filename | ASM_filename' ]...)
]
[ SIZE size_clause ]
[ BLOCKSIZE size_clause
[ REUSE ]
[ MIRROR | HIGH | UNPROTECTED ]
REFERENCE reference_spreadsheet_name ON (subquery) spreadsheet_column_clauses [ cell_reference_options ]
PARTITION [partition] [table_partition_description] )
PARTITION BY REFERENCE ( constraint ) [ (reference_partition_desc...) ]
REFERENCES [ schema. ] { object_table | view }
[ (column [, column ]...) ]
[ON DELETE { CASCADE | SET NULL } ]
[ constraint_state ]
REGISTER [ OR REPLACE ] [ PHYSICAL | LOGICAL ] LOGFILE [ file_specification [, file_specification ]... [ FOR logminer_session_name ]
{ column_definition
| virtual_column_definition
| { out_of_line_constraint
| out_of_line_ref_constraint
| supplemental_logging_props
}
}
[, { column_definition
| virtual_column_definition
| { out_of_line_constraint
| out_of_line_ref_constraint
| supplemental_logging_props
}
}
]...
[ (relational_properties) ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ physical_properties ]
[ table_properties ]
;
RENAME COLUMN old_name TO new_name
RENAME
{ PARTITION partition | SUBPARTITION subpartition }
TO new_name
RENAME { partition_extended_name
| subpartition_extended_name
} TO new_name
RESIZE
{ ALL [ SIZE size_clause ]
| [QUORUM | REGULAR] DISK
disk_name [ SIZE size_clause ]
[, disk_name [ SIZE size_clause ] ]...
| DISKS IN [QUORUM | REGULAR] FAILGROUP
failgroup_name [ SIZE size_clause ]
[, failgroup_name [ SIZE size_clause ] ]...
}
{ { SESSIONS_PER_USER
| CPU_PER_SESSION
| CPU_PER_CALL
| CONNECT_TIME
| IDLE_TIME
| LOGICAL_READS_PER_SESSION
| LOGICAL_READS_PER_CALL
| COMPOSITE_LIMIT
}
{ integer | UNLIMITED | DEFAULT }
| PRIVATE_SGA
{ size_clause | UNLIMITED | DEFAULT }
}
RETURN { UPDATED | ALL } ROWS
{ RETURN | RETURNING } expr [, expr ]...
INTO data_item [, data_item ]...
{ object_privilege | ALL [ PRIVILEGES ] }
[, { object_privilege | ALL [ PRIVILEGES ] } ]...
on_object_clause
FROM grantee_clause
[ CASCADE CONSTRAINTS | FORCE ]
{ system_privilege
| role
| ALL PRIVILEGES
}
[, { system_privilege
| role
| ALL PRIVILEGES
}
]...
FROM grantee_clause
{ START ROLLING MIGRATION TO 'ASM_version'
| STOP ROLLING MIGRATION
}
{ ROLLUP | CUBE } (grouping_expression_list)
[ schema. ] [ type. | package. ]
{ function | procedure | method }
[ @dblink_name ]
( [ argument [, argument ]... ] )
{ ENABLE | DISABLE } ROW MOVEMENT
SAMPLE [ BLOCK ]
(sample_percent)
[ SEED (seed_value) ]
{ SCOPE FOR
({ ref_column | ref_attribute })
IS [ schema. ] { scope_table_name | c_alias }
}
[, SCOPE FOR
({ ref_column | ref_attribute })
IS [ schema. ] { scope_table_name | c_alias }
]...
{ SEARCH
{ DEPTH FIRST BY c_alias [, c_alias]...
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
| BREADTH FIRST BY c_alias [, c_alias]...
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
}
SET ordering_column
}
{ WHEN condition THEN return_expr }...
GUARD { ALL | STANDBY | NONE }
{ { ENABLE | DISABLE } RESTRICTED SESSION
| SET ENCRYPTION WALLET OPEN
IDENTIFIED BY { "wallet_password" | "HSM_auth_string" }
| SET ENCRYPTION WALLET CLOSE
[ IDENTIFIED BY { "wallet_password" | "HSM_auth_string" } ]
| set_encryption_key
}
{ physical_attributes_clause
| TABLESPACE tablespace
| logging_clause
}...
SEGMENT SPACE MANAGEMENT { AUTO | MANUAL }
{ [t_alias.] *
| { query_name.*
| [ schema. ]
{ table | view | materialized view } .*
| expr [ [ AS ] c_alias ]
}
[, { query_name.*
| [ schema. ]
{ table | view | materialized view } .*
| expr [ [ AS ] c_alias ]
}
]...
}
{ SET ENCRYPTION KEY
{
[ "certificate_id" ] IDENTIFIED BY "wallet_password"
|
IDENTIFIED BY "HSM_auth_string" [ MIGRATE USING "wallet_password" ]
}
}
parameter_name =
parameter_value [, parameter_value ]...
[ COMMENT = string ]
[ DEFERRED ]
[ { SCOPE = { MEMORY | SPFILE | BOTH }
| SID = { 'sid' | '*' }
}...
]
SET SUBPARTITION TEMPLATE
{ ( range_subpartition_desc [, range_subpartition_desc]... )
| ( list_subpartition_desc [, list_subpartition_desc]... )
| hash_subpartition_quantity
}
SET TIME_ZONE =
'{ { + | - } hh : mi | time_zone_region }'
SHRINK SPACE [ COMPACT ] [ CASCADE ]
SHUTDOWN [ IMMEDIATE ] dispatcher_name
expr
{ WHEN comparison_expr THEN return_expr }...
FOR dimension_column
{ IN ( { literal [, literal ]...
| subquery
}
)
| [ LIKE pattern ] FROM literal TO literal
{ INCREMENT | DECREMENT } literal
}
insert_into_clause
{ values_clause [ returning_clause ]
| subquery
} [ error_logging_clause ]
integer [ K | M | G | T | P | E ]
SPLIT PARTITION partition_name_old
AT (literal [, literal ]...)
[ INTO (index_partition_description,
index_partition_description
)
]
[ parallel_clause ]
NESTED TABLE column INTO
( PARTITION partition [segment_attributes_clause],
PARTITION partition [segment_attributes_clause] [split_nested_table_part]
) [ split_nested_table_part ]
SPLIT partition_extended_name
{ AT (literal [, literal]... )
[ INTO ( range_partition_desc, range_partition_desc ) ]
| VALUES (literal [, literal] ... )
[ INTO (list_partition_desc, list_partition_desc ) ]
} [ split_nested_table_part]
[ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
SPLIT subpartition_extended_name
{ AT ( literal [, literal]... )
[ INTO (range_subpartition_desc, range_subpartition_desc) ]
| VALUES ({ literal | NULL [, literal | NULL ]...})
[ INTO (list_subpartition_desc, list_subpartition_desc) ]
} [ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
[+ | -] days hours : minutes : seconds [. frac_secs ]
{ activate_standby_db_clause
| maximize_standby_db_clause
| register_logfile_clause
| commit_switchover_clause
| start_standby_clause
| stop_standby_clause
| convert_database_clause
} [ parallel_clause ]
START LOGICAL STANDBY APPLY
[ IMMEDIATE ]
[ NODELAY ]
[ NEW PRIMARY dblink
| INITIAL [ scn_value ]
| { SKIP FAILED TRANSACTION | FINISH }
]
{ MOUNT [ { STANDBY | CLONE } DATABASE ]
| OPEN
{ [ READ WRITE ]
[ RESETLOGS | NORESETLOGS ]
[ UPGRADE | DOWNGRADE ]
| READ ONLY
}
}
{ SI_StillImage
| SI_AverageColor
| SI_PositionalColor
| SI_ColorHistogram
| SI_Texture
| SI_FeatureList
| SI_Color
}
{ STOP | ABORT } LOGICAL STANDBY APPLY
STORAGE
({ INITIAL size_clause
| NEXT size_clause
| MINEXTENTS integer
| MAXEXTENTS { integer | UNLIMITED }
| maxsize_clause
| PCTINCREASE integer
| FREELISTS integer
| FREELIST GROUPS integer
| OPTIMAL [ size_clause | NULL ]
| BUFFER_POOL { KEEP | RECYCLE | DEFAULT }
| FLASH_CACHE { KEEP | NONE | DEFAULT }
| ENCRYPT
} ...
)
WITH {SYSTEM | USER} MANAGED STORAGE TABLES
[ {N | n} ]
{ '[ c ]...'
| { Q | q } 'quote_delimiter c [ c ]... quote_delimiter'
}
[ FINE | COARSE ]
SUBPARTITION BY HASH (column [, column ]...)
[ SUBPARTITIONS integer
[ STORE IN (tablespace [, tablespace ]...) ]
| subpartition_template
]
SUBPARTITION BY LIST (column) [ subpartition_template ]
SUBPARTITION BY RANGE ( column [, column]... ) [subpartition_template]
SUBPARTITION subpartition | SUBPARTITION FOR ( subpartition_key_value [, subpartition_key_value]... )
SUBPARTITION TEMPLATE
( { range_subpartition_desc [, range_subpartition_desc] ...
| list_subpartition_desc [, list_subpartition_desc] ...
| individual_hash_subparts [, individual_hash_subparts] ...
}
) | hash_subpartition_quantity
{ query_block
| subquery { UNION [ALL] | INTERSECT | MINUS } subquery
[ { UNION [ALL] | INTERSECT | MINUS } subquery ]...
| ( subquery )
} [ order_by_clause ]
WITH query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause ] [cycle_clause] [, query_name ([c_alias [, c_alias]...]) AS (subquery) [search_clause] [cycle_clause]]...
WITH { READ ONLY
| CHECK OPTION
} [ CONSTRAINT constraint ]
{ [ ELEMENT ] IS OF [ TYPE ] ( [ONLY] type)
| [ NOT ] SUBSTITUTABLE AT ALL LEVELS
}
{ ADD | DROP } SUPPLEMENTAL LOG
{ DATA
| supplemental_id_key_clause
| supplemental_plsql_clause
}
DATA
( { ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY }
[, { ALL | PRIMARY KEY | UNIQUE | FOREIGN KEY } ]...
)
COLUMNS
GROUP log_group (column [ NO LOG ] [, column [ NO LOG ] ]...) [ ALWAYS ]
SUPPLEMENTAL LOG { supplemental_log_grp_clause
| supplemental_id_key_clause
}
DATA FOR PROCEDURAL REPLICATION
{ ADD SUPPLEMENTAL LOG
{ supplemental_log_grp_clause | supplemental_id_key_clause }
[, SUPPLEMENTAL LOG
{ supplemental_log_grp_clause | supplemental_id_key_clause }
]...
| DROP SUPPLEMENTAL LOG
{ supplemental_id_key_clause | GROUP log_group }
[, SUPPLEMENTAL LOG
{ supplemental_id_key_clause | GROUP log_group }
]...
}
SWITCH ALL LOGFILES TO BLOCKSIZE integer
PARTITION BY SYSTEM [ PARTITIONS integer
| reference_partition_desc
[, reference_partition_desc ...]
]
TABLE (collection_expression) [ (+) ]
{ COMPRESS [ BASIC
| FOR { OLTP
| { QUERY | ARCHIVE } [ LOW | HIGH ]
}
]
| NOCOMPRESS
}
[ schema. ] table [ t_alias ] (index_expr [ ASC | DESC ] [, index_expr [ ASC | DESC ] ]...) [ index_properties ]
[ deferred_segment_creation ]
[ segment_attributes_clause ]
[ table_compression | key_compression ]
[ OVERFLOW [ segment_attributes_clause ] ]
[ { LOB_storage_clause
| varray_col_properties
| nested_table_col_properties
}...
]
|
Note: You can specifydeferred_segment_creation in this clause starting with Oracle Database 11g Release 2 (11.2.0.2). |
{ range_partitions
| hash_partitions
| list_partitions
| reference_partitioning
| composite_range_partitions
| composite_hash_partitions
| composite_list_partitions
| system_partitioning
}
[ column_properties ]
[ table_partitioning_clauses ]
[ CACHE | NOCACHE ]
[ RESULT_CACHE ( MODE {DEFAULT | FORCE } ) ]
[ parallel_clause ]
[ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
[ enable_disable_clause ]...
[ row_movement_clause ]
[ flashback_archive_clause ]
[ AS subquery ]
{ ONLY (query_table_expression)
| query_table_expression [ pivot_clause | unpivot_clause ]
} [ flashback_query_clause ]
[ t_alias ]
{ EXTENT MANAGEMENT LOCAL
| DATAFILE file_specification [, file_specification ]...
| SYSAUX DATAFILE file_specification [, file_specification ]...
| default_tablespace
| default_temp_tablespace
| undo_tablespace
}
[ USING 'encrypt_algorithm' ]
TABLESPACE GROUP { tablespace_group_name | '' }
{ logging_clause
| [ NO ] FORCE LOGGING
}
RETENTION { GUARANTEE | NOGUARANTEE }
{ { ONLINE
| OFFLINE [ NORMAL | TEMPORARY | IMMEDIATE ]
}
| READ { ONLY | WRITE }
| { PERMANENT | TEMPORARY }
}
TEMPORARY TABLESPACE tablespace [ TEMPFILE file_specification [, file_specification ]... ] [ tablespace_group_clause ] [ extent_management_clause ]
DROP AFTER integer { M | H }
TRACE [ AS 'filename' [ REUSE ] ] [ RESETLOGS | NORESETLOGS ]
TRUNCATE { partition_extended_name
| subpartition_extended_name
}
[ { DROP [ ALL ] | REUSE } STORAGE ]
[ update_index_clauses [ parallel_clause ] ]
|
Note: You can specify theALL keyword in this clause starting with Oracle Database 11g Release 2 (11.2.0.2). |
[ BIGFILE | SMALLFILE ] UNDO TABLESPACE tablespace [ TABLESPACE file_specification [, file_specification ]...]
UNDO TABLESPACE tablespace [ DATAFILE file_specification [, file_specification ]... ] [ extent_management_clause ] [ tablespace_retention_clause ]
UNDROP DISKS
table_reference UNPIVOT [ {INCLUDE | EXCLUDE} NULLS ]
( { column | ( column [, column]... ) }
pivot_for_clause
unpivot_in_clause
)
IN
( { column | ( column [, column]... ) }
[ AS { literal | ( literal [, literal]... ) } ]
[, { column | ( column [, column]... ) }
[ AS {literal | ( literal [, literal]... ) } ]
]...
)
UPDATE INDEXES
[ (index ( update_index_partition
| update_index_subpartition
)
[, (index ( update_index_partition
| update_index_subpartition
)
)
]...
)
{ UPDATE | INVALIDATE } GLOBAL INDEXES
{ update_global_index_clause
| update_all_indexes_clause
}
index_partition_description
[ index_subpartition_clause ]
[, index_partition_description
[ index_subpartition_clause ] ...
SUBPARTITION [ subpartition ]
[ TABLESPACE tablespace ]
[, SUBPARTITION [ subpartition ]
[ TABLESPACE tablespace ]
]...
SET
{ { (column [, column ]...) = (subquery)
| column = { expr | (subquery) | DEFAULT }
}
[, { (column [, column]...) = (subquery)
| column = { expr | (subquery) | DEFAULT }
}
]...
| VALUE (t_alias) = { expr | (subquery) }
}
UPGRADE [ [NOT ] INCLUDING DATA ] [ column_properties ]
{ ADD USER user [, user]...
| DROP USER user [, user]... [CASCADE]
}
{ ADD USERGROUP usergroup WITH MEMBER user [, user]...
| MODIFY USERGROUP usergroup { ADD | DROP } MEMBER user [, user]...
| DROP USERGROUP usergroup
}
USING [ schema. ] [ package. | type. ] function_name
USING INDEX
{ [ schema. ] index
| (create_index_statement)
| index_properties
}
USING { [ schema. ] statistics_type | NULL }
USING [ schema. ] implementation_type [ array_DML_clause ]
{ VALIDATE REF UPDATE [ SET DANGLING TO NULL ]
| VALIDATE STRUCTURE
[ CASCADE { FAST | COMPLETE { OFFLINE | ONLINE } [ into_clause ] } ]
}
VALUES ({ expr | DEFAULT }
[, { expr | DEFAULT } ]...
)
VARRAY varray_item
{ [ substitutable_column_clause ] varray_storage_clause
| substitutable_column_clause
}
STORE AS [SECUREFILE | BASICFILE] LOB
{ [LOB_segname] ( LOB_storage_parameters )
| LOB_segname
}
column [datatype] [GENERATED ALWAYS] AS (column_expression) [VIRTUAL] [ inline_constraint [inline_constraint]... ]
WHERE condition
{ ROWS | RANGE }
{ BETWEEN
{ UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
AND
{ UNBOUNDED FOLLOWING
| CURRENT ROW
| value_expr { PRECEDING | FOLLOWING }
}
| { UNBOUNDED PRECEDING
| CURRENT ROW
| value_expr PRECEDING
}
}
XMLATTRIBUTES
[ ENTITYESCAPING | NOENTITYESCAPING ]
[ SCHEMACHECK | NOSCHEMACHECK ]
value_expr [ { [AS] c_alias } | { AS EVALNAME value_expr } ]
[, value_expr [ { [AS] c_alias } | { AS EVALNAME value_expr } ]
]...
XMLNAMESPACES
( [ string AS identifier ]
[ [, string AS identifier ]
]...
[ DEFAULT string ]
)
PASSING [ BY VALUE ]
expr [ AS identifier ]
[, expr [ AS identifier ]
]...
column
{ FOR ORDINALITY
| datatype [ PATH string ] [ DEFAULT expr ]
}
[XDB.] XMLINDEX [ local_XMLIndex_clause ]
[ parallel_clause ]
[ XMLIndex_parameters_clause ]
[ XMLSCHEMA XMLSchema_URL ]
ELEMENT { element | XMLSchema_URL # element }
[ ALLOW { ANYSCHEMA | NONSCHEMA } ]
[ DISALLOW { ANYSCHEMA | NONSCHEMA } ]
[ XML_passing_clause ] [ COLUMNS XML_table_column [, XML_table_column]...]
XMLTYPE [ COLUMN ] column [ XMLType_storage ] [ XMLSchema_spec ]
STORE
{ AS
{ OBJECT RELATIONAL
| [SECUREFILE | BASICFILE]
{ CLOB | BINARY XML }
[ { LOB_segname [ (LOB_parameters) ]
| (LOB_parameters)
}
]
}
| { ALL VARRAYS AS { LOBS | TABLES } }
}
OF XMLTYPE
[ (oject_properties) ]
[ XMLTYPE XMLType_storage ]
[ XMLSchema_spec ]
[ XMLType_virtual_columns ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ OID_clause ]
[ OID_index_clause ]
[ physical_properties ]
[ table_properties ]
OF XMLTYPE [ XMLSchema_spec ]
WITH OBJECT IDENTIFIER
{ DEFAULT | ( expr [, expr ]...) }
VIRTUAL COLUMNS ( column AS (expr) [, column AS (expr) ]... )
[-] P [ years Y ] [months M] [days D] [T [hours H] [minutes M] [seconds [. frac_secs] S ] ]
This chapter presents the syntax for the subclauses found in the syntax for SQL statements, functions, expressions and conditions.
This chapter includes the following section:
SQL*Plus is a command-line tool that provides access to the Oracle RDBMS. SQL*Plus enables you to:
Enter SQL*Plus commands to configure the SQL*Plus environment
Startup and shutdown an Oracle database
Connect to an Oracle database
Enter and execute SQL commands and PL/SQL blocks
Format and print query results
SQL*Plus is available on several platforms.
The commands shown in Table A-1 are SQL*Plus commands available in the command-line interface. Not all commands or command parameters are shown.
Table A-1 Basic SQL*Plus Commands
Copyright © 1994-2012, Oracle and/or its affiliates. All rights reserved.
Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.
Intel and Intel Xeon are trademarks or registered trademarks of Intel Corporation. All SPARC trademarks are used under license and are trademarks or registered trademarks of SPARC International, Inc. AMD, Opteron, the AMD logo, and the AMD Opteron logo are trademarks or registered trademarks of Advanced Micro Devices. UNIX is a registered trademark of The Open Group.
This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.
The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.
If this is software or related documentation that is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:
U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle America, Inc., 500 Oracle Parkway, Redwood City, CA 94065.
This software or hardware is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications that may create a risk of personal injury. If you use this software or hardware in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure its safe use. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software or hardware in dangerous applications.
This software or hardware and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.
If this document is in prerelease status:
This documentation is in prerelease status and is intended for demonstration and preliminary use only. It may not be specific to the hardware on which you are using the software. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to this documentation and will not be responsible for any loss, costs, or damages incurred due to the use of this documentation.
A data type is a classification of a particular type of information or data. Each value manipulated by Oracle has a data type. The data type of a value associates a fixed set of properties with the value. These properties cause Oracle to treat values of one data type differently from values of another.
The data types recognized by Oracle are:
{ CHARACTER [VARYING] (size)
| { CHAR | NCHAR } VARYING (size)
| VARCHAR (size)
| NATIONAL { CHARACTER | CHAR }
[VARYING] (size)
| { NUMERIC | DECIMAL | DEC }
[ (precision [, scale ]) ]
| { INTEGER | INT | SMALLINT }
| FLOAT [ (size) ]
| DOUBLE PRECISION
| REAL
}
{ character_datatypes
| number_datatypes
| long_and_raw_datatypes
| datetime_datatypes
| large_object_datatypes
| rowid_datatypes
}
{ any_types
| XML_types
| spatial_types
| media_types
| expression_filter_type
}
User-defined data types use Oracle built-in data types and other user-defined data types to model the structure and behavior of data in applications.
SQL statements are the means by which programs and users access data in an Oracle database.
The sections that follow show each SQL statement and its related syntax. Refer to Chapter 5, "Subclauses" for the syntax of the subclauses listed in the syntax for the statements.
ALTER CLUSTER [ schema. ]cluster
{ physical_attributes_clause
| SIZE size_clause
| allocate_extent_clause
| deallocate_unused_clause
| { CACHE | NOCACHE }
} ...
[ parallel_clause ] ;
ALTER DATABASE [ database ]
{ startup_clauses
| recovery_clauses
| database_file_clauses
| logfile_clauses
| controlfile_clauses
| standby_database_clauses
| default_settings_clauses
| instance_clauses
| security_clause
} ;
ALTER DATABASE LINK dblink
{ CONNECT TO user IDENTIFIED BY password [ dblink_authentication ]
| dblink_authentication
};
ALTER DIMENSION [ schema. ] dimension
{ ADD { level_clause
| hierarchy_clause
| attribute_clause
| extended_attribute_clause
}
} ...
|
{ DROP { LEVEL level [ RESTRICT | CASCADE ]
| HIERARCHY hierarchy
| ATTRIBUTE attribute [ LEVEL level [ COLUMN column ] ]...
}
} ...
|
COMPILE
;
ALTER DISKGROUP
{ { diskgroup_name
{ { add_disk_clause | drop_disk_clause }
[, { add_disk_clause | drop_disk_clause } ]...
| resize_disk_clauses
} [ rebalance_diskgroup_clause ]
| { disk_online_clause
| disk_offline_clause
| rebalance_diskgroup_clause
| check_diskgroup_clause
| diskgroup_template_clauses
| diskgroup_directory_clauses
| diskgroup_alias_clauses
| diskgroup_volume_clauses
| diskgroup_attributes
| modify_diskgroup_file
| drop_diskgroup_file_clause
| usergroup_clauses
| user_clauses
| file_permissions_clause
| file_owner_clause
}
| { diskgroup_name [, diskgroup_name ] ...
| ALL
} { undrop_disk_clause
| diskgroup_availability
| enable_disable_volume
}
}
ALTER FLASHBACK ARCHIVE flashback_archive
{ SET DEFAULT
| { ADD | MODIFY } TABLESPACE tablespace [flashback_archive_quota]
| REMOVE TABLESPACE tablespace_name
| MODIFY RETENTION flashback_archive_retention
| PURGE { ALL
| BEFORE { SCN expr | TIMESTAMP expr}
}
};
ALTER FUNCTION [ schema. ] function function_compile_clause
ALTER INDEX [ schema. ]index
{ { deallocate_unused_clause
| allocate_extent_clause
| shrink_clause
| parallel_clause
| physical_attributes_clause
| logging_clause
} ...
| rebuild_clause
| PARAMETERS ( 'ODCI_parameters' )
)
| COMPILE
| { ENABLE | DISABLE }
| UNUSABLE
| VISIBLE | INVISIBLE
| RENAME TO new_name
| COALESCE
| { MONITORING | NOMONITORING } USAGE
| UPDATE BLOCK REFERENCES
| alter_index_partitioning
}
;
ALTER INDEXTYPE [ schema. ] indextype
{ { ADD | DROP } [ schema. ] operator ( parameter_types )
[ , { ADD | DROP } [schema. ] operator ( parameter_types ) ]... [ using_type_clause ]
| COMPILE
}
[ WITH LOCAL [ RANGE ] PARTITION ] [ storage_table_clause ]
;
ALTER JAVA
{ SOURCE | CLASS } [ schema. ]object_name
[ RESOLVER
( ( match_string [, ] { schema_name | - } )... )
]
{ { COMPILE | RESOLVE }
| invoker_rights_clause
} ;
ALTER LIBRARY [ schema. ] library_name library_compile_clause
ALTER MATERIALIZED VIEW
[ schema. ] materialized_view
[ physical_attributes_clause
| modify_mv_column_clause
| table_compression
| LOB_storage_clause [, LOB_storage_clause ]...
| modify_LOB_storage_clause [, modify_LOB_storage_clause ]...
| alter_table_partitioning
| parallel_clause
| logging_clause
| allocate_extent_clause
| deallocate_unused_clause
| shrink_clause
| { CACHE | NOCACHE }
]
[ alter_iot_clauses ]
[ USING INDEX physical_attributes_clause ]
[ MODIFY scoped_table_ref_constraint
| alter_mv_refresh
]
[ { ENABLE | DISABLE } QUERY REWRITE
| COMPILE
| CONSIDER FRESH
] ;
ALTER MATERIALIZED VIEW LOG [ FORCE ]
ON [ schema. ]table
[ physical_attributes_clause
| add_mv_log_column_clause
| alter_table_partitioning
| parallel_clause
| logging_clause
| allocate_extent_clause
| shrink_clause
| move_mv_log_clause
| { CACHE | NOCACHE }
] [ mv_log_augmentation ] [ mv_log_purge_clause ]
;
ALTER OPERATOR [ schema. ] operator
{ add_binding_clause
| drop_binding_clause
| COMPILE
} ;
ALTER OUTLINE [ PUBLIC | PRIVATE ] outline
{ REBUILD
| RENAME TO new_outline_name
| CHANGE CATEGORY TO new_category_name
| { ENABLE | DISABLE }
} ...
;
ALTER PACKAGE [ schema. ] package package_compile_clause
ALTER PROCEDURE [ schema. ] procedure procedure_compile_clause
ALTER PROFILE profile LIMIT
{ resource_parameters | password_parameters } ...
;
ALTER RESOURCE COST
{ { CPU_PER_SESSION
| CONNECT_TIME
| LOGICAL_READS_PER_SESSION
| PRIVATE_SGA
} integer
} ...
;
ALTER ROLE role
{ NOT IDENTIFIED
| IDENTIFIED
{ BY password
| USING [ schema. ] package
| EXTERNALLY
| GLOBALLY
}
} ;
ALTER ROLLBACK SEGMENT rollback_segment
{ ONLINE
| OFFLINE
| storage_clause
| SHRINK [ TO size_clause ]
};
ALTER SEQUENCE [ schema. ] sequence
{ INCREMENT BY integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
} ...
;
ALTER SESSION
{ ADVISE { COMMIT | ROLLBACK | NOTHING }
| CLOSE DATABASE LINK dblink
| { ENABLE | DISABLE } COMMIT IN PROCEDURE
| { ENABLE | DISABLE } GUARD
| { ENABLE | DISABLE | FORCE } PARALLEL
{ DML | DDL | QUERY } [ PARALLEL integer ]
| { ENABLE RESUMABLE [ TIMEOUT integer ] [ NAME string ]
| DISABLE RESUMABLE
}
| SYNC WITH PRIMARY
| alter_session_set_clause
} ;
ALTER SYSTEM
{ archive_log_clause
| checkpoint_clause
| check_datafiles_clause
| distributed_recov_clauses
| FLUSH { SHARED_POOL | BUFFER_CACHE | REDO TO target_db_name [ [ NO ] CONFIRM APPLY ] }
| end_session_clauses
| SWITCH LOGFILE
| { SUSPEND | RESUME }
| quiesce_clauses
| rolling_migration_clauses
| security_clauses
| shutdown_dispatcher_clause
| REGISTER
| SET alter_system_set_clause
[ alter_system_set_clause ]...
| RESET alter_system_reset_clause
[ alter_system_reset_clause ]...
} ;
ALTER TABLE [ schema. ] table
[ alter_table_properties
| column_clauses
| constraint_clauses
| alter_table_partitioning
| alter_external_table
| move_table_clause
]
[ enable_disable_clause
| { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS }
] ...
;
ALTER TABLESPACE tablespace
{ DEFAULT [ table_compression ] storage_clause
| MINIMUM EXTENT size_clause
| RESIZE size_clause
| COALESCE
| SHRINK SPACE [ KEEP size_clause]
| RENAME TO new_tablespace_name
| { BEGIN | END } BACKUP
| datafile_tempfile_clauses
| tablespace_logging_clauses
| tablespace_group_clause
| tablespace_state_clauses
| autoextend_clause
| flashback_mode_clause
| tablespace_retention_clause
} ;
ALTER TRIGGER [ schema. ] trigger
{ ENABLE
| DISABLE
| RENAME TO new_name
| trigger_compile_clause
} ;
ALTER TYPE [ schema. ]type alter_type_clauses
ALTER USER
{ user
{ IDENTIFIED
{ BY password [ REPLACE old_password ]
| EXTERNALLY [ AS 'certificate_DN' | AS 'kerberos_principal_name' ]
| GLOBALLY [ AS '[directory_DN]' ]
}
| DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE { tablespace | tablespace_group_name }
| { QUOTA { size_clause
| UNLIMITED
} ON tablespace
} ...
| PROFILE profile
| DEFAULT ROLE { role [, role ]...
| ALL [ EXCEPT role [, role ] ... ]
| NONE
}
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
| ENABLE EDITIONS [ FORCE ]
} ...
| user [, user ]... proxy_clause
} ;
ALTER VIEW [ schema. ] view
{ ADD out_of_line_constraint
| MODIFY CONSTRAINT constraint
{ RELY | NORELY }
| DROP { CONSTRAINT constraint
| PRIMARY KEY
| UNIQUE (column [, column ]...)
}
| COMPILE
| { READ ONLY | READ WRITE }
} ;
ANALYZE
{ { TABLE [ schema. ] table
| INDEX [ schema. ] index
} [ partition_extension_clause ]
| CLUSTER [ schema. ] cluster
}
{ validation_clauses
| LIST CHAINED ROWS [ into_clause ]
| DELETE [ SYSTEM ] STATISTICS
} ;
ASSOCIATE STATISTICS WITH
{ column_association | function_association }
[ storage_table_clause ] ;
AUDIT
{ audit_operation_clause [ auditing_by_clause | IN SESSION CURRENT ]
| audit_schema_object_clause
| NETWORK
} [ BY { SESSION | ACCESS } ]
[ WHENEVER [ NOT ] SUCCESSFUL ]
;
CALL
{ routine_clause
| object_access_expression
}
[ INTO :host_variable
[ [ INDICATOR ] :indicator_variable ] ] ;
COMMENT ON
{ COLUMN [ schema. ]
{ table. | view. | materialized_view. } column
| EDITION edition_name
| INDEXTYPE [ schema. ] indextype
| MATERIALIZED VIEW materialized_view
| MINING MODEL [ schema. ] model
| OPERATOR [ schema. ] operator
| TABLE [ schema. ] { table | view }
}
IS string ;
COMMIT [ WORK ]
[ [ COMMENT string ]
| [ WRITE [ WAIT | NOWAIT ] [ IMMEDIATE | BATCH ]
]
| FORCE { string [, integer ]
| CORRUPT_XID string
| CORRUPT_XID_ALL
}
] ;
CREATE CLUSTER [ schema. ] cluster
(column datatype [ SORT ]
[, column datatype [ SORT ] ]...
)
[ { physical_attributes_clause
| SIZE size_clause
| TABLESPACE tablespace
| { INDEX
| [ SINGLE TABLE ]
HASHKEYS integer [ HASH IS expr ]
}
}...
]
[ parallel_clause ]
[ NOROWDEPENDENCIES | ROWDEPENDENCIES ]
[ CACHE | NOCACHE ] ;
CREATE [ OR REPLACE ] CONTEXT namespace
USING [ schema. ] package
[ INITIALIZED { EXTERNALLY | GLOBALLY }
| ACCESSED GLOBALLY
] ;
CREATE CONTROLFILE
[ REUSE ] [ SET ] DATABASE database
[ logfile_clause ]
{ RESETLOGS | NORESETLOGS }
[ DATAFILE file_specification
[, file_specification ]... ]
[ MAXLOGFILES integer
| MAXLOGMEMBERS integer
| MAXLOGHISTORY integer
| MAXDATAFILES integer
| MAXINSTANCES integer
| { ARCHIVELOG | NOARCHIVELOG }
| FORCE LOGGING
]...
[ character_set_clause ] ;
CREATE DATABASE [ database ]
{ USER SYS IDENTIFIED BY password
| USER SYSTEM IDENTIFIED BY password
| CONTROLFILE REUSE
| MAXDATAFILES integer
| MAXINSTANCES integer
| CHARACTER SET charset
| NATIONAL CHARACTER SET charset
| SET DEFAULT
{ BIGFILE | SMALLFILE } TABLESPACE
| database_logging_clauses
| tablespace_clauses
| set_time_zone_clause
}... ;
CREATE [ SHARED ] [ PUBLIC ] DATABASE LINK dblink
[ CONNECT TO
{ CURRENT_USER
| user IDENTIFIED BY password [ dblink_authentication ]
}
| dblink_authentication
]...
[ USING connect_string ] ;
CREATE DIMENSION [ schema. ] dimension
level_clause ...
{ hierarchy_clause
| attribute_clause
| extended_attribute_clause
}...
;
CREATE [ OR REPLACE ] DIRECTORY directory AS 'path_name' ;
CREATE DISKGROUP diskgroup_name
[ { HIGH | NORMAL | EXTERNAL } REDUNDANCY ]
{ [ QUORUM | REGULAR ][ FAILGROUP failgroup_name ]
DISK qualified_disk_clause [, qualified_disk_clause]...
} ...
[ ATTRIBUTE { 'attribute_name' = 'attribute_value' }... ]
;
CREATE EDITION edition [ AS CHILD OF parent_edition ] ;
CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive TABLESPACE tablespace [flashback_archive_quota] flashback_archive_retention ;
CREATE [ OR REPLACE ] FUNCTION plsql_source
CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index
ON { cluster_index_clause
| table_index_clause
| bitmap_join_index_clause
}
[ UNUSABLE ] ;
CREATE [ OR REPLACE ] INDEXTYPE [ schema. ] indextype
FOR [ schema. ] operator (paramater_type [, paramater_type ]...)
[, [ schema. ] operator (paramater_type [, paramater_type ]...)
]...
using_type_clause
[WITH LOCAL [RANGE] PARTITION ]
[ storage_table_clause ]
;
CREATE [ OR REPLACE ] [ AND { RESOLVE | COMPILE } ] [ NOFORCE ]
JAVA { { SOURCE | RESOURCE } NAMED [ schema. ] primary_name
| CLASS [ SCHEMA schema ]
}
[ invoker_rights_clause ]
[ RESOLVER ( (match_string [,] { schema_name | - })...) ]
{ USING { BFILE (directory_object_name, server_file_name)
| { CLOB | BLOB | BFILE } subquery
| 'key_for_BLOB'
}
| AS source_char
}
CREATE [ OR REPLACE ] LIBRARY plsql_source
CREATE MATERIALIZED VIEW [ schema. ] materialized_view
[ column_alias [ENCRYPT [encryption_spec]] [, column_alias [ENCRYPT [encryption_spec]] ]... ]
[ OF [ schema. ] object_type ]
[ (scoped_table_ref_constraint) ]
{ ON PREBUILT TABLE
[ { WITH | WITHOUT } REDUCED PRECISION ]
| physical_properties materialized_view_props
}
[ USING INDEX
[ physical_attributes_clause
| TABLESPACE tablespace
]...
| USING NO INDEX
]
[ create_mv_refresh ]
[ FOR UPDATE ]
[ { DISABLE | ENABLE } QUERY REWRITE ]
AS subquery ;
CREATE MATERIALIZED VIEW LOG ON [ schema. ] table
[ physical_attributes_clause
| TABLESPACE tablespace
| logging_clause
| { CACHE | NOCACHE }
]...
[ parallel_clause ]
[ table_partitioning_clauses ]
[ WITH [ { OBJECT ID
| PRIMARY KEY
| ROWID
| SEQUENCE
| COMMIT SCN
}
[ { , OBJECT ID
| , PRIMARY KEY
| , ROWID
| , SEQUENCE
| , COMMIT SCN
}
]... ]
(column [, column ]...)
[ new_values_clause ]
] [ mv_log_purge_clause ]
;
CREATE [ OR REPLACE ] OPERATOR [ schema. ] operator binding_clause ;
CREATE [ OR REPLACE ] [ PUBLIC | PRIVATE ] OUTLINE [ outline ] [ FROM [ PUBLIC | PRIVATE ] source_outline ] [ FOR CATEGORY category ] [ ON statement ] ;
CREATE [ OR REPLACE ] PACKAGE plsql_source
CREATE [ OR REPLACE ] PACKAGE BODY plsql_source
CREATE PFILE [= 'pfile_name' ]
FROM { SPFILE [= 'spfile_name']
| MEMORY
} ;
CREATE [ OR REPLACE ] PROCEDURE plsql_source
CREATE PROFILE profile
LIMIT { resource_parameters
| password_parameters
}...
;
CREATE RESTORE POINT restore_point
[ AS OF {TIMESTAMP | SCN} expr ]
[ PRESERVE
| GUARANTEE FLASHBACK DATABASE
];
CREATE ROLE role
[ NOT IDENTIFIED
| IDENTIFIED { BY password
| USING [ schema. ] package
| EXTERNALLY
| GLOBALLY
}
] ;
CREATE [ PUBLIC ] ROLLBACK SEGMENT rollback_segment [ TABLESPACE tablespace | storage_clause ]...];
CREATE SCHEMA AUTHORIZATION schema
{ create_table_statement
| create_view_statement
| grant_statement
}...
;
CREATE SEQUENCE [ schema. ] sequence
[ { INCREMENT BY | START WITH } integer
| { MAXVALUE integer | NOMAXVALUE }
| { MINVALUE integer | NOMINVALUE }
| { CYCLE | NOCYCLE }
| { CACHE integer | NOCACHE }
| { ORDER | NOORDER }
]...
;
CREATE SPFILE [= 'spfile_name' ]
FROM { PFILE [= 'pfile_name' ]
| MEMORY
} ;
CREATE [ OR REPLACE ] [ PUBLIC ] SYNONYM [ schema. ] synonym FOR [ schema. ] object [ @ dblink ] ;
CREATE [ GLOBAL TEMPORARY ] TABLE [ schema. ] table
{ relational_table | object_table | XMLType_table }
CREATE
[ BIGFILE | SMALLFILE ]
{ permanent_tablespace_clause
| temporary_tablespace_clause
| undo_tablespace_clause
} ;
CREATE [ OR REPLACE ] TRIGGER plsql_source
CREATE [OR REPLACE] TYPE plsql_source
CREATE [ OR REPLACE ] TYPE BODY plsql_source
CREATE USER user
IDENTIFIED { BY password
| EXTERNALLY [ AS 'certificate_DN' | AS 'kerberos_principal_name' ]
| GLOBALLY [ AS '[ directory_DN ]' ]
}
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| { QUOTA { size_clause | UNLIMITED } ON tablespace }...
| PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
[ DEFAULT TABLESPACE tablespace
| TEMPORARY TABLESPACE
{ tablespace | tablespace_group_name }
| { QUOTA { size_clause | UNLIMITED } ON tablespace }...
| PROFILE profile
| PASSWORD EXPIRE
| ACCOUNT { LOCK | UNLOCK }
| ENABLE EDITIONS
]...
] ;
CREATE [OR REPLACE]
[[NO] FORCE] [EDITIONING] VIEW [schema.] view
[ ( { alias [ inline_constraint... ]
| out_of_line_constraint
}
[, { alias [ inline_constraint...]
| out_of_line_constraint
}
]
)
| object_view_clause
| XMLType_view_clause
]
AS subquery [ subquery_restriction_clause ] ;
DELETE [ hint ]
[ FROM ]
{ dml_table_expression_clause
| ONLY (dml_table_expression_clause)
} [ t_alias ]
[ where_clause ]
[ returning_clause ]
[error_logging_clause];
DISASSOCIATE STATISTICS FROM
{ COLUMNS [ schema. ]table.column
[, [ schema. ]table.column ]...
| FUNCTIONS [ schema. ]function
[, [ schema. ]function ]...
| PACKAGES [ schema. ]package
[, [ schema. ]package ]...
| TYPES [ schema. ]type
[, [ schema. ]type ]...
| INDEXES [ schema. ]index
[, [ schema. ]index ]...
| INDEXTYPES [ schema. ]indextype
[, [ schema. ]indextype ]...
}
[ FORCE ] ;
DROP CLUSTER [ schema. ] cluster [ INCLUDING TABLES [ CASCADE CONSTRAINTS ] ] ;
DROP CONTEXT namespace ;
DROP DATABASE ;
DROP [ PUBLIC ] DATABASE LINK dblink ;
DROP DIMENSION [ schema. ] dimension ;
DROP DIRECTORY directory_name ;
DROP DISKGROUP diskgroup_name
[ FORCE INCLUDING CONTENTS
| { INCLUDING | EXCLUDING } CONTENTS
];
DROP EDITION edition [CASCADE];
DROP FLASHBACK ARCHIVE flashback_archive;
DROP FUNCTION [ schema. ] function_name ;
DROP INDEX [ schema. ] index [ FORCE ] ;
DROP INDEXTYPE [ schema. ] indextype [ FORCE ] ;
DROP JAVA { SOURCE | CLASS | RESOURCE }
[ schema. ] object_name ;
DROP LIBRARY library_name ;
DROP MATERIALIZED VIEW [ schema. ] materialized_view [ PRESERVE TABLE ] ;
DROP MATERIALIZED VIEW LOG ON [ schema. ] table ;
DROP OPERATOR [ schema. ] operator [ FORCE ] ;
DROP OUTLINE outline ;
DROP PACKAGE [ BODY ] [ schema. ] package ;
DROP PROCEDURE [ schema. ] procedure ;
DROP PROFILE profile [ CASCADE ] ;
DROP RESTORE POINT restore_point ;
DROP ROLE role ;
DROP ROLLBACK SEGMENT rollback_segment ;
DROP SEQUENCE [ schema. ] sequence_name ;
DROP [PUBLIC] SYNONYM [ schema. ] synonym [FORCE] ;
DROP TABLE [ schema. ] table [ CASCADE CONSTRAINTS ] [ PURGE ] ;
DROP TABLESPACE tablespace
[ INCLUDING CONTENTS [ {AND | KEEP} DATAFILES ]
[ CASCADE CONSTRAINTS ]
] ;
DROP TRIGGER [ schema. ] trigger ;
DROP TYPE [ schema. ] type_name [ FORCE | VALIDATE ] ;
DROP TYPE BODY [ schema. ] type_name ;
DROP USER user [ CASCADE ] ;
DROP VIEW [ schema. ] view [ CASCADE CONSTRAINTS ] ;
EXPLAIN PLAN [ SET STATEMENT_ID = string ] [ INTO [ schema. ] table [ @ dblink ] ] FOR statement ;
FLASHBACK [ STANDBY ] DATABASE [ database ]
{ TO { { SCN | TIMESTAMP } expr
| RESTORE POINT restore_point
}
| TO BEFORE { SCN | TIMESTAMP} expr
| RESETLOGS
}
}
FLASHBACK TABLE
[ schema. ] table
[, [ schema. ] table ]...
TO { { { SCN | TIMESTAMP } expr
| RESTORE POINT restore_point
} [ { ENABLE | DISABLE } TRIGGERS ]
| BEFORE DROP [ RENAME TO table ]
} ;
GRANT { grant_system_privileges
| grant_object_privileges
} ;
INSERT [ hint ]
{ single_table_insert | multi_table_insert } ;
LOCK TABLE [ schema. ] { table | view }
[ partition_extension_clause
| @ dblink
] [, [ schema. ] { table | view }
[ partition_extension_clause
| @ dblink
]
]...
IN lockmode MODE
[ NOWAIT
| WAIT integer
] ;
MERGE [ hint ]
INTO [ schema. ] { table | view } [ t_alias ]
USING { [ schema. ] { table | view }
| subquery
} [ t_alias ]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ]
[ error_logging_clause ] ;
NOAUDIT
{ audit_operation_clause [ auditing_by_clause ]
| audit_schema_object_clause
| NETWORK
}
[ WHENEVER [ NOT ] SUCCESSFUL ] ;
PURGE { { TABLE table | INDEX index }
| { RECYCLEBIN | DBA_RECYCLEBIN }
| TABLESPACE tablespace [ USER username ]
} ;
RENAME old_name TO new_name ;
REVOKE { revoke_system_privileges
| revoke_object_privileges
} ;
ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] savepoint | FORCE string ] ;
SAVEPOINT savepoint ;
[ subquery_factoring_clause ] subquery [ for_update_clause ] ;
SET { CONSTRAINT | CONSTRAINTS }
{ constraint [, constraint ]...
| ALL
}
{ IMMEDIATE | DEFERRED } ;
SET ROLE
{ role [ IDENTIFIED BY password ]
[, role [ IDENTIFIED BY password ] ]...
| ALL [ EXCEPT role [, role ]... ]
| NONE
} ;
SET TRANSACTION
{ { READ { ONLY | WRITE }
| ISOLATION LEVEL
{ SERIALIZABLE | READ COMMITTED }
| USE ROLLBACK SEGMENT rollback_segment
} [ NAME string ]
| NAME string
} ;
TRUNCATE CLUSTER [schema.] cluster
[ {DROP | REUSE} STORAGE ] ;
TRUNCATE TABLE [schema.] table
[ {PRESERVE | PURGE} MATERIALIZED VIEW LOG ]
[ {DROP [ ALL ] | REUSE} STORAGE ] ;
|
Note: You can specify theALL keyword in this statement starting with Oracle Database 11g Release 2 (11.2.0.2). |
UPDATE [ hint ]
{ dml_table_expression_clause
| ONLY (dml_table_expression_clause)
} [ t_alias ]
update_set_clause
[ where_clause ]
[ returning_clause ]
[error_logging_clause] ;
This chapter presents data types that are recognized by Oracle and available for use within SQL.
This chapter includes the following sections:
This chapter presents the syntax for combining values, operators, and functions into expressions.
This chapter includes the following section:
This section shows the syntax for the Oracle-supplied data types.
{ SYS.AnyData | SYS.AnyType | SYS.AnyDataSet }
{ XMLType | URIType }
{ SDO_Geometry | SDO_Topo_Geometry |SDO_GeoRaster }
{ ORDAudio
| ORDImage
| ORDVideo
| ORDDoc
| ORDDicom
| still_image_object_types
}
Expression