Discussion Locked

Oracle database file

0 helpful
5 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Need space in hard disk after this database clean up the used space in the ORANT\DATABASE\USR1ORCL.ORA does not go down, I have the file size set to 1.8 GB and the storage manager shows 1.7 used. Is this a high water mark or is really the space used in the file.
Before Cleanup
Export DMP File 558 Meg
Export Time > 2 Hours
Import Time > 3 Hours
Traffic Table 6,158,000 Rows
Traffic Report > 12 Minutes

After Cleanup
Export DMP File 199 Meg
Export Time < .5 Hours
Import Time > 1 Hour
Traffic Table 757,000 Rows
Traffic Report < 30 Seconds

5 replies

by christian.boulet

Hi,

As I understand it, all you did is deleting data from your tables, then performed Export/import to de-fragment your data files... Right??

But what you need to do now is a Alter Table DEALLOCATE UNUSED in order to de-allocate unused extents. Then you will be able to do a ALTER TABLESPACE xxx COALESCE
wich will do for each datafile in the tablespace, coalesces all contiguous free extents into larger contiguous extents.

... to be used by other tables...

Hope this helps...

Another thing you can do is SELECT tablespace_name, sum(bytes) from dba_free_space group by tablespace_name

which will give you the free extents on your tablespace...

Good luck!

Christian

This discussion is closed to new replies.