SYMPTOMS
You receive the following errors when trying to make new connections:
ORA-00604: error occurred at recursive SQL level 1
Cause: An error occurred while processing a recursive SQL statement.
(a statement applying to internal dictionary tables).
Action: If the situation described in the next message on the stack can be corrected, do so; otherwise, contact customer support.
ORA-00018: maximum number of sessions exceeded
Cause: An operation requested a resource that was unavailable.
The maximum number of sessions is specified by the initialization parameter SESSIONS.
When this maximum is reached, no more requests are processed.
Action: Try the operation again in a few minutes.
If this message occurs often, shut down Oracle, increase the SESSIONS parameter in the initialization parameter file, and restart Oracle.
NOTE: No new connections are permitted to the database.
However when querying v$resource_limit it shows this is not true.
SQL> alter system set sessions=<new value> scope=spfile;
SQL> shutdown immediate
SQL> startupNAME TYPE VALUE --------------------------------- -------- ------- java_max_sessionspace_size integer 0 java_soft_sessionspace_limit integer 0 license_max_sessions integer 0 license_sessions_warning integer 0 logmnr_max_persistent_sessions integer 1 session_cached_cursors integer 20 session_max_open_files integer 10 sessions integer 4096 shared_server_sessions integer 100
At the time of the error:
SQL> alter system set sessions=<new value> scope=spfile; SQL> shutdown immediate SQL> startup
ORA-18 MAXIMUM NUMBER OF SESSIONS EXCEEDED
CAUSE
Cause#1:
The maximum number of sessions in the database have been exceeded
Cause#2:
This issue has been addressed in the following bug that has been closed as not a bug:
Bug:11825730 - ORA-18 MAXIMUM NUMBER OF SESSIONS EXCEEDED
Development explained that the problem is due to shared server being enabled and the shared_server_sessions is set to a low value. Verified by the output from the event 18 trace file:
SQL> alter system set sessions=<new value> scope=spfile; SQL> shutdown immediate SQL> startup
SOLUTION
Solution#1:
Increase the value of the SESSIONS parameter in the parameter file.
If using spfile, then do the following:
SQL> alter system set sessions=<new value> scope=spfile;
SQL> shutdown immediate
SQL> startup
-or-
Increase the value of PROCESSES since SESSIONS is derived off of the PROCESSES parameter.
SESSIONS: derived (1.1 * PROCESSES) + 5 (versions prior to 11.2)
SESSIONS: derived (1.8 * PROCESSES) + 22 (11.2+)
Solution#2:
Limit the number of sessions per user using resource limit profiles or resource manager. Refer Limit Maxmimum Concurrent Sessions on Database Excluding Background Processes (Doc ID 452892.1)
Solution#3:
1. Increase the value for the init parameter shared_server_sessions
- OR -
2. Disable shared server
By default when shared server is configured, the connection made to the DB will be shared connections unless explicitly specified in the tnsnames.ora file that the service is using dedicated connections. Therefore if you do not intend to use shared server connections please disable shared server to allow for dedicated connections as follows. You disable shared server by setting SHARED_SERVERS to 0. No new client can connect in shared mode. However, when you set SHARED_SERVERS to 0, Oracle Database retains some shared servers until all shared server connections are closed. The number of shared servers retained is either the number specified by the preceding setting of
SHARED_SERVERS or the value of the MAX_SHARED_SERVERS parameter, whichever is smaller. If both SHARED_SERVERS and MAX_SHARED_SERVERS are set to 0, then all shared servers will terminate and requests from remaining shared server clients will be queued until the value of SHARED_SERVERS or MAX_SHARED_SERVERS is raised again.
After you disable shared server, all connections will be dedicated by default.
Solution#4:
Check if there is any user or application creating excessive sessions. Contact the application vendor in order to check why there are so many opened sessions (possibly sessions leak at the application level).
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.
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.
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.
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
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.
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;
WARNING: Oracle executable binary mismatch detected along with ORA-00600 [kqlmlktype:type] (Doc ID 2143111.1)
Issue reported in alert.log:
Binary of new process does not match binary which started instance
issue alter system set "_disable_image_check" = true to disable these messages
Along with-
Call stack:
kskirefreshattr kskthrun ksklogin ksk_use_rm_info kxfpProcessJoin kxfpProcessMsg kxfpqidqr kxfprdp.
CAUSE
The alert log clearly says that there is a mismatch between the binary that started the instance and the binary that was used to attach to the instance when the error happened.
The call stack is similar to unpublished Bug 20918604 - LNX64-121-CMT:HIT ORA-00600:[KQLMLKTYPE:NOT-KQLM-ID] DURING PATCH DATABASE. - status 33 - Suspended, Req'd Info not Avail
Another bug with similar symptoms is unpublished BUG 16093089 - OAM - ORA 600 [KQLMLKTYPE:TYPE - status 32 - Not a Bug.
SOLUTION
Whenever the binary is linked or changed, you need to make sure that all instances are shutdown.
So in this case, the following action plan would fix this problem:
- Shutdown the instance
- Ensure that all related processes have been killed
- Restart the instance
If the issue still persists even after applying above action plan, then it would be a good idea to relink Oracle binaries by referring below document-
Relinking Oracle Home FAQ ( Frequently Asked Questions) (Doc ID 1467060.1)
ause: String concatenation result is more than the maximum size.
Action: Make sure that the result is less than the maximum size.
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