Re: Moving Table in Tablespace

Keith Jamieson wrote:
>
> Thats a nice idea, unfortunately some of us are restricted as to what
> version of Oracle we can go to, due to our use of certain products.
> ie in our case, we cant go past Oracle 8.0.5 because our partner does not
> support any version later than this.
>
> Jonathan Lewis wrote in message
> <954876200.1778.3.nnrp-09.9e984b29_at_news.demon.co.uk>...
> >
> >Get 8.1 and use 'alter table XXX move tablespace YYY';
> >Then 'alter index xxx_pk rebuild tablespace zzz' etc.
> >The nologging option will make it faster but may not
> >be appropriate.
> >
> >--
> >
> >Jonathan Lewis
> >Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
> >
> >Duarte Nuno Sousa wrote in message <38E9E89A.5576F60_at_solsuni.pt>...
> >>
> >>What is the best mode of moving tables, with data, across Tablespaces ?
> >>
> >>Duarte Nuno de Sousa
> >>duarte.sousa_at_solsuni.pt
> >>
> >>
> >
> >

	CREATE TABLE new
	TABLESPACE xxx
	AS SELECT * FROM old;

	CREATE INDEX new_x1 ....

	RENAME old TO really_old;
	RENAME new TO old;

You just have to make sure that no one is updating or inserting between the time you issue the CREATE TABLE and the last RENAME.

-- 
Jerry Gitomer 
Once I learned how to spell DBA, I became one