Well, I don't see why it is relevant (i mean, you cannot recover them - only that they are there taking up space is all that matters). But, since you asked anyway

In looking at the view:


create or replace view dba_unused_col_tabs (
OWNER
,TABLE_NAME
,COUNT
) as
select u.name, o.name, count(*)
from sys.user$ u, sys.obj$ o, sys.col$ c
where c.obj# = o.obj#
and bitand(c.property,32768) = 32768 -- is unused column
and bitand(c.property, 1) != 1 -- not ADT attribute col
and bitand(c.property, 1024) != 1024 -- not NTAB's setid col
and u.user# = o.owner#
group by u.name, o.name

/

we see we could suck some of the data out from sys.col$. You will find however the column name is "wiped out" (replaced with SYS_C...... stuff). That is so the unique index on col$(obj#,name) is obeyed if you add a column back with the same name....

So, the name is gone, but the datatype and size is still there. If you take the above view and hack out a bit from the DBA_TAB_COLUMNS view, you can get:

select u.name, o.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE', 23, 'RAW', 24, 'LONG RAW',
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
105, 'MLSLABEL',
106, 'MLSLABEL',
111, 'unknown',
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, 'unknown',
122, 'unknown',
123, 'unknown',
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
c.length, c.precision#, c.scale
from sys.user$ u, sys.obj$ o, sys.col$ c
where c.obj# = o.obj#
and bitand(c.property,32768) = 32768 -- is unused column
and bitand(c.property, 1) != 1 -- not ADT attribute col
and bitand(c.property, 1024) != 1024 -- not NTAB's setid col
and u.user# = o.owner#
/


which shows you the stuff that we can see.

No, setting unused is simply a flag delete -- the space is not reclaimed.

Later, when you drop, it does EXACTLY the work that would have been done by DROP COLUMN in the first place.

You know -- you can incrementally checkpoint the drop column command as it is going along.

Yes, it can take a relatively long time to REWRITE a 1,000,000 row table depending on your system.

Big Table = "set unused"
Small Table = "drop column"

big and small being relative terms of course.

it is a new table, this new table hasn't any "unused" columns???

only the original table would have them.