Re: copy tables from one oracle server to another one

On Oct 3, 10:55 am, kulkarni.ni..._at_gmail.com wrote:
> Hello,
>
> We're basically in a situation where we want to copy some tables from
> one oracle database in to another oracle database.Both of these oracle
> databases sits on different server and have same platform windows
> 2003. I'm basically new to oracle world so I don't know much about
> export/import utilities. Oracle environment is 9.2.0.0 on both the
> server.
>
> Thanks,

Depending upon the data types in these tables the COPY command is still available (old as it may be) as well as the Create Table ... As Select ... syntax (this would require a database link). Yes, there's always the tried and true exp/imp scenario to copy tables into a different database; for ease of use to the newbie I would ensure the same tablespaces and 'owners' (user accounts) exist on both servers (yes, it is possible to override that and put the tables into the default tablespace of the new 'owner', but I think it would be easier on the uninitiated to create, or have created, the same tablespaces on both servers). Using exp/imp is fairly straightforward:

exp user/pass file=<somefilenamehere> tables=<some comma-separated list of tables here> compress=n statistics=none

If the tables are not owned by the user account supplied to the exp utility you'll need to qualify the names with the 'owner', for example:

exp bing/bong file=waldo_tbls.dmp tables=waldo.mytab,waldo.myothertab compress=n statistics=none

I would suggest you either have a DBA perform this export/import scenario, or that you use the table 'owner' as the connection information to exp and imp:

exp waldo/bob file=my_tbls.dmp tables mytab,myothertab compress=n statistics=none

Doing this the imp is fairly simple (again, provided the 'waldo' user and the necessary tablespaces are created):

imp waldo/bob file=my_tbls.dmp full=y ignore=y

This should create and populate the desired tables in the desired database.

All of this information, and much more, is available here:

http://tahiti.oracle.com

or

http://www.oracle.com/pls/db92/homepage?remark=tahiti

David Fitzjarrell Received on Wed Oct 03 2007 - 11:31:26 CDT