CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Random guy
Random guy

Reputation: 923

How to drop many columns in table in oracle when columns are very large?

I have a table and the table consists of 70 columns.I want to drop about 68 columns and i just want 2 columns in my table.So,whatever I tried is given below:

Alter table_name drop column where column
_name not in (col1, col2);

I found this step is time consuming. Is there any other better option where I can delete 68 columns without getting much time engaged?

Upvotes: 0

Views: 603

Answers (2)

Matthew McPeak
Matthew McPeak

Reputation: 17934

Write a script to generate the DDL to save typing.

CREATE TABLE test_drop ( a number, b number, c number, d number, e number);

select 'alter table ' || owner || '.' || table_Name || ' drop column ' || listagg(column_name,',') within group ( order by column_name) || ';' drop_ddl
from dba_tab_columns 
where owner = user
and table_Name = 'TEST_DROP'
and column_Name not in ('A','B')
group by owner, table_name;

+-----------------------------------------------+
|                 DROP_DDL                      |
+-----------------------------------------------+
| alter table APPS.TEST_DROP drop column C,D,E; |
+-----------------------------------------------+

Then copy the output to and you have your DDL. Recreating the table is an unnecessary pain.

Upvotes: 1

TurtlesAllTheWayDown
TurtlesAllTheWayDown

Reputation: 406

Create a new table with a SELECT: CREATE new_table AS SELECT col1, col2 FROM old_table

Drop the old table (use the purge option if you don't want it in the recycle bin)

Rename the new table to the old table name: RENAME new_table TO old_table

You'll also need to add back any indexes, triggers, constraints or grants that you had on the old table if you want them on the new table.

Upvotes: 3

Related Questions