Connecting to Oracle with Passwords with Spaces

Every once in a while I’ll be in the situation where two things intersect: I’ll want to log into the database using the command line directly (using sqlplus, sqlcl, expdp, rman, etc.) and someone has created a password with spaces in it for the user I want to connect as.

One might assume that you’d only have to escape the double quotes around the password (using \” ), but it turns out you actually need to provide an extra set of double quotes around the password when it has spaces in addition to the escaped double quotes.

[oracle@thecloud ~]$ sqlplus sys@orcl as sysdba
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:14:33 2019
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Enter password:
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
SYS@orcl AS SYSDBA> create user bob identified by "Password with spaces";
User created.
SYS@orcl AS SYSDBA> grant create session to bob;
Grant succeeded.
SYS@orcl AS SYSDBA> exit

[oracle@thecloud ~]$ sqlplus bob/\""Password with spaces\""@orcl 
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:18:26 2019
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle.  All rights reserved.
Connected to:
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production
Version 18.6.0.0.0
BOB@orcl > exit

If bob’s password was just Password and not “Password with spaces” then we’d be able to connect using either of the below:

[oracle@thecloud ~]$ sqlplus bob/Password@orcl
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:18:26 2019
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved. 
Connected to: 
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production 
Version 18.6.0.0.0
 BOB@orcl > exit

[oracle@thecloud ~]$ sqlplus bob/"Password"@orcl
SQL*Plus: Release 18.0.0.0.0 - Production on Sat Dec 14 06:18:26 2019 
Version 18.6.0.0.0
Copyright (c) 1982, 2018, Oracle. All rights reserved. 
Connected to: 
Oracle Database 18c EE Extreme Perf Release 18.0.0.0.0 - Production 
Version 18.6.0.0.0
BOB@orcl > exit

Once the password has spaces in it, you must include the ‘double double quotes’ and escape the first set of quotes as in the first example.