Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: copy data of one table to anothere from export (dump) file

Re: copy data of one table to anothere from export (dump) file

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 17 Oct 2007 22:42:12 +0200
Message-ID: <471673A4.6060707@gmail.com>


Mark D Powell schrieb:
> On Oct 17, 11:34 am, DA Morgan <damor..._at_psoug.org> wrote:

>> sonu wrote:
>>> Hi,
>>> There is one export dump file say exp.dmp. From that dump file i want
>>> to load data of table say X into Y which is same as X schema wise. Is
>>> it possible?
>>> Regards,
>>> AB.
>> Your request is far from clear but you might want to look here:http://www.psoug.org/reference/import.html
>> specifically at the FROM USER and TO USER syntax.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> The traditional exp and imp utilities do not supporting importing
> Table_A into another table by any other name other than Table_A. You
> can switch owners but not table_names.
>
> Look at CTAS (create table as select). You might be able to perform a
> CTAS or use CTAS in conjuction with exp/imp activity to perform the
> task. The rename command might also be of use in this task or a
> database link if you are crossing platforms.
>
> HTH -- Mark D Powell --
>
>
>

Of course, renaming the target table after import seems to be the most straightforward way to achieve that, however, just for fun, one could exploit the fact, that import checks only the name of the target object, not the object_type and import directly into another table:

SQL> conn scott/tiger
Connected.
SQL> create user tiger identified by scott;

User created.

SQL> grant unlimited tablespace to tiger;

Grant succeeded.

SQL> create table tiger.pme as select * from emp where 1=2;

Table created.

SQL> create synonym tiger.emp for tiger.pme;

Synonym created.

SQL> !exp userid=scott/tiger tables=emp file=emp.dmp statistics=none

Export: Release 10.2.0.3.0 - Production on Wed Oct 17 22:37:17 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options Export done in AL32UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table                            EMP         14 rows exported
Export terminated successfully without warnings.

SQL> !imp userid=scott/tiger fromuser=scott touser=tiger file=emp.dmp ignore=y

Import: Release 10.2.0.3.0 - Production on Wed Oct 17 22:37:22 2007

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path import done in AL32UTF8 character set and AL16UTF16 NCHAR character set . importing SCOTT's objects into TIGER

. . importing table                          "EMP"         14 rows imported
Import terminated successfully without warnings.

SQL> select count(*) from tiger.pme;

   COUNT(*)


         14

SQL> Best regards

Maxim Received on Wed Oct 17 2007 - 15:42:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US