Dropping a partition with "REUSE STORAGE' clause
I am not a DBA. i was giving this task to drop a large table which have 4Billion rows and has 22000 partitions. what would be the best way to drop the table and get back the space.
my DBA suggested to do the following two steps
1) Drop partition with REUSE STORAGE and
2) drop tablespace including contents and datafiles.
I dont understand how to drop the partitions with reuse storage clause. i tries to do this and i am getting the following error
alter table COVERAGE_DETAIL_FACT drop partition COVG_FACT_20480 reuse storage
14048 "a partition maintenance operation may not be combined with other operations"
can any one help me out with this.
Thank you in advance.
Ask your DBA how he intends to "REUSE STORAGE" if he's going to "drop tablespace including contents and datafiles"?
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
I agree with lkbrown, you should not include reuse storage. You might want to drop the tables constraints and indexes first, then drop the table. You can also drop the table with cascade constraints, but it would probably be faster to drop the constraints and indexes first.
Originally Posted by LKBrwn_DBA
I would make sure that I am trying to drop the right table, especially since its 4 billions rows. If you are wrong, that is a lot of data to restore.
this space intentionally left blank
Thank you. this helped me.
Click Here to Expand Forum to Full Width