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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
