Oracle DDL (Data Definition Language)

In this section we will discuss all storage parameters and the CREATE command for all objects. The most important storage parameters are those dealing with the prevention of chaining and fragmentation.

The CREATE Command

CREATE <modifier> object_type object_name
create options,
	STORAGE (storage_parameters)

The ALTER Command

The ALTER command is issued against databases, tablespaces, tables, indexes, clusters and sequences. Procedures, functions, packages, package bodies, triggers and views can only be compiled using the ALTER command.

ALTER object-type schema.object_name COMPILE;

Any DDL (Data Definition Language) command such as ALTER, DROP and TRUNCATE force an implicit COMMIT command. Any previous DDL or DML commands will be commited by a subsequent DDL command.

The DROP Command

The drop command allows for removal of database objects. This deletion process applies to tables, indexes, clusters, tablespaces, sequences, stored objects and synonyms. Commands such as ALTER with DEALLOCATE, DROP or TRUNCATE can result in tablespace fragmentation.

A table which is frequently created and dropped and shared among several users can have a trigger added to it to create a session identifer (a session ID) column. Thus one can then delete the user's rows when that user is finished. Another alternative is the use of the TRUNCATE command where the table does not have to be continually created and dropped.