?
Solved

Oracle Table Update/Delete not working

Posted on 2012-09-20
7
Medium Priority
?
721 Views
Last Modified: 2012-09-20
I am working with a table in Oracle and when an Update or Delete command is run the timer just keeps ticking over and no errors are returned.

Select statements are working perfectly.

There are only approx 10,000 records in this table and no changes have been made that I am aware of.

Any help would be appreciated.

Michael
0
Comment
Question by:Michael Fowler
  • 3
  • 2
  • 2
7 Comments
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 38420444
your question seems not to have sufficient info. Kindly give table desc and your update query.
0
 
LVL 23

Author Comment

by:Michael Fowler
ID: 38420460
The table in question is securities table used as part of the EAM Ellipse.

I noticed the issue because when I attempted to make changes to users security profile via the application the application would time out.

I then went to the database itself and ran a select statements eg

Select * from msf020

or

Select * from msf020 where entity = xxxxxx and dstrct_code = xx

Note: x's denote data

These select statements worked exactly as expected with timeframes in the milliseconds.

I then attempted to make a change to the table with

update msf020
set global_profile = xxxxx
where entity = xxxxxx and dstrct_code = xxxx

the corresponding select statement for the above updates retuens one row of data and the field global_profile is valid. When I ran this statement it just kept running and no errors were returned. After 15 minutes I aborted the statement.

I then tried to delete the record using

delete from msf020
where entity = xxxxxx and dstrct_code = xxxx

and I got the same result as above.

This problem is happening to all records in the table and other tables are not affected.

Hope this provides the info you need

Michael
0
 
LVL 14

Expert Comment

by:Muhammad Ahmad Imran
ID: 38420489
>> Select * from msf020 where entity = xxxxxx and dstrct_code = xx

Is this gives you only one records or many?
As you said you have 10,000 records in this table, there shouldn't be any delay.

>> After 15 minutes I aborted the statement.

Why you aborted, be patient and see what happened for a little longer, the prompt must give you a message successful or unsuccessful.

check any constraints restricting you to do that.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 28

Accepted Solution

by:
Naveen Kumar earned 1500 total points
ID: 38420492
May be someone else is upating / deleting the data in the table and hence it could have been locked by some other users.

Check v$locked_object or v$lock to see if this table is locked by someone else. If so, then your session will await until that session releases the lock when that users does a commit or rollback to end that dml transaction.
0
 
LVL 23

Author Comment

by:Michael Fowler
ID: 38420518
OK for some reason the system has come good with no changes. I have no idea what happened but I am happy that the problem has gone away.

Note: I logged the issue with our DBA for when he returns

Michael
0
 
LVL 23

Author Closing Comment

by:Michael Fowler
ID: 38420528
I would guess that this is the most likely scenario and so I have awarded the points. Now to convince management that only the dba and systems admin (me) should have read/write access to the database

Michael
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 38420718
Good and thanks.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question