Monday, February 07, 2011

sqlplus / as sysdba


In Oracle 9, you could not connect to a database using sqlplus / as sysdba:

TEST9> sqlplus / as sysdba
Usage: SQLPLUS [ [<option>] [<logon>] [<start>] ]
where <option> ::= -H | -V | [ [-L] [-M <o>] [-R <n>] [-S] ]
      <logon>  ::= <username>[/<password>][@<connect_string>] | / | /NOLOG
      <start>  ::= @<URI>|<filename>[.<ext>] [<parameter> ...]
        "-H" displays the SQL*Plus version banner and usage syntax
        "-V" displays the SQL*Plus version banner
        "-L" attempts log on just once
        "-M <o>" uses HTML markup options <o>
        "-R <n>" uses restricted mode <n>
        "-S" uses silent mode
TEST9>

I guess this was because there were spaces in the connect string but I’m not sure. A connect string without spaces did not cause a problem:

TEST9> sqlplus andrew/reid

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 7 17:10:25 2011

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL>

There were at least 3 ways round this. You could surround the connect string with single quotes:

TEST9> sqlplus '/ as sysdba'

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 7 17:12:39 2011

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL>

You could surround the connect string with double quotes:

TEST9> sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 7 17:16:05 2011

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production

SQL>

Or you could use sqlplus /nolog then make the connection within SQL*Plus:

TEST9> sqlplus /nolog

SQL*Plus: Release 9.2.0.7.0 - Production on Mon Feb 7 17:20:01 2011

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

SQL> conn / as sysdba
Connected.
SQL>

In Oracle 10, the problem disappeared:

TEST10> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Mon Feb 7 17:23:57 2011

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

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

SQL>

No comments: