?
Solved

Oracle Data Pump Password with special characters

Posted on 2011-05-04
13
Medium Priority
?
2,030 Views
Last Modified: 2012-05-11
Dear experts,

I am trying to use the datapump utility as sys in order to export some user schemas. However, I cannot authenticate, since my sys password contains special characters such as brackets. Can you tell me how to avoid expdp from misinterpreting my logon string? I tried to pass the password in quotes but that doesn't do the trick.

Here is an example of what my logon string looks like:
expdp sys/test(B) as sysdba schemas=test directory=data_pump_export dumpfile=test.dmp logfile=test.log

Open in new window


Brgds,

Freezer2010
0
Comment
Question by:skahlert2010
  • 9
  • 4
13 Comments
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35695811
whats the (B) stands for? if u are refering to SID then use @B
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35695814
or wrap it with doublequotes "test(b)"
0
 

Author Comment

by:skahlert2010
ID: 35695840
Hello OP_ZAHARIN! First of all thanks for answering so quickly!

The (B) is part of the pwd! I just tried to wrap it in doublequotes like before and it works to a certain extend. Now expdp fusses about the privilege "as sysdba" in my connection. When I remove the "as sysdba" I am asked to specify the login name again and finally get the chance to enter all credentials.

However it is not the desired behaviour I am expecting.

I want to start an export with:

expdp sys/"password"@myinstance as sysdba schemas=test directory=data_pump_export dumpfile=test.dmp logfile=test.log

Open in new window


Have you experienced similar problems?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35695865
hi skahlert2010,
- try single quote around it. expdp 'sys/"password"@myinstance as sysdba'
0
 

Author Comment

by:skahlert2010
ID: 35696001
I did and receive the following error message like before:

invalid positional parameter value 'sysdba'

It seems as if there's no other solution!
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35696002
- i can see that you specify a directory. make sure that you register the directory first and make sure the folder exists at the OS level:

SQL> CREATE DIRECTORY data_pump_export AS 'c:\temp\data_pump_export';
SQL> GRANT read,write ON DIRECTORY data_pump_export to <anyuser>;

- if you did not specify any directory in the expdp command, then the dump file will be created in the default directory known as DATA_PUMP_DIR. you can query the folder location in dba_directories:

SQL> SELECT * FROM dba_directories
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35696012
- i have test the syntax and it works perfectly fine . just a note, use doublequotes on your password only and singlequotes around the userid to sysdba as follows:

expdp 'sys/"test(B)" as sysdba' schemas=test directory=data_pump_export dumpfile=test.dmp logfile=test.log

Open in new window

0
 

Author Comment

by:skahlert2010
ID: 35696059
What if you try to clarify the instance that you want to connect to like

expdp 'sys/"test45(B)"@mydb as sysdba'

Does it still work for you? I just wonder why I am facing these difficulties while everything works for you. Apart from that my export works if I avoid adding the sysdba privilege. Then I am prompted for the user and password and my export starts! The directory is valid!

Sorry for the inconvenience but it seems as if there's no other solution in my environment. I am running 10.2.0.2
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35696078
- yes. i've tried with the instance name specified. i've also changed my sys password to test(B) to test the sysdba and the export runs ok. do not copy-paste from my syntax. type the single quotes and doublequotes manually.

expdp 'sys/"test(B)"@xe as sysdba' schemas=test directory=data_pump_export dumpfile=test.dmp logfile=test.log

Open in new window

0
 
LVL 23

Accepted Solution

by:
OP_Zaharin earned 2000 total points
ID: 35696132
- are you on windows or linux/unix platform? if you're on linux/unix, you need to use both doublequotes and singlequote together around userid and sysdba. i can't test the password though as i don't have linux/unix to test:

expdp "'sys/test(B)@xe as sysdba'"

Open in new window


OR
- this is not tested, you need to experiment it. do notice ' & " wrapping on overall and ' & " wrapping on the password:

expdp "'sys/'"test(B)'"@xe as sysdba'"

Open in new window

0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35696150
OR try this without the id and password:

on windows:
expdp '/ as sysdba' schemas=test directory=data_pump_export dumpfile=test.dmp logfile=test.log

Open in new window


on linux/unix:
expdp "/ as sysdba" schemas=test directory=data_pump_export dumpfile=test.dmp logfile=test.log

Open in new window

0
 

Author Comment

by:skahlert2010
ID: 35696307
Hello OP_Zaharin

I am on UNIX and wrapping the single quotes in double quotes did the trick!
Thanks for pointing me into the right direction! Your answer was perfectly right!

Best regards and have a nice day!

Freezer2010
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 35696312
- great freezer! have a nice day too :)
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question