Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> CLOBS AND Semicolons
Most of our Oracle databases are not that busy. I decided a week ago to start capturing individual SQL statements. I run
a korn shell script every minute to do so. The script invokes the following SQL
insert into oracle.statement_info (SID, USERNAME, STATUS, OSUSER, MACHINE, RUNTIME, ADDRESS, HASH_VALUE, SQL_TEXT, PIECE, RUN#, COMMAND_TYPE) SELECT A.SID, A.USERNAME, A.STATUS, A.OSUSER, A.MACHINE, SYSDATE, B.ADDRESS, B.HASH_VALUE, B.SQL_TEXT, B.PIECE, :V_RUN#, B.COMMAND_TYPE FROM V$SESSION A, V$SQLTEXT B WHERE A.SQL_ADDRESS = B.ADDRESS AND A.SQL_HASH_VALUE = B.HASH_VALUE and command_type <= 7AND (A.LAST_CALL_ET < 120 or a.status = 'ACTIVE')
create or replace package slac_stats_pkg is
procedure glom_statement;
end slac_stats_pkg;
/
create or replace package body slac_stats_pkg as
procedure glom_statement is
psid number; prun# number(10,0); paddress raw(4); phash_value number; pstatus varchar2(8); posuser varchar2(30); pmachine varchar2(64); pruntime date; pcommand_type number; ppiece number; pusername varchar2(30); statement_line varchar2(64); statement_buffer varchar2(32760); statement_buffer_length number; psql_text clob; offset number; maxrun# number(10,0); cursor get_statement is select piece, sql_text from statement_info where address = paddress and hash_value = phash_value and run# = prun# order by run#, address, hash_value, piece; cursor get_statement_metadata is select distinct sid, username, status, osuser, machine, runtime, address, hash_value, run#, command_type from oracle.statement_info where run# <= maxrun# order by run#, address, hash_value; begin select max(run#) into maxrun# from statement_info; open get_statement_metadata; loop fetch get_statement_metadata into psid, pusername, pstatus, posuser, pmachine, pruntime, paddress, phash_value, prun#, pcommand_type; exit when get_statement_metadata%notfound; open get_statement; loop fetch get_statement into ppiece, statement_line; exit when get_statement%notfound; statement_buffer := concat(statement_buffer, statement_line); end loop; close get_statement; statement_buffer := concat(statement_buffer,';'); statement_buffer_length := length(statement_buffer); offset := 1; insert into statement_info_temp values (slac_statement_seq.nextval, psid, pusername, pstatus, posuser, pmachine, pruntime,paddress, phash_value, prun#, pcommand_type, empty_clob()) return sql_text into psql_text; dbms_lob.write(psql_text, statement_buffer_length, offset, statement_buffer); commit; statement_buffer := null; end loop; close get_statement_metadata;
SQL> host cat statements_to_explain.sql
SET PAGESIZE 0
COLUMN STANZA FORMAT A79 WORD_WRAPPED;
SET TERMOUT OFF
SET FEEDBACK OFF
set scan off
set verify off
set arraysize 3
whenever sqlerror continue
exec slac_stats_pkg.glom_statement;
set long 16384
set arraysize 3
SPOOL explainthem.sql
Select
'alter session set current_schema = ' ||nvl(username, 'SYS') ||';' ||CHR(10) || 'EXPLAIN PLAN' ||CHR(10) || 'SET STATEMENT_ID = '''||to_char(statement_id)||'''' ||chr(10)||'FOR' ||CHR(10) ||
alter session set current_schema = SYS;
EXPLAIN PLAN
SET STATEMENT_ID = '168361'
FOR
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts#
and t.dflextpct!=0 and t.bitmapped=0 ;
. . .My question is simply why does DBMS_LOB.SUBSTR(SQL_TEXT, DBMS_LOB.GETLENGTH(SQL_TEXT), 1) return the statement with the ending semicolon, but when I simply select "SQL_TEXT" from the table the terminating semicolon is not seen; i.e.,
----------------------------------------------------------------------------------------------------------------
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
Ian MacGregor
Stanford Linear Acclerator Center
ian_at_SLAC.Stanford.edu
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: MacGregor, Ian A. INET: ian_at_SLAC.Stanford.EDU 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 Fri May 31 2002 - 11:23:42 CDT
![]() |
![]() |