alternatives of deleting column of table
Hello again,
I have a table which contains about 1.5 billion rows.
I want to delete a column of the table which is no longer needed.
In most rows the this column is still filled.
Hopefully I am looking for an statement which can be run online, during the night (low load).
Currently I use the following statement to get rid of the column:
alter table mytablel drop column col_to_be_deleted;
The problem is that this statement needs a very big undo_tablespace.
I assume that tis will also lead to many, many archive-logs -> the next problem.
(But I don't know for sure because we don't have archiving enabled on our developement system).
On production I think my dba will kill me when i produce that much archive-logs and undos at a time.
What solutions do I have to minimize the undo_usage and archiving?
Is it possible to to split the delete to multiple runs so that i have more, inexpensive runs?
I hope it is understandable what i mean.
If possible I prefer an online solution, but offline sceneries are also welcome.
As further information:
Soon I will introduce partitioning to this table. If possible I want also devide the old data into partitions.
Is it possible to import only all other columns of the table except the col_to_be_deleted? If that is possible it could maybe be combined with the partitioning introduction in a downtime.
Thanks,
Andreas