Managing Tables   «Prev  Next»

Lesson 7 Altering a table
ObjectiveModify a table by adding or modifying columns.

Modifying Table Column in Oracle

One of the great virtues of a relational database is its flexibility. Once you create a table in your Oracle database, you can change certain aspects of the table with the

ALTER TABLE SQL
command.

What the ALTER TABLE can and cannot do

With the ALTER TABLE command, you can change many aspects of an existing table structure. You can:
  1. Rename the table
  2. Add a column to an existing table
  3. Change the attributes of a column, such as the size, data type, or default value of the column
  4. Modify the storage characteristics of the table
  5. Add or change integrity constraints on the table

You can also modify other attributes of a table, such as the partitioning or parallelism associated with the table.
The ALTER TABLE command, however, cannot drop a column from an existing table.

Syntax for ALTER TABLE command

There is a wide range of syntactical options for the ALTER TABLE SQL command, reflecting the many types of modifications you can enact with the command.
The basic syntax for the command is shown in the MouseOver below:

Apply, Filter, Sort
  1. The required ALTER TABLE keywords begin the DDL statement which allows you to modify an existing table
  2. The tablename must identify an existing table. You may have to qualify the tablename with its schema name, as in schema.tablename
  3. The ADD, MODIFY, or DROP keywords specify the basic type of action you want to perform on the table.
  4. The action specific_syntax can include a range of options, based on how you wish to modify the existing table structure.
ALTER TABLE tablename ADD/MODIFY/DROP
action_specific_syntax

Alter Table Command
The next lesson shows how to delete a table from your Oracle database.

ALTER TABLE

Use the ALTER TABLE statement to alter the definition of a nonpartitioned table, a partitioned table, a table partition, or a table subpartition. For object tables or relational tables with object columns, use ALTER TABLE to convert the table to the latest definition of its referenced type after the type has been altered.
Note: Oracle recommends that you use the ALTER MATERIALIZED VIEW LOG statement, rather than ALTER TABLE, whenever possible for operations on materialized view log tables.

Prerequisites

The table must be in your own schema, or you must have ALTER object privilege on the table, or you must have ALTER ANY TABLE system privilege.
Additional Prerequisites for Partitioning Operations If you are not the owner of the table, then you need the DROP ANY TABLE privilege in order to use the drop_table_partition or truncate_table_partition clause. You must also have space quota in the tablespace in which space is to be acquired in order to use the
  1. add_table_partition,
  2. modify_table_partition,
  3. move_table_partition, and
  4. split_table_partition clauses.
When a partitioning operation cascades to reference-partitioned child tables, privileges are not required on the reference-partitioned child tables. Additional Prerequisites for Constraints and Triggers To enable a unique or primary key constraint, you must have the privileges necessary to create an index on the table.You need these privileges because Oracle Database creates an index on the columns of the unique or primary key in the schema containing the table.
To enable or disable triggers, the triggers must be in your schema or you must have the ALTER ANY TRIGGER system privilege.

Additional Prerequisites When Using Object Types
To use an object type in a column definition when modifying a table, either that object must belong to the same schema as the table being altered, or you must have either the EXECUTE ANY TYPE system privilege or the EXECUTE object privilege for the object type.
Additional Prerequisites for Flashback Data Archive Operations
To use the flashback_archive_clause to enable historical tracking for the table, you must have the FLASHBACK ARCHIVE object privilege on the flashback data archive that will contain the historical data. To use the flashback_archive_clause to disable historical tracking for the table, you must have the FLASHBACK ARCHIVE ADMINSTER system privilege or you must be logged in as SYSDBA.