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.

If you want to drop a column from a table, you can by creating a new table using the following steps:

1. Create another table with all of the columns you want to retain
2. Transfer the data from the original table to the new table with the SQL statement INSERT…INTO FROM SELECT…
3. Drop the original table
4. Use the ALTER TABLE command to change the name of the new table to the name of the original table

Do not "COMMIT" this transaction until you successfully rename the new table, otherwise you will lose data.

• Dropping a Column:
  You can use the table reorganization options to drop columns (because the source and target can have different column definitions). As an alternative, you can mark columns to have an unused state during regular usage, then drop them when a longer maintenance window is available. For example, you can mark the Wind column as unused:
  

  alter table TROUBLE 
  set unused column Wind;
  

  
  Marking a column as unused does not release the space previously used by the column until you drop the unused columns: alter table TROUBLE drop unused columns; You can query
  

  USER_UNUSED_COL_TABS, ALL_UNUSED_COL_TABS, and 
  DBA_UNUSED_COL_TABS 
  

  
  to see all tables with columns marked as unused.
  Note: Once you have marked a column as unused, you cannot access that column.
  You can drop multiple columns in a single command, as shown in the following listing:
  

  alter table TROUBLE 
  drop (Condition, Wind);
  

  
  Note: When dropping multiple columns, you should not use the column keyword of the alter table command; it causes a syntax error. The multiple column names must be enclosed in parentheses, as shown in the preceding listing. If the dropped columns are part of primary keys or unique constraints, you will need to also use the cascade constraints clause as part of your alter table command. If you drop a column that belongs to a primary key, Oracle will drop both the column and the primary key index.

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 below.

ALTER TABLE tablename ADD/MODIFY/DROP 
action_specific_syntax

This is a generic syntax for the `ALTER TABLE` statement in Oracle, which allows modifications to an existing table structure. It shows that the `ALTER TABLE` command can be followed by the table name and an action (`ADD`, `MODIFY`, or `DROP`) along with the specific syntax needed for the action.
This syntax is typically used to:

• ADD a new column or constraint to a table.
• MODIFY an existing column's datatype, default value, or constraints.
• DROP a column or constraint from a table.

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 Command

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^[1], 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:
  
  1. 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.
  2. 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.

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.

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

[1] ALTER MATERIALIZED VIEW LOG statement: The `ALTER MATERIALIZED VIEW LOG` statement modifies an existing materialized view log in a database. This allows you to change storage settings, manage extents, or adjust how the log handles changes to the master table it's associated with.