Home » Developer & Programmer » Data Integration » ORA-04021 in the middle of the night (Standard Edition Release 12.1.0.2.0 - 64bit Production)
ORA-04021 in the middle of the night [message #659437] Tue, 17 January 2017 03:13 Go to next message
Buchas
Messages: 87
Registered: March 2006
Member
Hello,

We have warehouse ETL(extract, transform, load) scripts running for ~8hours in the night. Sometimes in the middle of the night they crash with error "ORA-04021: timeout occurred while waiting to lock object" at random place. In the morning however the situation is gone - the statement which crashed with ORA-04021 at night, runs with no problems. How is it possible to know what (which session) is causing the crash? We can run some sqlplus script just after the crash, but what do we put inside?

Thanks in advance.
Re: ORA-04021 in the middle of the night [message #659451 is a reply to message #659437] Tue, 17 January 2017 09:58 Go to previous messageGo to next message
BlackSwan
Messages: 25748
Registered: January 2009
Location: SoCal
Senior Member
If application was properly instrumented, you should be able to identify the specific SQL that threw the error.
You won't find any clues by running SQL after the incident has transpired.
Re: ORA-04021 in the middle of the night [message #659633 is a reply to message #659451] Wed, 25 January 2017 01:10 Go to previous messageGo to next message
Buchas
Messages: 87
Registered: March 2006
Member
I can find the specific SQL, but the problem is they differ from time to time.
Is it possible to modify the ORA-04021 timeout value?
Re: ORA-04021 in the middle of the night [message #659634 is a reply to message #659437] Wed, 25 January 2017 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 65254
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

https://community.oracle.com/thread/2321256

MOS note169139.1: How to analyze ORA-04021 or ORA-4020 errors?

Re: ORA-04021 in the middle of the night [message #665924 is a reply to message #659451] Tue, 03 October 2017 01:47 Go to previous messageGo to next message
Buchas
Messages: 87
Registered: March 2006
Member
We are still struggling with the error "ORA-04021: timeout occurred while waiting to lock object".

The SQLs that are causing it are, for example:
DROP MATERIALIZED VIEW DWPIPE.CLAIM_FACT_PRANESTA_MV;
(There are a bunch of materialized views dropped in a row, the error pops up randomly while dropping one of those mviews.)

The time it takes the error to fire is short - ~9 seconds, so it is hard to run any other SQLs while it is running.

Quote:
https://community.oracle.com/thread/2321256

MOS note169139.1: How to analyze ORA-04021 or ORA-4020 errors?
Michel, your first link is about packages, so this is a different case. Your second link is generic My Oracle Support page, I don't understand what should I do with number "note169139.1".
Re: ORA-04021 in the middle of the night [message #665926 is a reply to message #665924] Tue, 03 October 2017 03:01 Go to previous messageGo to next message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
You won't be able to drop a materialized view while it is in use; a timeout is hardly surprising. What if you refresh the MV rather than dropping it? Or you could quiesce the database before attempting the DROP.
Re: ORA-04021 in the middle of the night [message #665927 is a reply to message #665926] Tue, 03 October 2017 03:10 Go to previous messageGo to next message
Buchas
Messages: 87
Registered: March 2006
Member
But if I execute only one single statement
DROP MATERIALIZED VIEW DWPIPE.CLAIM_FACT_PRANESTA_MV;
it succeeds with no errors. So I assume MV is not in use... I get errors only if I do a series of MV drops like this:
DECLARE
    I NUMBER;
    
            MV_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(MV_DOES_NOT_EXIST, -12003);
    
    TABLE_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT(TABLE_DOES_NOT_EXIST, -942);
    
    MV_EXIST_BUT_NOT_TABLE EXCEPTION;
    PRAGMA EXCEPTION_INIT(MV_EXIST_BUT_NOT_TABLE, -12083);    
     TYPE VARCHAR254_ARRAY IS TABLE OF VARCHAR2 (254)  INDEX BY BINARY_INTEGER;
    mat_views   VARCHAR254_ARRAY;
    sqlas varchar(500);
BEGIN

mat_views(0) := 'DWPIPE.RETURNS_FACT_MV';
mat_views(1) := 'DWPIPE.PREMIUMW_RE_FACT_MV';
mat_views(2) := 'DWPIPE.RETURNS_RE_FACT_MV';
mat_views(3) := 'DWPIPE.PW_RESERVE_RE_FACT_MV';
mat_views(4) := 'DWPIPE.PW_FUTURE_RESERVE_RE_FACT_MV';
mat_views(5) := 'DWPIPE.RBNS_COMMON_DELTA_RE_FACT_MV';
mat_views(6) := 'DWPIPE.RBNS_COMMON_RE_FACT_MV';
mat_views(7) := 'DWPIPE.ISLAIDOS_REG_RE_FACT_MV';
mat_views(8) := 'DWPIPE.ISMOKA_RE_FACT_MV';
mat_views(9) := 'DWPIPE.POLICY_AMOUNT_FACT_MV';
mat_views(10) := 'DWPIPE.REGRESAS_REG_RE_FACT_MV';
mat_views(11) := 'DWPIPE.ADJUST_RE_FACT_MV';
mat_views(12) := 'DWPIPE.PAID_RE_FACT_MV';
mat_views(13) := 'DWPIPE.TVK_RE_FACT_MV';
mat_views(14) := 'DWPIPE.TVK_RETURNS_RE_FACT_MV';
mat_views(15) := 'DWPIPE.TVK_WRITTEN_FACT_MV';
mat_views(16) := 'DWPIPE.TVK_WRITTEN_RE_FACT_MV';
mat_views(17) := 'DWPIPE.REINSURER_COMMISSY_MV';
mat_views(18) := 'DWPIPE.RBNS_PREV_DELTA_FACT_MV';
mat_views(19) := 'DWPIPE.PAID_LATE_FACT_MV';
mat_views(20) := 'DWPIPE.CLAIM_FACT_MV';
mat_views(21) := 'DWPIPE.CLAIM_FACT_PRANESTA_MV';
mat_views(22) := 'DWPIPE.IM_KOR_DENG_MV';
mat_views(23) := 'DWPIPE.PERDR_VISKAS_V';
mat_views(24) := 'DWPIPE.TVK_RETURNS_FACT_MV';
--mat_views(25) := 'DWPIPE.TVK2_RETURNS_FACT_MV';
mat_views(25) := 'DWPIPE.ADJUST_FACT_MV';
mat_views(26) := 'DWPIPE.PW_RESERVE_FACT_MV';
mat_views(27) := 'DWPIPE.PW_FUTURE_RESERVE_FACT_MV';
mat_views(28) := 'DWPIPE.RBNS_COMMON_DELTA_FACT_MV';
mat_views(29) := 'DWPIPE.RBNS_COMMON_FACT_MV';
mat_views(30) := 'DWPIPE.PAID_FACT_MV';
mat_views(31) := 'DWPIPE.ISMOKA_FACT_MV';
mat_views(32) := 'DWPIPE.TVK_FACT_MV';
--mat_views(34) := 'DWPIPE.TVK2_FACT_MV';
mat_views(33) := 'DWPIPE.ISLAIDOS_REG_FACT_MV';
mat_views(34) := 'DWPIPE.REGRESAS_REG_FACT_MV';
mat_views(35) := 'DWPIPE.PREMIUMW_FACT_MV';
mat_views(36) := 'DWPIPE.PAID_COMMISSY_FACT_MV';
mat_views(37) := 'DWPIPE.WRITTEN_OFF_DEBT_MV';

I:=mat_views.FIRST;

     WHILE I IS NOT NULL LOOP
           sqlas := 'DROP MATERIALIZED VIEW '||mat_views(I);
         BEGIN
              EXECUTE IMMEDIATE sqlas;
         EXCEPTION WHEN MV_DOES_NOT_EXIST THEN
              NULL;
         END; 
          sqlas := 'DROP TABLE '||mat_views(I);
         BEGIN
              EXECUTE IMMEDIATE sqlas;
         EXCEPTION WHEN TABLE_DOES_NOT_EXIST THEN
              NULL;
              WHEN MV_EXIST_BUT_NOT_TABLE THEN 
              NULL;
         END;
            sqlas :=  'DROP VIEW '||mat_views(I);
         BEGIN
              EXECUTE IMMEDIATE sqlas;
         EXCEPTION WHEN TABLE_DOES_NOT_EXIST THEN
              NULL;
         END;                  
          I := mat_views.NEXT(I);
     END LOOP;
  /*   exception when others then
       RAISE_APPLICATION_ERROR(-20335, sqlas);
*/
END;

Re: ORA-04021 in the middle of the night [message #665929 is a reply to message #665927] Tue, 03 October 2017 04:06 Go to previous message
John Watson
Messages: 7184
Registered: January 2010
Location: Global Village
Senior Member
Just try it with refreshes. Doing all the DDL is silly.
Previous Topic: oracle workflow login
Next Topic: ODI integrate with Golden Gate
Goto Forum:
  


Current Time: Thu Dec 14 15:27:05 CST 2017

Total time taken to generate the page: 0.09437 seconds