Database Reference
In-Depth Information
OLD_HASH_VALUE NOT NULL NUMBER
TEXT_SUBSET NOT NULL VARCHAR2(31)
PIECE NOT NULL NUMBER
SQL_ID VARCHAR2(13)
SQL_TEXT VARCHAR2(64)
ADDRESS RAW(8)
COMMAND_TYPE NUMBER
LAST_SNAP_ID NUMBER
This explains why it is impossible to obtain statement texts with original formatting in
Statspack reports. The table
STATS$SQLTEXT
may be queried as follows:
SQL> SELECT sql_text
FROM perfstat.stats$sqltext
WHERE old_hash_value=1455318379
ORDER BY piece;
SQL_TEXT
----------------------------------------------------------------
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.las
t_name FROM employees emp, employees mgr, departments d, locatio
ns l, jobs j WHERE emp.manager_id=mgr.employee_id AND emp.depart
ment_id=d.department_id AND d.location_id=l.location_id AND emp.
job_id=j.job_id
7 rows selected.
Due to the maximum piece length, there is no way to remove the forced line break after
64 characters. There is no SQL*Plus formatting option that glues consecutive lines together.
However, with some background in PL/SQL programming, creating the glue to solve the issue
at hand is straightforward.
The algorithm is as follows:
1.
Create an abstract data type that holds the hash value of the statement and the statement
itself as a
CLOB
. Remember, a single
CLOB
can hold at least 2 GB, whereas
VARCHAR2
columns
are limited to 4000 bytes.
Create a pipelined table function that selects rows from
STATS$SQLTEXT
piece by piece.
2.
Append each piece to a temporary
CLOB
using
DBMS_LOB.WRITEAPPEND
, that is, glue the
pieces together eliminating the forced line breaks.
3.
4.
When all pieces for a single SQL or PL/SQL statement have been exhausted, use row
pipelining (
PIPE
ROW (
object_type_instance
)
) to pass an instance of the abstract data
type to the caller of the function.
5.
Call the pipelined table function from SQL*Plus or any other database
client with the
TABLE
clause of the
SELECT
statement (
SELECT
*
FROM
TABLE(
function_name
(
optional_arguments
))
).
