The sections that follow show the syntax for each subclause found in:
See Also:
Oracle Database SQL Language Reference for detailed information about Oracle SQL
{ standard_actions | component_actions }...
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 ] [ index_compression ] [ parallel_clause ]
partitioning_storage_clause [ update_index_clauses ] [ parallel_clause ] [ indexing_clause ]
ADD individual_hash_subparts [ dependent_tables_clause ] [ update_index_clauses ] [ parallel_clause ]
list_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 ]
[ update_index_clauses ]
ADD list_subpartition_desc [, 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 ( period_definition )
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 ]
[ update_index_clauses ]
ADD range_subpartition_desc [, range_subpartition_desc ]... [ dependent_tables_clause ] [ update_index_clauses ]
[table_partition_description] [update_index_clauses]
ADD {
PARTITION [ partition ] add_range_partition_clause
[, PARTITION [ partition ] add_range_partition_clause ]...
| PARTITION [ partition ] add_list_partition_clause
[, PARTITION [ partition ] add_list_partition_clause ]...
| PARTITION [ partition ] add_system_partition_clause
[, PARTITION [ partition ] add_system_partition_clause ]...
[ BEFORE { partition_name | partition_number } ]
| PARTITION [ partition ] add_hash_partition_clause
} [ dependent_tables_clause ]
{ ADD | UPDATE } SECRET 'secret' FOR CLIENT 'client_identifier'
[ USING TAG 'tag' ]
IDENTIFIED BY keystore_password
[ WITH BACKUP [ USING 'backup_identifier' ] ]
ADD VOLUME asm_volume SIZE size_clause [redundancy_clause]
[ STRIPE_WIDTH integer {K | M} ]
[ STRIPE_COLUMNS integer ]
[ ATTRIBUTE (disk_region_clause) ]
COMPRESS ADVANCED LOW | NOCOMPRESS
+diskgroup_name [ (template_name) ] /alias_name
ALLOCATE EXTENT
[ ( { SIZE size_clause
| DATAFILE 'filename'
| INSTANCE integer
} ...
)
]
{ ALLOW | DISALLOW } CLUSTERING
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
}
ALTER KEYSTORE PASSWORD IDENTIFIED BY old_keystore_password SET new_keystore_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
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
}...
}
[ ENABLE | DISABLE ] QUERY REWRITE [ unusable_editions_clause ]
SET { { parameter_name = parameter_value }...
| EDITION = edition_name
| CONTAINER = container_name
| ROW ARCHIVAL VISIBILITY = { ACTIVE | ALL }
}
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
| 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
| inmemory_alter_table_clause
| ilm_clause
| 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
| [NO] ROW ARCHIVAL
| ADD attribute_clustering_clause
| MODIFY CLUSTERING [ clustering_when ] [ zonemap_clause ]
| DROP CLUSTERING
}
}
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
}
{ PCTFREE integer
| PCTUSED integer
| { CACHE | NOCACHE }
}...
[ 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 ])...
]
[ { integer | integer TO integer [, integer | integer TO integer ]... } | * ] Note: The outside square brackets shown in boldface type are part of the syntax. In this case, they do not represent optionality.
{ fully_qualified_file_name
| numeric_file_name
| incomplete_file_name
| alias_file_name
}
ATTRIBUTE level DETERMINES
{ dependent_column
| ( dependent_column
[, dependent_column ]... )
}
CLUSTERING [ clustering_join ] cluster_clause
[ clustering_when ] [ zonemap_clause ]
{ { 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
| SQL TRANSLATION PROFILE [ schema. ] profile
| DEFAULT
}
AUTOEXTEND
{ OFF
| ON [ NEXT size_clause ]
[ maxsize_clause ]
}
BACKUP KEYSTORE [ USING 'backup_identifier' ] IDENTIFIED BY keystore_password [ TO 'keystore_location' ]
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 ]
SET KEYSTORE CLOSE
[ IDENTIFIED BY keystore_password ]
[ CONTAINER = { ALL | CURRENT } ]
BY [ LINEAR | INTERLEAVED ] ORDER clustering_columns
CLUSTER [ schema. ] cluster index_attributes
PARTITION BY RANGE (column[, column ]...)
( PARTITION [ partition ]
range_values_clause table_partition_description
[, PARTITION [ partition ]
range_values_clause table_partition_description
]...
)
( column [, column ]... )
clustering_column_group | ( clustering_column_group [, clustering_column_group ]... )
[ schema. ] table JOIN [ schema. ] table ON ( equijoin_condition )
[, JOIN [ schema. ] table ON ( equijoin_condition ) ]...
[ { YES | NO } ON LOAD ] [ { YES | NO } ON DATA MOVEMENT ]
COALESCE PARTITION [ parallel_clause ]
COALESCE PARTITION [ update_index_clauses ] [ parallel_clause ] [ allow_disallow_clustering ]
COALESCE SUBPARTITION subpartition [update_index_clauses] [parallel_clause] [allow_disallow_clustering]
COLUMNS [ schema. ]table.column
[, [ schema. ]table.column ]...
using_statistics_type
{ { add_column_clause
| modify_column_clauses
| drop_column_clause
| add_period_clause
| drop_period_clause
}...
| rename_column_clause
| { modify_collection_retrieval }...
| { modify_LOB_storage_clause }...
| { alter_varray_col_properties }...
}
column datatype [ SORT ] [ VISIBLE | INVISIBLE ]
[ DEFAULT [ ON NULL ] expr | identity_clause ]
[ 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
]
ACTIONS COMPONENT =
{ DATAPUMP | DIRECT_LOAD | OLS | XS } component_action [, component_action ]...
|
DV component_action ON object_name [, component_action ON object_name ]...
PARTITION BY HASH (column [, column ] ...)
{ subpartition_by_range
| subpartition_by_list
| subpartition_by_hash
}
{ individual_hash_partitions
| hash_partitions_by_quantity
}
PARTITION BY LIST ( column )
{ subpartition_by_range
| subpartition_by_list
| subpartition_by_hash
}
( list_partition_desc [, list_partition_desc]... )
PARTITION BY RANGE ( column [, column]... )
[ INTERVAL ( expr ) [ STORE IN ( tablespace [, tablespace]... ) ]]
{ subpartition_by_range
| subpartition_by_list
| subpartition_by_hash
}
( range_partition_desc [, 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_name
| PRIMARY KEY
| UNIQUE (column [, column ]...)
} constraint_state [ CASCADE ]
| 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 ]
{
SET CONTAINER_DATA = { ALL | DEFAULT | ( container_name [, container_name ]... ) }
|
ADD CONTAINER_DATA = ( container_name [, container_name ]... )
|
REMOVE CONTAINER_DATA = ( container_name [, container_name ]... )
}
[ FOR [ schema. ] container_data_object ]
CONTAINERS( [schema.] { table | view } )
[ WITH INDEX CONTEXT, SCAN CONTEXT implementation_type [ COMPUTE ANCILLARY DATA ] ] [ WITH COLUMN CONTEXT ]
CREATE { [ LOGICAL | PHYSICAL ] STANDBY | FAR SYNC INSTANCE }
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
}
]
CREATE_FILE_DEST = { NONE | 'directory_path_name' | diskgroup_name }
CREATE [ ENCRYPTION ] KEY [ USING TAG 'tag' ]
IDENTIFIED BY keystore_password
[ WITH BACKUP [ USING 'backup_identifier' ] ]
[ CONTAINER = { ALL | CURRENT } ]
CREATE
{ KEYSTORE 'keystore_location'
| [ LOCAL ] AUTO_LOGIN KEYSTORE FROM KEYSTORE 'keystore_location'
}
IDENTIFIED BY keystore_password
{ 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
}
FROM { src_pdb_name [ @ dblink ] } | { NON$CDB @ dblink }
[ pdb_storage_clause ]
[ file_name_convert ]
[ path_prefix_clause ]
[ tempfile_reuse_clause ]
[ SNAPSHOT COPY ]
[ user_tablespaces_clause ]
[ standbys_clause ]
[ logging_clause ]
[ create_file_dest_clause ]
[ NO DATA ]
ADMIN USER admin_user_name IDENTIFIED BY password [ pdb_dba_roles ] [ default_tablespace ] [ file_name_convert ] [ pdb_storage_clause ] [ path_prefix_clause ] [ tempfile_reuse_clause ] [ user_tablespaces_clause ] [ standbys_clause ] [ logging_clause ] [ create_file_dest_clause ]
[ AS CLONE ] USING filename [ source_file_name_convert ]
[ { [ COPY | MOVE ] file_name_convert } | NOCOPY ]
[ pdb_storage_clause ]
[ path_prefix_clause ]
[ tempfile_reuse_clause ]
[ user_tablespaces_clause ]
[ standbys_clause ]
[ logging_clause ]
[ create_file_dest_clause ]
CREATE MATERIALIZED ZONEMAP
[ schema. ] zonemap_name
[ ( column_alias [, column_alias ]... ) ]
[ zonemap_attributes ]
[ zonemap_refresh_clause ]
[ { ENABLE | DISABLE } PRUNING ]
AS query_block
CREATE MATERIALIZED ZONEMAP
[ schema. ] zonemap_name
[ zonemap_attributes ]
[ zonemap_refresh_clause ]
[ { ENABLE | DISABLE } PRUNING ]
ON [ schema. ] { table | materialized_view } ( column [, column]... )
{ CROSS | OUTER } APPLY { table_reference | collection_expression }
{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
| move_datafile_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
| [NO] FORCE FULL DATABASE CACHING
| 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 }
DELETE SECRET FOR CLIENT 'client_identifier' IDENTIFIED BY keystore_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
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
} [ POWER integer ] [ 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 }... ]
[ ONLINE ]
| 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_name
[ CASCADE ]
} [ ONLINE ]
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 ( PERIOD FOR valid_time_column )
DROP partition_extended_names [ update_index_clauses [ parallel_clause ] ]
DROP subpartition_extended_names [ 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_name
}
[ using_index_clause ]
[ exceptions_clause ]
[ CASCADE ]
[ { KEEP | DROP } INDEX ]
{ ENABLE | DISABLE } VOLUME
{ asm_volume [, asm_volume]...
| ALL
}
ENABLE PLUGGABLE DATABASE [ SEED [ file_name_convert ] [ SYSTEM tablespace_datafile_clauses ] [ SYSAUX tablespace_datafile_clauses ] ]
[ USING 'encrypt_algorithm' ] [ IDENTIFIED BY password ] [ 'integrity_algorithm' ] [ [ NO ] SALT ]
{ DISCONNECT SESSION 'integer1, integer2'
[ POST_TRANSACTION ]
| KILL SESSION 'integer1, integer2 [, @integer3]'
}
[ IMMEDIATE | NOREPLAY ]
LOG ERRORS
[ INTO [schema.] table ]
[ (simple_expression) ]
[ REJECT LIMIT { integer | UNLIMITED } ]
EVALUATE USING { CURRENT EDITION | EDITION edition | NULL EDITION }
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 ] ]
[ CASCADE ]
EXPORT [ ENCRYPTION ] KEYS WITH SECRET secret
TO 'filename'
IDENTIFIED BY keystore_password
[ WITH IDENTIFIER IN { 'key_id' [, 'key_id' ]... | ( subquery ) } ]
{ simple_expression
| compound_expression
| case_expression
| cursor_expression
| datetime_expression
| function_expression
| interval_expression
| JSON_object_access_expr
| model_expression
| object_access_expression
| scalar_subquery_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 } ]
FAILOVER TO target_db_name [ FORCE ]
FILE_NAME_CONVERT =
{ ( 'filename_pattern', 'replacement_filename_pattern'
[, 'filename_pattern', 'replacement_filename_pattern' ]... )
|
NONE
}
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 }
| VERSIONS PERIOD FOR valid_time_column BETWEEN
{ expr | MINVALUE } AND { expr | MAXVALUE }
| AS OF { SCN | TIMESTAMP } expr
| AS OF PERIOD FOR valid_time_column expr
}
{ FOR SYNCHRONOUS REFRESH USING staging_log_name
| FOR FAST REFRESH
}
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
| SNAPSHOT TIME date
}...
]
+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 ]
role [, role ]... TO program_unit [, program_unit ]...
{ system_privilege | role | ALL PRIVILEGES }
[, { system_privilege | role | ALL PRIVILEGES } ]...
TO { grantee_clause | grantee_identified_by } [ WITH { ADMIN | DELEGATE } OPTION ]
{ user | role | PUBLIC }
[, { user | role | PUBLIC } ]...
user [, user ]... IDENTIFIED BY password [, password ]...
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 ]...) ] [ table_compression | index_compression ] [ OVERFLOW STORE IN (tablespace [, tablespace ]...) ]
SUBPARTITIONS integer [STORE IN ( tablespace [, tablespace]... )]
[ table_compression ] [ inmemory_table_clause ] [ ilm_clause ]
{ CONNECT BY [ NOCYCLE ] condition [ START WITH condition ]
| START WITH condition CONNECT BY [ NOCYCLE ] condition
}
HIERARCHY hierarchy
(child_level { CHILD OF parent_level }...
[ dimension_join_clause ]
)
GENERATED [ ALWAYS | BY DEFAULT [ ON NULL ] ] AS IDENTITY [ ( identity_options ) ]
{ START WITH ( integer | LIMIT VALUE )
| INCREMENT BY integer
| ( MAXVALUE integer | NOMAXVALUE )
| ( MINVALUE integer | NOMINVALUE )
| ( CYCLE | NOCYCLE )
| ( CACHE integer | NOCACHE )
| ( ORDER | NOORDER ) }...
ILM
{ ADD POLICY ilm_policy_clause
| { DELETE | ENABLE | DISABLE } POLICY ilm_policy_name
| DELETE_ALL | ENABLE_ALL | DISABLE_ALL
}
{ table_compression | tiering_clause }
{ SEGMENT | GROUP | ROW }
{{ AFTER number { { DAY | DAYS } | { MONTH | MONTHS } | { YEAR | YEARS } }
OF { { NO ACCESS } | { NO MODIFICATION } | CREATION } }
| ON function_name }
{ ANCILLARY TO primary_operator
( parameter_type [, parameter_type ]...)
[, primary_operator
( parameter_type [, parameter_type ]...)
]...
| context_clause
}
IMPORT [ ENCRYPTION ] KEYS WITH SECRET secret FROM 'filename' IDENTIFIED BY keystore_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
+diskgroup_name [ (template_name) ]
[ { physical_attributes_clause
| logging_clause
| ONLINE
| TABLESPACE { tablespace | DEFAULT }
| index_compression
| { SORT | NOSORT }
| REVERSE
| VISIBLE | INVISIBLE
| partial_index_clause
| parallel_clause
}...
]
{ prefix_compression
| advanced_index_compression
}
{ column | column_expression }
[ INCLUDING column_name ] OVERFLOW [ segment_attributes_clause ]
[ { mapping_table_clause
| PCTTHRESHOLD integer
| prefix_compression
}...
]
[ index_org_overflow_clause ]
PARTITION
[ partition
[ { segment_attributes_clause
| index_compression
}...
| PARAMETERS ( 'ODCI_parameters' )
]
[ USABLE | 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 ] [ index_compression ] [ USABLE | UNUSABLE ]
[, SUBPARTITION
[ subpartition ][ TABLESPACE tablespace ] [ index_compression ] [ USABLE | UNUSABLE ]
]...
)
}
INDEXING { ON | OFF }
PARTITION [partition] [indexing_clause] [partitioning_storage_clause] [, PARTITION [partition] [indexing_clause] [partitioning_storage_clause]]...
SUBPARTITION [subpartition] [indexing_clause] [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 ]
}
[ INMEMORY [ inmemory_parameters ] ] [ inmemory_column_clause ] | NO INMEMORY
INMEMORY [ inmemory_parameters ] | NO INMEMORY
{ INMEMORY [ inmemory_memcompress ] | NO INMEMORY } ( column [, column ]... )
[ { INMEMORY [ inmemory_memcompress ] | NO INMEMORY } ( column [, column ]... ) ]...
DISTRIBUTE [ AUTO | BY { ROWID RANGE | PARTITION | SUBPARTITION } ]
DUPLICATE | DUPLICATE ALL | NO DUPLICATE
MEMCOMPRESS FOR { DML | QUERY [ LOW | HIGH ] | CAPACITY [ LOW | HIGH ] }
| NO MEMCOMPRESS
[ inmemory_memcompress ] [ inmemory_priority ] [ inmemory_distribute ] [ inmemory_duplicate ]
PRIORITY { NONE | LOW | MEDIUM | HIGH | CRITICAL }
INMEMORY [ inmemory_parameters ] [ inmemory_column_clause ] | NO INMEMORY
{ [ 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'
INSTANCES = { ( 'instance_name' [, 'instance_name' ]... )
| ALL [ EXCEPT ( 'instance_name' [, '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 | cross_outer_apply_clause }...
JSON_exists_column | JSON_query_column | JSON_value_column | JSON_nested_path | ordinality_column
COLUMNS ( JSON_column_definition [, JSON_column_definition ]... )
column_name JSON_value_return_type EXISTS PATH JSON_path_expression [ JSON_exists_on_error_clause ]
{ ERROR | TRUE | FALSE } ON ERROR
NESTED PATH JSON_path_expression JSON_columns_clause
$[ object_step | array_step ]...
column_name JSON_query_return_type FORMAT JSON [ JSON_query_wrapper_clause ] PATH JSON_path_expression [ JSON_query_on_error_clause ]
{ ERROR | NULL | EMPTY } ON ERROR
VARCHAR2 [ ( size [BYTE | CHAR] ) ]
[ RETURNING JSON_query_return_type ] [ PRETTY ] [ ASCII ]
WITHOUT [ ARRAY ] WRAPPER | WITH [ UNCONDITIONAL | CONDITIONAL ] [ ARRAY ] WRAPPER
{ ERROR | NULL | DEFAULT literal } ON ERROR
column_name JSON_value_return_type PATH JSON_path_expression [ JSON_value_on_error_clause ]
{ ERROR | NULL | DEFAULT literal } ON ERROR
{ VARCHAR2 [ ( size [BYTE | CHAR] ) ]
| NUMBER [ ( precision [, scale] ) ]
}
[ RETURNING JSON_value_return_type ] [ ASCII ]
{ set_key
| create_key
| use_key
| set_key_tag
| export_keys
| import_keys
| migrate_key
| reverse_migrate_key
}
{ create_keystore
| open_keystore
| close_keystore
| backup_keystore
| alter_keystore_password
| merge_into_new_keystore
| merge_into_exist_keystore
}
LEVEL level IS
{ level_table.level_column
| (level_table.level_column
[, level_table.level_column ]...
)
}
PARTITION [partition]
list_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 LIST (column)
(PARTITION [ partition ]
list_values_clause table_partition_description
[, PARTITION [ partition ]
list_values_clause table_partition_description
]...
)
SUBPARTITION [subpartition] list_values_clause [indexing_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 ARCHIVED 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 KEYSTORE 'keystore1_location' [ IDENTIFIED BY keystore1_password ] INTO EXISTING KEYSTORE 'keystore2_location' IDENTIFIED BY keystore2_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
MERGE KEYSTORE 'keystore1_location' [ IDENTIFIED BY keystore1_password ] AND KEYSTORE 'keystore2_location' [ IDENTIFIED BY keystore2_password ] INTO NEW KEYSTORE 'keystore3_location' IDENTIFIED BY keystore3_password
MERGE PARTITIONS partition_or_key_value
{ , partition_or_key_value [, partition_or_key_value ]...
| TO partition_or_key_value }
[ INTO partition_spec ]
[ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
[ allow_disallow_clustering ]
MERGE SUBPARTITIONS subpartition_or_key_value
{ , subpartition_or_key_value [, subpartition_or_key_value ]...
| TO subpartition_or_key_value }
[ INTO { range_subpartition_desc
| list_subpartition_desc
}
]
[ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
[ allow_disallow_clustering ]
WHEN MATCHED THEN
UPDATE SET column = { expr | DEFAULT }
[, column = { expr | DEFAULT } ]...
[ where_clause ]
[ DELETE where_clause ]
SET [ ENCRYPTION ] KEY IDENTIFIED BY HSM_auth_string MIGRATE USING software_keystore_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
[ query_partition_clause ] [ order_by_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 [ ON NULL ] expr | identity_clause | DROP IDENTITY ]
[ { ENCRYPT encryption_spec } | DECRYPT ]
[ inline_constraint ... ]
[ LOB_storage_clause ]
[ alter_XMLSchema_clause ]
COLUMN column [ NOT ] SUBSTITUTABLE AT ALL LEVELS [ FORCE ]
column { VISIBLE | INVISIBLE }
MODIFY NESTED TABLE collection_item
RETURN AS { LOCATOR | VALUE }
MODIFY
{ ( modify_col_properties | modify_virtcol_properties
[, modify_col_properties | modify_virtcol_properties ]... )
| ( modify_col_visibility [, modify_col_visibility ]... )
| modify_col_substitutable
}
MODIFY FILE 'filename' ATTRIBUTE ( disk_region_clause ) [, 'filename' ATTRIBUTE ( disk_region_clause ) ]...
MODIFY partition_extended_name
{ partition_attributes
| coalesce_table_subpartition
| alter_mapping_table_clause
| [ REBUILD ] UNUSABLE LOCAL INDEXES
| indexing_clause
}
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
| index_compression
}...
| PARAMETERS ('ODCI_parameters')
| COALESCE [ CLEANUP ]
| 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_table_subpartition
| [ REBUILD ] UNUSABLE LOCAL INDEXES
| indexing_clause
}
{ 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 OPAQUE TYPE anydata_column STORE ( type_name [, type_name ]... ) UNPACKED
MODIFY partition_extended_name
{ partition_attributes
| { add_range_subpartition
| add_hash_subpartition
| add_list_subpartition
}
| coalesce_table_subpartition
| alter_mapping_table_clause
| [ REBUILD ] UNUSABLE LOCAL INDEXES
| indexing_clause
}
MODIFY DEFAULT ATTRIBUTES
[ FOR partition_extended_name ]
[ deferred_segment_creation ]
[ indexing_clause ]
[ segment_attributes_clause ]
[ table_compression ]
[ inmemory_clause ]
[ PCTTHRESHOLD integer ]
[ prefix_compression ]
[ alter_overflow_clause ]
[ { LOB (LOB_item) | VARRAY varray } (LOB_parameters) ]...
{ 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]... )
| indexing_clause
}
column [ datatype ] [ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ] evaluation_edition_clause [ unusable_editions_clause ]
MODIFY VOLUME asm_volume [ ATTRIBUTE (disk_region_clause) ] [ MOUNTPATH 'mountpath_name' ] [ USAGE 'usage_name' ]
MOVE DATAFILE ( 'filename' | 'ASM_filename' | file_number ) [ TO ( 'filename' | 'ASM_filename' ) ] [ REUSE ] [ KEEP ]
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 ]
[ allow_disallow_clustering ]
MOVE partition_extended_name [ MAPPING TABLE ] [ table_partition_description ] [ update_index_clauses ] [ parallel_clause ] [ allow_disallow_clustering ] [ ONLINE ]
MOVE subpartition_extended_name [ indexing_clause ]
[ partitioning_storage_clause ] [ update_index_clauses ]
[ parallel_clause ] [ allow_disallow_clustering ] [ ONLINE ]
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
}
}
.{ simple_name | "complex_name" | * }
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 | ID }
{ 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
| index_compression
}...
] [ USABLE | UNUSABLE ] [ index_subpartition_clause ]
[, PARTITION
[ partition ]
[ { segment_attributes_clause
| index_compression
}...
] [ USABLE | UNUSABLE ] [ index_subpartition_clause ]
]...
)
{ STORE IN (tablespace[, tablespace ]...)
| (PARTITION [ partition ] [ TABLESPACE tablespace ]
[ index_compression ] [ USABLE | UNUSABLE ]
[, PARTITION [ partition ] [ TABLESPACE tablespace ]
[ index_compression ] [ USABLE | UNUSABLE ]] ...
)
}
( PARTITION
[ partition ]
[ { segment_attributes_clause
| index_compression
}...
] [ USABLE | UNUSABLE ]
[, PARTITION
[ partition ]
[ { segment_attributes_clause
| index_compression
}...
] [ USABLE | UNUSABLE ]
]...
)
ON { [ schema. ] object
| USER user [, user]...
| DIRECTORY directory_name
| EDITION edition_name
| MINING MODEL [ schema. ] mining_model_name
| JAVA { SOURCE | RESOURCE } [ schema. ] object
| SQL TRANSLATION PROFILE [ schema. ] profile
}
( PARTITION
[ partition ]
[ { segment_attributes_clause
| index_compression
}...
] [ USABLE | UNUSABLE ]
[, PARTITION
[ partition ]
[ { segment_attributes_clause
| index_compression
}...
] [ USABLE | UNUSABLE ]
]...
)
SET KEYSTORE OPEN
IDENTIFIED BY keystore_password
[ CONTAINER = { ALL | CURRENT } ]
ORDER [ SIBLINGS ] BY
{ expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
[, { expr | position | c_alias }
[ ASC | DESC ]
[ NULLS FIRST | NULLS LAST ]
]...
column_name FOR ORDINALITY
[ 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 ] [ 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 ]...
}
INDEXING { PARTIAL | FULL }
[ { 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 ]
[ inmemory_clause ]
[ { { LOB LOB_item | VARRAY varray } (modify_LOB_parameters) }...]
PARTITION partition | PARTITION FOR ( partition_key_value [, partition_key_value]... )
{ PARTITION | PARTITIONS }
partition | { FOR ( partition_key_value [, partition_key_value ]... ) }
[, 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 | FOR ( partition_key_value [, partition_key_value ]... )
PARTITION [ partition ] [ table_partition_description ]
[ { TABLESPACE tablespace
| OVERFLOW [TABLESPACE tablespace]
| table_compression
| index_compression
| inmemory_clause
| 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 }
}
PATH_PREFIX = { 'path_name' | NONE }
[ pdb_name ] { pdb_open | pdb_close | pdb_save_or_discard_state }
{ pdb_name [, pdb_name ]... | ALL [ EXCEPT pdb_name [, pdb_name ]... ] }
{ pdb_open | pdb_close | pdb_save_or_discard_state }
CLOSE [ IMMEDIATE ] [ instances_clause | relocate_clause ]
[ pdb_name ] DATAFILE
{ { { 'filename' | filenumber } [, 'filename' | filenumber ]... } | ALL }
{ ONLINE | OFFLINE }
ROLES = ( role [, role ]... )
{ ENABLE | DISABLE } FORCE { LOGGING | NOLOGGING }
RECOVER [ AUTOMATIC ] [ FROM 'location' ]
[ DATABASE
|
TABLESPACE tablespace [, tablespace ]...
|
DATAFILE { 'filename' | filenumber }
[, 'filename' | filenumber ]...
|
LOGFILE 'filename'
|
CONTINUE [ DEFAULT ]
]
{ logging_clause
| pdb_force_logging_clause
}
OPEN
{ [ READ WRITE | READ ONLY ] [ RESTRICTED ] [ FORCE ]
| [ READ WRITE ] UPGRADE [ RESTRICTED ]
| RESETLOGS
}
[ instances_clause ]
[ pdb_name ] { pdb_general_recovery
| { BEGIN | END } BACKUP
| { ENABLE | DISABLE } RECOVERY
}
{ SAVE | DISCARD } STATE [ instances_clause ]
[ pdb_name ]
{ DEFAULT EDITION = edition_name
| SET DEFAULT ( BIGFILE | SMALLFILE ) TABLESPACE
| DEFAULT TABLESPACE tablespace_name
| DEFAULT TEMPORARY TABLESPACE { tablespace | tablespace_group_name }
| RENAME GLOBAL_NAME TO database.domain [. domain ]...
| set_time_zone_clause
| database_file_clauses
| supplemental_db_logging
| pdb_storage_clause
| pdb_logging_clauses
}
STORAGE
{ ( MAXSIZE { UNLIMITED | size_clause }
|
MAX_SHARED_TEMP_SIZE { UNLIMITED | size_clause }... )
|
UNLIMITED
}
pdb_name UNPLUG INTO 'filename'
PERIOD FOR valid_time_column [ ( start_time_column, end_time_column ) ]
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 ] [ inmemory_clause ] [ 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 ]
[ inmemory_table_clause ] [ ilm_clause ]
| [ deferred_segment_creation ] ORGANIZATION
{ HEAP [ segment_attributes_clause ] heap_org_table_clause
| INDEX [ segment_attributes_clause ] index_org_table_clause
| EXTERNAL external_table_clause
}
| CLUSTER cluster (column [, column ]...)
}
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]...
}
)
{ function_declaration | procedure_declaration }...
COMPRESS [ integer ] | NOCOMPRESS
PRIVILEGES system_privilege [, system_privilege ]...
{ FUNCTION [ schema. ] function_name
|
PROCEDURE [ schema. ] procedure_name
|
PACKAGE [ schema. ] package_name }
{ 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 )
[ with_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
{ expr[, expr ]...
| ( expr[, expr ]... )
}
{ ENABLE | DISABLE } QUERY REWRITE [ unusable_editions_clause ]
{ query_name
| [ schema. ]
{ table [ partition_extension_clause
| @ dblink
]
| { view | materialized view } [ @ dblink ]
} [sample_clause]
| [ LATERAL ] (subquery [ subquery_restriction_clause ])
| table_collection_expression
}
QUIESCE RESTRICTED | UNQUIESCE
PARTITION [partition]
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 [indexing_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
| index_compression
| logging_clause
| partial_index_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_model_name ON (subquery) model_column_clauses [ cell_reference_options ]
PARTITION [partition] [table_partition_description] )
PARTITION BY REFERENCE ( constraint ) [ (reference_partition_desc...) ]
REFERENCES [ schema. ] object [ (column [, column ]...) ]
[ON DELETE { CASCADE | SET NULL } ]
REGISTER [ OR REPLACE ] [ PHYSICAL | LOGICAL ] LOGFILE [ file_specification [, file_specification ]... [ FOR logminer_session_name ]
{ column_definition
| virtual_column_definition
| period_definition
| { out_of_line_constraint | out_of_line_ref_constraint }
| supplemental_logging_props
}
[, { column_definition
| virtual_column_definition
| period_definition
| { out_of_line_constraint | out_of_line_ref_constraint }
| supplemental_logging_props
}
]...
[ (relational_properties) ]
[ ON COMMIT { DELETE | PRESERVE } ROWS ]
[ physical_properties ]
[ table_properties ]
RELOCATE [ TO 'instance_name' ] | NORELOCATE
RENAME COLUMN old_name TO new_name
RENAME
{ DISK old_disk_name TO new_disk_name [, old_disk_name TO new_disk_name ]...
| DISKS ALL }
RENAME
{ PARTITION partition | SUBPARTITION subpartition }
TO new_name
RENAME { partition_extended_name
| subpartition_extended_name
} TO new_name
REPLACE DISK disk_name WITH 'path_name' [ FORCE | NOFORCE ] [, disk_name WITH 'path_name' [ FORCE | NOFORCE ] ]... [ POWER integer ] [ WAIT | NOWAIT ]
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 ]...
SET [ ENCRYPTION ] KEY IDENTIFIED BY software_keystore_password REVERSE MIGRATE USING HSM_auth_string
{ object_privilege | ALL [ PRIVILEGES ] }
[, { object_privilege | ALL [ PRIVILEGES ] } ]...
on_object_clause
FROM revokee_clause
[ CASCADE CONSTRAINTS | FORCE ]
{ role [, role ]... | ALL } FROM program_unit [, program_unit ]...
{ system_privilege | role | ALL PRIVILEGES }
[, { system_privilege | role | ALL PRIVILEGES } ]...
FROM revokee_clause
{ user | role | PUBLIC }
[, { user | role | PUBLIC } ]...
ROLES role [, role ]...
{ START ROLLING MIGRATION TO 'ASM_version'
| STOP ROLLING MIGRATION
}
{ START ROLLING PATCH
| STOP ROLLING PATCH
}
{ ROLLUP | CUBE } (grouping_expression_list)
[ schema. ] [ type. | package. ]
{ function | procedure | method }
[ @dblink_name ]
( [ argument [, argument ]... ] )
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
{ ENABLE | DISABLE } ROW MOVEMENT
[ row_pattern | ] row_pattern_term Note: The vertical bar is part of the syntax rather than BNF notation.
[ RUNNING | FINAL ] aggregate_function
CLASSIFIER()
MATCH_RECOGNIZE {
[ row_pattern_partition_by ]
[ row_pattern_order_by ]
[ row_pattern_measures ]
[ row_pattern_rows_per_match ]
[ row_pattern_skip_to ]
PATTERN (row_pattern)
[ row_pattern_subset_clause ]
DEFINE row_pattern_definition_list
}
variable_name AS condition
row_pattern_definition [, row_pattern_definition ]...
row_pattern_primary [ row_pattern_quantifier ]
MATCH_NUMBER()
expr AS c_alias
MEASURES row_pattern_measure_column [, row_pattern_measure_column ]...
{ PREV | NEXT }
( [ RUNNING | FINAL ] { FIRST | LAST } ( expr [, offset ] ) [, offset] )
[ RUNNING | FINAL ] { FIRST | LAST } ( expr [, offset ] )
{ PREV | NEXT } ( expr [, offset ] )
row_pattern_nav_logical | row_pattern_nav_physical | row_pattern_nav_compound
ORDER BY column [, column ]...
PARTITION BY column [, column ]...
PERMUTE ( row_pattern [, row_pattern ]...)
variable_name
| $
| ^
| ( [ row_pattern ] )
| {- row_pattern -}
| row_pattern_permute
Note: The curly brackets are part of the syntax rather than BNF notation.
* [ ? ]
| + [ ? ]
| ? [ ? ]
| { [ unsigned_integer ] , [ unsigned_integer ] } [ ? ]
| { unsigned_integer }
Note: The curly brackets are part of the syntax rather than BNF notation.
row_pattern_classifier_func | row_pattern_match_num_func | row_pattern_navigation_func | row_pattern_aggregate_func
ONE ROW PER MATCH | ALL ROWS PER MATCH
AFTER MATCH {
SKIP TO NEXT ROW
| SKIP PAST LAST ROW
| SKIP TO FIRST variable_name
| SKIP TO LAST variable_name
| SKIP TO variable_name
}
SUBSET row_pattern_subset_item [, row_pattern_subset_item ]...
variable_name = ( variable_name [, variable_name ] )
[ row_pattern_term ] row_pattern_factor
SAMPLE [ BLOCK ]
(sample_percent)
[ SEED (seed_value) ]
{ SCOPE FOR ({ ref_column | ref_attribute })
IS [ schema. ] { scope_table_name | c_alias }
}
SCRUB [ FILE 'ASM_filename' | DISK disk_name ]
[ REPAIR | NOREPAIR ]
[ POWER { AUTO | LOW | HIGH | MAX } ]
[ WAIT | NOWAIT ]
[ FORCE | NOFORCE ]
{ 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 }...
{ add_update_secret
| delete_secret
}
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" ]
}
}
SET [ ENCRYPTION ] KEY [ USING TAG 'tag' ]
IDENTIFIED BY keystore_password
[ WITH BACKUP [ USING 'backup_identifier' ] ]
[ CONTAINER = { ALL | CURRENT } ]
SET TAG 'tag' FOR 'key_id' IDENTIFIED BY keystore_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
parameter_name =
parameter_value [, parameter_value ]...
[ COMMENT = string ]
[ DEFERRED ]
[ { SCOPE = { MEMORY | SPFILE | BOTH }
| SID = { 'sid' | '*' }
}...
]
[ CONTAINER = { CURRENT | ALL } ]
SET SUBPARTITION TEMPLATE
{ ( range_subpartition_desc [, range_subpartition_desc]... )
| ( list_subpartition_desc [, list_subpartition_desc]... )
| ( individual_hash_subparts [, individual_hash_subparts]... )
| ()
| 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 ]
SOURCE_FILE_NAME_CONVERT =
{ ( 'filename_pattern', 'replacement_filename_pattern'
[, 'filename_pattern', 'replacement_filename_pattern' ]... )
|
NONE
}
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 ) ]
| INTO ( { range_partition_desc [, range_partition_desc ]...
| list_partition_desc [, list_partition_desc ]... }
, partition_spec )
} [ split_nested_table_part ]
[ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
[ allow_disallow_clustering ]
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) ]
| INTO ( { range_subpartition_desc [, range_subpartition_desc ]...
| list_subpartition_desc [, list_subpartition_desc ]... }
, subpartition_spec )
} [ dependent_tables_clause ]
[ update_index_clauses ]
[ parallel_clause ]
[ allow_disallow_clustering ]
[+ | -] days hours : minutes : seconds [. frac_secs ]
ACTIONS
{ { object_action | ALL }
ON { DIRECTORY directory_name
| MINING MODEL [ schema. ] object_name
| [ schema. ] object_name }
| { system_action | ALL }
}
[ { object_action | ALL }
ON { DIRECTORY directory_name
| MINING MODEL [ schema. ] object_name
| [ schema. ] object_name }
| { system_action | ALL } ]...
{ { 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 ] }
|
{ switchover_clause | failover_clause }
STANDBYS = { NONE | ALL }
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 | SUBPARTITIONS }
subpartition | { FOR ( subpartition_key_value [, subpartition_key_value ]... ) }
[, subpartition | { FOR ( subpartition_key_value [, subpartition_key_value ]... ) } ]...
subpartition | FOR ( subpartition_key_value [, subpartition_key_value ]... )
SUBPARTITION [ subpartition ] [ partitioning_storage_clause ]
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 ] [ row_limiting_clause ]
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
SWITCHOVER TO target_db_name [ VERIFY | FORCE ]
PARTITION BY SYSTEM [ PARTITIONS integer
| reference_partition_desc
[, reference_partition_desc ...]
]
TABLE (collection_expression) [ (+) ]
COMPRESS
| ROW STORE COMPRESS [ BASIC | ADVANCED ]
| COLUMN STORE COMPRESS [ FOR { QUERY | ARCHIVE } [ LOW | HIGH ] ]
[ [NO] ROW LEVEL LOCKING ]
| NOCOMPRESS
[ schema. ] table [ t_alias ] (index_expr [ ASC | DESC ] [, index_expr [ ASC | DESC ] ]...) [ index_properties ]
[ deferred_segment_creation ]
[ indexing_clause ]
[ segment_attributes_clause ]
[ table_compression | prefix_compression ]
[ inmemory_clause ]
[ OVERFLOW [ segment_attributes_clause ] ]
[ { LOB_storage_clause
| varray_col_properties
| nested_table_col_properties
}...
]
{ range_partitions
| list_partitions
| hash_partitions
| composite_range_partitions
| composite_list_partitions
| composite_hash_partitions
| reference_partitioning
| system_partitioning
}
[ column_properties ]
[ indexing_clause ]
[ table_partitioning_clauses ]
[ attribute_clustering_clause ]
[ CACHE | NOCACHE ]
[ RESULT_CACHE ( MODE {DEFAULT | FORCE } ) ]
[ parallel_clause ]
[ ROWDEPENDENCIES | NOROWDEPENDENCIES ]
[ enable_disable_clause ]...
[ row_movement_clause ]
[ flashback_archive_clause ]
[ ROW ARCHIVAL ]
[ AS subquery ]
{ { { ONLY (query_table_expression) | query_table_expression }
[ flashback_query_clause ]
[ pivot_clause | unpivot_clause | row_pattern_clause ] }
| containers_clause
}
[ t_alias ]
{ EXTENT MANAGEMENT LOCAL
| DATAFILE file_specification [, file_specification ]...
| SYSAUX DATAFILE file_specification [, file_specification ]...
| default_tablespace
| default_temp_tablespace
| undo_tablespace
}
DATAFILES { SIZE size_clause | autoextend_clause }...
[ 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 }
}
TEMPFILE REUSE
TEMPORARY TABLESPACE tablespace [ TEMPFILE file_specification [, file_specification ]... ] [ tablespace_group_clause ] [ extent_management_clause ]
TIER TO tablespace [ READ ONLY ]
DROP AFTER integer { M | H }
TRACE [ AS 'filename' [ REUSE ] ] [ RESETLOGS | NORESETLOGS ]
TRUNCATE { partition_extended_names | subpartition_extended_names }
[ { DROP [ ALL ] | REUSE } STORAGE ]
[ update_index_clauses [ parallel_clause ] ] [ CASCADE ]
[ 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
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]... ) } ]
]...
)
[ UNUSABLE BEFORE { CURRENT EDITION | EDITION edition } ]
[ UNUSABLE BEGINNING WITH { CURRENT EDITION | EDITION edition | NULL EDITION } ]
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 ]
USE [ ENCRYPTION ] KEY 'key_id' [ USING TAG 'tag' ] IDENTIFIED BY keystore_password [ WITH BACKUP [ USING 'backup_identifier' ] ]
{ ADD USER user [, user]...
| DROP USER user [, user]... [CASCADE]
| REPLACE USER old_user WITH new_user [, old_user WITH new_user]...
}
USER_TABLESPACES =
{ ( 'tablespace' [, 'tablespace' ]... )
| NONE
| ALL [ EXCEPT ( 'tablespace' [, 'tablespace' ]... ) ]
}
{ 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 ] [ VISIBLE | INVISIBLE ] [ GENERATED ALWAYS ] AS (column_expression) [ VIRTUAL ] [ evaluation_edition_clause ] [ unusable_editions_clause ] [ 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
}
}
WITH [ plsql_declarations ] [ subquery_factoring_clause ]
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 | XMLTYPE [ (SEQUENCE) BY REF ] }
[ PATH string ] [ DEFAULT expr ]
}
[XDB.] XMLINDEX [ local_XMLIndex_clause ]
[ parallel_clause ]
[ XMLIndex_parameters_clause ]
[ XMLSCHEMA XMLSchema_URL ]
ELEMENT { element | XMLSchema_URL # element }
[ STORE ALL VARRAYS AS { LOBS | TABLES } ]
[ { ALLOW | DISALLOW } NONSCHEMA ]
[ { ALLOW | DISALLOW } ANYSCHEMA ]
[ XML_passing_clause ] [ RETURNING SEQUENCE BY REF ] [ 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 | ID }
{ 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 ] ]
{ TABLESPACE tablespace
| SCALE integer
| PCTFREE integer
| PCTUSED integer
| { CACHE | NOCACHE }
}...
{ WITH MATERIALIZED ZONEMAP [ ( zonemap_name ) ] }
|
{ WITHOUT MATERIALIZED ZONEMAP }
REFRESH
[ FAST | COMPLETE | FORCE ]
[ ON { DEMAND | COMMIT | LOAD | DATA MOVEMENT | LOAD DATA MOVEMENT } ]