[Update 2012-11] Bug 7648406: "PSEUDO cursor is as follows. table______" [Update 2010-06] Oracle finally published a note on pseudo cursors (I wish it was partly prodded by my SR a year ago!). According to https://supporthtml.oracle.com/ep/faces/secure/km/DocumentDisplay.jspx?id=1298471.1 the way to query pseudo cursors is select Pseudo_cursor, sql_id,obj_id hex_obj_id ,obj# object_id, u.name owner, o.name object_name from (select distinct KGLNAOBJ Pseudo_cursor,kglobt03 sql_id ,substr(KGLNAOBJ ,instr(KGLNAOBJ,'_',1,3)+1 ,instr(KGLNAOBJ,'_',1,4)-instr(KGLNAOBJ,'_',1,3)-1) obj_id ,(case when replace(translate(substr(upper(KGLNAOBJ) ,instr(KGLNAOBJ,'_',1,3)+1 ,instr(KGLNAOBJ,'_',1,4) -instr(KGLNAOBJ,'_',1,3)-1) ,'0123456789ABCDEF','................') ,'.') is null then 'Y' else 'N' end) is_safe_to_compare from x$kglob) k , obj$ o, user$ u where obj#=decode(is_safe_to_compare,'Y',to_number(obj_id,'xxxxxxxxxx'),0) and o.owner#=u.user#; Everything else is history! Seriously, since pseudo cursors directly read the object (LOB etc), there's no real SQL. The official "answer" shown above just tells you which object is being read; that's what we were able to do earlier anyway. But the answer at least tells us it's futile or even incorrect to find the "real" SQL associated with a pseudo cursor. [Update 2012-11] Just because a SQL can't be found in v$sql* views doesn't mean it's this type of pseudo cursor. If it's found in v$open_cursor and v$db_object_cache to have text in the form 'table_a_b_c_d_e_f', then it is. Otherwise, look further. For example, a DDL may not exist in those views. But it must be in x$kglob. Find it by select * from x$kglob where kglobt03 = ''. ******** ignore anything below ******** [Update 2009-12] The SQLs in the article below need to be reviewed and possibly removed. They're useless. A more reliable way to associate such pseudo cursor with real SQL is based on their first_load_time (i.e. first_load_time in v$sql and kglnatim in x$kglob). set pagesize 1000 linesize 120 long 10000 longc 100 select sql_id, sql_fulltext from v$sql where first_load_time in (select distinct to_char(kglnatim,'yyyy-mm-dd/hh24:mi:ss') from x$kglob where kglobt03 = '') order by sql_id; select * from dba_objects where object_id = to_number('part c of table_a_b_c_d_e_f','xxxxxxxx'); desc --Make sure the output of the first SQL uses the LOB column(s) of the table Bug 5500044 "table_4_d_186748_0_0_0" is a special internal cursor for getting permissions on object id 0x186748 = 1599304 decimal. This now wants to check privileges of the LOB using the table_x_x_xxxx_x_x cursor What are the table_a_b_c_d_e_f SQLs? Sometimes when you join v$session.sql_id (or hash_value) to v$sql% family views, you don't see the SQL. But you find the SQL in v$open_cursor. Unfortunately, the SQL looks like table_a_b_c_d_e_f, for example, table_4_200_4139_0_0_0. I've never figured out why Oracle needs to create these pseudo cursors. I even opened an SR ask this (7142902.992, for those who can see), but it turned out to be a waste of time. On further research, you find that common to all these pseudo SQLs is that the c component here is the object_id in hex for a table that has LOB or object type column(s), and the a component probably refers to the SQL command type, 4 for select, etc. This encouraging news helps you narrow down the actual dependent SQL that created these mysterious pseudo SQLs, because you can search for v$sql% views for sql_text like '%THETABLE%', where THETABLE has the said object_id. But that's far from satisfactory. What if multiple SQLs reference the same LOB-containing SQL? How do you know which one is actually run by the session? This short note explains my preliminary findings. The heart of the finding is about x$kgltr (kernel generic, librarycache, translation), a fixed table not exposed to any v$ view. The three columns of this table, kglhdadr, kgltrorg, and kgltrfnl, represent the handle address of the alias (not sure if this is a proper name), the address of the referent, and the address of the final referent in case of multiple reference translation. Basically, the pseudo SQL table_a_b_c_d_e_f will be the referent or final referent (maybe just final referent but I'll verify later), and the alias is the actual SQL that indirectly created the cryptic SQL. With this in mind, the query below can help you find the actual SQL select a.kglnaobj -- distinct may be needed because v$object_dependency may have multiple entries from x$kglob a, x$kgltr b, v$object_dependency c, dba_objects d where a.kglhdadr = b.kglhdadr and (b.kgltrorg = c.to_address or b.kgltrfnl = c.to_address) -- just b.kgltrfnl... may be enough and c.to_name = d.object_name and c.to_owner = d.owner and d.object_id = to_number('&hexobjid','xxxxxxxx') -- hexobjid is e.g. 4139 / Well, not all SQLs can be found this way, for various reasons, the real, dependent, SQL may have been aged out, or my finding is imperfect. I actually wanted to see if I can enumerate all the pseudo SQLs to find their actual SQLs. The following code with x as ( select /*+ materialize */ to_number(replace(regexp_substr(e.name,'_[^_]+_',1,2), '_'),'xxxxxxxx') oid from v$db_object_cache e where name like 'table%') select a.kglnaobj from x$kglob a, x$kgltr b, v$object_dependency c, dba_objects d, x where a.kglhdadr = b.kglhdadr and (b.kgltrfnl = c.to_address or b.kgltrorg = c.to_address) and c.to_name = d.object_name and c.to_owner = d.owner and d.object_id = x.oid; or with x as ( select /*+ materialize */ d.object_name, d.owner from v$db_object_cache e, dba_objects d where e.name like 'table%' and d.object_id = to_number(replace(regexp_substr(e.name,'_[^_]+_',1,2), '_'),'xxxxxxxx') and rownum < 11) select a.kglnaobj from x$kglob a, x$kgltr b, v$object_dependency c, x where a.kglhdadr = b.kglhdadr and (b.kgltrfnl = c.to_address or b.kgltrorg = c.to_address) and c.to_name = x.object_name and c.to_owner = x.owner; takes too long to run. When I have time, I'll come back to this. Yong Huang October 2008