Column Operations

Often, you will need to perform maintenance on a table at the column level.

Dropping a Column

Designs change. It will often become apparent after a design has been implemented that some of the design assumptions were inaccurate, and a column will need to be removed from the table. In previous releases of the database, this would have been tricky, but in Oracle 9i it has been simplified. All you have to do to drop a column is ALTER the table and DROP the column:

 ALTER TABLE addresses DROP Street_addr3; 

Dropping columns can take a significant amount of time if a significant amount of data is being deleted from the table as a result of the drop. For this same reason, dropping a column may require a significant amount of undo space. If you want to minimize the impact of the drop on undo space, you can specify that checkpoints occur periodically (for example, every 2,500 or 5,000 rows). The primary drawback to this option is that the table is marked invalid until the operation completes. If the instance fails during the operation, the table will remain invalid on startup, and the operation will have to be completed to remove the invalidation. If this occurs, the command to continue with the operation follows:

 ALTER TABLE addresses DROP COLUMNS CONTINUE; 

There are, of course, restrictions on dropping columns from tables as follows:

• You cannot drop a column from an object type table.

• You cannot drop a column from nested tables.

• You cannot drop all the columns in a table.

• You cannot drop a partitioning key column.

• You cannot drop a column from a table owned by SYS.

• You cannot drop a parent key column.

• You cannot drop a column from an index-organized table if the column is a primary key.

Unused Columns

Instead of immediately removing a column from a table, you can mark the column as unused and then remove it later. This has the advantage of speed and does not cause additional undo space to be created at busy times. Marking a column as unused does not reclaim the disk space because the data is not removed. Columns that have been marked as unused can be removed later from the table.

Unused columns are interesting; they pretend that they are not actually a part of the table. You cannot query the columns nor do they appear if you describe the table. A column with the same name and attributes as the column marked as unused can be added to the table.

Because the column cannot be seen when you describe the table or when you query it, you need a means to determine tables with unused columns. To do this, you have to query the DBA_UNUSED_COL_TABS data dictionary view. This query does not identify the name of the unused column, only that an unused column exists in the table. You cannot mark an unused column as used again, so to get rid of the column, you need to issue the following command:

 ALTER TABLE addresses DROP UNUSED COLUMNS; 

A LONG or a LONG RAW column that is set to unused but has not yet been dropped will prevent the addition of a LONG or a LONG RAW column to the table, even though you can't see the unused column.

Adding Columns

Additional design issues may come to light that suggest that you might need to capture even more information connected to a given table. For example, you can add a column to include cell phone numbers in a table. In this case, even though phone numbers are numbers, they don't participate in mathematical operations, so they can be stored as character data. And because there are international phone numbers with country codes, you must make the column large enough for any country codes, parentheses, and slashes that the user might want to add:

 ALTER TABLE addresses ADD (cellphone_number varchar2(20)); 

Altering Columns

Finally, you may need to change the characteristics of a column. You may need to change a column's size because needs have changed and now the column must store more information. Or you may need to change a column's data type (we stored ZIP codes as a number, and people still keep insisting on averaging them). To change the size, you can use the following statement:

 ALTER TABLE addresses MODIFY zip_code varchar2(15); 

Now, where do we get information on a table? Who owns the table? Is the table part of a cluster? What are a table's columns and similar information?