ORA-04021 timeout occurred while waiting to lock object

Error : ORA-04021 timeout occurred while waiting to lock object


Solutions:


1. Look in V$SESSION_EVENT and V$SESSION_WAIT to see what the session is waiting on.
2. Use the V$LOCKED_OBJECT view to find out if anyone else is locking the object.
3. Check  DB links which aren't working correctly.

Kill the relevant sessions if needed.

ORA-16014

SQL> ALTER DATABASE open
*
ERROR at line 1:
ORA-16014: log 1 sequence# 10484 not archived, no available destinations
ORA-00312: online log 1 thread 1:
'C:\ORACLE\PRODUCT\10.2.0\STAR\REDO01.LOG'




You are getting this message because Oracle can not find enough space on your archive destination to write the archive.Create some space in your archive destination. Also check if the destination you specified EXISTS.

Memory Notification: Library Cache Object loaded into SGA..... Heap size 2183K exceeds notification threshold (2048K

Memory Notification: Library Cache Object loaded into SGA

Heap size 2183K exceeds notification threshold (2048K)

These are warning messages only which should not cause any problems. 
These warning messages appear due to new event messaging mechanism and memory manager in 10g Release 2. In 10g R2 we have a new undocumented parameter that sets the KGL heap size warning threshold. Warnings are written if heap size exceeds this threshold. 

Default Threshold in 10.2.0.1 is 2 MB only. But Default Threshold value in 10.2.0.2 is 50 MB which is reasonable and recommended value. 

Solution: 


Set _kgl_large_heap_warning_threshold to a reasonable high value to prevent these warning messages.

SQL> alter system set "_kgl_large_heap_warning_threshold"= 52428800 scope=spfile;

SQL> shutdown immediate; 

SQL> startup;

Incarnation Error in Recovery

Following error may thrown during recovery process if incarnation of live and backup databases are different..


An incarnation is a seperate ‘life path’ of an Oracle Database, which is created at the time when a RESETLOGS is issued.


ORA-00283: recovery session canceled due to errors
ORA-19909: datafile 1 belongs to an orphan incarnation
ORA-01110: data file 1:


First check the incarnation of both the databases using rman.


RMAN> list incarnation


Check the current incarnation of the LIVE database and apply the same number to backup database.


RMAN> RESET DATABASE TO INCARNATION 4 ;


Then try to recover again..

How to exit from : Import file: expdat.dmp >

When your import file path was wrong you may get a prompt like below and you may wonder how to exit from it..


IMP-00002: failed to open /quit.dmp for read
Import file: expdat.dmp >


Use "Ctrl D" to exit from the prompt 
or you may also kill the session from separate OS window/terminal.

Move a Table From One Tablespace to Another

Use below command to change the tablespace of a table..


ALTER TABLE tablename MOVE TABLESPACE newtablespace


You can find out details of existing tablespaces through below view..


SELECT DISTINCT * FROM DBA_TABLESPACES

Rename/Move Data File /Redo File

Use below command..


alter database rename file '/archives/redo/redo13b.log' to '/u01/app/oracle/oradata/star10g2/redo13b.log'

Search for column names in an Oracle DB

SELECT OWNER,TABLE_NAME,COLUMN_NAME FROM ALL_TAB_COLUMNS 
WHERE COLUMN_NAME LIKE '%X%'


where "X" is the column name you are looking for

ORA-00054: resource busy and acquire with NOWAIT specified

If you administer a busy database you will see this error from time to time. 

It simply means that session has a lock on an object that your session is trying to update (or lock). More specifically, your session has asked for a lock on the same object, but has specified the 'nowait' clause. The nowait clause, upon finding something locked, returns an error, rather than waiting for the lock to be released.

Your options are as follows:

• Try again later. Hopefully whatever was locking the object will have finished, and you can continue with your work.
• Attempt to find out who/what is locking the object and kill it off.