Oracle 10g Free Training : Altering Tables

In this training you will learn about Altering Tables - Modifying an Existing Column Definition, Adding Table Columns, Renaming Table Columns, Dropping Table Columns, Removing Columns from Tables, Marking Columns Unused and Removing Unused Columns.

Oracle 10g Free Training : Altering Tables

Altering Tables

You alter a table using the ALTER TABLE statement. To alter a table, the table must be contained in your schema, or you must have either the ALTER object privilege for the table or the ALTER ANY TABLE system privilege.

Many of the usages of the ALTER TABLE statement are presented in the following sections:

Altering Physical Attributes of a Table

Moving a Table to a New Segment or Tablespace

Manually Allocating Storage for a Table

Modifying an Existing Column Definition

Adding Table Columns

Renaming Table Columns

Dropping Table Columns

You alter a table using the ALTER TABLE statement. To alter a table, the table must be contained in your schema, or you must have either the ALTER object privilege for the table or the ALTER ANY TABLE system privilege. Many of the usages of the ALTER TABLE statement are presented in the following sections: Altering Physical Attributes of a Table Moving a Table to a New Segment or Tablespace Manually Allocating Storage for a Table Modifying an Existing Column Definition Adding Table Columns Renaming Table Columns Dropping Table Columns

Caution: If a view, materialized view, trigger, domain index, function-based index, check constraint, function, procedure of package depends on a base table, the alteration of the base table or its columns can affect the dependent object.

Modifying an Existing Column Definition

Use the ALTER TABLE ... MODIFY statement to modify an existing column definition. You can modify column datatype, default value, or column constraint.

You can increase the length of an existing column, or decrease it, if all existing data satisfies the new length. You can change a column from byte semantics to CHAR semantics or vice versa. You must set the initialization parameter BLANK_TRIMMING=TRUE to decrease the length of a nonempty CHAR column.

If you are modifying a table to increase the length of a column of datatype CHAR, realize that this can be a time consuming operation and can require substantial additional storage, especially if the table contains many rows. This is because the CHAR value in each row must be blank-padded to satisfy the new column length.

Adding Table Columns

To add a column to an existing table, use the ALTER TABLE ... ADD statement.

The following statement alters the admin_emp_dept table to add a new column named bonus:

ALTER TABLE admin_emp_dept

ADD (bonus NUMBER (7,2));

Figure 6. Alter Table

If a new column is added to a table, the column is initially NULL unless you specify the DEFAULT clause. When you specify a default value, the database updates each row in the new column with the values specified. Specifying a DEFAULT value is not supported for tables using table compression.

You can add a column with a NOT NULL constraint to a table only if the table does not contain any rows, or you specify a default value.

Renaming Table Columns

Oracle Database lets you rename existing columns in a table. Use the RENAME COLUMN clause of the ALTER TABLE statement to rename a column. The new name must not conflict with the name of any existing column in the table. No other clauses are allowed in conjunction with the RENAME COLUMN clause.

The following statement renames the comm column of the admin_emp_dept table.

ALTER TABLE admin_emp_dept

RENAME COLUMN comm TO commission;

Figure 7. Alter Table Change Column Name

As noted earlier, altering a table column can invalidate dependent objects. However, when you rename a column, the database updates associated data dictionary tables to ensure that function-based indexes and check constraints remain valid.

Oracle Database also lets you rename column constraints.

Note: The RENAME TO clause of ALTER TABLE appears similar in syntax to the RENAME COLUMN clause, but is used for renaming the table itself.

Next Page: Oracle 10g Tutorials: Altering Tables - Page 2