Oracle Error 04020
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog ora-04020 deadlock detected while trying to lock object materialized view
utlrp.sql ORA-04020 error on ora-04024: self-deadlock detected dbms_standard Oracle Database Tips by Burleson Consulting April 11, 2015 Question: I am upgrading Oracle from 32 bit toOracle Ora-04024
64 bit and after running utlrp.sql I get this error: ORA-04020: deadlock detected while trying to lock object SYS.DBMS_STANDARD ORA-04045: errors during reompilation/revalidation of SYS.STANDARD ORA-06553: PLS-213: package STANDARD not accessible
Ora-04021
Answer: See MOSC notes 156900.1 and 130409.1, Bug 1612147, and also see how to diagnose deadlocks. Also see related error Flashback error ORA-06553: PLS-213: package STANDARD not accessible. Obviously, the PLS-213 error indicates that the STANDARD package is not installed or it is invalid. First see if the standard package is invalid: select status from dba_objects where owner = 'SYS' and metalink dba_object = 'STANDARD'; If it is valid, rre-un $ORACLE_HOME/rdbms/admin/utlrp.sql to re-create and re-validate the STANDARD package. Here is one way to re-enable the dbms_standard package: 1 - disable the trigger SYS.CDC_ALTER_CTABLE_BEFORE 2 - re-run utlrp.sql 3 - enable trigger SYS.CDC_ALTER_CTABLE_BEFORE Also try this possible fix: 1. Set parameters in your updated initSID.ora (create from spfile): AQ_TM_PROCESSES=0 _SYSTEM_TRIG_ENABLED=FALSE 2. Rename spfile, shutdown and STARTUP MIGRATE 3. Run catalog.sql again 4. Comment parameters added in step 1 5. Rename back your spfile 6. Shutdown and STARTUP normal Get the Complete Oracle SQL Tuning Information The landmark book "Advanced Oracle SQL Tuning The Definitive Reference" is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher. Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experi
detected while trying to lock object... From:
Ora-06512
exclusive mode. In rare cases, you may go in to deadlock situations if your dependent objects has some form of recursive relationship and need to be compiled too . I would suggest to find the dependent objects (look at dba_dependencies) and compile them first. Then try to compile your object. If still you have a problem, then when the process is hanging, look at the v$session_Wait to find out what the session is waiting http://www.dba-oracle.com/t_utlrp_sql_ora_04020_error_dbms_standard.htm for. If it is waiting for library cache pin on a particular object then you could look at x$kgllk table to find out who else if using that particular object(kglnaobj is the object name and you could use this column ). Otherwise, before compiling the object from sqlplus , dump the errorstack like this and then compile the object. Look at the trace file generated to find the offending session.: alter session set events '4020 http://www.orafaq.com/maillist/oracle-l/2001/02/09/0993.htm trace name errorstack, level 15'; Hope this helps!! ~ Thanks Riyaj "Re-yas" Shamsudeen Certified Oracle DBA i2 technologies www.i2.com "These are my opinions and does not bind my employer" "Mohammad Rafiq" To: Multiple recipients of list ORACLE-L
CommunityOracle User Group CommunityTopliners CommunityOTN Speaker BureauJava CommunityError: You don't have JavaScript enabled. This tool uses JavaScript and much of it will not work correctly without it enabled. Please turn JavaScript back on and reload this page. Please enter a title. You can not post a blank message. Please type your message and try again. More discussions in PL/SQL and SQL All PlacesDatabaseDatabase Application DevelopmentPL/SQL and SQL This discussion is archived 1 Reply Latest reply on Jan 18, 2002 7:24 AM by 172845 ORA-04020: deadlock error message using Function-Based Index 172845 Jan 18, 2002 7:16 AM I try to create a function-based index and get the following error message: ERROR at line 2: ORA-04020: deadlock detected while trying to lock object SDGGAH.OPP_TERMIN_MAPPINGER I do only have one session open against the database. I have tried this using several databases (standalone on Windows PC and Unix based with several users) and get the same error each time. I have tried both running and debugging using both SQL*PLus and Toad. Both give the same error message. I use versions 8.1.5 and 8.1.7 Can anyone help me here ? The DDL for the function and the function-based index are shown below. Regards Bongo6 ... ************************************* ** The function I have created is: ** ************************************* CREATE OR REPLACE function guh_ret_var ( p_year in number , p_month in number , p_day in number ) return varchar2 deterministic is begin return to_char(p_year)||to_char(p_month)||to_char(p_day); end guh_ret_var; / ************************************************* ** The function-based index I have created is: ** ************************************************* CREATE INDEX SDGGAH.guh_test_find ON SDGGAH.my_diary (substr(guh_ret_var(my_year,my_month,my_day),1,100)) COMPUTE STATISTICS TABLESPACE INDX; 63Views Tags: none (add) This content has been marked as final. Show 1 reply 1. Correction:ORA-04020: deadlock error message using Function-Based Index 172845 Jan 18, 2002 7:24 AM (in response to 172845) Correction: I have tried both running and debugging using both SQL*PLus and Toad. Both give the same error message. Sh