This was tested on Oracle 11.1.0.6.0 on Windows XP. You can see if your database is open or closed by querying open_mode from v$database:
C:\>sqlplus / as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Sat Sep 15 23:18:58 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to an idle instance.
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01034: ORACLE not available
Process ID: 0
Session ID: 0 Serial number: 0
SQL> startup nomount
ORACLE instance started.
Total System Global Area 535678976 bytes
Fixed Size 1334320 bytes
Variable Size 201327568 bytes
Database Buffers 327155712 bytes
Redo Buffers 5861376 bytes
SQL> select open_mode from v$database;
select open_mode from v$database
*
ERROR at line 1:
ORA-01507: database not mounted
SQL> alter database mount
2 /
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
MOUNTED
SQL> alter database open;
Database altered.
SQL> select open_mode from v$database;
OPEN_MODE
----------
READ WRITE
SQL>
I will look at what all these open_mode values mean in another post.
Showing posts with label sqlplus / as sysdba. Show all posts
Showing posts with label sqlplus / as sysdba. Show all posts
Saturday, September 15, 2012
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>
Subscribe to:
Posts (Atom)