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

From: Marcus Stumm <marcus.stumm_at_arcor.de>
Date: 02 Apr 2007 20:30:12 GMT
Message-ID: <461167d4$0$20299$9b4e6d93@newsspool3.arcor-online.net>

Am Wed, 28 Mar 2007 22:26:58 +0200 schrieb Maxim Demenko:

> 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

I tried both 9i and 10g syntax. In both cases I need to provide the correct prefix to get the query working. The machine where I suspecting the sbt issue is running 9.2.0.7 on Windows. However the query came back with nothing although the Job hung when the achivelog backup should start. In the MML log file I cann see that sbtinit2 was send, sbtbackup was missing.

regards

Marcus Received on Mon Apr 02 2007 - 15:30:12 CDT