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?
Printable View
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?
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,