Trying to combine 2 scripts

From: Vince Laurent <vincelaurent_at_sbcglobal.net>
Date: Tue, 25 May 2004 14:47:17 GMT
Message-ID: <2sm6b0d59ci8uks290qt5ml4m6ffu8d5ra@4ax.com>

I got this nice script off of DBAToolZ and it does 1/2 of what I need:

set verify off
set serveroutput on size 1000000
set linesize 132
DECLARE
   CURSOR lcobj_cur IS

      select session_id


      ,      oracle_username

      ,      os_user_name

      ,      object_id obj_id

      ,      locked_mode

      from   V$LOCKED_OBJECT
      where  oracle_username!='BMC' ;

   CURSOR obj_cur(p_obj_id IN NUMBER) IS
      select owner||'.'||object_name object_name


      ,      object_type

      from dba_objects
      where object_id = p_obj_id;

BEGIN

   DBMS_OUTPUT.PUT_LINE(RPAD('Sid',5)||
                        RPAD('OS-User',10)||
                        RPAD('Owner.Object Name',30)||
                        RPAD('Object Type',35));

   DBMS_OUTPUT.PUT_LINE(RPAD('-',1,'-')||
                        RPAD('-',6,'-')||
                        RPAD('-',41,'-')||
                        RPAD('-',31,'-'));

   FOR lcobj IN lcobj_cur
   LOOP

      FOR obj IN obj_cur(lcobj.obj_id)
      LOOP
         DBMS_OUTPUT.PUT_LINE(RPAD(lcobj.session_id,5)||
                              RPAD(lcobj.oracle_username,10)||
                              RPAD(lcobj.os_user_name,10)||
                              RPAD(obj.object_name,45)||
                              RPAD(obj.object_type,35));
      END LOOP;

   END LOOP;
END;
/

This script does the other half:

col sql_text format a80 word_wrapped

select sql_text
from v$sqltext
where address = (select sql_address from v$session where sid=524) and hash_value = (select sql_hash_value from v$session where sid=524)
order by piece

/

So... when I run the first I get:
Sid OS-User Owner.Object Name Object Type

---

226  SISIUSER  002s06  SISI.MODULE_USAGE   TABLE
240  SISIUSER  113-s06 SISI.MODULE_USAGE   TABLE
459  SISIUSER  007s05  SISI.MODULE_USAGE   TABLE

But I would like to take the SID and pass it to part 2. My SQL skills are minimal at best. I am unsure how to approah this in SQL. I can program loops in shell scripts, some langauages and such but unsure how to approach it in SQL. An example would be nice.

Thanks!
Vince Received on Tue May 25 2004 - 09:47:17 CDT