Sometimes you need to copy table across databases/schemas. Oracle provides some utilities for them. We can summarize them as follow:
Trasportable Tablespaces
IMPD/EXPD utilities(Import/Export data pump)
IMP/EXP(Import/Export)
SQL*Loader
CTAS(Create Table As Select) with dblinks
SQL*Plus COPY command
In my opinion, the easiest way is using the least famous one, the last one: SQL*Plus COPY command
I demonstrate a simple example to show how to copy table u1 schema to hr schema:
SQL> copy from u1/u1@XE to hr/hr@XE create ttttt using select * from t;
Trasportable Tablespaces
IMPD/EXPD utilities(Import/Export data pump)
IMP/EXP(Import/Export)
SQL*Loader
CTAS(Create Table As Select) with dblinks
SQL*Plus COPY command
In my opinion, the easiest way is using the least famous one, the last one: SQL*Plus COPY command
I demonstrate a simple example to show how to copy table u1 schema to hr schema:
SQL> copy from u1/u1@XE to hr/hr@XE create ttttt using select * from t;
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 TTTTT created.
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table TTTTT created.
9 rows selected from u1@XE.
9 rows inserted into TTTTT.
9 rows committed into TTTTT at hr@XE.
9 rows inserted into TTTTT.
9 rows committed into TTTTT at hr@XE.
SQL> conn hr/hr@XE
Connected.
SQL> select * from ttttt;
Connected.
SQL> select * from ttttt;
I
----------
1
2
3
4
5
6
7
8
9
----------
1
2
3
4
5
6
7
8
9
9 rows selected.
SQL>
No comments:
Post a Comment