asked on

Setting Path of Oracle so Dump file can be created on any location

Hi I am using 11G database, Right now for creating Dump file I have to go to Database bin folder(H:\app\Dell\product\11.2.0\dbhome_1\BIN) and through command prompt I have to go to that path then i am able to create expdp command but it generated dump file on different location((H:\app\Dell\admin\Raxa7\dpdump) ) what I want is without going to go into Bin folder I should be able to execute expdp command and create dump on same path from any location. Please help how I can do that. FYI I have installed 2 database in my machine.

ASKER CERTIFIED SOLUTION

Geert G

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sudhanshum

ASKER

Thanks for reply, I have few questions:
1 create or replace directory data_pump_dir as 'h:\app\dell\admin\Raxa7\dpdump';
above command I can execute through SQLPLUS?
2 and if you don't run that script with the exporting user, you need to supply grants too
Did not understand properly
3 create directory your_dir as 'h:\yourpath';
above command I can create through SQL Plus?

SOLUTION

Sujith

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geert G

those "create or replace dir.." are sql statements.
sql*plus is for executing sql, so yes, they run with that (or sql developer, toad, ... )

you need the required privileges to create an oracle database directory object

if you have the privs to create a dir in the database,
the statement always works, even if the directory does not physically exist on the oracle database host

the exporting user needs read/write priviliges to work on the directory being used for export
sample with userX and userY

connect userX/pw 
create directory test_dir as 'c:\temp';

expdp userY/pw directory=test_dir schemas=test dumpfile=test.dmp
> this will fail

Open in new window

solution:

connect userX/pw
grant read,write on directory test_dir to userY;

Open in new window

SOLUTION

Mark Geerlings

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial