Moving data from one tablespace/user to another in 8.1.7

From: rockitrod <member29360_at_dbforums.com>
Date: Thu, 08 May 2003 19:27:18 +0000
Message-ID: <2856408.1052422038_at_dbforums.com>

Hi,

I am in QA and do multiple installations of a product that uses the database for the repository. I'm trying to find a way to migrate my data between product builds. With user A assigned to tablespace A, I want to move the data to user B assigned to tablespace B.

I converted tablespace A to a transportable tablespace (from the Guide for Managing Tablespaces) and am trying to use exp/imp utilities to acheive the above. But, I'm getting the below errors. Can anyone help me with fixing the errors or suggest a different way to do this? I would greatly appreciate it!

C:\>imp transport_tablespace=y
  datafiles=('C:\ORACLE\ORADATA\LOCALORA\RECOVERY_REP_150.ORA')   tablespaces=(recovery_rep_1
50) tts_owners=(recovery_rep_150) fromuser=(recovery_rep_150)

    touser=(rec_rep_153) file=c:\rep150.DMP

Import: Release 8.1.7.4.1 - Production on Thu May 8 11:53:23 2003

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Username: sys as sysdba
Password:

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production With the Partitioning option
JServer Release 8.1.7.4.1 - Production

Export file created by EXPORT:V08.01.07 via conventional path About to import transportable tablespace(s) metadata... import done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
IMP-00017: following statement failed with ORACLE error 29349:
"BEGIN

 sys.dbms_plugts.beginImpTablespace('RECOVERY_REP_150',12,'SYS',1,0,"
"8192,1,4597242,1,505,5,5,0,50,1,0,0,2163937968,1,0,4501257,NULL,0,-
 0,NULL,NU"
"LL); END;"

IMP-00003: ORACLE error 29349 encountered
ORA-29349: tablespace 'RECOVERY_REP_150' already exists
ORA-06512: at "SYS.DBMS_PLUGTS", line 1396
ORA-06512: at line 1

 importing RECOVERY_REP_150's objects into REC_REP_153 IMP-00017: following statement failed with ORACLE error 900:
"CREATE TABLE "SLIDE_URI" ("URI_ID" NUMBER(*,0), "URI"
 VARCHAR2(3200) NOT NU"
"LL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
 STORAGE(S"
"EG_FILE 13 SEG_BLOCK 2 OBJNO_REUSE 49127 INITIAL 40960 NEXT 40960
 MINEXTENT"
"S 1 MAXEXTENTS 505 PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL"
" DEFAULT) TABLESPACE "RECOVERY_REP_150""

IMP-00003: ORACLE error 900 encountered
ORA-00900: invalid SQL statement
IMP-00017: following statement failed with ORACLE error 900:

"CREATE TABLE "SLIDE_OBJECT" ("URI_ID" NUMBER(*,0), "CLASS_NAME"
 VARCHAR2(32" Thanks!
Lorinda

--
Posted via http://dbforums.com

Received on Thu May 08 2003 - 21:27:18 CEST