export as user A in tablespace B, import as user C in tablespace D:

From: Dries Verachtert <dries_at_ulyssis.org>
Date: Thu, 2 Nov 2000 10:13:26 +0100 (CET)
Message-Id: <10668.120914@fatcity.com>

Hello,

We got 2 databaseservers here: 1 online server and 1 development server. We use oracle8i 8.1.6 on linux and the site runs on php4 on apache.

At the online server, the whole site uses the login 'wine'. At the development server, we have a user named 'winemirror'. I need to export all tables, constraints and rows (just everything owned by the user) at the online server and import this data back in the schema of 'winemirror' at the development server, because we need the data to test some new fancy statistics stuff. We can't use another 'wine' user instead of 'winemirror' because that's the backupdatabase in case the online fails.

Can anyone explain me how those imp and exp utilities work? If the usernames are the same, everything works ok: onlineserver# exp wine/<pass> FILE=wine.dmp develserver# imp wine/<pass> FILE=wine.dmp (+ some exports like NLS_LANG..)

When the username is different, i think i should use: develserver# imp winemirror/<pass> FILE=wine.dmp FROMUSER=wine TOUSER=winemirror

But this doesn't work.. i get a lot of warnings about the tables already existing.

a part of the output is:

. . importing table                   "TBLACTIONS"          0 rows imported

IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "TBLACTIONS" ADD CONSTRAINT "TBLACTIONS" PRIMARY KEY ("ILOGINI"
"D","ISEQNBR") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIA"
"L 54272000 NEXT 2048000 MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 99 FREELISTS"
" 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WINE4" ENABLE "
. . importing table "TBLACTIONTYPES" 1 rows imported IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "TBLACTIONTYPES" ADD CONSTRAINT "PK_TBLACTIONTYPES" PRIMARY KE"
"Y ("IID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 57"
"344 NEXT 40960 MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 50 FREELISTS 1 FREELI"
"ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WINE4" ENABLE "

. . importing table                 "TBLADDRESSES"        339 rows imported

IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "TBLADDRESSES" ADD CONSTRAINT "PK_TBLADDRESSES" PRIMARY KEY (""
"IID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 57344 "
"NEXT 40960 MINEXTENTS 2 MAXEXTENTS 50 PCTINCREASE 50 FREELISTS 1 FREELIST G"
"ROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "WINE4" ENABLE "
It always tries to use tablespace wine4. That's the name of the tablespace of the user at the online server. At the develserver, the user winemirror has it's own tablespace winemirror.

Has anyone any ideas what i'm doing wrong? Just let me know if you need any further information. All help is welcome.

If you want to know why all the loginnames contain 'wine': it's a winecommunity/wineshop site at http://www.underthecork.com Received on Thu Nov 02 2000 - 03:13:26 CST