Oracle user's password contains special characters, how to deal with cases of "/" How to export data?

May 17 posted in Oracle (RSS), 505 views.

Author: olivenan

MSN: olivenan@hotmail.com

Usually we set Oracle password password will be set up more complex and special symbols are generally composed of alphanumeric

However, there are special symbols, modify your password, or the import and export data will be in trouble, the following is an explanation of the circumstances:

In this paper, a special symbol "/" as an example

1, Linux environment, the database is Oracle10g

1, modify your password

[oracle @ olivenan oracle] $ sqlplus '/ as sysdba'

SQL * Plus: Release 10.2.0.1.0 - Production on Wed May 17 14:33:32 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> alter user test identified by aa / aa;

alter user test identified by aa / aa
*
ERROR at line 1:
ORA-00922: missing or invalid option

Here should be used ""
SQL> alter user test identified by "aa / aa";

User altered.

SQL>

[oracle @ olivenan oracle] $ exp test / aa / aa file = test.dmp buffer = 65536

Export: Release 10.2.0.1.0 - Production on Wed May 17 14:32:10 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

EXP-00004: invalid username or password
Username:

Here should be escaped using the "" dealing with passwords, use the '' handle user name and password

[oracle @ olivenan oracle] $ exp 'test / "aa / aa"' file = test.dmp buffer = 65536

Export: Release 10.2.0.1.0 - Production on Wed May 17 14:32:52 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. Exporting pre-schema procedural objects and actions

...............

Export success, here omitted

2, Windows Xp, the database for Oracle9i

C: \ Documents and Settings \ w> sqlplus / nolog

SQL * Plus: Release 9.2.0.1.0 - Production on Wed May 17 14:56:34 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL> connect sys / olivenan as sysdba
Connected.
SQL> alter user test identified by aa / aa;
alter user test identified by aa / aa
*
ERROR at line 1:
ORA-00922: missing or invalid option

Used here ""
SQL> alter user test identified by "aa / aa";

User altered.

SQL>

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C: \ Documents and Settings \ w> exp test / aa / aa file = aa.dmp buffer = 65536

Export: Release 9.2.0.1.0 - Production on Wed May 17 14:58:50 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

EXP-00004: invalid username or password
Username:
Password:
EXP-00056: ORACLE error 1017 encountered
ORA-01017: invalid username / password; logon denied
Username: ^ Z ^ Z

EXP-00030: Unexpected End-Of-File encountered while reading input
EXP-00000: Export terminated unsuccessfully

Used here "" "" "" to handle user passwords

C: \ Documents and Settings \ w> exp test / "" "aa / aa" "" file = aa.dmp buffer = 65536

Export: Release 9.2.0.1.0 - Production on Wed May 17 14:59:10 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)

About to export specified users ...
. Exporting pre-schema procedural objects and actions
. Exporting foreign function library names for user test
. Exporting PUBLIC type synonyms
. Exporting private type synonyms
. Exporting object type definitions for user test
About to export test's objects ...
. Exporting database links
. Exporting sequence numbers
........

Export success, here omitted

If you need reproduced requested to keep their address, thank you.