--*******************************************************************************
--
--      System: Oracle Database Utility Procedure - DSC
--      Application: dsc_dba1.sql
--      Author: Rick Kupcunas
--      Date: 12/10/1994
--      Version: 1.0
--
--      Description
--      This package contains procedures that are of assistance for performing
--      miscellaneous database activities.  This package and package body must
--      be created under the user SYS in order to recognize some of the tables
--      being accessed.
--
--      Version  Date      Author        Description
--      1.0      12-10-94  Mark Cauwels  Original
--      1.1      01-06-95  Mark Cauwels  Added procedure source_display
--
--
--
--  Package: dsc_dba1
--
--      Procedure: table_access
--      Description: 
--              This procedure will display information about user sessions 
--              that are accessing a specified object.
--      Input parameters:
--           1) Owner of the object being accessed (varchar2)
--           2) Name of the object being accessed (varchar2)
--      Output:
--           1) Database user id of the user session accessing the object
--           2) Operating system id of the user session accessing the object
--           3) Session id of the user session accessing the object
--      Tables accessed:
--              v$access, v$session
--      Special considerations:
--              The user executing this procedure must "set serveroutput on"
--              in order to have the output data from the procedure displayed.
--      Example:
--              execute dsc_dba1.table_access('sys','dual');
--
--
--      Procedure: lock_waits
--      Description: 
--              This procedure will display information about user sessions
--              that exist in the database that are waiting on the release
--              of a lock on an object by another user session(s).
--      Input parameters:
--           None
--      Output:
--           1) Database user id, operating system id and session id of the 
--              user session holding the lock
--           2) Database user id, operating system id and session id of the 
--              user session(s) waiting for the release of the lock
--      Tables accessed:
--              v$session, dba_waiters
--      Special considerations:
--              The user executing this procedure must "set serveroutput on"
--              in order to have the output data from the procedure displayed.
--              This query is resource intensive, be aware...
--      Example:
--              execute dsc_dba1.lock_waits;
--
--
--      Procedure: source_display
--      Description: 
--              This procedure will display the source code (and relative line
--              numbers) for a users package, package body, procedure or 
--              function.  If any errors exist for the source code, the error
--              lines will be displayed at the relative position in the source
--              code listing where they apply.
--      Input parameters:
--           1) Name of the (source code) object to be displayed (varchar2)
--           2) Type of the (source code) object to be displayed (varchar2)
--              (e.g. PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION)
--      Output:
--           1) The source code of the object and any error messages for the
--              object if they exist.
--      Tables accessed:
--              dba_source, dba_errors, dual
--      Special considerations:
--              The user executing this procedure must "set serveroutput on"
--              in order to have the output data from the procedure displayed.
--              The user executing this procedure may only display the source
--              code and corresponding error messages for objects which they
--              are the owner.  In order to remain in sync with the manner in
--              which Oracle handles security for packages, package bodies, 
--              etc., this procedure was written to allow a user to view 
--              only his/her own source code and errors.
--      Example:
--              execute dsc_dba1.source_display('calc_pct','package body');
--
--
--      Procedure: sql_display
--      Description: 
--              This procedure will display the SQL corresponding to a user.
--      Input parameters:
--           1) Owner of the SQL code (this may be an Operating System 
--              process or a database user)
--           2) Type of value that has been supplied for parameter 1
--              (i.e. PS = Operating System process              
--                    DU = Database User)
--      Output:
--           1) The SQL that is currently documented for the owner.
--      Tables accessed:
--              v$process, v$session, v$sqltext
--      Special considerations:
--              The user executing this procedure must "set serveroutput on"
--              in order to have the output data from the procedure displayed.
--              The user SYS must be able to reference the view V$SQLTEXT
--              from the environment in which this procedure is executed.
--              (e.g. A bug exists in that when a database is initially set
--               up, the v$sqltext view can only be seen by the user SYS 
--               from within SQLDBA.  The following statements must be 
--               executed in order to allow the view to be seen from other
--               environments:
--                 create or replace view v_$sqltext as select * from v$sqltext;
--                 create public synonym v$sqltext for v_$sqltext;)
--      Example:
--              execute dsc_dba1.source_display('calc_pct','package body');
--
--
--
--*******************************************************************************

CREATE OR REPLACE PACKAGE dsc_dba1 AS
    PROCEDURE table_access (object_owner VARCHAR2, object_name VARCHAR2);
    PROCEDURE lock_waits ;
    PROCEDURE source_display (source_name VARCHAR2, source_type VARCHAR2);
    PROCEDURE sql_display (sql_owner VARCHAR2, param1_type VARCHAR2);
END dsc_dba1; 
/

CREATE OR REPLACE PACKAGE BODY dsc_dba1 AS

    PROCEDURE table_access (object_owner VARCHAR2, object_name VARCHAR2) AS
              tmp_username  VARCHAR2(30);
              tmp_osuser    VARCHAR2(15);
              tmp_sid       NUMBER;
              CURSOR table_access_username IS
                     SELECT a.username, a.osuser, a.sid
                       FROM v$session a, v$access b
                      WHERE a.sid = b.sid
                        AND b.owner  = upper(object_owner)
                        AND b.object = upper(object_name);
    BEGIN
        OPEN table_access_username;
        FETCH table_access_username INTO tmp_username, tmp_osuser, tmp_sid;
        IF table_access_username%FOUND
        THEN
             dbms_output.put_line('The following users found to be accessing object '||upper(object_owner)||'.'||upper(object_name)||':');
             LOOP 
                  dbms_output.put_line('Username: '||rpad(tmp_username,7,' ')||
                                       '  OS User: '||rpad(tmp_osuser,8,' ')||
                                       '  Sid: '||rpad(tmp_sid,3,' '));
                  FETCH table_access_username INTO tmp_username, tmp_osuser, 
                                                   tmp_sid;
                  EXIT WHEN table_access_username%NOTFOUND;
             END LOOP;
        ELSE
             dbms_output.put_line('No users found to be accessing object '||upper(object_owner)||'.'||upper(object_name));
        END IF;
    END;

    PROCEDURE lock_waits AS
              tmp_hld_holder_sid       NUMBER;
              tmp_waiter_username      VARCHAR2(30);
              tmp_waiter_osuser        VARCHAR2(15);
              tmp_waiter_sid           NUMBER;
              tmp_holder_username      VARCHAR2(30);
              tmp_holder_osuser        VARCHAR2(15);
              tmp_holder_sid           NUMBER;
              CURSOR lock_waits IS
                     SELECT w.username, w.osuser, w.sid,
                            h.username, h.osuser, h.sid
                       FROM v$session w, v$session h, dba_waiters c
                      WHERE c.waiting_session = w.sid
                        AND c.holding_session = h.sid
                      ORDER BY c.waiting_session, c.holding_session;
    BEGIN
        OPEN lock_waits;
        FETCH lock_waits INTO tmp_waiter_username, tmp_waiter_osuser, 
                              tmp_waiter_sid, tmp_holder_username,  
                              tmp_holder_osuser, tmp_holder_sid;
        tmp_hld_holder_sid := -1;
        IF lock_waits%FOUND
        THEN
             dbms_output.put_line('The following locks were found: ');
             LOOP 
                  IF tmp_hld_holder_sid = tmp_holder_sid
                  THEN
                       dbms_output.put_line('*     Waiting username: '||
                                            rpad(tmp_waiter_username,7,' ')||
                                            '  OS User: '||
                                            rpad(tmp_waiter_osuser,8,' ')||
                                            '  Sid: '||
                                            rpad(tmp_waiter_sid,3,' '));
                  ELSE
                       tmp_hld_holder_sid := tmp_holder_sid;
                       dbms_output.put_line('Lock username: '||
                                            rpad(tmp_holder_username,7,' ')||
                                            '  OS User: '||
                                            rpad(tmp_holder_osuser,8,' ')||
                                            '  Sid: '||
                                            rpad(tmp_holder_sid,3,' '));
                       dbms_output.put_line('*     Waiting username: '||
                                            rpad(tmp_waiter_username,7,' ')||
                                            '  OS User: '||
                                            rpad(tmp_waiter_osuser,8,' ')||
                                            '  Sid: '||
                                            rpad(tmp_waiter_sid,3,' '));
                  END IF;
                  FETCH lock_waits INTO tmp_waiter_username, tmp_waiter_osuser, 
                                        tmp_waiter_sid, tmp_holder_username,  
                                        tmp_holder_osuser, tmp_holder_sid;
                  EXIT WHEN lock_waits%NOTFOUND;
             END LOOP;
        ELSE
             dbms_output.put_line('No locks were found.');
        END IF;
    END;  

    PROCEDURE source_display (source_name VARCHAR2, source_type VARCHAR2) AS
              tmp_source_line          NUMBER;
              tmp_line_type            VARCHAR2(6);
              tmp_source_text          VARCHAR2(200);
              tmp_user_id              VARCHAR2(30);
              CURSOR source_display_user IS
                     SELECT user
                       FROM dual;
              CURSOR source_display_text IS
                     SELECT a.line, 'SOURCE', substr(a.text,1,200)
                       FROM sys.dba_source a
                      WHERE a.owner  = tmp_user_id
                        AND a.name   = upper(source_name)
                        AND a.type   = upper(source_type)
                     UNION
                     SELECT b.line, 'ERROR', substr(b.text,1,200)
                       FROM sys.dba_errors b
                      WHERE b.owner  = tmp_user_id
                        AND b.name   = upper(source_name)
                        AND b.type   = upper(source_type)
                      ORDER BY 1 asc, 2 desc;
    BEGIN
        OPEN source_display_user;
        FETCH source_display_user INTO tmp_user_id;
        CLOSE source_display_user;
        OPEN source_display_text;
        FETCH source_display_text INTO tmp_source_line, tmp_line_type,
                                       tmp_source_text;
        IF source_display_text%FOUND
        THEN
             dbms_output.enable(1000000);
             LOOP 
                IF tmp_line_type = 'SOURCE'
                THEN
                     dbms_output.put_line(tmp_source_line||'  '||
                                          tmp_source_text);
                ELSE 
                     dbms_output.put_line('** ERROR **  '||tmp_source_text);
                END IF;
                FETCH source_display_text INTO tmp_source_line, tmp_line_type,  
                                               tmp_source_text;
                EXIT WHEN source_display_text%NOTFOUND;
             END LOOP;
        ELSE
             dbms_output.put_line('No source found for a '||source_type||' named '||tmp_user_id||'.'||source_name||'.');
        END IF;
        CLOSE source_display_text;
    END;

    PROCEDURE sql_display (sql_owner VARCHAR2, param1_type VARCHAR2) AS
              tmp_sql_text  VARCHAR2(64);
              tmp_osuser    VARCHAR2(15);
              tmp_username  VARCHAR2(30);
              tmp_sid       NUMBER;
              tmp_hld_sid   NUMBER := 0;
              CURSOR sql_DU_cursor IS
                     SELECT b.osuser, b.username, b.sid, a.sql_text
                       FROM v$sqltext a, v$session b
                      WHERE a.address    = b.sql_address
                        AND a.hash_value = b.sql_hash_value
                        AND b.username   = upper(sql_owner)
                      ORDER BY b.sid, a.piece;
              CURSOR sql_PS_cursor IS
                     SELECT b.osuser, b.username, b.sid, a.sql_text
                       FROM v$sqltext a, v$session b, v$process c
                      WHERE a.address    = b.sql_address
                        AND a.hash_value = b.sql_hash_value
                        AND b.paddr      = c.addr
                        AND c.spid       = sql_owner
                      ORDER BY b.sid, a.piece;
    BEGIN
        dbms_output.enable(1000000);
        IF upper(param1_type) = 'DU'
        THEN
            OPEN sql_DU_cursor;
            FETCH sql_DU_cursor INTO tmp_osuser, tmp_username, tmp_sid, tmp_sql_text;
            IF sql_DU_cursor%FOUND
            THEN
                 LOOP 
                     IF tmp_hld_sid != tmp_sid 
                     THEN
                         IF tmp_hld_sid != 0
                         THEN
                             dbms_output.put_line('**********************************************************************');
                         END IF;
                         dbms_output.put_line('*');
                         dbms_output.put_line('The following SQL text has been found for . . .');
                         dbms_output.put_line('OSUser: '||tmp_osuser||'   Database User: '||tmp_username||'   Sid: '||tmp_sid);
                         dbms_output.put_line('**********************************************************************');
                     END IF;
                     dbms_output.put_line(tmp_sql_text);
                     tmp_hld_sid := tmp_sid;
                     FETCH sql_DU_cursor INTO tmp_osuser, tmp_username, tmp_sid, tmp_sql_text;
                     EXIT WHEN sql_DU_cursor%NOTFOUND;
                 END LOOP;
                 dbms_output.put_line('**********************************************************************');
            ELSE
                 dbms_output.put_line('No SQL text found for database user '||upper(sql_owner)||'.');
            END IF;
        ELSIF upper(param1_type) = 'PS'
        THEN
            OPEN sql_PS_cursor;
            FETCH sql_PS_cursor INTO tmp_osuser, tmp_username, tmp_sid, tmp_sql_text;
            IF sql_PS_cursor%FOUND
            THEN
                 dbms_output.put_line('The following SQL text has been found for . . .');
                 dbms_output.put_line('OSUser: '||tmp_osuser||'   Database User: '||tmp_username||'   Sid: '||tmp_sid);
                 dbms_output.put_line('**********************************************************************');
                 LOOP 
                      dbms_output.put_line(tmp_sql_text);
                      FETCH sql_PS_cursor INTO tmp_osuser, tmp_username, tmp_sid, tmp_sql_text;
                      EXIT WHEN sql_PS_cursor%NOTFOUND;
                 END LOOP;
                 dbms_output.put_line('**********************************************************************');
            ELSE
                 dbms_output.put_line('No SQL text found for OS process '||upper(sql_owner)||'.');
            END IF;
        ELSE 
            dbms_output.put_line('* PROCEDURE USAGE');
            dbms_output.put_line('*    Parameter 1             Parameter 2');
            dbms_output.put_line('*    -----------             -----------');
            dbms_output.put_line('*    OS Process              Value "PS" ');
            dbms_output.put_line('*    Database user           Value "DU" ');
        END IF;
    END;

END dsc_dba1;
/

