Re: Connecting to Oracle Instances - N00B

Shadow wrote:
> I'm trying to connect to a specific Oracle instance on my localhost.
> If I do:
>
> - sqlplus connect as system/passwd_at_instance as sysdba
>
> should that connect me?

No. This will:

sqlplus /nolog

<interesting version information here>

SQL> connect system/passwd_at_tns_alias

Connecting to SYSTEM as sysdba is incorrect; SYS is the sysdba account, and it should be used by those who know what they are doing. Also, why do you insist upon using SQL*Net for a local connection? If you haven't configured your tnsnames.ora file simply using the SID in your connect string, as you have illustrated, gets you nowhere. You should configure your environment (with UNIX/Linux provided as an example) as shown below:

$ export ORACLE_SID=<actual Oracle SID here>

Then the following command will connect you:

$ sqlplus system/password

<interesting version information here>

SQL> If you don't know your ORACLE_SID you can find it listed in /etc/oratab (on most UNIX; Sun does things a bit differently and locates the file in /var/opt/oracle). Failling that, you can do the following:

on Unix/Linux --

$ ps -ef | grep ora_ | grep -v grep

You should see a list of processes named ora_pppp_xxxx -where pppp is the process (smon, pmon, arch, etc.) and xxxx is your SID. As an example --

/export/home/oracle> ps -ef | grep ora_ | grep -v grep

  oracle 11607     1   0   Dec 26 ?           1:34 ora_dbw0_dex
  oracle 11611     1   0   Dec 26 ?           9:41 ora_ckpt_dex
  oracle 11609     1   0   Dec 26 ?           0:57 ora_lgwr_dex
  oracle 11605     1   0   Dec 26 ?          11:29 ora_pmon_dex
  oracle 11613     1   0   Dec 26 ?           0:45 ora_smon_dex
  oracle 11615     1   0   Dec 26 ?           0:01 ora_reco_dex
  oracle 11617     1   0   Dec 26 ?           3:08 ora_cjq0_dex

dex is the value for ORACLE_SID.

on Windows --

Look in Services for OracleServiceXXXX; XXXX is, again, your SID. Windows would have a service named OracleServiceDEX for this same SID.

Set your ORACLE_SID environment variable and stop using SQL*Net for local connections. And properly configure your tnsnames.ora file (using the Net Assistant) for remote access.

David Fitzjarrell Received on Wed Jan 04 2006 - 13:52:28 CST