DELETE OF 1 LINE

[heraldoaraujo]

Class good night,

I'm trying to delete 1 line of a table and I just can not, I'vê tried even by her rowid and nothing. Does anyone have a clue that is happening?

Embrace.

[leandromiranda87]

An error is displayed ???

If you can not, muito probably does not have permissions for this ...

[heraldoaraujo]

I am allowed, no error returns. It is a table of many insert and delete. I speak of a table of more than 100 thousand records. Until a few days ago it worked, today can not delete any line.

[leandromiranda87]

Do not know ...

Idiot question ... But, are you doing commit ??

I have no more ideas ..

[heraldoaraujo]

Yes I'm giving Commit. But it also has an application that makes inserts and query on this table. Correct me if you are wrong But Oracle makes lock by line in DML statement right? If yes there is any dynamic view that displays this information that user is leasing the line or to the table?

[leandromiranda87]

Yes, Oracle does yes. However, select only Locka the line when you are doing the SELECT, after finalized it no longer does. In addition, Oracle makes a well-organized muito queue control, so, your delete should be running until it arrived it turns it off and only after you could give the commit.

Have you tried to do a select in this given, using for update ??

Sometimes I use to avoid rewriting code, but if the delete is not working, I do not know if it will be useful.

[Trevisolli]

Brother, just adding another question:

Is there any delete trigger in this table, or, relationship with some "child"?
embrace,

[stcoutinho]

Sorry,

Add more questions to the list:

- Are you using a tool (eg SQL Developer) to delete the line?
- If you are using a tool, have you tried to connect to SQL * Plus and run the delete command,
followed after Commit? Is there any error message in this case?
- What version of your database and what platform?
- Could you put the complete SQL commands for delete that you tried to run?
- This table you mentions would be a "normal" table? Does it have any additional feature,
as partitioning, compression (compressed date)? She would not be a materialized view, right?

I think a simple delete test in a SQL * Plus session should give some error message, lock indefinitely (if the record has been leased) or run successfully. If the command did not give a mistake, it should return you as many lines were affected. If zero lines were affected, use the delete wher to mount a SELECT command.

As colleagues mentioned, evaluate possible triggers acting in the table or dependencies (PK-FK) of "daughters" tables.

Hugs,

Sergio Coutinho

[heraldoaraujo]

Good afternoon class,

I was trying for PL / SQL Developer via "For Update" without success I tried via direct command line.

The DBA tried for SQLPlus and unsuccessfully, tried for Rowid and Nenhum success. The error message does not appear because it is locked and does not return the cursor to enter a new command

The version is oragle 10g 10.2.0.5.0 - 64bi, the platform and red hat but I do not know The version.

The command I use is:

Select all

delete from table where id_pk = 3456 and col_de_apenas_2_valores = 1;

It is only a normal table, without view and without partitioning.

Now about the trigger I "I think" you do not have, I do not know which view or table that displays the trigger on the bench!

[madmax]

Dude,

I think it gave a deadlock on this table.

See in the Bank Log if there is ORA0060 - Deadlock Detected, see if you can find SID and Session and a Kill

to remove deadlock sometimes Just downloading the seat.

I hope I have helped.

[diegolenhardt]

It's simple,
when you run the delete, get queries that show the bench locks, will show who is holding ...

Someone ta locking ...

[gleisoncandido]

Heraldaraujo, how are you?
Knife The following, turn the update and when to lock, turn this query below that will show in which session is giving the deadlock, I hope I have helped.

Select all

SELECT 
username, s.process,s.command,s.program 
, s.osuser osuser 
, s.sid sid 
, s.serial# serial 
, l.lmode lmode 
, decode(L.LMODE,1,'No Lock', 
2,'Row Share', 
3,'Row Exclusive', 
4,'Share', 
5,'Share Row Exclusive', 
6,'Exclusive','NONE') lmode_desc 
, l.type type 
, decode(l.type,'BL','Buffer hash table instance lock', 
'CF',' Control file schema global enqueue lock', 
'CI','Cross-instance function invocation instance lock', 
'CS','Control file schema global enqueue lock', 
'CU','Cursor bind lock', 
'DF','Data file instance lock', 
'DL','Direct loader parallel index create', 
'DM','Mount/startup db primary/secondary instance lock', 
'DR','Distributed recovery process lock', 
'DX','Distributed transaction entry lock', 
'FI','SGA open-file information lock', 
'FS','File set lock', 
'HW','Space management operations on a specific segment lock', 
'IN','Instance number lock', 
'IR','Instance recovery serialization global enqueue lock', 
'IS','Instance state lock', 
'IV','Library cache invalidation instance lock', 
'JQ','Job queue lock', 
'KK','Thread kick lock', 
'MB','Master buffer hash table instance lock', 
'MM','Mount definition gloabal enqueue lock', 
'MR','Media recovery lock', 
'PF','Password file lock', 
'PI','Parallel operation lock', 
'PR','Process startup lock', 
'PS','Parallel operation lock', 
'RE','USE_ROW_ENQUEUE enforcement lock', 
'RT','Redo thread global enqueue lock', 
'RW','Row wait enqueue lock', 
'SC','System commit number instance lock', 
'SH','System commit number high water mark enqueue lock', 
'SM','SMON lock', 
'SN','Sequence number instance lock', 
'SQ','Sequence number enqueue lock', 
'SS','Sort segment lock', 
'ST','Space transaction enqueue lock', 
'SV','Sequence number value lock', 
'TA','Generic enqueue lock', 
'TD','DDL enqueue lock', 
'TE','Extend-segment enqueue lock', 
'TM','DML enqueue lock', 
'TT','Temporary table enqueue lock', 
'TX','Transaction enqueue lock', 
'UL','User supplied lock', 
'UN','User name lock', 
'US','Undo segment DDL lock', 
'WL','Being-written redo log instance lock', 
'WS','Write-atomic-log-switch global enqueue lock') type_desc 
, request 
, block 
FROM 
v$lock l 
, v$session s 
WHERE 
s.sid = l.sid 
AND 
l.type <> 'MR' 
AND 
s.type <> 'BACKGROUND' 
AND 
(block = 1 OR request > 0) 
ORDER BY 
username;