DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2013

    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

    error report:
    14048 "a partition maintenance operation may not be combined with other operations"

    can any one help me out with this.

    Thank you in advance.

  2. #2
    Join Date
    Jul 2002
    Lake Worth, FL


    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

  3. #3
    Join Date
    Nov 2000
    Pittsburgh, PA
    Quote Originally Posted by LKBrwn_DBA View Post
    Ask your DBA how he intends to "REUSE STORAGE" if he's going to "drop tablespace including contents and datafiles"?
    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.

    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

  4. #4
    Join Date
    Oct 2013
    Thank you. this helped me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Click Here to Expand Forum to Full Width

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.