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

exportimportoracle

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

Best Answer

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
/
Related Question