Oracle 10g Tutorials: Altering Tables

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.

Dropping Table Columns

You can drop columns that are no longer needed from a table, including an index-organized table. This provides a convenient means to free space in a database, and avoids your having to export/import data then re-create indexes and constraints.

You cannot drop all columns from a table, nor can you drop columns from a table owned by SYS. Any attempt to do so results in an error.

Removing Columns from Tables

When you issue an ALTER TABLE ... DROP COLUMN statement, the column descriptor and the data associated with the target column are removed from each row in the table. You can drop multiple columns with one statement. The ALTER TABLE ... DROP COLUMN statement is not supported for tables using table compression.

The following statements are examples of dropping columns from the employee table. The first statement drops only the sal column:

ALTER TABLE employee DROP COLUMN sal;

Figure 8. Alter Table Drop Column

The next statement drops both the bonus and comm columns:

ALTER TABLE employee DROP (bonus, comm);

Figure 9. Alter Table Drop Columns

Marking Columns Unused

If you are concerned about the length of time it could take to drop column data from all of the rows in a large table, you can use the ALTER TABLE ... SET UNUSED statement. This statement marks one or more columns as unused, but does not actually remove the target column data or restore the disk space occupied by these columns. However, a column that is marked as unused is not displayed in queries or data dictionary views, and its name is removed so that a new column can reuse that name. All constraints, indexes, and statistics defined on the column are also removed.

To mark the hiredate and mgr columns as unused, execute the following statement:

ALTER TABLE admin_emp_dept SET UNUSED (hiredate, mgr);

Figure 9. Alter Table Unused Columns

You can later remove columns that are marked as unused by issuing an ALTER TABLE ... DROP UNUSED COLUMNS statement. Unused columns are also removed from the target table whenever an explicit drop of any particular column or columns of the table is issued.

The data dictionary views USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, or DBA_UNUSED_COL_TABS can be used to list all tables containing unused columns. The COUNT field shows the number of unused columns in the table.

SELECT * FROM DBA_UNUSED_COL_TABS;

OWNER TABLE_NAME COUNT

--------------------------- --------------------------- ----------------------------

SYSTEM ADMIN_EMP_DEPT 2

Figure 10. Select Unused Columns

.
Removing Unused Columns

The ALTER TABLE ... DROP UNUSED COLUMNS statement is the only action allowed on unused columns. It physically removes unused columns from the table and reclaims disk space.

In the ALTER TABLE statement that follows, the optional clause CHECKPOINT is specified. This clause causes a checkpoint to be applied after processing the specified number of rows, in this case 250. Checkpointing cuts down on the amount of undo logs accumulated during the drop column operation to avoid a potential exhaustion of undo space.

ALTER TABLE admin_emp_dept DROP UNUSED COLUMNS CHECKPOINT 250;

Figure 11. Alter table drop unused columns