Search This Blog

Saturday, December 23, 2006

Table Copy From OneDB to AnotherDB

You can use Three Method.
1.Database link
2.Export or Import
3.Copy Command.

------------------------------------------
1.Database link
sql>conn u1/u1@db2
sql>create table a as select * from all_objects where rownum <= 10; table created. For database link you have "database link" or "create session" privs. sql>conn u1/u1@db1
connected
sql>create database link db2
connect to u1
identified by u1
using 'db2';
database link created
sql>create table t as select * from a@db2;
table created
sql>select count(*) from t;
----------------------------------------------------------
2.Through Export or Import Utilities.
------------------------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table d as select * from all_objects where rownum <= 10; Table created. SQL> host exp a1/a1@db1 tables=d file=d:\oracle\d.dmp log=d:\oracle\d.log
Export: Release 10.1.0.2.0 - Production on Sat Dec 23 18:24:52 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table D 10 rows exported
Export terminated successfully without warnings.
SQL> conn a1/a1@db2
Connected.
SQL> host imp a1/a1@db2 fromuser=a1 touser=a1 file=d:\oracle\d.dmp log=d:\oracle
\d.log ignore=y
Import: Release 10.1.0.2.0 - Production on Sat Dec 23 18:25:58 2006
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Produc
tion
With the Partitioning, OLAP and Data Mining options
Export file created by EXPORT:V10.01.00 via conventional path
import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. . importing table "D" 10 rows imported
Import terminated successfully without warnings.
------------------------------------------------------
3.Through Copy Command.
----------------------
SQL> conn a1/a1@db1
Connected.
SQL> create table m as select * from all_objects where rownum <= 10; Table created. SQL> con a1/a1@db2
Connected.
SQL> copy from a1/a1@db1 to a1/a1@db2 -
> create m -
> using select * from m;
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table M created.
10 rows selected from a1@db1.
10 rows inserted into M.
10 rows committed into M at a1@db2.