|
Page 1 of 2
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.
|
