Eliminate database fragmentation (export, drop and re-import tables)

Question

I have one datafile that is highly fragmented. Its size is 10GB now and real size that is used is 2.5GB,so 7.5 is free.

I decided to export data, drop that datafile, recreate it and import data.

What should I mention? Is there any chance to loose some objects?? After dropping datafile?

I have one tablespace, with one datafile. Please provide me with any suggestions about exporting scripts.

There is an option:

TABLESPACES list of tablespaces to export

if I write script like that

exp username/aaa file=a.dmp tablespaces=my_tb_name log=a.log grants=y indexes=y constraints=y triggers=y

is it enough to import everything from that tablespace and anything that is related to the containing objects(I mean grants, constraints, triggers...)

And is it necessary to be on a database server not on a client to run the export ?

Thank you a lot, waiting you clever suggestions

• Jack Douglas: Why export? Why not just migrate the objects to a fresh tablespace and drop the old? Also why is it 'fragmented' is it locally-managed with uniform extents? Also what version?
• kupa: @JackDouglas thank you. Answer is because I don't have space on server I have just 153MB, not enough for creating another tablespace and migrating objects. I mapped new network drive and want to export there. My tablespace is locally-managed with AUTOALLOCATE. Version is 10g(10.2.0.3.0)
• Jack Douglas: Is this a production server? Are you sure fragmentation is causing a problem or is you main aim to free up that space? If you are that strapped for space can you consider adding more to the server? Generally I'd consider export/import a last resort.
• kupa: @JackDouglas It is a test server but very important one, I must not loose any data. We need space. I am also planning to migrate that database to another server but not now. Sysadmins are not adding space to this server. So my last chance is to free up that space for other services. There is also installed JBOSS. Why do you think that export/import a last resort? Is there any change to loose something?
• Jack Douglas: Unless there are few or no dependencies between objects in different tablespaces it is just complicated - try Uwe's suggestion for size first I'd say

Answer 1

Instead of export/import, you should use

alter table t shrink space;

which is much less dangerous & keeps the table even accessable during the reorganization. After the shrink, you may use below script (originally from Tom Kyte) to find out to what size you can resize (making them smaller) the datafile(s):

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size'
/
select file_name,
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
 ceil( blocks*&&blksize/1024/1024) currsize,
 ceil( blocks*&&blksize/1024/1024) -
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
 ( select file_id, max(block_id+blocks-1) hwm
 from dba_extents
 group by file_id ) b
where a.file_id = b.file_id(+)
/
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd
from dba_data_files a,
 ( select file_id, max(block_id+blocks-1) hwm
 from dba_extents
 group by file_id ) b
where a.file_id = b.file_id(+)
 and ceil( blocks*&&blksize/1024/1024) -
 ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0
/

• kupa: @OweHesse Thank you for your great answer. One question. Srinking table requires enabling row movement and after performing shrinking, should I disable it or will it be disables automatically(I mean row movement).If it is enables will it cause any problem?
• kupa: @OweHesse and what about indexes and lob segments are they shrunk also or should I do it manually?
• Uwe Hesse: The enable row movement is apart from shrinking only relevant for partitioned tables - you may keep it enabled without concern for ordinary tables. Indexes can be also shrunk if you add "cascade" to the alter table command. Lob segments need to be shrunk explicitly, though.