Reg - Deletion & fragmentaion

[ctcyl]

Hi Oracle Gurus...

Dear friends I want to have some help from your experience, As I am deleting about 10 lacs records from transation table and inserting into new table, then taking export of new table and then droping that table.

I want to know, if we drop the table, what will happen with the space of the tablespace and what was the status of the fragmentation..

Reply for above will highly appreciate...

Naresh

[marist89]

Once you drop the table, the space will be freed. If the space that was freed was next to some other free space, it will eventually coalesed into one chunk of free space.

[APylypenko]

here's a script that will show you the fragmentation of your tablespace, you'll see where are your free chunks and how big are they



select
'free space' Owner, /*"owner" of free space*/
' ' Object, /*blank object name */
File_ID, /*file ID for the extent header */
Block_ID, /*block ID for the extent header */
Blocks /*length of the extent, in blocks*/
from DBA_FREE_SPACE
where Tablespace_Name = UPPER('Dm_TS')
union
select
SUBSTR(Owner,1,20), /*owner name (first 20 chars) */
SUBSTR(Segment_Name,1,32), /*segment name */
File_ID, /*file ID for extent header */
Block_ID, /*block ID for block header */
Blocks /*length of the extent in blocks*/
from DBA_EXTENTS
where Tablespace_Name = UPPER('Dm_TS')
order by 3,4