Every DBA or systems designer faces the problem of modifying a table structure after data has been loaded into the table. Structure changes are often a result of new or changing requirements as the system matures. Prior to Oracle 8i, there was no easy way to remove a column from a table, even if the column had no data in it. The only way to drop a column was to drop and recreate the table. This became more and more difficult as data accumulated in the table, especially if the table was related to other objects, such as grants, indexes, views, and foreign keys. Oracle provides some new parameters for dropping a column from a table:
the SET UNUSED parameter and
the DROP COLUMN parameter
for the
ALTER TABLE
command. The following diagram provides a detailed explanation of these new parameters.
ALTER TABLE tablename SET UNUSED
(COLUMN columnname | (col1, col2, ...))
[CASCADE CONSTRAINTS | INVALIDATE];
ALTER TABLE tablename DROP
{COLUMN columnname | (col1, col2, ...)} UNUSED COLUMNS
[CASCADE CONSTRAINTS | INVALIDATE];
ALTER TABLE STORM_TRACK SET UNUSED (LAT, LON);
ALTER TABLE STORM_TRACK DROP UNUSED COLUMNS;
ALTER TABLE SHIPPING_ACTION DROP COLUMN LOG_TIME;
After setting a column as unused, it does not appear in the DESC command, or in SELECT * queries
CASCADE CONSTRAINTS must be used if a constraint contains a combination of used and unused columns
INVALIDATE causes Oracle to invalidate objects that depend on the dropped or unused column(s), such as views. Currently, this is always done, whether or not you specify the INVALIDATE keyword.
You can use the SET UNUSED phrase to mark columns for dropping later. Because this command does not affect the actual storage of the table data, it is much faster to execute. You may want to mark columns as unused during the day when you do not want to disturb your users and then drop the column later when users are not logged into the system. When you drop the column, the data in that column is removed and the storage space is recovered.
Some restrictions to dropping columns are:
You cannot drop an attribute from an object table.
You can drop a column that is an object type, except when the column is a nested table type.
You cannot drop a column from any table owned by SYS.
You cannot drop a column that is part of the table's primary key.
Query the USER_UNUSED_COL_TABS, DBA_UNUSED_COL_TABS, or ALL_UNUSED_COL_TABS data dictionary views to find out which columns have been marked as UNUSABLE.
LOBFILEs and Secondary Data Files (SDFs)
LOB data can be lengthy enough that it makes sense to load it from a LOBFILE. In LOBFILEs, LOB data instances are still considered to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILEs).
Therefore, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.
For example, you might have a table that stores employee names, IDs, and their resumes. When loading this table, you could read the employee names and IDs from the main data files and you could read the resumes, which can be quite lengthy, from LOBFILEs. You might also use LOBFILEs to facilitate the loading of XML data. You can use XML columns to hold data that models structured and semistructured data. Such data can be quite lengthy. Secondary data files (SDFs) are similar in concept to primary data files. Like primary data files, SDFs are a collection of records, and each record is made up of fields.
The SDFs are specified on a per control-file-field basis. Only a collection_fld_spec can name an SDF as its data source.
SDFs are specified using the SDF parameter. The SDF parameter can be followed by either the file specification string, or a FILLER field that is mapped to a data field containing one or more file specification strings.
Report Remove Unused Column - Exercise
Click the link below to try your hand at writing SQL commands to drop columns. Report Remove Unused Column - Exercise
The next lesson looks at Oracle database limits.
