Re: Transfer Data to another tablespace with different name

Thnaks. I tried that. But it looks like that it tries to create the PKs and Indexes in the original tablespace. That's why I got a lot of 1658 errors.

IMP-00017: following statement failed with ORACLE error 1658:  "CREATE UNIQUE INDEX "PK_SKILLPREFERENCES" ON "SKILLPREFERENCES" ("ID" ) PC"
 "TFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST"
 " GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "BETATEST" LOGGING" IMP-00003: ORACLE error 1658 encountered ORA-01658: unable to create INITIAL extent for segment in tablespace BETATEST "BETATEST" is the original tablespace. As you can see it tries to create it in there and because doesn't have enough space, it comes up with that error. But why it creates it there?

here is the command I used:

imp tblspc_user/tblspc_user fromuser=betatest touser=tblspc_user file=".\desktop\a.dmp" log=".\desktop\b.log"

tblspc_user is the second user (S2) and the second tablespace is called "tblspc". Should I have logged in as Sys or system?

• Amir

On Mon, Nov 24, 2008 at 11:58 AM, Harshan Vasudevan Eppurath < harshan.eppurath_at_capgemini.com> wrote:

> In that case simple export and import should do it. When importing
> specify
>
>
>
> Imp fromuser=s1 touser=s2
>
>
>
> You have to precreate user S2 and tablespace t2 and make default tablespace
> for s2 as t2.
>
>
>
> Regards,
>
> Harshan
>
>
> ------------------------------
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Amir Gheibi
> *Sent:* Monday, November 24, 2008 9:05 AM
> *To:* Martin Berger
> *Cc:* ORACLE-L
> *Subject:* Re: Transfer Data to another tablespace with different name
>
>
>
> Martin,
>
>
> Thanks for the reply. The link seems very helpful.
> My objective is clear. There is a schema called "S1" and all of its objects
> are located in one tablespace called "T1". I need to make a copy of the
> tablespace (T1) with a different name (T2) and all the objects inside T2 be
> owned by another user. (Belong to another Schema; S2).
>
> So S1's objects are all inside T1 and S2's objects are inside T2.
>
> - Amir
>
> On Mon, Nov 24, 2008 at 4:22 AM, Martin Berger <martin.a.berger_at_gmail.com>
> wrote:
>
> Amir,
>
>
>
> you want to make a copy, but preserve foreign keys. And I assume, you also
> want to preserve the table-name?
>
> Which one should be the one which serves the Foreign Keys?
>
>
>
> You also cannot have the same object twice within the same schema.
>
>
>
> There might be methods how to keep the same data in two tables within the
> same schema; I would start checking streams or Replication.
>
>
>
> If you want to make a copy of the tablespace, check
>
>
> http://blog.tanelpoder.com/2008/10/21/transportable-tablespaces-and-rowid-uniqueness/
>
> maybe this will bring some inspiration ;-)
>
>
>
> but first, please make sure what your demand is.
>
>
>
> best regards,
>
> Martin
>
>
>
> --
>
> Martin Berger http://berxblog.blogspot.com
>
>
>
>
>
> I want to keep the original copy intact.. I want to make a copy of the
> tablespace..
>
> You can always use move command to put data into another tablespace and
> then rename the objects
>
> ________________________________
>
> How can I transfer data from a tablespace in one db to another tablespace
> in the same db?
>
> I have Foreign Key and Sequence objects:
> - Apparently when you use IMP and you already have the structure in the
> destination tablespace created, tables are not imported in the right
> sequence in which the FK - PK relationships are respected and you end up
> with a lot of Key violation errors. It imports the tables that have FK
> objects and since it can not find the PK related to the FK, it doesn't
> import the row and raise an error.
> - Sequences should keep their last used values.
>
> Does anyone have a comprehensice sholution that covers everything?
>
> I'm using 10g R2.
>
>
>
>
>
>
>
>
> This message contains information that may be privileged or confidential and is the property of the Capgemini Group. It is
> intended only for the person to whom it is addressed. If you are not the intended recipient, you are not authorized to
> read, print, retain, copy, disseminate, distribute, or use this message or any part thereof. If you receive this message
> in error, please notify the sender immediately and delete all copies of this message.
>
>

--
http://www.freelists.org/webpage/oracle-l