Recently I need to do some task base on the Production data and I don’t want any accident happen to my production database so I decided to copy the production tables I need over to the UAT database and do the ETL. I got 2 option to achieve this:

1. Create dblink to another database and copy the tables and data over.
2. Use the sql*plus copy command

Since this is only a one time task so I opted to use option 2.  The syntax is as below:

COPY FROM scott/tiger@db_p-
CREATE new_emp USING SELECT * FROM emp;

OR

COPY TO scott/tiger@db_u-
CREATE new_emp USING SELECT * FROM emp;

depends on which server you login. You can also create the table in your target database first and just replace the CREATE command with INSERT command and it will still work.  At first I try to run these command in the sqlplus gui and I got all kinds of error:

ERROR at line 1:
ORA-00900: invalid SQL statement

ERROR:
ORA-00928: missing SELECT keyword

ERROR:
ORA-00904: : invalid identifier

Then I realize these are all cause by the GUI sqlplus, can you believe it? Then I just launch my sqlplus in command promt, then another error pops up:

CPY0006: Select list has more columns than destination table

Then I discover it is caused by my sqlplus is v8.1.7 and both of my oracle database are 10g. So I got no choice but to use the 10g client  which have sqlplus v10.2 and run the command and whoala tables copy successfully.

Bookmark It

Hide Sites