Re: Moving tables from one tablespace to another

From: Stuart Hemming <shemminga_at_cix.compulink.co.uk>
Date: 1996/01/10
Message-ID: <DKyI6w.A6F_at_cix.compulink.co.uk>#1/1

> cfischer_at_ancillary.utmrad1.utmb.edu wrote:

> >
> > I have been given the task of upgrading/moving an application. The
> > tables are currently in Oracle v6 database but they are all in the
> > system tablespace. I need to move them to an Oracle7 db on another
> > machine and I need to put the tables in an application data tablespace
> > and the indexes in an index tablespace. Everything is under one user.
> > I
> > know I can export the user but when I import it it wants to put it
> > into
> > the system tablespace again. How do I convince it that that is not the
> > right thing to do? Do I have to edit the export file and change ALL
> > the
> > references?(Yuk)
> >
> > please post or send replies to cfischer_at_ancillary.utmrad1.utmb.edu

We recently had to do a similar thing. Export the user who owns the tables then import with SHOW=Y, INDEXES=Y INDEXFILE=<filename>.sql

Now using you favourite editor, edit the file to (a) remove the "REM" in front of all the CREATE TABLE/CLUSTER statements, (b) replace all occurrences of "TABLESPACE SYSTEM" with TABLESPACE <whatever>.

Execute the resultant file to create all of the objects. Now run import again with IGNORE=Y.

I hope this helps.

---

These are *my* opinions. Please don't make the mistake of believing anybody else is stupid enough to think like this!

---

 Stuart Hemming                     | shemminga_at_cix.compulink.co.uk
 The Data Base                      | Tel: + 44 (0) 115 925 0005
 Nottingham UK                      | Fax: + 44 (0) 115 929 4668
-------------------------------------------------------------------

Received on Wed Jan 10 1996 - 00:00:00 CET