Oracle DDL (Data Definition Language)

• Storage - the default storage parameters for all objects created in the database.
• DDL - Data Definition Language. DDL includes all commands used to create, alter and drop objects. Objects include tables, indexes, clusters, sequences, triggers, procedures, functions and packages.
• INITIAL - the byte size of the initial extent of the object segment. All values are rounded to the nearest block size.
• NEXT - this determines the disk space to be allocated to the datafile in the current tablespace when more space is required.
• PCTINCREASE - this determines how much to grow each extent after the INITIAL and NEXT extents are used. Thus with PCTINCREASE set to 50% then each subsequent extent will be increased by 50% of the previously created extent. PCTINCREASE is the growth rate for subsequent extents. Note that a tablespace with a default storage setting for PCTINCREASE of zero will not be automatically coalesced. For example with INTIAL = 50K, NEXT = 20K and PCTINCREASE = 30K the third extent will be NEXT + (NEXT * PCTINCREASE / 100) which is 20 + (20 * 30 / 100) = 20 + 6 = 26K.
• PCTFREE - this determines how much space to leave in each block for future updates and the addition of new rows. The default is 10%.
• PCTUSED - the minimum level of space to maintain in a block. This defaults to 40%.
• INITTRANS - the initial number of active transactions that are allocated within each block. A large value will reduce the amount of space available for data in a block. A change to this parameter only affects blocks added after the change.
• MAXTRANS - the maximum number of active transactions that can update a block concurrently.
• Initial Extents - the initial extent for an object is the inital space allocation of the initial extent for that object.
• MAXEXTENTS - the largest number of extents allowed for an object. Defaults to the maximum allowed for the block size.
• DDL Commands.
  
  
  • CREATE - the command supplying the basis for creation of all database objects.
  • ALTER - additions of, changes to, and removals of existing objects.
  • DROP - complete object removal.
  • TRUNCATE - removes a table's data without removal of the table definition.
  • COMMENT - attach comments to database objects.

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.

• object_type.
  • TABLE - cascade constraints ar permitted.
  • TABLESPACE - can use INCLUDING CONTENTS.
  • USER - can use CASCADE.
  • PROCEDURE.
  • PACKAGE - can only drop the package as a whole.
  • FUNCTION.
  • TRIGGER.
  • SYNONYM.
  • DATABASE LINK.
  • VIEW.
• schema - must have drop privelages if not owner.
• object_name.
• CASCADE CONSTRAINTS - drop any related foreign key constraints for a parent table. For ON DELETE CASCADE all related rows in child tables are also dropped.
• INCLUDING CONTENTS - forces a drop for a tablespace even when the tablespace contains objects.
• CASCADE - drops user's objects aswell as users.