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

"Steve Blomeley" <steveblomeley_at_yahooDOTco.uk> wrote in message news:3ABDF81E.DA644908_at_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
>

Doesn't work if there is plenty of free space in the tablespace, but it's all concentrated in the *middle* of the file. If there's a single block that's in use towards the end of the file, you won't be able to shrink down past that point.

> 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.
>

True. 8i only. And don't forget that all indexes on all such moved tables are invalidated by the move, and hence need to be rebuilt. And I thought the syntax was technically 'alter table blah move tablespace X storage(...)' -I guess the existing tablespace defaults if you don't specify it?

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

Also true, but has no effect on existing segments, even if they acquire new extents. Only brand new segments without their own storage clause will pick up the change.

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

No, only the 'move tablespace' command is new in 8i. The others have been around since the Jurassic Age.

Regards
HJR
>
> hth
> SB
Received on Sun Mar 25 2001 - 14:39:08 CST