Moving Tablespaces in Oracle 8i

Ever tried to move a tablespace to another in Oracle 8i? Well, it's hard, unless you know the trick. The problem is that while Oracle provides tools for exporting and importing tablespaces, namely exp and imp, these tools only work when the import is done on the same tablespace from where the export was done. Sometimes what you want to do is move the data from one tablespace to another. This document describes a dirty hack to do that.

Say you have a tablespace call "PRODUCTION" which you need to move to a new tablespace called "DEVELOPMENT". These are the steps to move all the data from "PRODUCTION" to "DEVELOPMENT".

If "DEVELOPMENT" already exists, delete it and then recreate it.

    $ svrmgrl

    Oracle Server Manager Release 3.1.7.0.0 - Production

    Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    SVRMGR> connect internal
    SVRMGR> drop user development cascade;
    SVRMGR> drop tablespace development including contents cascade constraints;
    SVRMGR> exit;
    $ rm -f /ora8/m02/oradata/ora8/development.dbf
    $ svrmgrl

    Oracle Server Manager Release 3.1.7.0.0 - Production

    Copyright (c) 1997, 1999, Oracle Corporation.  All Rights Reserved.

    Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
    With the Partitioning option
    JServer Release 8.1.7.0.0 - Production

    SVRMGR> create tablespace development datafile '/ora8/m02/oradata/ora8/development.dbf' size 50m autoextend on default storage (pctincrease 1);
    SVRMGR> create user development identified by development default tablespace development temporary tablespace temp quota unlimited on development;
    SVRMGR> grant connect, resource, ctxapp, javasyspriv, query rewrite to development;
    SVRMGR> revoke unlimited tablespace from development;
    SVRMGR> alter user development quota unlimited on development;
    SVRMGR> exit;

Now export the "PRODUCTION" tablespace using the exp utility. While logged in as the oracle user do:
```
    $ exp production/production file=production.dmp owner=production consistent=Y direct=Y
```
This will create a file called production.dmp that contains a dump of your tablespace.
Hack the .dmp file with xemacs (or optionally emacs).
1. If the file is too big (more than 128MB) you'll need to split it in smaller chunks and then put it back together. This is due to a limitation in Emacs in which you can't open files that are bigger than 128MB.
```
    $ split -b100m production.dmp
```
  This will create files called "aa", "ab", "ac", etc. each 100MB in size.
2. For each of these files, open them with emacs and do a search for '"PRODUCTION"' and replace it with '"DEVELOPMENT"' (note that the double quotes are part of the search and replace). The easiest way to do this is with the command M-% in Emacs.
3. Finally, put the files back together.
```
    $ mv aa development.dmp
    $ cat a* >> development.dmp
```

Now import the file development.dmp using the imp utility. As the oracle user type:

    $ imp development/development file=development.dmp buffer=8192000 full=y

That's it. Now everything that was in "PRODUCTION" is now on "DEVELOPMENT".

Note

According to Doris Groveau this approach will dump all tablespaces in the production schema and import them into the development schema. Therefore, if there are many tablespaces in production they will all be imported into development.

This works because whenever you specify an owner and do not specify which tables to export, everything that has been created by that owner is exported.

obonilla@galileo.edu