ORA-00000: normal, successful completion
Cause: Normal exit.
Action: None.
________________________
ORA-00001 unique constraint violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
Action: Either remove the unique restriction or do not insert the key.
ORA-00001 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.
________________________
ORA-00017: session requested to set trace event
Cause: The current session was requested to set a trace event by another session.
Action: This is used internally; no action is required.
________________________
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-00019: maximum number of session licenses exceeded
Cause: All licenses are in use.
Action: Increase the value of the LICENSE MAX SESSIONS initialization parameter.
________________________
ORA-00020 maximum number of processes exceeded
Cause: All process state objects are in use.
Action: Increase the value of the PROCESSES initialization parameter.
ORA-00020 comes under "Oracle Database Server Messages". These messages are generated by the Oracle database server when running any Oracle program.
Reference: Oracle Documentation
How to increase PROCESSES 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 "PROCESSES" parameter you should also plan to increase "sessions 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-00021: session attached to some other process; cannot switch session
Cause: The user session is currently used by others.
Action: Do not switch to a session attached to some other process.
________________________
ORA-00022: invalid session ID; access denied
Cause: Either the session specified does not exist or the caller does not have the privilege to access it.
Action: Specify a valid session ID that you have privilege to access, that is either you own it or you have the CHANGE_USER privilege.
________________________
ORA-00023: session references process private memory; cannot detach session
Cause: An attempt was made to detach the current session when it contains references to process private memory.
Action: A session may contain references to process memory (PGA) if it has an open network connection, a very large context area, or operating system privileges. To allow the detach, it may be necessary to close the session"s database links and/or cursors. Detaching a session with operating system privileges is always disallowed.
________________________
ORA-00024: logins from more than one process not allowed in single-process mode
Cause: Trying to login more than once from different processes for ORACLE started in single-process mode.
Action: Logoff from the other process.
________________________
ORA-00025: failed to allocate string
Cause: Out of memory.
Action: Restart with larger sga heap.
________________________
ORA-00026: missing or invalid session ID
Cause: Missing or invalid session ID string for ALTER SYSTEM KILL SESSION.
Action: Retry with a valid session ID.
________________________
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
________________________
ORA-00028: your session has been killed
Cause: A privileged user has killed your session and you are no longer logged on to the database.
Action: Login again if you wish to continue working.
________________________
ORA-00029: session is not a user session
Cause: The session ID specified in an ALTER SYSTEM KILL SESSION command was not a user session (for example, recursive, etc.).
Action: Retry with a user session ID.
________________________
ORA-00030: User session ID does not exist.
Cause: The user session ID no longer exists, probably because the session was logged out.
Action: Use a valid session ID.
________________________
ORA-00031 To ORA-00061 Errors
ORA-00031: session marked for kill
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptable operation is done.
Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.
ORA-00032: invalid session migration password
Cause: The session migration password specified in a session creation call was invalid (probably too long).
Action: Retry with a valid password (less than 30 chars).
ORA-00033: current session has empty migration password
Cause: An attempt was made to detach or clone the current session and it has an empty migration password. This is not allowed.
Action: Create the session with a non-empty migration password.
ORA-00034: cannot string in current PL/SQL session
Cause: An attempt was made to issue a commit or rollback from a PL/SQL object (procedure, function, package) in a session that has this disabled (by "alter session disable commit in procedure")
Action: Enable commits from PL/SQL in this session, or do not attempt to use commit or rollback in PL/SQL when they are disabled in the current session.
ORA-00036: maximum number of recursive SQL levels (string) exceeded
Cause: An attempt was made to go more than the specified number of recursive SQL levels.
Action: Remove the recursive SQL, possibly a recursive trigger.
ORA-00037: cannot switch to a session belonging to a different server group
Cause: An attempt was made to switch to a session in a different server group. This is not allowed.
Action: Make sure the server switches to a session that belongs to its server group.
ORA-00038: Cannot create session: server group belongs to another user
Cause: An attempt was made to create a non-migratable session in a server group that is owned by a different user.
Action: A server group is owned by the first user who logs into a server in the server group in non-migratable mode. All subsequent non-migratable mode logins must be made by the user who owns the server group. To have a different user login in non-migratable mode, the ownership of the server group will have to be changed. This can be done by logging off all current sessions and detaching from all existing servers in the server group and then having the new user login to become the new owner.
ORA-00040: active time limit exceeded - call aborted
Cause: The Resource Manager SWITCH_TIME limit was exceeded.
Action: Reduce the complexity of the update or query, or contact your database administrator for more information.
ORA-00041: active time limit exceeded - session terminated
Cause: The Resource Manager SWITCH_TIME limit was exceeded.
Action: Reduce the complexity of the update or query, or contact your database administrator for more information.
ORA-00042: Unknown Service name string
Cause: An attempt was made to use an invalid application service.
Action: Use a valid service name from SERVICE$ or add a new service using the DBMS_SERVICE package.
ORA-00050: operating system error occurred while obtaining an enqueue
Cause: Could not obtain the operating system resources necessary to cover an oracle enqueue. This is normally the result of an operating system user quota that is too low.
Action: Look up the operating system error in your system documentation and perform the needed action.
ORA-00051: timeout occurred while waiting for a resource
Cause: Usually due to a dead instance.
Action: Check for any dead, unrecovered instances and recover them.
ORA-00052: maximum number of enqueue resources (string) exceeded
Cause: Ran out of enqueue resources.
Action: Increase the value of the ENQUEUE_RESOURCES initialization parameter.
ORA-00053: maximum number of enqueues exceeded
Cause: Ran out of enqueue state objects.
Action: Increase the value of the ENQUEUES initialization parameter.
__________________________________
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-00055: maximum number of DML locks exceeded
Cause: Ran out of DML lock state objects.
Action: Increase the value of the DML_LOCKS initialization parameter and warm start.
ORA-00056: DDL lock on object "string.string" is already held in an incompatible mode
Cause: An attempt was made to acquire a DDL lock that is already locked.
Action: This happens if you attempt to drop a table that has parse locks on it.
ORA-00057: maximum number of temporary table locks exceeded
Cause: The number of temporary tables equals or exceeds the number of temporary table locks. Temporary tables are often created by large sorts.
Action: Increase the value of the TEMPORARY_TABLE_LOCKS initialization parameter and warm start.
ORA-00058: DB_BLOCK_SIZE must be string to mount this database (not string)
Cause: DB_BLOCK_SIZE initialization parameter is wrong for the database being mounted. It does not match the value used to create the database.
Action: Fix the value of the DB_BLOCK_SIZE parameter or mount a database that matches the value.
ORA-00059: maximum number of DB_FILES exceeded
Cause: The value of the DB_FILES initialization parameter was exceeded.
Action: Increase the value of the DB_FILES parameter and warm start.
ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptable operation is done.
Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.
ORA-00032: invalid session migration password
Cause: The session migration password specified in a session creation call was invalid (probably too long).
Action: Retry with a valid password (less than 30 chars).
ORA-00033: current session has empty migration password
Cause: An attempt was made to detach or clone the current session and it has an empty migration password. This is not allowed.
Action: Create the session with a non-empty migration password.
ORA-00034: cannot string in current PL/SQL session
Cause: An attempt was made to issue a commit or rollback from a PL/SQL object (procedure, function, package) in a session that has this disabled (by "alter session disable commit in procedure")
Action: Enable commits from PL/SQL in this session, or do not attempt to use commit or rollback in PL/SQL when they are disabled in the current session.
ORA-00036: maximum number of recursive SQL levels (string) exceeded
Cause: An attempt was made to go more than the specified number of recursive SQL levels.
Action: Remove the recursive SQL, possibly a recursive trigger.
ORA-00037: cannot switch to a session belonging to a different server group
Cause: An attempt was made to switch to a session in a different server group. This is not allowed.
Action: Make sure the server switches to a session that belongs to its server group.
ORA-00038: Cannot create session: server group belongs to another user
Cause: An attempt was made to create a non-migratable session in a server group that is owned by a different user.
Action: A server group is owned by the first user who logs into a server in the server group in non-migratable mode. All subsequent non-migratable mode logins must be made by the user who owns the server group. To have a different user login in non-migratable mode, the ownership of the server group will have to be changed. This can be done by logging off all current sessions and detaching from all existing servers in the server group and then having the new user login to become the new owner.
ORA-00040: active time limit exceeded - call aborted
Cause: The Resource Manager SWITCH_TIME limit was exceeded.
Action: Reduce the complexity of the update or query, or contact your database administrator for more information.
ORA-00041: active time limit exceeded - session terminated
Cause: The Resource Manager SWITCH_TIME limit was exceeded.
Action: Reduce the complexity of the update or query, or contact your database administrator for more information.
ORA-00042: Unknown Service name string
Cause: An attempt was made to use an invalid application service.
Action: Use a valid service name from SERVICE$ or add a new service using the DBMS_SERVICE package.
ORA-00050: operating system error occurred while obtaining an enqueue
Cause: Could not obtain the operating system resources necessary to cover an oracle enqueue. This is normally the result of an operating system user quota that is too low.
Action: Look up the operating system error in your system documentation and perform the needed action.
ORA-00051: timeout occurred while waiting for a resource
Cause: Usually due to a dead instance.
Action: Check for any dead, unrecovered instances and recover them.
ORA-00052: maximum number of enqueue resources (string) exceeded
Cause: Ran out of enqueue resources.
Action: Increase the value of the ENQUEUE_RESOURCES initialization parameter.
ORA-00053: maximum number of enqueues exceeded
Cause: Ran out of enqueue state objects.
Action: Increase the value of the ENQUEUES initialization parameter.
__________________________________
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-00055: maximum number of DML locks exceeded
Cause: Ran out of DML lock state objects.
Action: Increase the value of the DML_LOCKS initialization parameter and warm start.
ORA-00056: DDL lock on object "string.string" is already held in an incompatible mode
Cause: An attempt was made to acquire a DDL lock that is already locked.
Action: This happens if you attempt to drop a table that has parse locks on it.
ORA-00057: maximum number of temporary table locks exceeded
Cause: The number of temporary tables equals or exceeds the number of temporary table locks. Temporary tables are often created by large sorts.
Action: Increase the value of the TEMPORARY_TABLE_LOCKS initialization parameter and warm start.
ORA-00058: DB_BLOCK_SIZE must be string to mount this database (not string)
Cause: DB_BLOCK_SIZE initialization parameter is wrong for the database being mounted. It does not match the value used to create the database.
Action: Fix the value of the DB_BLOCK_SIZE parameter or mount a database that matches the value.
ORA-00059: maximum number of DB_FILES exceeded
Cause: The value of the DB_FILES initialization parameter was exceeded.
Action: Increase the value of the DB_FILES parameter and warm start.
ORA-00060: deadlock detected while waiting for resource
Cause: Transactions deadlocked one another while waiting for resources.
Action: Look at the trace file to see the transactions and resources involved. Retry if necessary.
Reference: Oracle Documentation
No comments:
Post a Comment