Re: Tablespace export ???? how ???

Select * from dba_segments where tablespace_name='X';

You're particularly interested in the segment name and its owner (but I can't remember off the top of my head whether the segment name column is called seg_name, segment_name, segmentname, obj_name, object_name, tablename, table_name, table or some other variant that the designers of the Oracle Data Dictionary have decided to inflict upon us. I suspect it's 'segment_name', but I find "*" rather easier to remember).

Now export. If you're using the command line, it's a question of tables=(scott.emp, freddy.salaries, andso.adinfinitum)

Then... drop tablespace X including contents

Finally, create tablespace X datafile 'path/filename' size 1000m (I believe in being generous with space provision).

Import, using the same comma separated list of tablespaces.

Just one word of warning: do not use this as an opportunity to re-name the tablespace, because you are at risk, if you do, from discovering that all your tables get re-created in the SYSTEM tablespace. Keep the names exactly the same, and it won't happen.

Regards
HJR "Venkat Iyer" <venkat_p_iyer_at_yahoo.com> wrote in message news:3abd8267.0_at_news.tm.net.my...
> hi there guys and gals...
>
> i'm trying ot reclaim some space from my datafiles....i need to drop a
> tablespace and recreate it with the rite storage and sizing
> parameters.....how do i just export the contents of a tablespace, drop it
> and recreate it ?????
>
> thanx in advance
> venkat
>
>
Received on Sat Mar 24 2001 - 23:55:34 CST