Re: Transfer Data to another tablespace with different name

If you can afford to make tablepspace T1 temporarily read only, you could use transportable tablespaces to do this. Without providing the exact syntax the steps you would need to take would be;

   alter tablespace T1 read only;
   use exp to create a dmp file containing the metadata for tablespace T1    user exp with rows=n to create a dmp file containing S1's other objects    at the operating system level copy the datafiles belonging to T1 to new    files intended to belong to T2
   alter tablespace T1 read write;
   temporarily rename T1 to T1_ORIG
   import the metadata dmp file created in step 2 using the files created    in step 4 - also specifying fromuser=S1 touser=S2    import the dump file created in step3 again specifying fromuser=S1    touser=S2 , also ignore=y
   alter tablespace T1 read write
   rename tablespace T1 (the new one imported in step 6) to T2    rename tablspace T1_ORIG back to T1

Although this looks like a lot of steps it is actually the quckest way to achieve you describe below. Sorry I haven't had time to provide the exact syntax but if you have any questions I'll be happy to answer them.

Cheers,

Ian

P.S. If you want to look datapump I believe there is an option to rename the tablespace on the impdp step which would save having to rename each tablespace later

|---------+----------------------------->


|         |           gheibia_at_gmail.com |

|         |           Sent by:          |

|         |           oracle-l-bounce_at_fr|

|         |           eelists.org       |

|         |                             |

|         |                             |

|         |           24/11/2008 03:34  |

|         |           Please respond to |

|         |           gheibia           |

|         |                             |

|---------+----------------------------->
  >--------------------------------------------------------------------------------------------------------------|
  |                                                                                                              |
  |       To:       martin.a.berger_at_gmail.com                                                                    |
  |       cc:       oracle-l_at_freelists.org                                                                       |
  |       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 email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.

For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^