RE: SQL Text

From: Toepke, Kevin M <ktoepke_at_CMS.CENDANT.COM>
Date: Wed, 14 Feb 2001 05:13:32 -0800
Message-ID: <F001.002B3B4A.20010214042551@fatcity.com>

Sanjay:  

I use the folowing PL/SQL block to check on what is in the SGA. It ignores internal processes and the current session.  

Hope this helps
Kevin  

DECLARE
    CURSOR c_session IS

        SELECT ses.sid, ses.program, ses.sql_address, ses.username, ses.osuser

              ,ses.serial#, pro.spid
        FROM   v$session ses
              ,v$process pro
        WHERE  ses.username IS NOT NULL
        AND    ses.username NOT IN ('DBSNMP', 'sys', 'SYS', 'system',
'SYSTEM')
        AND   pro.addr     = ses.paddr
        ORDER BY ses.sid, ses.program, ses.sql_address;
 
    CURSOR c_sql_text    (
        is_sql_address          IN v$session.sql_address%TYPE
        ) IS
        SELECT txt.sql_text
        FROM   v$sqltext_with_newlines txt
        WHERE  txt.address  = is_sql_address
        ORDER BY txt.piece;
 
    ls_stmt                     VARCHAR2(4000);
 
    PROCEDURE put_line (
        is_string               IN VARCHAR2
        ) IS
        ln_len                  NUMBER(4) := LENGTH(is_string);
        ln_index                NUMBER(4);
        ln_comma                NUMBER(4);
        ln_space                NUMBER(4);
        ln_oparn                NUMBER(4);
        ln_cparn                NUMBER(4);
        ln_equal                NUMBER(4);
        ls_start                VARCHAR2(4000);
        ls_end                  VARCHAR2(4000);
    BEGIN
        IF (ln_len <= 80) THEN
            IF (SUBSTR(is_string, ln_len, 1) = CHR(10)) THEN
                DBMS_OUTPUT.PUT(is_string);
            ELSE
                DBMS_OUTPUT.PUT_LINE(is_string);
            END IF;
        ELSE
            ln_index := INSTR(is_string, CHR(10));
            IF (ln_index != 0) THEN
                ls_start := SUBSTR(is_string, 1, ln_index - 1);
                ls_end   := SUBSTR(is_string, ln_index + 1, ln_len);
                put_line(ls_start);
                put_line(ls_end);
            ELSE
                ln_space := INSTR(is_string, ' ', 79-ln_len, 1);
                ln_comma := INSTR(is_string, ',', 79-ln_len, 1);
                ln_oparn := INSTR(is_string, '(', 79-ln_len, 1);
                ln_cparn := INSTR(is_string, ')', 79-ln_len, 1);
                ln_equal := INSTR(is_string, '=', 79-ln_len, 1);
 
                ln_index := GREATEST(ln_space, ln_comma, ln_oparn, ln_cparn
                                    ,ln_equal);
                IF (ln_index != 0) THEN
                    ls_start := SUBSTR(is_string, 1, ln_index);
                    ls_end   := SUBSTR(is_string, ln_index + 1, ln_len);
                    put_line(ls_start);
                    put_line(ls_end);
                ELSE
                    DBMS_OUTPUT.PUT_LINE('....' || SUBSTR(is_string, 1,
70));
                END IF;
            END IF;
        END IF;

    END;
BEGIN
    FOR r_session IN c_session LOOP

        ls_stmt := NULL;  

        DBMS_OUTPUT.PUT('SID, SERIAL#, USER, OSUSER, SPID: ');
        DBMS_OUTPUT.PUT_LINE(r_session.sid        || ', ' ||
                             r_session.serial#    || ', ' ||
                             r_session.username   || ', ' ||
                             r_session.osuser     || ', ' ||
                             r_session.spid       );
        DBMS_OUTPUT.PUT_LINE(RPAD('_', 79, '_'));
        FOR r_sql_text in c_sql_text(r_session.sql_address) LOOP
            IF (ls_stmt IS NULL) THEN
                ls_stmt := r_sql_text.sql_text;
            ELSE
                ls_stmt := ls_stmt || r_sql_text.sql_text;
            END IF;
        END LOOP;
 
        put_line(ls_stmt);
        put_line('.');

    END LOOP;
END;
/

-----Original Message-----
Sent: Tuesday, February 13, 2001 6:31 PM To: Multiple recipients of list ORACLE-L

hi,  

I want to see the latest SQL Statements that I sent to Database. I know that I can make use of V$sqlarea but it restricts only to first 1000 characters. But my query is more than that.  

Any help.  

Sanjay

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Toepke, Kevin M
  INET: ktoepke_at_CMS.CENDANT.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Feb 14 2001 - 07:13:32 CST