SID no longer appears in "details" tab queries

[admin]

Hi Marco,
I'm afraid there is another bug. I believe this started right after I took the code update to 9.0.6.1665:

In the session browser, on the Details tabs, the SID column no longer shows up in the output. I can run the same query from a SQL window and the sid column displays in there. This behaviour occurs not only with my custom queries behind the details tabs but also for the default tab titled "cursors". I use these details tabs ALL THE TIME so this is very inconvenient. Please verify and let me know if I'm missing something.
Thanks,
Loren Gollhardt

 

[Marco Kalter]

The SID column will indeed not be displayed on the details tab, because it is already present in the session list. This has not changed in 9.0.6.

 

[admin]

Hi Marco,
I used the Cursors detail tab as my example above because I thought that would be a good example, however, I see now that maybe that was not a good example because that particular query is related to a particular sid that is already listed in the filter query. So here's a better example of what I mean: I have a custom query that I put in a tab long ago that shows me the rollback status of all sessions across the entire database. It is independent of the filter. It is NOT related to a particular sid in the filter and so it should independently display the sid column to show the various session sid's and their rollback status. Previously, I am almost 100% certain that this query would display the SID column when I accessed it from the detail tab. Now it does not. Try it for yourself, in and out of a tab, and you will see what I mean. The SID column should NOT be excluded from being displayed across the board in every case - but only when the underlying detail query references a sid from the adjoining filter query. In other words, when a SID column from any table or view is in the select clause of a detail query but the query has a qualifier like this: "AND SID=:SID", you do not need to display the SID column because there is only one sid involved, but when a sid column is in the select statement of a detail query with no qualifier in the where clause, then the sid column should be displayed because the user obviously wants to see the list of all sids meeting all the other conditions.

Now that I've upgraded I have no way to go back and test on the earlier version to verify 100% what I'm saying is true, so if you test this query on a previous version in a detail tab and the sid column does not display, then I will have to believe you and I will admit that I'm wrong, but I am almost certain that I used to get the sid column displayed with this query in a detail tab. Please test it and let me know.

Thanks for your assistance.

select
s.inst_id
, s.sid
, s.serial#
, s.username
, to_date(t.start_time, 'MM/DD/YY HH24:MI:SS') start_time
, EXTRACT(DAY FROM (SYSDATE - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS')) DAY TO SECOND )
|| ' days '
|| EXTRACT(HOUR FROM (SYSDATE - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS')) DAY TO SECOND )
|| ' hrs '
|| EXTRACT(MINUTE FROM (SYSDATE - to_date(t.start_time, 'MM/DD/YY HH24:MI:SS')) DAY TO SECOND )
|| ' mins'
"ELAPSED_TIME"
, t.space
, t.recursive
, t.noundo
, t.used_urec
, round((t.USED_UBLK * 16)/1024,2) used_MB
from
gv$transaction t,
gv$session s
where s.sADDR = t.SES_ADDR
order by used_urec desc;

 

[Theod]

Hi,

I simple use an alias after s.sid "sid" to avoid this feature.
In this example I'll see the master and all childs-sessions at an detail-tab for an selected master-session.

SELECT px.QCSid
       ,px.Qcserial#
       ,px.qcinst_id
      ,px.sid "sid"
      ,px.serial#
      ,s.inst_id inst
      ,px.SERVER_GROUP
      ,px.SERVER_SET
      ,px.SERVER#
      ,s.STATUS
       --,s.SQL_HASH_VALUE, s.PREV_HASH_VALUE
       --,s.FIXED_TABLE_SEQUENCE,s. ROW_WAIT_BLOCK#, s.LAST_CALL_ET,
      ,s.seq#
      ,s.state
      ,s.wait_time
      ,s.event
      ,do.object_name wait_object
      ,s.p1text
      ,s.P1
      ,s.p2text
      ,s.p2
      ,wait_class
      ,s.blocking_session_status
      ,case when s.blocking_instance is NOT NULL then s.blocking_instance ||':'|| s.blocking_session end blocking_session
FROM  gv$session s
LEFT join dba_objects do on s.row_wait_obj# = do.object_id
RIGHT JOIN gv$px_session px on s.sid = px.sid and s.serial# = px.serial#
WHERE 1 = 1
AND px.QCsid = :sid --and px.Qcserial# = :serial#
ORDER  BY QCSid, server_group NULLS FIRST, server_set, server#

 

[admin]

Thanks, Theod,
From comparing mine with yours I found that in order to make plsql developer show the sid column in a detail tab one must alias the SID column and also enclose the alias in double quotes.

This works:
select sid "sid" from v$session;

These do not:
select sid sid from v$session;
select sid from v$session;

 

[Claus Pedersen]

It seems that PLD is case sensitive with regard of this query. An unquoted column name or column alias in Oracle is default always upper case (like everything else in Oracle). A quoted alias is case sensitive, i.e. "sid" will be sid in Oracle whereas sid or "SID" will be SID in Oracle.

 

[admin]

Thanks Claus,
Glad to have your input on this also. To clarify, the problem was not HOW pld was displaying the column header but that pld would not display the SID column at all when selected in a detail tab query. As it turns out, when SID is selected by a query in a detail tab, pld will not display the SID column at all unless a double quoted alias is used on that column.