Andrew's Oracle Blog: sqlplus /nolog

Showing posts with label sqlplus /nolog. Show all posts

Friday, February 11, 2011

CONN / AS SYSOPER in Windows XP

I was running Oracle on Windows XP and found I could not do CONN / AS SYSOPER:

C:\>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Feb 11 23:44:21 2011

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

SQL> conn / as sysoper
ERROR:
ORA-01031: insufficient privileges

SQL>

I went to the Oracle 10 client software under All Programs and found the Administration Assistant for Windows under Configuration and Migration Tools (click to enlarge it and bring it into focus):

I expanded it out as shown below and highlighted OS Database Operators - Computer:

I clicked on Action then Add/Remove:

This displayed the screen below:

I clicked the pull down arrow to the right of the Domain: box and selected the only choice displayed. This populated the Name / Description box:

I wanted to CONN / AS SYSOPER from user Andrew so I highlighted it and then I could click on the Add button:

Which added Andrew to the OS Database Operators - Computer box at the bottom:

So I clicked OK then I was able to do CONN / AS SYSOPER. When you do this, you log on as the PUBLIC user:

C:\>sqlplus / as sysoper

SQL*Plus: Release 10.2.0.1.0 - Production on Sat Feb 12 19:34:57 2011

Connected to an idle instance.

SQL> show user
USER is "PUBLIC"
SQL>

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

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

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

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

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

SQL>

sqlplus /nolog

This allows you to start a SQL*Plus session without connecting to a database:

TEST11>sqlplus /nolog

SQL*Plus: Release 11.1.0.6.0 - Production on Mon Feb 7 14:01:41 2011

SQL> SELECT SYS_CONTEXT('USERENV','DB_NAME') DATABASE

2 FROM DUAL;

SP2-0640: Not connected

SQL> SHOW USER

USER is ""

SQL>