ORA-ERRORS

ORA-00020 maximum number of processes exceeded 

ORA-00018 maximum number of sessions exceeded
Cause: All session state objects are in use.
Action: Increase the value of the SESSIONS initialization parameter.

Reference: Oracle Documentation

ORA-00018 comes under "Oracle Database Server Messages". These messages are generated 
by the Oracle database server when running any Oracle program.

How to increase SESSION initialization parameter:

1. Login as sysdba
 sqlplus / as sysdba
 
2. Check Current Setting of Parameters
 sql> show parameter sessions
 sql> show parameter processes
 sql> show parameter transactions

3. If you are planning to increase "sessions" parameter you should also plan to increase 
"processes and "transactions" parameters.
A basic formula for determining  these parameter values is as follows:
  processes=x
  sessions=x*1.1+5
  transactions=sessions*1.1
  
4. These paramters can't be modified in memory. You have to modify the spfile only 
(scope=spfile) and bounce the instance.
 sql> alter system set processes=500 scope=spfile;
 sql> alter system set sessions=555 scope=spfile;
 sql> alter system set transactions=610 scope=spfile;
 sql> shutdown abort
 sql> startup 

ORA-00054: resource busy and acquire with NOWAIT specified

 ORA-00054: resource busy and acquire with NOWAIT specified
Cause: The NOWAIT keyword forced a return to the command prompt
because a resource was unavailable for a LOCK TABLE or SELECT FOR
UPDATE command.
Action: Try the command after a few minutes or enter the command without
the NOWAIT keyword.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e0.htm

Example:
SQL> alter table emp add (mobile varchar2(15));
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified


How to avoid the ORA-00054:
    - Execute DDL at off-peak hours, when database is idle.
    - Execute DDL in maintenance window.
    - Find and Kill the session that is preventing the exclusive lock.


Other Solutions:

Solution 1:
In Oracle 11g you can set ddl_lock_timeout i.e. allow DDL to wait for the object to 
become available, simply specify how long you would like it to wait:
 
SQL> alter session set ddl_lock_timeout = 600;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered. 


Solution 2:
Also In 11g, you can mark your table as read-only to prevent DML:
SQL> alter table emp read only;
Session altered.

SQL> alter table emp add (mobile varchar2(15));
Table altered.


Solution 3 (for 10g):
DECLARE
 MYSQL VARCHAR2(250) := 'alter table emp add (mobile varchar2(15))';
 IN_USE_EXCEPTION EXCEPTION;
 PRAGMA EXCEPTION_INIT(IN_USE_EXCEPTION, -54);
BEGIN
 WHILE TRUE LOOP
  BEGIN
   EXECUTE IMMEDIATE MYSQL;
   EXIT;
  EXCEPTION
   WHEN IN_USE_EXCEPTION THEN 
    NULL;
  END;
  DBMS_LOCK.SLEEP(1);
 END LOOP;
END;


Solution 4: 

Step 1: Identify the session which is locking the object
select a.sid, a.serial#
from v$session a, v$locked_object b, dba_objects c 
where b.object_id = c.object_id 
and a.sid = b.session_id
and OBJECT_NAME='EMP';

Step 2: kill that session using
alter system kill session 'sid,serial#'; 

ORA-00257 archiver error. Connect internal only, until freed

 

ORA-00257 archiver error. Connect internal only, until freed.

Cause: The archiver process received an error while trying to archive a redolog. 
If the problem is not resolved soon, the database will stop executing transactions. 
The most likely cause of this message is the destination device is out of space 
to store the redo log file.

Action: Check the archiver trace file for a detailed description of the problem.
Also, verify that the device specified in the initialization parameter ARCHIVE_LOG_DEST 
is set up properly for archiving.

ORA-00257 comes under "Oracle Database Server Messages". 
These messages are generated by the Oracle database server when running any Oracle program.
How to free archive destination device:
Most probably archive destination is full. We should backup archive logs, then remove them.

We can use following steps for this
1. find the location of Archive destination by
 show parameter archive_dest
 
 lets say it provide  LOCATION=/u10/oradata/mydb/arch

2. move some files to some other location using os command
 cd /u10/oradata/mydb/arch
 mv /u10/oradata/mydb/arch/* /u15/oradata/mydb/arch-bkp/
 

Or it can be done using RMAN also

rman target /

RMAN> backup archive log all format '/u15/oradata/mydb/arch-bkp';

RMAN> delete archive until time 'trunc(sysdate)';

ORA-00027 cannot kill current session

ORA-00027 cannot kill current session
Cause: An attempt was made to use ALTER SYSTEM KILL SESSION to kill the
current session.
Action: If it is necessary to kill the current session, do so from another session.

ORA-00027 comes under "Oracle Database Server Messages". 

These messages are generated by the Oracle database server when running any Oracle program.

How to kill session:
1. identify which session to kill using following query:
 select 
  s.sid,
  s.serial#,
  spid,
  trim(s.machine) machine,
  trim(s.module) module,
  status
 from
  v$session s,
  v$process p
 where
  paddr=addr
  and module is not null
 order by 1,2
 
2. Killing a session
 ALTER SYSTEM KILL SESSION 'sid,serial#';
 
 or you can kill at OS level on linux using
 kill -9 spid

Oracle: Stopping Query without killing the session

  There is an Oracle event, 10237, which is described as "simulate ^C (for testing purposes)".
You should have the SID and SERIAL# of the session you want to interrupt.
Call SYS.DBMS_SYSTEM.SET_EV( sid, serial#, 10237, 1, '' ) to activate the event in the target session. Any currently executing statement should be interrupted (receiving "ORA-01013: user requested cancel of current operation").
As long as the event is set, any further statements the session attempts to execute will immediately terminate with the same error.
To deactivate the event, make the same call with the fourth parameter set to "0". The session will then be able to execute statements again.
Here's some sample code. This is meant to be run as an anonymous block in SQLPlus, with substitution variables "sid" and "serial" defined appropriately. You could turn it into a stored procedure with those as its parameters.

exec sys.dbms_system.set_ev(&sid, &serial, 10237, 1, '');
exec sys.dbms_system.set_ev(&sid, &serial, 10237, 0, '');

NOTE: Your user must have permission to execute it and it will be granted  by SYSDBA as
grant execute on  dbms_system to my_user;

 ORA-01489: result of string concatenation is too long

ORA-01489: result of string concatenation is too long
Cause: String concatenation result is more than the maximum size.
Action: Make sure that the result is less than the maximum size.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14219/e900.htm


Example:
SQL> SELECT LPAD('x',4000,'x') || LPAD('x',4000,'x')  || LPAD('x',4000,'x') FROM DUAL;
SELECT LPAD('x',4000,'x') || LPAD('x',4000,'x')  || LPAD('x',4000,'x') FROM DUAL
                                                                                                   *
ERROR at line 1:
ORA-01489: result of string concatenation is too long


Problem Description:
The problem with this query is with the use of CONCAT operator (||).

e.g.: select char1 || char2 from dual
Concat operator returns char1 concatenated with char2. The string returned is in the 
same character set as char1. So here concat operator is trying to return varchar2, 
which has limit of 4000 characters and getting exceeded.

This problem may also come when we try to CONCAT a VARCHAR2 with CLOB.
e.g.: select char1 || clob from dual

So here we can simply convert its first string to CLOB and avoid this error.
After converting first string to CLOB, CONCAT operator will return string of CLOB type


Solution:
SELECT TO_CLOB(LPAD('x',4000,'x')) || LPAD('x',4000,'x')  || LPAD('x',4000,'x') 
FROM DUAL