Re: Query sbt events throws an error ORA-00918: column ambiguously defined

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Wed, 28 Mar 2007 22:26:58 +0200
Message-ID: <460ACF92.1000700@gmail.com>

Marcus Stumm schrieb:
> Am Mon, 26 Mar 2007 20:38:41 +0200 schrieb sybrandb:
>

>> On 26 Mar 2007 18:20:49 GMT, Marcus Stumm <marcus.stumm_at_arcor.de>
>> wrote:
>>
>>> Hello,
>>>
>>> I am suspecting an issue with a Media Management Library. The Backup and
>>> Recovery Advanced User's Guide from oracle states the the follwoing query
>>> can be used to monitor sbt events.
>>>
>>> SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT, 
>>> STATE, CLIENT_INFO
>> >FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
>>> WHERE sw.EVENT LIKE 'sbt%'
>>> AND s.SID=sw.SID
>>> AND s.PADDR=p.ADDR;
>>>
>>> When I run this statement:
>>>
>>> SQL> SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
>>>  2  STATE, CLIENT_INFO
>>>  3  FROM v$Session_WAIT sw,V$session s, v$process p
>>>  4  WHERE sw.EVENT LIKE 'SBT%'
>>>  5  and s.SID=sw.SID
>>>  6  and s.PADDR=p.ADDR;
>>>
>>> it throws an error
>>>
>>> STATE, CLIENT_INFO
>>> *
>>> ERROR at line 2:
>>> ORA-00918: column ambiguously defined
>>>
>>>
>>> But I can not see the reason why as my SQL-knowledge is not straight
>>> enough. I would be greateful id somebody can help me out.
>>>
>>> Thanks in advance
>>>
>>> Marcus
>> The state column occurs in more than one table, so SQL doesn't know
>> from which one.
>> You need to run
>> DESC <table>
>> and find out in which tables it occurs, choose the right one and
>> prefix state with the alias of this table.
>>
>> Hth

>
> Hello
>
> following yur suggestions done the trick:
>
> select p.SPID, sw.EVENT, sw.SECONDS_IN_WAIT AS SEC_WAIT,
> sw.STATE, CLIENT_INFO
> FROM V$SESSION_WAIT sw, v$SESSION s, V$PROCESS p
> where sw.EVENT LIKE 'sbt%'
> and s.SID=sw.SID
> and s.PADDR=p.ADDR
>
> How ever I tried all combinations but all coming back with:
>
> now rows selected
>
> so the MML can not be the issue.
>
> Thansk for your help.

On which Oracle version are you ?
It seems to me as there is a documentation bug in place, because the query you provided is indeed given in docs for 10g http://download-uk.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmtroub004.htm#sthref1194 whereas it will not work properly( all sbt related wait events changed names from 9i to 10g), so they are now all called like "Backup: sbtbackup" ( on 9i the same was
"sbtbackup") , so if you are on 10g - the filter expression should be changed to something like EVENT LIKE 'Backup: sbt%' to return wait events if any (or modify it even to EVENT LIKE '%sbt%' - to work on both versions).

Best regards

Maxim Received on Wed Mar 28 2007 - 15:26:58 CDT