Re: Transfer Data to another tablespace with different name
Date: Mon, 24 Nov 2008 11:34:47 +0800
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.
On Mon, Nov 24, 2008 at 4:22 AM, Martin Berger <martin.a.berger_at_gmail.com>wrote:
> 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
> maybe this will bring some inspiration ;-)
> but first, please make sure what your demand is.
> best regards,
> Martin Berger http://berxblog.blogspot.com
> I want to keep the original copy intact.. I want to make a copy of the
> 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.