Dropping Large Tablespace
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Dropping Large Tablespace

  1. #1
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150

    Question Dropping Large Tablespace

    Does dropping a tablespace generates archive logs close to the size of tablespace being dropped?

    Say a 100Gb tablespace, will it generate 100 Gb archive logs or just the information that says that the tablespace got dropped?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,015
    I wouldn't think that dropping a 100GB tablespace would generate 100GB worth of redo, even if it is nearly full. I'm thinking that dropping a tablespace is unrecoverable, and thus redo would be minimal. However, you should really try it, on a test system of course.
    this space intentionally left blank

  3. #3
    Join Date
    Jan 2001
    Location
    St. Louis. MO
    Posts
    150
    This is also what I am thingking, the thing is, it's not too often that you drop large tablespace.

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    I assume you are thinking in using...
    Code:
    drop tablespace mytablespace including contents and datafiles;
    ... if this is the case Oracle will:

    1- drop all objects in the target tablespace(*)
    2- drop any index or lob segment in other tablespaces that are related to dropped objects in the target tablespace.
    3- drop target tablespace.
    4- drop datafiles related to target tablespace.

    As you might remember you do not have to worry about how much redo you are generating during a drop operation

    (*) only exception is when some or the partitions of a partitioned object are sitting in target tablespace but not all of them. If this is the case -thanks god- Oracle would refuse to drop the tablespace .
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  5. #5
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Quote Originally Posted by PAVB
    (*) only exception is when some or the partitions of a partitioned object are sitting in target tablespace but not all of them. If this is the case -thanks god- Oracle would refuse to drop the tablespace .
    i wonder why, when it can drop dependent structures, why not this?

    Rgds
    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by abhaysk
    i wonder why, when it can drop dependent structures, why not this?
    Well, this is actually a question for Angelo but I find current behavior as being a very nice feature to keep.

    Other partitions of the same table/index are not actually "dependent structures", they are part of the same table/index structure, I would say they are "peer structures".

    Dropping "dependent structures" ensures consistency.

    Dropping "peer structures" would destroy consistency.

    Does it makes sense?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Well when oracle can drop objects like index/lob/nested table?/etc (dependent structures), why not drop a logical object which is part of TS in question. When we are dropping a TS we would obiviously know what are the objects in it. And if we have taken a decesion to drop, then we know that we dont need those objects any more.

    - Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are not listening.

    An index depends on a table.

    A partition table does not depends on other partition of the same table, there is no dependency relationship, they are peers.

    That being said, if you do not like the solution -which I find brilliant, go and talk to Angelo.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  9. #9
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    I do understand they are not dependents, all i was making here is a different point.. may be it does not make sense to you.. thats ok..
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

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