JL Computer Consultancy

Turning a dump into an SQL statement (only tested on Unix)

June 1998


One of the spot checks I make on a troublesome system is to spot a busy user, and dump out the full text of the SQL statement they are running from the V$SQLTEXT dynamic view.

This view has a column sql_text which is defined as a varchar2(64), and reporting from this view leaves you with a completely butchered, illegible and unusable block of text. Given the right setup, the following quick and dirty C program will read this block of text, and turn it into something which is fairly readable, and which will probably execute. It does make mistakes, but 98% of simple SQL will be reformatted correctly, although the last couple of lines tend to get rather long.

There are 4 parts to the note: an SQL script to demonstrate dumping from v$sqltext, a sample dump, the C program, and the result of running the C program against the dump.

To compile the C program, try:

 

cc stripsql.c -o stripsql

To use the C program try:

 

stripsql <get_text.lst >target.sql

Back to Main Index of Topics


rem
rem     Script:        get_text.sql
rem     Author:        J.P.Lewis
rem     Last Update:   01-June-1998
rem     Purpose:       Get recent SQL Text for an Oracle Username
rem
rem     Input variables:
rem            Oracle Username of the user
rem     Notes:
rem            The linesize of 64 is MOST important
rem            You really shouldn't hit v$sqltext like this
rem
set pagesize 0
set linesize 64
 
set feedback off
set trimspool off
set verify off
clear columns
clear breaks
define m_user=&m_username
spool get_text
select sql_text from v$sqltext
where hash_value in (
        select sql_hash_value from V$session
        where username = upper('&m_user')
        union
        select prev_hash_value from v$session
        where username = upper('&m_user')
        )
order by hash_value,piece
;
spool off
set linesize 80

Back to Main Index of Topics


The raw dump file (from a parallel query slave).
SELECT /*+ ROWID(A1) */ A1."SUPP_NUMBER",A1."BITM_NUMB_VRNT",A1.
"BITM_NUMB_PACK",A1."STIT_NUMB",A1."STIT_DESC",A1."SIZE_LONG_DES
C",A1."USER_INTF_SRCE",A1."DATE_INAC",A1."ORGU_FNAM",A1."ORGU_NU
MB",A1."OBUS_TCOD",A1."OU_REF",A1."USER_INTF_SUPP_NUMB",A1."USER
_INTF_ORGU_FNAM" FROM "REFDATA"."T2SSSV" A1 WHERE ROWID BETWEEN 
:B1 AND :B2                                                     

Back to Main Index of Topics


The Output of the stripping program
SELECT /*+ ROWID(A1) */ A1."SUPP_NUMBER",
               A1."BITM_NUMB_VRNT",
               A1."BITM_NUMB_PACK",
               A1."STIT_NUMB",
               A1."STIT_DESC",
               A1."SIZE_LONG_DESC",
               A1."USER_INTF_SRCE",
               A1."DATE_INAC",
               A1."ORGU_FNAM",
               A1."ORGU_NUMB",
               A1."OBUS_TCOD",
               A1."OU_REF",
               A1."USER_INTF_SUPP_NUMB",
               A1."USER_INTF_ORGU_FNAM" FROM "REFDATA"."T2SSSV" A1 WHERE ROWID BETWEEN :B1 AND :B2                                                     ;

Back to Main Index of Topics


/*
        File:          stripsql.c
        Author:        Jonathan Lewis
        Dated:         Jun 1997
        Purpose:       Tidy up the output from v$sql_text
        Notes:
        ------
        Report a single SQL statement from v$sqltext.
        Set the linesize to 64 before doing it, and pagesize 0
        This program reads the resulting flat file
                converts commas into {comma, return, tab, tab}
               eliminates all other {return} characters
               sticks a ';' on the end
        This tends to makes the SQL text almost readable, and
        ready for becoming the target.sql in explain7/8.sql.
        Sometimes spurious spaces appear or disappear, and the
        lines after the WHERE and FROM can be a little long.
        Compiling:
               cc stripsql.c -o stripsql 
        Usage:
               stripsql <get_text.lst >target.sql
*/
#include <stdio.h>
main () {
        int c;
        while (! feof(stdin)) {
               c = getchar();
                if (!feof(stdin)) {
                       if (c != '\n')
                               putchar(c);
                       if (c == ',')  {
                               putchar('\n');
                               putchar('\t');
                               putchar('\t');
                       }
               }
               else {
                       putchar(';');
               }
        }
}

Back to Main Index of Topics