Database Reference
In-Depth Information
Capturing SQL Statements with Formatting Preserved
We have already come a long way, yet we could go one step further by enabling Statspack to
save SQL statements with line breaks and tab stops preserved. If you don't have any apprehen-
sions about changing a single line in the package body of the
STATSPACK
package, please follow suit.
Remember,
V$SQLTEXT_WITH_NEWLINES
preserves line breaks and tab stops, whereas
V$SQLTEXT
,
which is queried by Statspack, does not. First of all, we need to authorize the user
PERFSTAT
to
access the dynamic performance view
V$SQLTEXT_WITH_NEWLINES
.
SQL> CONNECT / AS SYSDBA
SQL> GRANT SELECT ON v_$sqltext_with_newlines TO PERFSTAT;
I used the Revision Control System
7
(RCS) to save the original version of
spcpkg.sql
, which
contains the
STATSPACK
package body, as version 1.1. Since
V$SQLTEXT
is referenced only once in
this file, it's sufficient to change a single line as shown here (the line number is from an Oracle10
g
version of
spcpkg.sql
):
$ rcsdiff spcpkg.sql
=================================================================
RCS file: RCS/spcpkg.sql,v
retrieving revision 1.1
diff -r1.1 spcpkg.sql
4282c4282
< , v$sqltext vst
---
> , v$sqltext_with_newlines vst
Please note that the change made only has an effect on new SQL statements captured
by Statspack. Any statement with a hash value already present in
STATS$SQLTEXT.(OLD_)
HASH_VALUE
will not be captured again. To recapture existing statements, export the schema
PERFSTAT
to save past snapshots and run
sptrunc.sql
to purge all snapshots. This removes all data
from
STATS$SQLTEXT
. Don't worry, Statspack configuration data in the tables
STATS$STATSPACK_
PARAMETER
and
STATS$IDLE_EVENT
is preserved in spite of the warning that appears when running
sptrunc.sql
and claims that it “removes ALL data from Statspack tables”.
After editing
spcpkg.sql
, recreating the package
STATSPACK
, re-running the application,
and capturing its SQL statements with Statspack, we can finally view the statement with all
formatting fully preserved.
$ sqlplus -s system/secret
@sp_sqltext_get.sql 14
55318379
SELECT emp.last_name, emp.first_name, j.job_title, d.department_name, l.city,
l.state_province, l.postal_code, l.street_address, emp.email,
emp.phone_number, emp.hire_date, emp.salary, mgr.last_name
FROM hr.employees emp, hr.employees mgr, hr.departments d, hr.locations l, hr.jobs j
WHERE emp.manager_id=mgr.employee_id
AND emp.department_id=d.department_id
AND d.location_id=l.location_id
AND emp.job_id=j.job_id
7.
RCS is open source software. Precompiled executables for Windows ship with Cygwin and most Linux
distributions. The command
rcsdiff
displays differences between releases of a file.
