From: Jerry Gitomer Subject: Re: Table reorg.. what's the best way? Date: 2000/05/05 Message-ID: <20000505.4202900@noname.nodomain.nowhere>#1/1 Content-Transfer-Encoding: quoted-printable References: <8es91r$94j$1@nnrp1.deja.com> X-Priority: 3 (Normal) Content-Type: text/plain; charsetISO-8859-1 X-Complaints-To: abuse@rcn.com X-Trace: 5p+mnKeceUBKFKlNv/me23PDr8R3ysYuNIEc0OZyCJE Mime-Version: 1.0 NNTP-Posting-Date: 5 May 2000 03:20:14 GMT Newsgroups: comp.databases.oracle.server Although you can do this with an export and import I find it much faster to use T.O.A.D. To generate scripts to recreate the table, its indexes, its grants, and its constraints and then edit the script to reflect the new tablespaces (you are going to put the indexes in a different tablespace than the data, aren't you). Rename the existing table. Create the new table. Insert into new select * from old. Drop the indexes on the old table. Create the indexes on the new table. I am getting ready to use this technique to move 1300+ tables and their indexes in an ERP application in order to get them out of 2 tablespaces and to enable uniform sizing so that I won't ever have to reorganize the database in the future. >>>>>>>>>>>>>>>>>> Original Message <<<<<<<<<<<<<<<<<< On 5/4/00, 11:38:22 AM, Tom wrote regarding Table reorg.. what's the best way?: > 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.