Delete Query Causing Deadlock ORA-60

Nov 5, 2012

I am using oracle 10.2.0.3 and i am receiving very slow response time for the below query and sometimes resulting in a deadlock throwing ora-60 error.

DELETE FROM GBC_CORE.SPI_ELEMENT_ID TRGT WHERE (TRGT.URI) NOT IN (
SELECT DISTINCT FROMTOURI.URI FROM ( SELECT SERVICEACCESSNAME AS URI,
SUBSTR( SERVICEACCESSNAME,1,INSTR( SERVICEACCESSNAME ,'_')-1) AS
FROM_URI, SUBSTR( SERVICEACCESSNAME,INSTR( SERVICEACCESSNAME
,'_')+1,LENGTH(SERVICEACCESSNAME)) AS TO_URI FROM
TRPT.V_TRPT_SPI_VIEW@DBLNK_FCE_TRPT ) FROMTOURI,
[code]...

View 1 Replies


ADVERTISEMENT

Getting Deadlock Detected While Waiting For Resource Error For Select Query

Nov 4, 2013

i am getting a below error whenever executing the below select query. some times it will show dead lock detected while waiting for resource and terminated...some times it executes and gives result..but all the time it writes an alert to alert log 

Env: Linux / Oracle 11.2.0.3.3..Error from alert log:Errors in file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trc:ORA-00060: deadlock detected while waiting for resourceORA-10387: parallel query server interrupt (normal)  Trace file info... bdrdb_p017_6076.trc:Trace file /u01/oracle/oracle/diag/rdbms/bdrdb/bdrdb/trace/bdrdb_p017_6076.trcOracle Database 11g Enterprise Edition Release 11.2.0.3.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsORACLE_HOME = /u01/oracle/oracle/product/11.2.0/dbhome_1System.
[code]....

View 11 Replies View Related

Deadlock On Different Tables

Feb 18, 2013

I am working on 11g and AIX...We got deadlock recently and we need to investigate why it happen and resolution, so it will not happen in future. When we saw deadlock trace file ,it shows 2 sqls queries on different tables..and lock is exclusive lock.

----- Information for the OTHER waiting sessions -----
SELECT 1 FROM HOS_DTL WHERE ( HOS_LCN_DTL.HOS_LCN_DTL_ID = :1 ) FOR UPDATE WAIT 180

and

----- Current SQL Statement for this session ------
DELETE FROM EI_INV WHERE ( EI_INV.EI_INV_ID = :1 )

But these two tables not related at all 'HOS_DTL' and 'EI_INV' . Can deadlock happen if sqls queries fired on those table are not related at all and deadlock trace file can show those two sqls ..?

View 7 Replies View Related

SQL & PL/SQL :: How To Select And Delete At The Same Query

Jun 10, 2010

select SQL_CALC_FOUND_ROWS navl.xydata.Lat,
navl.xydata.Longi,
MIN(navl.xydata.GpsTime) as mn,
MAX(navl.xydata.GpsTime) as mx,
timediff(MAX(navl.xydata.GpsTime) ,MIN(navl.xydata.GpsTime) ) as idle,
[code]......

i want to delete WHERE mn = mx .

View 4 Replies View Related

SQL & PL/SQL :: DEADLOCK Found When Trying To Get Lock

Dec 5, 2012

While i am trying to execute below mentioned query i am facing "DEADLOCK FOUND WHEN TRYING TO GET LOCK" error.

UPDATE PLAN
SET PLAN_PMS_BLOCK_ID =''
WHERE PLAN_PMS_BLOCK_ID<>''
AND PLAN_PMS_BLOCK_ID NOT BETWEEN '0' AND '9'
AND PLAN_PMS_BLOCK_ID NOT BETWEEN 'A' AND 'Z'
AND PLAN_PMS_BLOCK_ID NOT IN('-')
AND LENGTH(PLAN_PMS_BLOCK_ID )=1;

View 7 Replies View Related

SQL & PL/SQL :: Query To Delete Two Weeks Old Data?

Sep 12, 2013

I want to delete the records grater than two weeks old.From Saturday to Friday we are considering as Week.

P_ID CREATED_DT
105 28/AUG/2013
106 29/AUG/2013
107 30/AUG/2013
108 31/AUG/2013
109 01/SEP/2013
110 02/SEP/2013

[code]....

From 28/AUG/2013 to 30/AUG/2013 records has to be deleted becuase thease records are two weeks old.

From 31/AUG/2013 to 06/SEP/2013 records belongs to one week and from 07/SEP/2013 to 13/SEP/2013 belongs to another week.Today is 12/SEP/2013 so from 06/SEP/2013 to 12/SEP/2013 should be existed in the table.Once date changed to 13/SEP/2013 then from 31/AUG/2013 to 06/SEP/2013 records has to be deleted.

I am posting table and insert scripts.

CREATE TABLE process_data(p_id NUMBER,CREATED_DT DATE);
INSERT INTO process_data VALUES(TO_DATE('28/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('29/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('30/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('31/AUG/2013','DD/MON/YYYY');
INSERT INTO process_data VALUES(TO_DATE('01/SEP/2013','DD/MON/YYYY');

[code]....

View 3 Replies View Related

SQL & PL/SQL :: Delete After Count Oldest Row Query?

Mar 22, 2011

I have one table that have many records. For the maintenance purpose I like to delete old record based on Customer No.-That is Mobile NO.If each Customer have more than 300 records, I like to delete by everyday batch process.can't figure out how to apply each Customer No.(Specific Column), I could sort (order by few column - SAVE_DT or SMS_ARV_CLC) how to write this kind of query? I try rownum but no more progress.

Here is my table
CREATE TABLE TM_060_SMS_TEST
(
SMS_SEQ VARCHAR2(18 BYTE),
SMS_RCV_CLC VARCHAR2(14 BYTE),
CUST_NUM VARCHAR2(12 BYTE),

[code]...

View 12 Replies View Related

Deadlock Detected While Trying To Lock Object XDB

Jul 19, 2007

I keep getting an ora-04020: deadlock detected while trying to lock object XDB. SDNRB..The statement I'm trying to issue is:

REVOKE execute on abc."descript_T" FROM PUBLIC;

I am not able to find a solution for this besides the fact of trying the operation again at a later time.I did, but get the same error every single time.

View 6 Replies View Related

SQL & PL/SQL :: Delete After Count Oldest Row Query BULK

Mar 24, 2011

I like to increase speed to delete our table. Is it possible to use BULK COLLECT or FORALL this query? This is not single delete or select, maybe I got the error? Is it possible to use BULK method to this query?

delete from
TM_060_SFS_TEST
WHERE
rowid in (
SELECT
[code]......

View 1 Replies View Related

PL/SQL :: Writing A Query To Delete Duplicates From A Table

Aug 16, 2012

I have data some thing like this.

NO NAME LOC SAL
------------------------------------------------------------------
1 A HYD 100
2 B BGL 200
1 A HYD 200
1 A HYD 150

I want to delete duplicate records (group by no,name,loc) but only max(qty) record should be retained. So I need output like this.

NO NAME LOC SAL
------------------------------------------------------------------
1 A HYD 200
2 B BGL 200

View 3 Replies View Related

SQL & PL/SQL :: ORA-04020 - Deadlock Detected While Trying To Lock Object

Jun 8, 2012

I am trying to fetch data from a table but it takes time and give such an error:

SQL> Select * from adm_users;
Select * from adm_users
*
ERROR at line 1:

[Code]....

View 8 Replies View Related

Deadlock In Database Because Of Concurrent Data Deletion

Feb 4, 2013

I am facing Deadlock issue in my transaction when record is been deleted in the same table in parallel from a PLSQL package. The package is been called from the Java code.The example and the table structure is given below.

Col1 of tab1 is foreign key to col1 of tab2.
Commit will not be done until all the below dml scripts are executed in both environment.

1st session:

Delete from tab1 where col1=1001;
Delete from tab2 where col1=2001;

Result: Deletion successful

2nd session:

Delete from tab1 where col1=1002;
Delete from tab2 where col1=2002;

Result: Deletion successful

1st session:

Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;

Result: Deletion successful

2nd session:

Delete from tab1 where col1=1004;
Delete from tab2 where col1=2004;

Result: Query is executing for a longer time.

1st session:

Delete from tab1 where col1=1003;
Delete from tab2 where col1=2003;

Result: Query is not executed and throws Deadlock in the back end.

View 3 Replies View Related

SYS JOB Causing Waits

Oct 25, 2013

I am using,  Release 11.2.0.3.0 of oracle. In our database we observe, high 'Application' wait followed by 'other' and 'User I/O'. After investigating through the wait class 'Other', i found that 90% of the wait is due to wait event 'Enq: WF Contention '  that to excatly 5 PM to 5.30 PM daily. Then i found from the dba_hist_active_sess_history, that the sessions experiencing this waits are oracle internal(M004),

Now i can see two of the oracle jobs has been scheduled at this particular time.

1)dbms_autotask_prvt.age
2)dbms_scheduler.auto_purge

And these were having frequency daily.So, i am suspecting these were experiencing the waits. Now my question is, can i decrease the frequency(may be weekly) of these jobs and it will not put any negative impact on my DB? Or should change the schedule time to some less peak hour or whether these are 'not required' and can be disabled?

View 3 Replies View Related

SQL & PL/SQL :: Delete Rows Returned By Complex Join Query

Apr 5, 2013

How Can I delete the returned two rows?

1 select s.reg_no,s.course_code,
2 s.section src_sec,a.section a_sec,a.att_date,a.att_flag
3 from attendance a ,src s
4 where a.semester_code=1
5 and a.semester_year=2013
6 and s.semester_code=1
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Delete Multiple Table Data By Single Query Without Any Condition?

May 10, 2012

can we delete multiples table through the single query?

suppose we have 2 table first one is emp and second is client

i want delete all data from emp and client through the single line query

View 1 Replies View Related

Getting ORA-00060 - Deadlock Detected While Waiting For Resource While Deleting Datafile?

Nov 18, 2010

I am getting "ORA-00060: deadlock detected while waiting for resource" while deleting a datafile.

when i checked in dba_data_files and V$datafile the ONLINE_STATUS/STATUS is in RECOVER mode.

I do not need this datafile so i tried to drop using the following command ..

ALTER TABLESPACE DATAP_LARGE DROP DATAFILE 'D:\ORACLE\ORADATA\DATAP\DATAP_LARGE_12.DBF';

I checked the Trace file geenrated for the deadlock, the Session which is in question is the session where i ran the command (SQLPLUS).

View 8 Replies View Related

Security :: How To Know Which Session Causing TX Lock

Feb 28, 2011

I have to find out the cause of a lock where a particular session with some serial no is causing a TX level lock for certain duration.

View 17 Replies View Related

Insert Statement Causing ORA-01858?

Dec 4, 2012

i'm executing a load test using the below statement:

INSERT INTO BPMBI.PPROCINSTANCE SELECT * FROM BPMBI.PPROCINSTANCE_BKP WHERE ROWNUM < 501;
COMMIT;
EXIT;

I've been using the same for a few days without any error while now wehn i try to use the same i get below error:

SQL> insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500;
insert into pprocinstance select * from pprocinstance_bkp where rownum <= 500
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected.

I've been trying to check what the issue is and when i execute the rownum < 140 works fine while rownum < 150 is showing the same error.

View 6 Replies View Related

SQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables

Jul 17, 2012

write a query to delete similar records in particular fields(columns) in different tables.

View 2 Replies View Related

Real Application Clusters :: 11GR2 RAC Deadlock Trace File - Where Have Reported Row IDs Gone?

Jun 3, 2013

I have a deadlock trace file to analyse and i used to be able to see the rowid as a 16 bit hex value in the trace file, which i could then query on to get the actual real world row name.I see in the 11GR2 deadlock trace the formatting is different and for the life of me i am unable to see the rowid. Has Oracle stopped reporting this now, or is their another way to get this value? The deadlock graph shows me

*** 2013-05-14 14:49:15.047
Submitting asynchronized dump request [28]
Global blockers dump end:-----------------------------------
Global Wait-For-Graph(WFG) at ddTS[0.16e] :
BLOCKED 0x4362890f8 5 wq 2 cvtops x1 TX 0x3d001b.0x18a3021 [FF000-0001-00000002] inst 1
BLOCKER 0x436288f38 5 wq 1 cvtops x28 TX 0x3d001b.0x18a3021 [102000-0001-00000002] inst 1
[code]....

View 0 Replies View Related

SQL & PL/SQL :: ALTER TABLE Causing Procedure To Go INVALID?

Aug 3, 2010

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I altered existing table EVENT_SUB - added 3 columns. After that, I noticed all the procedures which had mention of this table name went in INVALID status, even if its simple SELECT, ALTER OR INSERT as shown below..

SELECT * FROM EVENT_SUB

OR
INSERT INTO EVENT_SUB...
OR
ALTER TABLE EVENT_SUB
WHERE....

So I had to recompile all the procedures associated with it. Is there any other ways to achieve this, like a line of code to add in the procedure itself, right after this DDL statements.

Sometimes i use this:
select object_name, object_type from all_objects where owner='TOYCOM' and status='INVALID'
Then, I would simply recompile the invalid objects.

For indexes, i do...

alter index <name> rebuild;

BTW, I did try to preview message, and then click on Create Topic, it gave me error..again.

"A system error has occurred.

View 3 Replies View Related

How To Get Session / SQL Text Details Causing Excessive Archives Log

May 29, 2013

In my case , the archives are getting generating in excess count, how to get sql text/sid information.

View 2 Replies View Related

Forms :: Trigger Causing Error ORA-06502 And ORA-01403?

May 2, 2006

My layout is working well enough triggers though are causing me some problems. I am getting the following error message just about every time I write a trigger so I am getting the feeling I am doing something fundamentally wrong. Like there is something I am just not getting because I am getting this error on the simplest of actions.

The error:
FRM-40735: WHEN-VALIDATE-ITEM trigger raised unhandled exception ORA-06502.
--------------------------------------------------------------
ORA-01403: no data found

The trigger is this:
cs3_prog.set_date_mode;

this calls the following procedure in the package cs3_prog:

procedure set_date_mode IS
BEGIN
:cs3_data.street := 'something';
end;

this in the past did a lot more but I kept throwing stuff out to see what could have been causing the problem. This does actually work if I ok through the first error message and then try entering data again into the field the trigger is tied to. On this second firing of the trigger is does successfully alter the value.

Just the fact that this is happening every time I create a trigger and it seems to happen regardless of what I have the trigger doing leads me to believe I have something fundamentally wrong and I would love to have it set straight.

View 11 Replies View Related

XML DB :: DeleteXml() Causing Data Format Error In Database

Jul 21, 2013

I'm using the oracle  xml db 11g in my website application. Last week I found that using the deletexml() to delete a node in the xml may cause a terrible problem that the rest data in the database had wrong xml format. I wonder is it a bug when using binary xml storage.  My xml data is describe below, 

<root>     <nodes>          <node id="1">               <name>John</name>               <age>16</age>               <hobby>football</hobby>          </node>          <node id="2">               <name>Alex</name>               <age>22</age>               <hobby>table tenisl</hobby>         </node>           .....     <nodes></root>  

I using deletexml() to delete just a node once time, "update projects set object_value=deletexml(object_value, '/root/nodes/node[@id="1"]')" Usually it successes and the rest data is as expected, but sometime, I find that after deleting a node, any query in the database except "select * from projects" gets null. And the rest data has wrong format like below,

<root>     <nodes>          <node id="1">               <name>John</name>               <age>/hobby>          </node>          <node id="2">               <name>Alex</name>               <age>22</age>               <hobby>table tenisl</hobby>         </node>           .....     <nodes></root>

Just one format error causes the whole xml data valueless, but it seems that in most time the deletexml() works well.

View 8 Replies View Related

Real Application Clusters :: Global Cache Service Process Died After Resolving Deadlock?

Sep 30, 2013

what could be the reason for a LMSn process not heartbeating after a global enqueue service deadlock was detected? The happened in a 3instance RAC and after the LMS1 process stop heartbeating oe of the instances crashed afterwards and another instance crashed some minutes after. reason for the process crash after resolving deadlock?

View 5 Replies View Related

Application Express :: Causing (new Line) In Display Of Text In A Report

Jul 27, 2012

Oracle 11.2; application express 4.1...I have a table with a unicode clob column into which I insert descriptive text. Within these text entries I have inserted CR/LF (00D00A). However when I display the text entries in a report, the text streams instead of appearing with separate lines.

My report statement:
select text_entry_dt, entry_authy_nm, entry_text
from obj_text
where obj_id = :P41_FIND_ID
  and text_type_cd = 'DES'

hat to do to obtain line separation within the entry_text content?

View 3 Replies View Related

Oracle 11g R2 - (SHAD) Process Causing ORA-00020 Maximum Number Of Processes

May 23, 2013

Oracle 11g R2 on WIndows 20o8 R2 Standard edition

I have a test server that keeps running into ORA-00020: maximum number of processes error each night. Our current setting is '600' processes to be allowed. There are no users to this database except myself. No front end app connects to it either.

I found that ORACLE.EXE (SHAD) process coming from SYSMAN user is the one eating up all the processes and eventually causing that error.

From v$session, the program is OMS.

From v$process, the hostname is the server itself.

I narrowed it down to restarting the ConsoleService, if i do that, then the number of processes drops down. So now im not sure why something within the ConsoleService is logging in, taking a process and not releasing it...What i can check?

View 3 Replies View Related

PL/SQL :: Huge Disk Sort Causing Continuous Full Of Temporary Tablespace

Jan 2, 2013

We are facing problem as temporary tablespace getting full continuously. During below running query, temporary tablespace getting full continuously and now it is not managable so we had stop the processing but we need to resolve this issue as business impact is there.

MERGE INTO HDFCMPR.MPR_TB_MPRMASTER
USING (SELECT /*+ USE_HASH(A,B) FULL(A) FULL(B) */
MER_TRACKID, TRANID, DECODE (UCAF, 'n', NULL, UCAF) UCAF,
A.ID
[code]....

View 5 Replies View Related

Server Utilities :: Data Pump Import Order - Causing Constraint Violation

Nov 16, 2010

We are trying to import data into existing tables in a schema using data pump

However the foreign key tables are being imported first and then the master table data thus violating the constraints

Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)

Is it a normal behaviour during data pump import?

Is it possible that the keys being sequence generated are causing this?

As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?

View 3 Replies View Related

PL/SQL :: To Delete Child Records Manually Without Using Oracle Delete Cascade

Oct 9, 2012

I have to write a procedure that accepts schema name, table name and column value as parameters....I knew that i need to use metadata to do that deleting manually.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved