1 SQL Statements
This chapter presents the syntax for Oracle SQL statements.
This chapter includes the following section:
Syntax for SQL Statements
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 Subclauses for the syntax of the subclauses listed in the syntax for the statements.
See Also:
Oracle Database SQL Language Reference for detailed information about SQL statements
ADMINISTER KEY MANAGEMENT { keystore_management_clauses | key_management_clauses | secret_management_clauses } ;
ALTER ANALYTIC VIEW [ schema. ] analytic_view_name { RENAME TO new_av_name | COMPILE };
ALTER ATTRIBUTE DIMENSION [ schema. ] attr_dim_name { RENAME TO new_attr_dim_name | COMPILE };
ALTER AUDIT POLICY policy [ ADD [ privilege_audit_clause ] [ action_audit_clause ] [ role_audit_clause ] ] [ DROP [ privilege_audit_clause ] [ action_audit_clause ] [ role_audit_clause ] ] [ CONDITION { DROP | 'audit_condition' EVALUATE PER { STATEMENT | SESSION | INSTANCE } } ] [ ONLY TOPLEVEL ] ;
ALTER CLUSTER [ schema. ] cluster { physical_attributes_clause | SIZE size_clause | [ MODIFY PARTITION partition ] 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 | prepare_clause | drop_mirror_copy | lost_write_protection | cdb_fleet_clauses | property_clause } ;
ALTER DATABASE DICTIONARY
ALTER DATABASE DICTIONARY { ENCRYPT CREDENTIALS | REKEY CREDENTIALS | DELETE CREDENTIALS KEY };
ALTER [ SHARED ] [ PUBLIC ] 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_clause } [ rebalance_diskgroup_clause ] | replace_disk_clause | rename_disk_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 | convert_redundancy_clause | usergroup_clauses | user_clauses | file_permissions_clause | file_owner_clause | scrub_clause | quotagroup_clauses | filegroup_clauses } | { 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 } } | [NO] OPTIMIZE DATA };
ALTER FUNCTION [ schema. ] function_name { function_compile_clause | { EDITIONABLE | NONEDITIONABLE } }
ALTER HIERARCHY [ schema. ] hierarchy_name { RENAME TO new_hier_name | COMPILE };
ALTER INDEX [ schema. ]index { { deallocate_unused_clause | allocate_extent_clause | shrink_clause | parallel_clause | physical_attributes_clause | logging_clause | partial_index_clause } ... | rebuild_clause [ { DEFERRED | IMMEDIATE } INVALIDATION ] | PARAMETERS ( 'ODCI_parameters' ) | COMPILE | { ENABLE | DISABLE } | UNUSABLE [ ONLINE ] [ { DEFERRED | IMMEDIATE } INVALIDATION ] | VISIBLE | INVISIBLE | RENAME TO new_name | COALESCE [ CLEANUP ] [ parallel_clause ] | { 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 INMEMORY JOIN GROUP [ schema. ] join_group { ADD | REMOVE } ( [ schema. ] table ( column ) ) ;
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 | { EDITIONABLE | NONEDITIONABLE } }
ALTER LOCKDOWN PROFILE { lockdown_features | lockdown_options | lockdown_statements } ;
ALTER MATERIALIZED VIEW [ schema. ] materialized_view [ physical_attributes_clause | modify_mv_column_clause | table_compression | inmemory_table_clause | 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 ] [ evaluation_edition_clause ] [ { ENABLE | DISABLE } ON QUERY COMPUTATION ] [ alter_query_rewrite_clause | 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 ] [ for_refresh_clause ] ;
ALTER MATERIALIZED ZONEMAP [ schema. ] zonemap_name { alter_zonemap_attributes | zonemap_refresh_clause | { ENABLE | DISABLE } PRUNING | COMPILE | REBUILD | UNUSABLE } ;
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_name { package_compile_clause | { EDITIONABLE | NONEDITIONABLE } }
ALTER PLUGGABLE DATABASE { pdb_unplug_clause | pdb_settings_clauses | pdb_datafile_clause | pdb_recovery_clauses | pdb_change_state | pdb_change_state_from_root | application_clauses | snapshot_clauses | prepare_clause | drop_mirror_copy | lost_write_protection } ;
ALTER PROCEDURE [ schema. ] procedure_name { procedure_compile_clause | { EDITIONABLE | NONEDITIONABLE } }
ALTER PROFILE profile LIMIT { resource_parameters | password_parameters } ... [ CONTAINER = { CURRENT | ALL } ] ;
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 AS domain_name_of_directory_group } } [ CONTAINER = { CURRENT | ALL } ] ;
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 } | { KEEP | NOKEEP } | { SCALE {EXTEND | NOEXTEND} | NOSCALE } | { SHARD {EXTEND | NOEXTEND} | NOSHARD } | { SESSION | GLOBAL } } ... ;
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 } | { ENABLE | DISABLE } SHARD DDL | SYNC WITH PRIMARY | alter_session_set_clause } ;
ALTER [ PUBLIC ] SYNONYM [ schema. ] synonym { EDITIONABLE | NONEDITIONABLE | COMPILE } ;
ALTER SYSTEM { archive_log_clause | checkpoint_clause | check_datafiles_clause | distributed_recov_clauses | FLUSH { SHARED_POOL | GLOBAL CONTEXT | BUFFER_CACHE | FLASH_CACHE | REDO TO target_db_name [ [ NO ] CONFIRM APPLY ] } | end_session_clauses | SWITCH LOGFILE | { SUSPEND | RESUME } | quiesce_clauses | rolling_migration_clauses | rolling_patch_clauses | security_clauses | affinity_clauses | shutdown_dispatcher_clause | REGISTER | SET alter_system_set_clause [ alter_system_set_clause ]... | RESET alter_system_reset_clause [ alter_system_reset_clause ]... | RELOCATE CLIENT client_id | ALTER SYSTEM CANCEL SQL ' session_id serial_number [ @instance_id ] [ sql_id ] ' | FLUSH PASSWORDFILE_METADATA_CACHE } ;
ALTER TABLE [ schema. ] table [ memoptimize_read_clause ] [ memoptimize_write_clause ] [ alter_table_properties | column_clauses | constraint_clauses | alter_table_partitioning [ { DEFERRED | IMMEDIATE } INVALIDATION ] | alter_external_table | move_table_clause | modify_to_partitioned | modify_opaque_type ] [ enable_disable_clause | { ENABLE | DISABLE } { TABLE LOCK | ALL TRIGGERS | CONTAINER_MAP | CONTAINERS_DEFAULT } ] ... ;
ALTER TABLESPACE tablespace alter_tablespace_attrs ;
ALTER TABLESPACE SET tablespace_set alter_tablespace_attrs ;
ALTER TRIGGER [ schema. ] trigger_name { trigger_compile_clause | { ENABLE| DISABLE } | RENAME TO new_name | { EDITIONABLE | NONEDITIONABLE } } ;
ALTER TYPE [ schema. ] type_name { alter_type_clause | { EDITIONABLE | NONEDITIONABLE } }
ALTER USER { user { IDENTIFIED { BY password [ REPLACE old_password ] | EXTERNALLY [ AS 'certificate_DN' | AS 'kerberos_principal_name' ] | GLOBALLY [ AS '[directory_DN]' ] } | NO AUTHENTICATION | DEFAULT COLLATION collation_name | DEFAULT TABLESPACE tablespace | [ LOCAL ] 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 [ FOR object_type [, object_type ]... ] [ FORCE ] | [HTTP] DIGEST { ENABLE | DISABLE } | CONTAINER = { CURRENT | ALL } | container_data_clause } ... | 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 } | { EDITIONABLE | NONEDITIONABLE } } ;
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 | DIRECT_PATH LOAD [ auditing_by_clause ] } [ BY { SESSION | ACCESS } ] [ WHENEVER [ NOT ] SUCCESSFUL ] [ CONTAINER = { CURRENT | ALL } ] ;
AUDIT { POLICY policy [ { BY user [, user]... } | { EXCEPT user [, user]... } | by_users_with_roles ] [ WHENEVER [ NOT ] SUCCESSFUL ] } | { CONTEXT NAMESPACE namespace ATTRIBUTES attribute [, attribute ]... [, CONTEXT NAMESPACE namespace ATTRIBUTES attribute [, attribute ]... ]... [ BY user [, user]... ] } ;
CALL { routine_clause | object_access_expression } [ INTO :host_variable [ [ INDICATOR ] :indicator_variable ] ] ;
COMMENT ON { AUDIT POLICY policy | 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 ] ] ;
CREATE [ OR REPLACE ] [ { FORCE | NOFORCE } ] ANALYTIC VIEW [ schema. ] analytic_view [ sharing clause ] [ classification_clause ]... using_clause dim_by_clause measures_clause [ default_measure_clause ] [ default_aggregate_clause ] [ cache_clause ] ;
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] ATTRIBUTE DIMENSION [ schema. ] attr_dimension [ sharing_clause ] [ classification_clause ]... ] [ DIMENSION TYPE { STANDARD | TIME } ] attr_dim_using_clause attributes_clause [ attr_dim_level_clause ]... [ all_clause ] ;
CREATE AUDIT POLICY policy [ privilege_audit_clause ] [ action_audit_clause ] [ role_audit_clause ] [ WHEN 'audit_condition' EVALUATE PER { STATEMENT | SESSION | INSTANCE } ] [ ONLY TOPLEVEL ] [ CONTAINER = { ALL | CURRENT } ] ;
CREATE CLUSTER [ schema. ] cluster (column datatype [ COLLATE column_collation_name ] [ SORT ] [, column datatype [ COLLATE column_collation_name ] [ SORT ] ]... ) [ { physical_attributes_clause | SIZE size_clause | TABLESPACE tablespace | { INDEX | [ SINGLE TABLE ] HASHKEYS integer [ HASH IS expr ] } }... ] [ parallel_clause ] [ NOROWDEPENDENCIES | ROWDEPENDENCIES ] [ CACHE | NOCACHE ] [ cluster_range_partitions ] ;
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 | SET STANDBY LOGGING FOR {DATA AVAILABILITY | LOAD PERFORMANCE} ]... [ 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 | [ BIGFILE | SMALLFILE ] USER_DATA TABLESPACE tablespace_name DATAFILE datafile_tempfile_spec [, datafile_tempfile_spec ]... | enable_pluggable_database }... ;
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 [ SHARING = { METADATA | NONE } ] AS 'path_name' ;
CREATE DISKGROUP diskgroup_name [ { HIGH | NORMAL | FLEX | EXTERNAL } REDUNDANCY ] { [ QUORUM | REGULAR ] [ FAILGROUP failgroup_name ] DISK qualified_disk_clause [, qualified_disk_clause ]... }... [ ATTRIBUTE { 'attribute_name' = 'attribute_value' } [, 'attribute_name' = 'attribute_value' ]... ] ;
CREATE EDITION edition [ AS CHILD OF parent_edition ] ;
CREATE FLASHBACK ARCHIVE [DEFAULT] flashback_archive TABLESPACE tablespace [flashback_archive_quota] [ [NO] OPTIMIZE DATA ] flashback_archive_retention ;
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] FUNCTION plsql_function_source
CREATE [ OR REPLACE ] [ FORCE | NOFORCE ] HIERARCHY [ schema. ] hierarchy [ sharing_clause ] [ classification_clause ]... ] hier_using_clause level_hier_clause [ hier_attrs_clause ] ;
CREATE [ UNIQUE | BITMAP ] INDEX [ schema. ] index ON { cluster_index_clause | table_index_clause | bitmap_join_index_clause } [ USABLE | UNUSABLE ] [ { DEFERRED | IMMEDIATE } INVALIDATION ] ;
CREATE [ OR REPLACE ] INDEXTYPE [ schema. ] indextype FOR [ schema. ] operator (parameter_type [, parameter_type ]...) [, [ schema. ] operator (parameter_type [, parameter_type ]...) ]... using_type_clause [WITH LOCAL [RANGE] PARTITION ] [ storage_table_clause ] ;
CREATE INMEMORY JOIN GROUP [ schema. ] join_group ( [ schema. ] table ( column ) , [ schema. ] table ( column ) [, [ schema. ] table ( column ) ]... ) ;
CREATE [ OR REPLACE ] [ AND { RESOLVE | COMPILE } ] [ NOFORCE ] JAVA { { SOURCE | RESOURCE } NAMED [ schema. ] primary_name | CLASS [ SCHEMA schema ] } [ SHARING = { METADATA | NONE } ] [ 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 ] [ EDITIONABLE | NONEDITIONABLE ] LIBRARY plsql_library_source
CREATE LOCKDOWN PROFILE profile_name ;
CREATE MATERIALIZED VIEW [ schema. ] materialized_view [ OF [ schema. ] object_type ] [ ( { scoped_table_ref_constraint | column_alias [ENCRYPT [encryption_spec]] } [, { scoped_table_ref_constraint | column_alias [ENCRYPT [encryption_spec]] } ]... ) ] [ DEFAULT COLLATION collation_name ] { 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 ] [ evaluation_edition_clause ] [ { ENABLE | DISABLE } ON QUERY COMPUTATION ] [ query_rewrite_clause ] 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 ] [ for_refresh_clause ] ;
{ create_zonemap_on_table | create_zonemap_as_subquery } ;
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 ] [ EDITIONABLE | NONEDITIONABLE ] PACKAGE plsql_package_source
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] PACKAGE BODY plsql_package_body_source
CREATE PFILE [= 'pfile_name' ] FROM { SPFILE [= 'spfile_name'] | MEMORY } ;
CREATE PLUGGABLE DATABASE { { pdb_name [ AS APPLICATION CONTAINER ] } | { AS SEED } } { create_pdb_from_seed | create_pdb_clone | create_pdb_from_xml | create_pdb_from_mirror_copy | using_snapshot_clause | container_map_clause } pdb_snapshot_clause;
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] PROCEDURE plsql_procedure_source
CREATE PROFILE profile LIMIT { resource_parameters | password_parameters }... [ CONTAINER = { CURRENT | ALL } ] ;
CREATE [ CLEAN ] RESTORE POINT restore_point [ FOR PLUGGABLE DATABASE pdb_name ] [ AS OF {TIMESTAMP | SCN} expr ] [ PRESERVE | GUARANTEE FLASHBACK DATABASE ];
CREATE ROLE role [ NOT IDENTIFIED | IDENTIFIED { BY password | USING [ schema. ] package | EXTERNALLY | GLOBALLY AS domain_name_of_directory_group } ] [ CONTAINER = { CURRENT | ALL } ];
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 [ SHARING = { METADATA | DATA | NONE } ] [ { INCREMENT BY | START WITH } integer | { MAXVALUE integer | NOMAXVALUE } | { MINVALUE integer | NOMINVALUE } | { CYCLE | NOCYCLE } | { CACHE integer | NOCACHE } | { ORDER | NOORDER } | { KEEP | NOKEEP } | { SCALE {EXTEND | NOEXTEND} | NOSCALE } | { SHARD {EXTEND | NOEXTEND} | NOSHARD } | { SESSION | GLOBAL } ]... ;
CREATE SPFILE [= 'spfile_name' ] FROM { PFILE [= 'pfile_name' ] [ AS COPY ] | MEMORY } ;
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] [ PUBLIC ] SYNONYM [ schema. ] synonym [ SHARING = { METADATA | NONE } ] FOR [ schema. ] object [ @ dblink ] ;
CREATE [ { GLOBAL | PRIVATE } TEMPORARY | SHARDED | DUPLICATED ] TABLE [ schema. ] table [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ] { relational_table | object_table | XMLType_table } [ MEMOPTIMIZE FOR READ ] [ MEMOPTIMIZE FOR WRITE ] [ PARENT [ schema. ] table ] [ MEMOPTIMIZE FOR READ ];
CREATE [ BIGFILE | SMALLFILE ] { permanent_tablespace_clause | temporary_tablespace_clause | undo_tablespace_clause } ;
CREATE TABLESPACE SET tablespace_set [ IN SHARDSPACE shardspace ] [ USING TEMPLATE ( { DATAFILE [, file_specification ]... ] permanent_tablespace_attrs ) ] ;
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] TRIGGER plsql_trigger_source
CREATE [OR REPLACE] [ EDITIONABLE | NONEDITIONABLE ] TYPE plsql_type_source
CREATE [ OR REPLACE ] [ EDITIONABLE | NONEDITIONABLE ] TYPE BODY plsql_type_body_source
CREATE USER user IDENTIFIED { BY password [ [HTTP] DIGEST { ENABLE | DISABLE } ] | EXTERNALLY [ AS 'certificate_DN' | AS 'kerberos_principal_name' ] | GLOBALLY [ AS '[ directory_DN ]' ] } | NO AUTHENTICATION [ DEFAULT COLLATION collation_name | DEFAULT TABLESPACE tablespace | [ LOCAL ] 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 | CONTAINER = { CURRENT | ALL } ]... ] ;
CREATE [OR REPLACE] [[NO] FORCE] [ EDITIONING | EDITIONABLE [ EDITIONING ] | NONEDITIONABLE ] VIEW [schema.] view [ SHARING = { METADATA | DATA | EXTENDED DATA | NONE } ] [ ( { alias [ VISIBLE | INVISIBLE ] [ inline_constraint... ] | out_of_line_constraint } [, { alias [ VISIBLE | INVISIBLE ] [ inline_constraint...] | out_of_line_constraint } ] ) | object_view_clause | XMLType_view_clause ] [ DEFAULT COLLATION collation_name ] [ BEQUEATH { CURRENT_USER | DEFINER } ] AS subquery [ subquery_restriction_clause ] [ CONTAINER_MAP | CONTAINERS_DEFAULT ] ;
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 ANALYTIC VIEW [ schema. ] analytic_view_name;
DROP ATTRIBUTE DIMENSION [ schema. ] attr_dimension_name;
DROP AUDIT POLICY policy ;
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 HIERARCHY [ schema. ] hierarchy_name;
DROP INDEX [ schema. ] index [ ONLINE ] [ FORCE ] [ { DEFERRED | IMMEDIATE } INVALIDATION ] ;
DROP INDEXTYPE [ schema. ] indextype [ FORCE ] ;
DROP INMEMORY JOIN GROUP [ schema. ] join_group ;
DROP JAVA { SOURCE | CLASS | RESOURCE } [ schema. ] object_name ;
DROP LIBRARY library_name ;
DROP LOCKDOWN PROFILE profile_name ;
DROP MATERIALIZED VIEW [ schema. ] materialized_view [ PRESERVE TABLE ] ;
DROP MATERIALIZED VIEW LOG ON [ schema. ] table ;
DROP MATERIALIZED ZONEMAP [ schema. ] zonemap_name ;
DROP OPERATOR [ schema. ] operator [ FORCE ] ;
DROP OUTLINE outline ;
DROP PACKAGE [ BODY ] [ schema. ] package ;
DROP PLUGGABLE DATABASE pdb_name [ { KEEP | INCLUDING } DATAFILES ] ;
DROP PROCEDURE [ schema. ] procedure ;
DROP PROFILE profile [ CASCADE ] ;
DROP RESTORE POINT restore_point [ FOR PLUGGABLE DATABASE pdb_name ] ;
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 [ { DROP | KEEP } QUOTA ] [ INCLUDING CONTENTS [ { AND | KEEP } DATAFILES ] [ CASCADE CONSTRAINTS ] ] ;
DROP TABLESPACE SET tablespace_set [ { DROP | KEEP } QUOTA ] [ 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 ] [ PLUGGABLE ] 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 } [ CONTAINER = { CURRENT | ALL } ] } | grant_roles_to_programs } ;
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 (Traditional Auditing)
NOAUDIT { audit_operation_clause [ auditing_by_clause ] | audit_schema_object_clause | NETWORK | DIRECT_PATH LOAD [ auditing_by_clause ] } [ WHENEVER [ NOT ] SUCCESSFUL ] [ CONTAINER = { CURRENT | ALL } ] ;
NOAUDIT { POLICY policy [ { BY user [, user]... } | by_users_with_roles ] } | { CONTEXT NAMESPACE namespace ATTRIBUTES attribute [, attribute ]... [, CONTEXT NAMESPACE namespace ATTRIBUTES attribute [, attribute ]... ]... [ BY user [, user]... ] } ;
PURGE { TABLE table | INDEX index | TABLESPACE tablespace [ USER username ] | TABLESPACE SET tablespace_set [ USER username ] | RECYCLEBIN | DBA_RECYCLEBIN } ;
RENAME old_name TO new_name ;
REVOKE { { revoke_system_privileges | revoke_object_privileges } [ CONTAINER = { CURRENT | ALL } ] } | revoke_roles_from_programs ;
ROLLBACK [ WORK ] [ TO [ SAVEPOINT ] savepoint | FORCE string ] ;
SAVEPOINT savepoint ;
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 ] [ CASCADE ] ;
UPDATE [ hint ] { dml_table_expression_clause | ONLY (dml_table_expression_clause) } [ t_alias ] update_set_clause [ where_clause ] [ returning_clause ] [error_logging_clause] ;