>
> Hi Ari
>
> I read some of your technical responses and I found some of them very
> helpful.
> I hope you can help me with a problem.
>
> Is there an easy way to delete empty columns from a table?
>
> Thanks in advance
>
> Gaston
In Oracle8, you can issue:
ALTER TABLE table_name DROP COLUMN column_name;
(Thanks to Keith Lewish )
In Oracle7, there is no easy way. The best way is to do the following steps:
Assume you wish to delete the OLD_COLUMN column from the THE_TABLE table:
THE_TABLE
--------
column_a number
column_b varchar2(10)
OLD_COLUMN varchar2(1000)
column_d number
1) Create a temporary table with all columns except the one you want to delete:
CREATE TABLE temp_table AS
SELECT column_a, column_b, column_d FROM THE_TABLE;
At this point, you will have the TEMP_TABLE that looks like:
TEMP_TABLE
--------
column_a number
column_b varchar2(10)
column_d number
2) DROP TABLE THE_TABLE;
3) RENAME TEMP_TABLE TO THE_TABLE
4) Recreate all triggers, constraints, indexes, grants, statistics on
THE_TABLE. Also recompile any functions, procedures, packages that depend
on THE_TABLE.
Easy, huh? ;)
-Ari Kaplan
Independent Oracle DBA Consultant
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
<-> For 150+ Oracle tips, visit my Web Page: <->
<-> <->
<-> www.arikaplan.com <->
<-> <->
<-> email: akaplan@interaccess.com <->
<-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><-><->
Back to Ari Kaplan's Home Page