RE: export as user A in tablespace B, import as user C in tablesp

It isn't a big deal because in 8i you can move tables around (see 'ALTER TABLE ... MOVE...'). Only, you should be careful and rebuild all indexes after you move a table. Moving tables around changes all rowids and thus invalidates all indexes on the table.
Sonja, being from Croatia myself, I hope it's OK for me to ask which company do you work for?

-----Original Message-----
From: Sonja Sehovic [mailto:sonja.sehovic_at_zg.tel.hr] Sent: Thursday, November 02, 2000 4:56 AM To: Multiple recipients of list ORACLE-L Subject: Re: export as user A in tablespace B, import as user C in tablespace D:

If I understood correctly you have different users, and tablespaces on the two servers?
What I did in such enent is to cerate this tables on the new tablespaces before the import. After is is done you start import with IGNORE=Y. That should do the trick

            Sonja

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 02, 2000 10:15 AM

>
> 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
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-1?Q?Sonja_Sehovic?=
  INET: sonja.sehovic_at_zg.tel.hr

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