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

From: <ramakrishna.gv_at_db.com>
Date: Thu, 2 Nov 2000 15:17:19 +0530
Message-Id: <10668.120926@fatcity.com>

Hi
In your import statement give option ignore=y. u can ignore those warnings as the objects are already exsting and it is trying to create the object again. to avoid the importing into wine tablespace,

     create indexfile with rows=no parameter
                change the tablespace names and other things which ever u want in indexfile.
                run the indexfile.
                import only the data with rows=yes.

Thanks & Regards

Ram
Tel:4236953

Message History ----------------------------------------

From: dries_at_ulyssis.org on 02/11/2000 09:15 GMT

Please respond to ORACLE-L_at_fatcity.com

To: ORACLE-L_at_fatcity.com
cc:
Subject: export as user A in tablespace B, import as user C in tablespace D:

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

Thanks in advance,
Dries Verachtert

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Dries Verachtert
  INET: dries_at_ulyssis.org

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Thu Nov 02 2000 - 03:47:19 CST