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

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

-- 
Sybrand Bakker
Senior Oracle DBA