CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Dortimer
Dortimer

Reputation: 617

Any non-SELECT queries don't run in Oracle

So, I can successfully run any SELECT statement, but doing any UPDATE statements just hang until they eventually time out. This occurs with trying to execute any stored procedures as well. Other users that connect to the database can run anything without running into this problem.

Is there a cache per user that I can dump or something along those lines? I usually get sick of waiting and cancel the operation, so I don't know if that has contributed to the problem or not.

Just for reference, it's things as simple as these:

UPDATE SOME_TABLE
SET SOME_COLUMN = 'TEST';

EXECUTE SOME_PROCEDURE(1234);

But this works:

SELECT * FROM SOME_TABLE; -- various WHERE clauses don't cause any problems.

UPDATE:

Probably a little disappointing for anyone who came here looking for an answer to a similar problem, but the issue ended up being twofold: The DBA didn't think it was important to give me many details, but there were limitations on the Oracle server that were intentionally set for procedures in general (temp space issues, and things of that ilk). And second, there was an update to the procedure that I wasn't aware of that'd run a sub-query for every record that's pulled in the query (thousands of records). That was removed and now it's running as expected.

Upvotes: 0

Views: 183

Answers (1)

Heiner
Heiner

Reputation: 100

In my experience this happens most often because there is another uncommitted operation on the table. For example: User 1 successfully issues an update but does not commit it or roll it back. User 2 (or even another session of User 1) issues another update which just hangs until the other pending update is committed or rolled back. You say that "other users" don't have the same problem, which makes me wonder if they are committing their changes. And if so, if they are updating the same table or a different one.

Upvotes: 2

Related Questions