Moving Tablespaces in Oracle 8iby Oscar Bonilla
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".
$ svrmgrl Oracle Server Manager Release 188.8.131.52.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 184.108.40.206.0 - Production With the Partitioning option JServer Release 220.127.116.11.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 18.104.22.168.0 - Production Copyright (c) 1997, 1999, Oracle Corporation. All Rights Reserved. Oracle8i Enterprise Edition Release 22.214.171.124.0 - Production With the Partitioning option JServer Release 126.96.36.199.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;
exputility. While logged in as the
$ exp production/production file=production.dmp owner=production consistent=Y direct=YThis will create a file called
production.dmpthat contains a dump of your tablespace.
.dmpfile with xemacs (or optionally emacs).
$ split -b100m production.dmpThis will create files called "aa", "ab", "ac", etc. each 100MB in size.
$ mv aa development.dmp $ cat a* >> development.dmp
imputility. As the
$ imp development/development file=development.dmp buffer=8192000 full=y
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.