Re: Table reorg.. what's the best way?

In article <8es91r$94j$1_at_nnrp1.deja.com>,   ray.thomas_at_pmlmail.com wrote:
> Hi all,
>
> I have 2 large tables with many extents. I have been instructed to
 move
> these tables to their own tablespace and reorg them at the same time.
 I
> have to create a tablespace, export the two tables and everything
> associated with them and import them into the new tablespace without
> losing any indexes, constraints, grants etc. Can anyone suggest the
> best method to accomplish this without losing anything in the process?
>
> Tom
> Please reply to: ray.thomas_at_pmlmail.com
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You don't mention the version. If the database is 8.1, you can use:

alter table T move tablespace Y storage ( .... );

and then alter index T_idx1 rebuild tablespace ....; for each index on T.

No muss, no fuss -- no lost triggers, constraints and so on.

Before that release, exp/imp or create table as select as others have posted.

--
Thomas Kyte                              tkyte_at_us.oracle.com
Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html
--
Opinions are mine and do not necessarily reflect those of Oracle Corp


Sent via Deja.com http://www.deja.com/
Before you buy.