Rem Filename   : sidcursors.sql
Rem 
Rem Parameters : &1 = the sid of the session for which all open cursors have to be listed.
Rem
Rem Author     :
Rem RDBMS      : Oracle V8.x.x
Rem
Rem Modification history
Rem --------------------
Rem 07-feb-2002  Initial creation.
Rem
Rem =================================================================================================
Rem
Rem Description : Lists all open cursors of the session which the given sid.
Rem
Rem               Username       : The user that executed the cursor.
Rem               Sid            : The id of the session that launched the statement.
Rem               Cursor         : The text of the cursor statement.
Rem
Rem Sample output
Rem -------------
Rem SYSTEM<GLORYETL>  @sidcursors 18
Rem
Rem Username          Sid Cursor
Rem --------------- ----- ---------------------------------------------------------------------------
Rem GLORY              18 select h.HIERARCHY_CODE_1, h.HIERARCHY_NAME_1,      h.HIERARCHY_CODE_2,
Rem                       h.HIERARCHY_NAME_2,      h.HIERARCHY_CODE_3, h.HIERARCHY_NAME_3,
Rem                       s.PROD_TYPE_DESC,     s.TRANS_AMT from vw_suppliers s, VW_CLIHIER_WITHEMPS
Rem                       h where s.LINK_TRANS_HIER = h.ORG_ID and s.HIERARCHY_NAME = 'UK client
Rem                       structure'  and s.HIERARCHY_TYPE = 'LOC' and h.client_master_code = '33'
Rem                       and month_num = 8 and year = 1999
Rem 
Rem GLORY              18 Select /*+ CHOOSE */ object_name ObjectName, object_type ObjectType  from
Rem                       user_objects where object_name = :OBJNAME order by 2
Rem 
Rem GLORY              18 select /*+ CHOOSE */ a1.constraint_name,   c1.column_name, c1.position
Rem                       from  DBA_CONS_COLUMNS C1, DBA_CONSTRAINTS A1  where A1.table_name =
Rem                       C1.Table_name  And  A1.constraint_name = C1.Constraint_Name  and
Rem                       A1.constraint_type = 'P'  and A1.table_name =:TabName  and C1.owner =
Rem                       A1.Owner  and A1.owner =:Owner  ORDER BY 3
Rem ...
Rem 
Rem =================================================================================================

SET LONG      25000
SET ARRAYSIZE 1
SET TRIMSPOOL ON
SET PAGESIZE  9999
SET VERIFY    OFF

COLUMN UserName FORMAT A15   HEADING "Username"
COLUMN Sid      FORMAT 9999  HEADING "Sid"
COLUMN cursor_type FORMAT A15   HEADING "cursor_type"
COLUMN SqlText  FORMAT A75   HEADING "Cursor"  WORD_WRAP

SELECT oc.user_name   "UserName",
       oc.sid         "Sid",
	   oc.hash_value,
	   --oc.cursor_type,
       (select s.sql_text from v$sql s where  oc.address     = s.address AND oc.hash_value  = s.hash_value and rownum = 1)   "SqlText"
  FROM v$open_cursor  oc
 WHERE oc.sid         = &1
 ORDER BY oc.sid;
 
SET ARRAYSIZE  20 
SET LONG       200