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

  • 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? Oct 27, 2011 at 18:51
  • @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(
    – kupa
    Oct 27, 2011 at 19:19
  • 1
    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. Oct 27, 2011 at 19:36
  • @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?
    – kupa
    Oct 27, 2011 at 19:45
  • 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 Oct 27, 2011 at 20:34

1 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
  • @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
    Oct 27, 2011 at 20:46
  • @OweHesse and what about indexes and lob segments are they shrunk also or should I do it manually?
    – kupa
    Oct 27, 2011 at 20:52
  • 1
    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.
    – Uwe Hesse
    Oct 28, 2011 at 6:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.