Drop tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Drop tables

  1. #1
    Join Date
    May 2002
    Posts
    35

    Drop tables

    Hello, Gurus, I have a question regarding dropping a table. Is there any draw back to create a big table and drop the table on the aspect of disk space usage? Will it cause the dropped table space to be unusable?

  2. #2
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    Hmmm depends on the a number of things. The affect is most likely fragmentation in the tablespace IF:

    a). Your segment resides in a Dictionay Manged Tablespace holding non-uniform extents. And another segment adds another extents between your creation and dropping in your BIG table. This can cause fragmentation.

    Although, if you Create the large table and then drop the table and coalesce the tablespace before another segment extentds you'd have succesfully eliminated the change of fragmentation ... at that time.

    IF.. you have set up LMT's with UNIFORM extents (or DMT with uniforma extents) .. then slightly less of an issue, the space will be reused with no fragmentation. The issue is, not related (as the previous case) is the extenstion of the datafile. If you expand your datafile (cause by the creation of BIG tablespace).. and another segment extends.. then you won't be able to RESIZE the datafile below the HWM (High Water Mark) until you rebuild the table who is occupying the the extent prior to the HWM. Just means a larger datafile than is required.

    Just something to look at...

    Alternatively... create a separate tablesapce, create the table, use the table, drop the table, drop the tablespace.

    Cheers,
    OCP 8i, 9i DBA
    Brisbane Australia

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