From: Thomas J. Kyte <tkyte@us.oracle.com>
Subject: Re: Table reorg.. what's the best way?
Date: 2000/05/05
Message-ID: <8euelf$m79$1@nnrp1.deja.com>#1/1
References: <8es91r$94j$1@nnrp1.deja.com>
X-Http-Proxy: 1.0 x36.deja.com:80 (Squid/1.1.22) for client 205.227.43.12
Organization: Deja.com - Before you buy.
X-Article-Creation-Date: Fri May 05 12:26:39 2000 GMT
X-MyDeja-Info: XMYDJUIDtkyte
Newsgroups: comp.databases.oracle.server
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.01; Windows NT 5.0)


In article <8es91r$94j$1@nnrp1.deja.com>,
  ray.thomas@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@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@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.