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

ALTER CLUSTER : To change the storage characteristics of a cluster and 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 archive log / no archive log 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 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, snapshot 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 a 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 a 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 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.