| ORA-04021 in the middle of the night [message #659437] |
Tue, 17 January 2017 03:13  |
Buchas
Messages: 101
Registered: March 2006
|
Senior 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 #665924 is a reply to message #659451] |
Tue, 03 October 2017 01:47  |
Buchas
Messages: 101
Registered: March 2006
|
Senior 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;
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 |
John Watson
Messages: 8962
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 |
Buchas
Messages: 101
Registered: March 2006
|
Senior Member |
|
|
But if I execute only one single statement
DROP MATERIALIZED VIEW DWPIPE.CLAIM_FACT_PRANESTA_MV;
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;
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
