Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: copy data of one table to anothere from export (dump) file
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
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 exportedExport 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 importedImport 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