Home » SQL & PL/SQL » SQL & PL/SQL » copy table from one server to another server
copy table from one server to another server [message #210917] Sat, 23 December 2006 04:28 Go to next message
mudalimuthu
Messages: 64
Registered: May 2005
Location: Bangalore
Member
i am trying to copy one table from one server to another server

sql :
copy from scott/tiger@oracle9i to scott/tiger@xe create new_emp using select * from emp;

i got the following error
ORA-00900: invalid SQL statement

wht would be the prob ?

regards muthu
Re: copy table from one server to another server [message #210921 is a reply to message #210917] Sat, 23 December 2006 05:36 Go to previous messageGo to next message
amul
Messages: 252
Registered: April 2001
Location: Chennai
Senior Member
what do you mean by one server to another?do you mean from one schema to another?you can specify the schema name in front...
Re: copy table from one server to another server [message #210930 is a reply to message #210917] Sat, 23 December 2006 08:31 Go to previous message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,
you can use database link.
or
import or export option.
or
copy command ( but The COPY command is likely to be made obsolete in a future release.)
----------------------------------------
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.

regards
Taj

[Updated on: Sat, 23 December 2006 08:32]

Report message to a moderator

Previous Topic: Plz provide sol. for this Sql Query
Next Topic: New to Oracle!
Goto Forum:
  


Current Time: Thu Dec 08 19:52:51 CST 2016

Total time taken to generate the page: 0.05366 seconds