Oracle : Common database connection errors (For junior DBAs)

## ORA-12514: TNS:listener does not currently know of service requested in connect descriptor ##
SQL> conn test/test@tdb1
ERROR:
ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

The connection request reaches listener but connection fails possibly due to below reasons.

Reason :

1) Service name used in connection description in tnsnames.ora or easy connect string does not exits.
2) Service is not running in database.
3) Service is not registered in listener.

## ORA-12545: Connect failed because target host or object does not exist ##

SQL> conn test/test@tdb1
ERROR:
ORA-12545: Connect failed because target host or object does not exist

Connection request does not reach the listener as host name where listener running is wrong.

Reason :

1) Host is wrong in tnsnames.ora file.
2) Host is not reachable from the machine that invokes client utility to connect to database.

## ORA-12541: TNS:no listener ##

SQL> conn test/test@tdb1
ERROR:
ORA-12541: TNS:no listener

Reason :

1) The listener port specified in tnsnames.ora is wrong.
2) The listener is not running on the host specified in tnsnames.ora

## ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit ##

SQL> conn test/test@tdb1
ERROR:
ORA-02391: exceeded simultaneous SESSIONS_PER_USER limit


Warning: You are no longer connected to ORACLE.

Reason :

1) The session limit specified in associated user profile exceeds SESSIONS_PER_USER.

Check the profile associated with the user connecting to database.

SQL> select username,profile from dba_users where username='TEST';

USERNAME                       PROFILE
------------------------------ ----------
TEST                           DEFAULT

SQL>

Check the profile limit SESSIONS_PER_USER

SQL> select profile,resource_name,limit from dba_profiles where resource_name='SESSIONS_PER_USER' and profile='DEFAULT';

PROFILE    RESOURCE_NAME                    LIMIT
---------- -------------------------------- ----------
DEFAULT    SESSIONS_PER_USER                2

## ORA-00020: maximum number of processes (33) exceeded ##

SQL> conn test/test
ERROR:
ORA-00020: maximum number of processes (33) exceeded


Reason :

1) The new process could not spawned as it reached the limit specified in processes parameter.



SQL> select * from v$resource_limit where resource_name='processes';

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                       32              33         33         33

SQL>

## ORA-01017: invalid username/password; logon denied ##

SQL> conn test/test1@tdb1
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.

The connection request reaches listener but it is rejected by database due to below reason.

Reason :

1) Either database username or password used for connection to database is not correct.

@@@@ - @@@@