Re: sqlplus connection from unix with and without the oracle sid

On Fri, May 16, 2008 at 6:39 AM, Rick Ricky <ricks12345_at_gmail.com> wrote:

> now oracle needs my oracle sid. I think my sysadmin changed something. I do
> not know what it is.
>
> when do you need to enter a SID when you log into oracle from a unix/linux
> shell and when don't you?
>

The following 2 variable have a lot to do with this.

ORACLE_SID
TWO_TASK Set the ORACLE_SID when you are logging onto a database on the local server.

SQL*Plus: Release 9.2.0.8.0 - Production on Fri May 16 11:11:29 2008

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

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

SQL>

If the database is on another server, you will need to include the ORACLE_SID
in the command line with SQLPLUS

This example is on the same server, but the principle is the same

$ sqlplus scott/tiger_at_dv10

SQL*Plus: Release 9.2.0.8.0 - Production on Fri May 16 11:12:27 2008

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

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

SQL>

Setting TWO_TASK overrides ORACLE_SID:

Here scott will logon to the hrdev server (DEV) rather than dv10, even though no ORACLE_SID was specified on the command line, and ORACLE_SID is set to dv10.

$ export TWO_TASK=hrdev
$ echo $ORACLE_SID
dv10
$ sqlplus scott/tiger

SQL*Plus: Release 9.2.0.8.0 - Production on Fri May 16 11:13:50 2008

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

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.6.0 - Production

SQL> select * from global_name;

GLOBAL_NAME

As to why you can no longer logon to the local database without specifying ORACLE_SID on the command line, there are many possibilities.

• the database is no longer on the server
• permissions where changed on the oracle executable
• something else?

Ask the DBA.

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

--
http://www.freelists.org/webpage/oracle-l