Oracle7 Server SQL Reference

Summary of SQL Commands

The tables in the following sections provide a functional summary of SQL commands and are divided into these categories:

Data Definition Language commands

Data Manipulation Language commands

Transaction Control commands

Session Control commands

System Control commands

Embedded SQL commands

Data Definition Language Commands

Data Definition Language (DDL ) commands allow you to perform these tasks:

create, alter, and drop objects

grant and revoke privileges and roles

analyze information on a table, index, or cluster

establish auditing options

add comments to the data dictionary

The CREATE, ALTER, and DROP commands require exclusive access to the object being acted upon. For example, an ALTER TABLE command fails if another user has an open transaction on the specified table.

The GRANT, REVOKE, ANALYZE, AUDIT, and COMMENT commands do not required exclusive access to the object being acted upon. For example, you can analyze a table while other users are updating the table.

Oracle7 implicitly commits the current transaction before and after every Data Definition Language statement.

Many Data Definition Language statements may cause Oracle7 to recompile or reauthorize schema objects. For information on how Oracle7 recompiles and reauthorizes schema objects and the circumstances under which a Data Definition Language statement would cause this, see the "Dependencies Among Schema Objects" chapter of Oracle7 Server Concepts.

Data Definition Language commands are not directly supported by PL/SQL, but may be available using packaged procedures supplied by Oracle corporation. For more information, see PL/SQL User's Guide and Reference.

Table 4 - 1 shows the Data Definition Language Commands.

Command Purpose
ALTER CLUSTER To change the storage characteristics of a cluster. To allocate an extent for a cluster.
ALTER DATABASE To open/mount the database. To convert an Oracle Version 6 data dictionary when migrating to Oracle7. To prepare to downgrade to an earlier release of Oracle7. To choose archivelog/noarchivelog mode. To perform media recovery. To add/drop/clear redo log file groups members. To rename a data file/redo log file member. To backup the current control file. To backup SQL commands (that can be used to re-create the database) to the trace file. To create a new data file. To resize one or more datafiles. To create a new datafile in place of an old one for recovery purposes. To enable/disable autoextending the size of datafiles. To take a data file online/offline. To enable/disable a thread of redo log file groups. To change the database's global name. To change the MAC mode. To set the DBHIGH or DBLOW labels.
ALTER FUNCTION To recompile a stored function.
ALTER INDEX To redefine an index's future storage allocation.
ALTER PACKAGE To recompile a stored package.
ALTER PROCEDURE To recompile a stored procedure.
ALTER PROFILE To add or remove a resource limit to or from a profile.
ALTER RESOURCE COST To specify a formula to calculate the total cost of resources used by a session.
ALTER ROLE To change the authorization needed to access a role.
ALTER ROLLBACK SEGMENT To change a rollback segment's storage characteristics. To bring a rollback segment online/offline. To shrink a rollback segment to an optimal or given size.
ALTER SEQUENCE To redefine value generation for a sequence.
ALTER SNAPSHOT To change a snapshot's storage characteristics, automatic refresh time, or automatic refresh mode.
ALTER SHAPSHOT LOG To change a snapshot log's storage characteristics.
ALTER TABLE To add a column/integrity constraint to a table. To redefine a column, to change a table's storage characteristics. To enable/disable/drop an integrity constraint. To enable/disable tables locks on a table. To enable/disable all triggers on a table. To allocate an extent for the table. To allow/disallow writing to a table. To modify the degree of parallelism for a table.
ALTER TABLESPACE To add/rename data files. To change storage characteristics. To take a tablespace online/offline. To begin/end a backup. To allow/disallow writing to a tablespace.
ALTER TRIGGER To enable/disable a database trigger.
ALTER USER To change a user's password, default tablespace, temporary tablespace, tablespace quotas, profile, or default roles.
ALTER VIEW To recompile a view.
ANALYZE To collect performance statistics, validate structure, or identify chained rows for a table, cluster, or index.
AUDIT To choose auditing for specified SQL commands or operations on schema objects.
COMMENT To add a comment about a table, view, shapshot, or column to the data dictionary.
CREATE CLUSTER To create a cluster that can contain one or more tables.
CREATE CONTROLFILE To recreate a control file.
CREATE DATABASE To create a database.
CREATE DATABASE LINK To create a link to a remote database.
CREATE FUNCTION To create a stored function.
CREATE INDEX To create an index for a table or cluster.
CREATE PACKAGE To create the specification of a stored package.
CREATE PACKAGE BODY To create the body of a stored package
CREATE PROCEDURE To create a stored procedure.
CREATE PROFILE To create a profile and specify its resource limits.
CREATE ROLE To create a role.
CREATE ROLLBACK SEGMENT To create a rollback segment.
CREATE SCHEMA To issue multiple CREATE TABLE, CREATE VIEW, and GRANT statements in a single transaction.
CREATE SEQUENCE To create a sequence for generating sequential values.
CREATE SHAPSHOT To create a snapshot of data from one or more remote master tables.
CREATE SNAPSHOT LOG To create a snapshot log containing changes made to the master table of a snapshot.
CREATE SYNONYM To create a synonym for a schema object.
CREATE TABLE To create a table, defining its columns, integrity constraints, and storage allocation.
CREATE TABLESPACE To create a place in the database for storage of schema objects, rollback segments, and temporary segments, naming the data files to comprise the tablespace.
CREATE TRIGGER To create a database trigger.
CREATE USER To create a database user.
CREATE VIEW To define a view of one or more tables or views.
DROP CLUSTER To remove a cluster from the database.
DROP DATABASE LINK To remove a database link.
DROP FUNCTION To remove a stored function from the database.
DROP INDEX To remove an index from the database.
DROP PACKAGE To remove a stored package from the database.
DROP PROCEDURE To remove a stored procedure from the database.
DROP PROFILE To remove a profile from the database.
DROP ROLE To remove a role from the database.
DROP ROLLBACK SEGMENT To remove a rollback segment from the database.
DROP SEQUENCE To remove a sequence from the database.
DROP SNAPSHOT To remove a snapshot from the database.
DROP SNAPSHOT LOG To remove a snapshot log from the database.
DROP SYNONYM To remove a synonym from the database.
DROP TABLE To remove a table from the database.
DROP TABLESPACE To remove a tablespace from the database.
DROP TRIGGER To remove a trigger from the database.
DROP USER To remove a user and the objects in the user's schema from the database.
DROP VIEW To remove a view from the database.
GRANT To grant system privileges, roles and object privileges to users and roles.
NOAUDIT To disable auditing by reversing, partially or completely, the effect of a prior AUDIT statement.
RENAME To change the name of a schema object.
REVOKE To revoke system privileges, roles, and object privileges from users and roles.
TRUNCATE To remove all rows from a table or cluster and free the space that the rows used.

Table 4 - 1. (continued) Data Definition Language Commands

Data Manipulation Language Commands

Data Manipulation Language (DML) commands query and manipulate data in existing schema objects. These commands do not implicitly commit the current transaction.

Command Purpose
DELETE To remove rows from a table.
EXPLAIN PLAN To return the execution plan for a SQL statement.
INSERT To add new rows to a table.
LOCK TABLE To lock a table or view, limiting access to it by other users.
SELECT To select data in rows and columns from one or more tables.
UPDATE To change data in a table.

Table 4 - 2. Data Manipulation Language Commands

All Data Manipulation Language commands except the EXPLAIN PLAN command are supported in PL/SQL.

Transaction Control Commands

Transaction Control commands manage changes made by Data Manipulation Language commands.

Command Purpose
COMMIT To make permanent the changes made by statements issued and the beginning of a transaction.
ROLLBACK To undo all changes since the beginning of a transaction or since a savepoint.
SAVEPOINT To establish a point back to which you may roll.
SET TRANSACTION To establish properties for the current transaction.

Table 4 - 3. Transaction Control Commands

All Transaction Control commands except certain forms of the COMMIT and ROLLBACK commands are supported in PL/SQL. For information on the restrictions, see COMMIT and ROLLBACK .

Session Control Commands

Session Control commands dynamically manage the properties of a user session. These commands do not implicitly commit the current transaction.

PL/SQL does not support session control commands.

Command Purpose
ALTER SESSION To enable/disable the SQL trace facility. To enable/disable global name resolution. To change the values of the session's NLS parameters. For Trusted Oracle7, to change the session label. To change the default label format. In a parallel server, to indicate that the session must access database files as if the session was connected to another instance. To close a database link. To send advice to remote databases for forcing an in-doubt distributed transaction. To permit or prohibit procedures and stored procedures from issuing COMMIT and ROLLBACK statements. To change the goal of the cost-based optimization approach.
SET ROLE To enable/disable roles for the current session.

Table 4 - 4. Session Control Commands

System Control Command

The single System Control command dynamically manages the properties of an Oracle7 instance. This command does not implicitly commit the current transaction.

ALTER SYSTEM is not supported in PL/SQL.

Command Purpose
ALTER SYSTEM To alter the Oracle7 instance by performing a specialized function.

Table 4 - 5. System Control Commands

Embedded SQL Commands

Embedded SQL commands place Data Definition Language, Data Manipulation Language, and Transaction Control statements within a procedural language program. Embedded SQL is supported by the Oracle Precompilers.

Command Purpose
ALLOCATE To allocate a cursor variable.
CLOSE To disable a cursor, releasing the resources it holds.
CONNECT To log on to an Oracle7 instance.
DECLARE CURSOR To declare a cursor, associating it with a query.
DECLARE DATABASE To declare the name of a remote database.
DECLARE STATEMENT To assign a SQL variable name to a SQL statement.
DECLARE TABLE To declare the structure of a table for semantic checking of embedded SQL statements by the Oracle Precompiler.
DESCRIBE To initialize a descriptor, a structure holding host variable descriptions.
EXECUTE To execute a prepared SQL statement or PL/SQL block or to execute an anonymous PL/SQL block.
EXECUTE IMMEDIATE To prepare and execute a SQL statement containing no host variables.
FETCH To retrieve rows selected by a query.
OPEN To execute the query associated with a cursor.
PREPARE To parse a SQL statement.
TYPE To perform user-defined equivalencing.
VAR To perform host variable equivalencing.
WHENEVER To specify handling for error and warning conditions.

Table 4 - 6. Embedded SQL Commands

`Command`	Purpose
`ALTER CLUSTER`	To change the storage characteristics of a cluster. To allocate an extent for a cluster.
`ALTER DATABASE`	To open/mount the database. To convert an Oracle Version 6 data dictionary when migrating to Oracle7. To prepare to downgrade to an earlier release of Oracle7. To choose archivelog/noarchivelog mode. To perform media recovery. To add/drop/clear redo log file groups members. To rename a data file/redo log file member. To backup the current control file. To backup SQL commands (that can be used to re-create the database) to the trace file. To create a new data file. To resize one or more datafiles. To create a new datafile in place of an old one for recovery purposes. To enable/disable autoextending the size of datafiles. To take a data file online/offline. To enable/disable a thread of redo log file groups. To change the database's global name. To change the MAC mode. To set the DBHIGH or DBLOW labels.
`ALTER FUNCTION`	To recompile a stored function.
`ALTER INDEX`	To redefine an index's future storage allocation.
`ALTER PACKAGE`	To recompile a stored package.
`ALTER PROCEDURE`	To recompile a stored procedure.
`ALTER PROFILE`	To add or remove a resource limit to or from a profile.
`ALTER RESOURCE COST`	To specify a formula to calculate the total cost of resources used by a session.
`ALTER ROLE`	To change the authorization needed to access a role.
`ALTER ROLLBACK SEGMENT`	To change a rollback segment's storage characteristics. To bring a rollback segment online/offline. To shrink a rollback segment to an optimal or given size.
`ALTER SEQUENCE`	To redefine value generation for a sequence.
`ALTER SNAPSHOT`	To change a snapshot's storage characteristics, automatic refresh time, or automatic refresh mode.
`ALTER SHAPSHOT LOG`	To change a snapshot log's storage characteristics.
`ALTER TABLE`	To add a column/integrity constraint to a table. To redefine a column, to change a table's storage characteristics. To enable/disable/drop an integrity constraint. To enable/disable tables locks on a table. To enable/disable all triggers on a table. To allocate an extent for the table. To allow/disallow writing to a table. To modify the degree of parallelism for a table.
`ALTER TABLESPACE`	To add/rename data files. To change storage characteristics. To take a tablespace online/offline. To begin/end a backup. To allow/disallow writing to a tablespace.
`ALTER TRIGGER`	To enable/disable a database trigger.
`ALTER USER`	To change a user's password, default tablespace, temporary tablespace, tablespace quotas, profile, or default roles.
`ALTER VIEW`	To recompile a view.
`ANALYZE`	To collect performance statistics, validate structure, or identify chained rows for a table, cluster, or index.
`AUDIT`	To choose auditing for specified SQL commands or operations on schema objects.
`COMMENT`	To add a comment about a table, view, shapshot, or column to the data dictionary.
`CREATE CLUSTER`	To create a cluster that can contain one or more tables.
`CREATE CONTROLFILE`	To recreate a control file.
`CREATE DATABASE`	To create a database.
`CREATE DATABASE LINK`	To create a link to a remote database.
`CREATE FUNCTION`	To create a stored function.
`CREATE INDEX`	To create an index for a table or cluster.
`CREATE PACKAGE`	To create the specification of a stored package.
`CREATE PACKAGE BODY`	To create the body of a stored package
`CREATE PROCEDURE`	To create a stored procedure.
`CREATE PROFILE`	To create a profile and specify its resource limits.
`CREATE ROLE`	To create a role.
`CREATE ROLLBACK SEGMENT`	To create a rollback segment.
`CREATE SCHEMA`	To issue multiple CREATE TABLE, CREATE VIEW, and GRANT statements in a single transaction.
`CREATE SEQUENCE`	To create a sequence for generating sequential values.
`CREATE SHAPSHOT`	To create a snapshot of data from one or more remote master tables.
`CREATE SNAPSHOT LOG`	To create a snapshot log containing changes made to the master table of a snapshot.
`CREATE SYNONYM`	To create a synonym for a schema object.
`CREATE TABLE`	To create a table, defining its columns, integrity constraints, and storage allocation.
`CREATE TABLESPACE`	To create a place in the database for storage of schema objects, rollback segments, and temporary segments, naming the data files to comprise the tablespace.
`CREATE TRIGGER`	To create a database trigger.
`CREATE USER`	To create a database user.
`CREATE VIEW`	To define a view of one or more tables or views.
`DROP CLUSTER`	To remove a cluster from the database.
`DROP DATABASE LINK`	To remove a database link.
`DROP FUNCTION`	To remove a stored function from the database.
`DROP INDEX`	To remove an index from the database.
`DROP PACKAGE`	To remove a stored package from the database.
`DROP PROCEDURE`	To remove a stored procedure from the database.
`DROP PROFILE`	To remove a profile from the database.
`DROP ROLE`	To remove a role from the database.
`DROP ROLLBACK SEGMENT`	To remove a rollback segment from the database.
`DROP SEQUENCE`	To remove a sequence from the database.
`DROP SNAPSHOT`	To remove a snapshot from the database.
`DROP SNAPSHOT LOG`	To remove a snapshot log from the database.
`DROP SYNONYM`	To remove a synonym from the database.
`DROP TABLE`	To remove a table from the database.
`DROP TABLESPACE`	To remove a tablespace from the database.
`DROP TRIGGER`	To remove a trigger from the database.
`DROP USER`	To remove a user and the objects in the user's schema from the database.
`DROP VIEW`	To remove a view from the database.
`GRANT`	To grant system privileges, roles and object privileges to users and roles.
`NOAUDIT`	To disable auditing by reversing, partially or completely, the effect of a prior AUDIT statement.
`RENAME`	To change the name of a schema object.
`REVOKE`	To revoke system privileges, roles, and object privileges from users and roles.
`TRUNCATE`	To remove all rows from a table or cluster and free the space that the rows used.

`Command`	Purpose
`DELETE`	To remove rows from a table.
`EXPLAIN PLAN`	To return the execution plan for a SQL statement.
`INSERT`	To add new rows to a table.
`LOCK TABLE`	To lock a table or view, limiting access to it by other users.
`SELECT`	To select data in rows and columns from one or more tables.
`UPDATE`	To change data in a table.

`Command`	Purpose
`COMMIT`	To make permanent the changes made by statements issued and the beginning of a transaction.
`ROLLBACK`	To undo all changes since the beginning of a transaction or since a savepoint.
`SAVEPOINT`	To establish a point back to which you may roll.
`SET TRANSACTION`	To establish properties for the current transaction.

`Command`	Purpose
`ALTER SESSION`	To enable/disable the SQL trace facility. To enable/disable global name resolution. To change the values of the session's NLS parameters. For Trusted Oracle7, to change the session label. To change the default label format. In a parallel server, to indicate that the session must access database files as if the session was connected to another instance. To close a database link. To send advice to remote databases for forcing an in-doubt distributed transaction. To permit or prohibit procedures and stored procedures from issuing COMMIT and ROLLBACK statements. To change the goal of the cost-based optimization approach.
`SET ROLE`	To enable/disable roles for the current session.

`Command`	Purpose
`ALTER SYSTEM`	To alter the Oracle7 instance by performing a specialized function.

`Command`	Purpose
`ALLOCATE`	To allocate a cursor variable.
`CLOSE`	To disable a cursor, releasing the resources it holds.
`CONNECT`	To log on to an Oracle7 instance.
`DECLARE CURSOR`	To declare a cursor, associating it with a query.
`DECLARE DATABASE`	To declare the name of a remote database.
`DECLARE STATEMENT`	To assign a SQL variable name to a SQL statement.
`DECLARE TABLE`	To declare the structure of a table for semantic checking of embedded SQL statements by the Oracle Precompiler.
`DESCRIBE`	To initialize a descriptor, a structure holding host variable descriptions.
`EXECUTE`	To execute a prepared SQL statement or PL/SQL block or to execute an anonymous PL/SQL block.
`EXECUTE IMMEDIATE`	To prepare and execute a SQL statement containing no host variables.
`FETCH`	To retrieve rows selected by a query.
`OPEN`	To execute the query associated with a cursor.
`PREPARE`	To parse a SQL statement.
`TYPE`	To perform user-defined equivalencing.
`VAR`	To perform host variable equivalencing.
`WHENEVER`	To specify handling for error and warning conditions.