In a data warehouse you may have to drop columns on very large tables (try to avoid it if you can), and the size of the table may cause complications. The most obvious is the time it may take to do a straightforward ALTER TABLE DROP COLUMN statement. You probably don’t want to wait for an hour in the middle of your implementation for the DROP statement to execute.Even if you’re still doing testing, you really want to get all the scripts run in so that you know about any problems as soon as possible.

On the other hand, it may be necessary to drop the column at that point in the implementation to avoid your other scripts erroring, which might lead to your implementation being “failed”. This is where setting the column “unused” comes in. Because it just sets a data dictionary flag, it executes instantaneously.

At the end of your implementation, when you know that everything has run correctly, you can clean up by dropping the unused columns. You will need to clean up, rather than just leaving them unmarked, if you want to regain the space. The columns are invisible to normal SQL (you can even add another column with the same name as the one you marked unused) but they are still there physically. You could also have a problem if your application does any querying of the metadata, such as building column lists. If the sql doesn’t filter out columns where HIDDEN_COLUMN = ‘YES’, then the hidden columns will appear, and with their odd names they’ll break any dynamic sql statement that you build with your column list.

The sql below illustrates the commands that you can use. Please note, per the code comments and the Ask Tom link, that in some cases you may want to find a different way of doing the physical clean up.

CREATE TABLE T1(OBJECT_ID NUMBER, OBJECT_NAME VARCHAR2(32));


INSERT INTO T1
(
OBJECT_ID
,OBJECT_NAME
)
SELECT
OBJECT_ID
,OBJECT_NAME
FROM ALL_OBJECTS;


--original table--
SELECT * FROM T1;


--instead of dropping column set it unused_-
ALTER TABLE T1 SET UNUSED COLUMN OBJECT_NAME;


--it's gone for normal purposes--
SELECT * FROM T1;


/*but it's actually just been renamed and had the HIDDEN_COLUMN flag set*/
SELECT ATC.COLUMN_NAME, ATC.HIDDEN_COLUMN FROM ALL_TAB_COLS ATC WHERE ATC.TABLE_NAME = 'T1';
\

/*
now we run all the other scripts in the implementation
*/


/*
we got here so everything else must have succeeded now get rid of the unused columns
but bear in mind that the DROP UNUSED COLUMNS command can be a problem with very large tables.
You'll probably need to test how long it takes in DEV before running it in PROD.
(alternatives approaches such as CTAS are discussed at https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:623063677753).
*/


ALTER TABLE T1 DROP UNUSED COLUMNS CHECKPOINT 250; --the CHECKPOINT clause reduces the active UNDO held by the process at any point in time



DROP TABLE T1;