Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Tablespace export ???? how ???

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

From: Steve Blomeley <steveblomeley_at_yahooDOTco.uk>
Date: Sun, 25 Mar 2001 14:52:30 +0100
Message-ID: <3ABDF81E.DA644908@yahooDOTco.uk>

Venkat Iyer wrote:
>
> 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

An alternative approach would be to resize the tablespace and associated tables using a few SQL commands.

If the datafile(s) contain plenty of free space you can shrink them with...
SQL> ALTER DATABASE DATAFILE 'filename.dbf' RESIZE n M ; where 'n' is the size in Mb to shrink the file down to

To change the storage settings for existing tables... SQL> ALTER TABLE my_table MOVE STORAGE( ... ) ; This will leave your table in the same tablespace, but will effectively rebuild it using the new storage settings.

And to change the default storage clause for the tablespace: SQL> ALTER TABLESPACE my_ts DEFAULT STORAGE ( ... );

I'm pretty sure you need 8i to use all these commands - Check your SQL manual for more details.

hth
SB Received on Sun Mar 25 2001 - 07:52:30 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US