Re: To free a db file, no other solution than exp/imp ??

There's always another way...

Which version are you using? If you are on Oracle 8i, then you can create a second tablespace and move the tables to that second tablespace with the ALTER TABLE MOVE command. After the tables have been moved, reduce your file size and then move the tables back.

If you are pre-8i, then you can do the same thing, but instead of the ALTER TABLE MOVE command, you can *copy* the table with CREATE TABLE AS SELECT into the new tablespace. Then drop your original tables. Resize your datafile and copy the tables back.

HTH,
Brian

monsri_at_my-deja.com wrote:
>
> Hi,
> I've purged a datafile that's almost 2G big, on which I
> still have 17 segments. Due to fragmentation, I still
> can't resize this file to anything, although all 17
> remaining segments amount to about 50M in size in this
> file !
>
> My question is: is there NO OTHER WAY of re-gathering
> together this file's extents than through an export/import,
> in order to resize it afterwards ??
>
> NO OTHER WAY AT ALL ??
>
> Thks !
> BO'M
>
> (here is the SQL I use to visualize my file's fragmentation:
> > select segment_name, block_id, blocks, file_id
> > from dba_extents
> > where file_id=&&1
> > union
> > select 'free' segment_name, block_id, blocks, file_id
> > from dba_free_space
> > where file_id=&&1
> > order by file_id,block_id
> > /)
>
> Sent via Deja.com
> http://www.deja.com/
 

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================