Home » RDBMS Server » Server Utilities » EXPORT and IMPORT from client machine
EXPORT and IMPORT from client machine [message #567932] Mon, 08 October 2012 07:08 Go to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello experts,
Is it also possible to use the EXPORT and IMPORT utilities from a client machine?
I want to give these utilities to one of my developers
without allowing him to sit in front of my Oracle server.

i am using oracle 11.2.0.1 and windows7 OS

thanks in advance.......

Re: EXPORT and IMPORT from client machine [message #567933 is a reply to message #567932] Mon, 08 October 2012 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it is possible.

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #567939 is a reply to message #567933] Mon, 08 October 2012 07:24 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,

there are 2 servers dev and prod , i want to export some tables from dev to prod database,
from the client machine(my local machine) and i can access the both of the database.

if i use:-

c:>Expdp devuser/password@db_name tables=test1,test2,test3 dumpfile=mydump.dmp

and c:>impdp produser/password@db_name tables=devuser.test1,devuser.test2, devuser.test3
dumpfile=mydump.dmp remap_schema=devuser:produser content=data_only

thses are using default data_pump_dir directory.

then what changes should i do ,so that both exp/imp dumpfiles will be saved on my local machine not on server.


thanks again.....
Re: EXPORT and IMPORT from client machine [message #567945 is a reply to message #567939] Mon, 08 October 2012 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use old export/import tools.
Data Pump works on the server.

You can also use Data Pump import across a db link, so that you have no file on servers and so can do it from a client.

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #567947 is a reply to message #567945] Mon, 08 October 2012 07:48 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
means sir, we can not do exp/imp with datapump utility.

thanks.....



Re: EXPORT and IMPORT from client machine [message #567951 is a reply to message #567947] Mon, 08 October 2012 07:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand what you say, can you use more words?

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #567955 is a reply to message #567951] Mon, 08 October 2012 08:27 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
means sir,

in the expdp/impdp(exp/imp thrpugh the datapump utility) command we give all the parameter at a time.

like:-

Expdp devuser/password@db_name tables=test1,test2,test3 dumpfile=mydump.dmp

impdp produser/password@db_name tables=devuser.test1,devuser.test2, devuser.test3
dumpfile=mydump.dmp remap_schema=devuser:produser content=data_only


but in old utility we have to manually give the value for each parameter lik:-

Microsoft Windows [Version 6.1.7600]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

C:\>exp

Export: Release 11.2.0.1.0 - Production on Mon Oct 8 18:37:49 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


Username: scott/tiger@testdb

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Enter array fetch buffer size: 4096 >

Export file: EXPDAT.DMP > sc

(1)E(ntire database), (2)U(sers), or (3)T(ables): (2)U > t

Export table data (yes/no): yes >

Compress extents (yes/no): yes >

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > emp

. . exporting table                            EMP          6 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >

Export terminated successfully without warnings.

C:\>


in 1st exp/imp command(expdp/impdp) can we EXPORT and IMPORT from a client machine or not?

thanks.....
Re: EXPORT and IMPORT from client machine [message #567957 is a reply to message #567955] Mon, 08 October 2012 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
but in old utility we have to manually give the value for each parameter lik:-


You can put all the parameters on the command line or in a parameter file; no difference with data pump for this.

Quote:
in 1st exp/imp command(expdp/impdp) can we EXPORT and IMPORT from a client machine or not?


Does the question on exp/imp or on expdp/impdp? This is not clear.
Anyway, I already answered for both.

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #567958 is a reply to message #567957] Mon, 08 October 2012 09:05 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
ya sir, you are absolutely right that we can put all the parameters at a time in in old utility command.
and the dumpfile can be saved on client machine, even if database is on another machine.
my problem is resolved , thanks a lot sir.

my requirement was that i need to exp some tables from one server and import to the another server database ,
without login into the any server, means exp and imp both process should be accomplished on my machine(as client machine).
and also all the parameters values should be given in one go ,
but you told that dumpfile generated by expdp(new utility of exp/imp) get saved in the
directory of host machine(on server), am i right or not sir?

if yes,then i have to go for old exp/imp utility.



and sir one more question :-

if i need to exp/imp by sys then what should be the right place for 'as sysdba'.

like-

C:\exp sys/password@devdb as sysdba path='d:/dm.dmp' tables=devuser.test,devuser.test2
LRM-00108: invalid positional parameter value 'as'

EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
EXP-00000: Export terminated unsuccessfully


it gives the error.

thanks.....
Re: EXPORT and IMPORT from client machine [message #567968 is a reply to message #567958] Mon, 08 October 2012 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
you told that dumpfile generated by expdp(new utility of exp/imp) get saved in the
directory of host machine(on server), am i right or not sir?


Yes, and I also told you (in another way) that you can't have NO dumpfile in any server using NETWORK_LINK parameter.

Quote:
if i need to exp/imp by sys


Why do you need SYS?

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #568000 is a reply to message #567968] Tue, 09 October 2012 01:22 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
i need to export schema

exp sys/password@devdb as sysdba path='d:/dm.dmp' schemas=devuser

imp sys/password@proddb as sysdba path='d:/dm.dmp' schemas=devuser remap_schema=devuser:produser

thanks.....
Re: EXPORT and IMPORT from client machine [message #568002 is a reply to message #568000] Tue, 09 October 2012 01:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need SYS to export a schema (unless there are some vpd policies...), use SYSTEM or better a DBA account you defined.

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #568109 is a reply to message #567932] Tue, 09 October 2012 09:02 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
ok. thanks a lot sir
Re: EXPORT and IMPORT from client machine [message #568111 is a reply to message #567932] Tue, 09 October 2012 09:44 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
hello sir,

how will we export/import only tables of a users (who has hundreds of tables)not proc,func,triggers etc
by the exp/imp without expdp/impdp?

thanks.....
Re: EXPORT and IMPORT from client machine [message #568114 is a reply to message #568111] Tue, 09 October 2012 10:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to list them.
Why don't you want to use data pump?

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #568119 is a reply to message #568114] Tue, 09 October 2012 10:11 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,

Michel Cadot wrote on Tue, 09 October 2012 10:00
You have to list them.


is there any way to generate tables name dynamically so that exp/imp command will be made
as the table exp/imp command?

Quote:

Why don't you want to use data pump?

Regards
Michel


because it is requirement to exp/imp both should be done from the 3rd machine.


thanks....
Re: EXPORT and IMPORT from client machine [message #568124 is a reply to message #568119] Tue, 09 October 2012 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
is there any way to generate tables name dynamically so that exp/imp command will be made
as the table exp/imp command?


Yes, query DBA_TABLES.

Quote:
because it is requirement to exp/imp both should be done from the 3rd machine.


And you dindn't understand what I explained you how to do it across a db link.

Regards
Michel
Re: EXPORT and IMPORT from client machine [message #568166 is a reply to message #568124] Wed, 10 October 2012 01:18 Go to previous messageGo to next message
neetesh87
Messages: 280
Registered: September 2011
Location: bhopal
Senior Member
thanks sir,
[quote title=Michel Cadot wrote on Tue, 09 October 2012 10:50]Quote:


And you dindn't understand what I explained you how to do it across a db link.

Regards
Michel



yes,i know that we can do it through the database link,but there is some long column in the tables,
and long columns can not be export/imported by using DB link as per my knowledge.


thanks....
Re: EXPORT and IMPORT from client machine [message #568173 is a reply to message #568166] Wed, 10 October 2012 01:40 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Try it and tell us.

Regards
Michel
Previous Topic: issue in export/import via datapump
Next Topic: EXP-00011: SCOTT.BONUS does not exist
Goto Forum:
  


Current Time: Thu Mar 28 09:39:33 CDT 2024