Re: Bizarre query failure

   If you've run the catblock.sql script, you can select from dba_waiters. You can tie this to the v$session table for more information on the user that is locking the table.

  You can also tie the hash_value and sql_address from the v$session view to the v$sqlarea view to get a description of the SQL statement that was being executed by that user.

Hope that helps.

  -Lisa

On Fri, 13 Aug 1999 15:41:14 GMT, "Tom Williamson" <tomw_at_action.cnchost.com> wrote:

>Thanks for the thoughts!
>
>The table only has about 1100 rows in it so I'm fairly sure it should update
>in less than a minute. ;)
>
>The locking issue sounds like the best possibility. How can I check who's
>holding what locks on what database objects? (Sorry, if this was MS-SQL
>Server I'd know where to go, but I'm a little new at Oracle.)
>
>Thanks again - Tom
>
>
>
>Connor McDonald wrote in message <37B3D751.3675_at_yahoo.com>...
>>Tom Williamson wrote:
>>>
>>> Simple problem with no good explanation: I am trying to update the data
>in
>>> a table. The following SQL statements work:
>>>
>>> select * from cards;
>>> select * from cards where pc_batch > 1165;
>>> update cards set pc_batch = 1000 where pc_batch = 0;
>>> update cards set pc_batch = 0 where pc_batch = 1000;
>>> update cards set pc_batch = -1 where pc_batch = 0;
>>> update cards set pc_batch = 0 where pc_batch = -1;
>>>
>>> The following SQL statements hang and never return:
>>>
>>> update cards set pc_batch = -1 where pc_batch = 1180;
>>> update cards set pc_batch = 0 where pc_batch = 1180;
>>> update cards set pc_batch = 0 where pc_batch > 1165;
>>>
>>> If I run these in SQL Worksheet (on Windows NT), the client hangs
>completely
>>> and must be killed manually. If I run them in a telnet window, I can
>>> recover the session using Ctrl-C. The message I get is:
>>>
>>> ERROR at line 1:
>>> ORA-01013: user requested cancel of current operation
>>>
>>> I am running Oracle 8.0.5 on Solaris/Intel. I have already rebooted the
>>> machine and restarted the database. Our server installation was set up
>by
>>> an Oracle consultant, so I'm fairly sure it's correct.
>>>
>>> More importantly, how can I fix this?
>>>
>>> Thanks - Tom
>>
>>Some possibilities:
>>
>>a. Big cards table ? So it takes a long to update.
>>b. Someone else has locked the cards table so you're updates just sit
>>and wait. In particular, if you aborted a SQL Worksheet, you may just
>>have locked yourself.
>>c. Tiny logfiles - the update is going superslow because logfiles are
>>too small - check alert log for "checkpoint not complete" messages...
>>
>>Just some thoughts - HTH
>>
>>--
>>===========================================
>>Connor McDonald
>>"These views mine, no-one elses etc etc"
>>connor_mcdonald_at_yahoo.com
>>
>>"Some days you're the pigeon, and some days you're the statue."
>
Received on Fri Aug 13 1999 - 11:55:44 CDT