The Jonathan Lewis Pages

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

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.

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