Oracle V$SQLTEXT - Complete SQL Text Retrieval Guide

Oracle V$SQLTEXT - Complete SQL Text Guide

Retrieve full SQL statement text from Oracle’s dynamic performance views with ready-to-run scripts.

Quick SQL Text Retrieval

-- Get full SQL text for a specific SQL_ID
SELECT SQL_TEXT
FROM V$SQLTEXT
WHERE SQL_ID = '&sql_id'
ORDER BY PIECE;

-- Get SQL with newlines preserved
SELECT SQL_TEXT
FROM V$SQLTEXT_WITH_NEWLINES
WHERE SQL_ID = '&sql_id'
ORDER BY PIECE;

---

Understanding V$SQLTEXT Views

V$SQLTEXT vs V$SQL

View	SQL Text Column	Max Length	Use Case
V$SQL	SQL_TEXT	1000 chars	Quick preview, truncated
V$SQL	SQL_FULLTEXT	CLOB	Full text, single row
V$SQLTEXT	SQL_TEXT	64 chars per piece	Full text, multiple rows
V$SQLTEXT_WITH_NEWLINES	SQL_TEXT	64 chars per piece	Preserves formatting

V$SQLTEXT Key Columns

Column	Description
SQL_ID	Unique SQL identifier
HASH_VALUE	Hash value of the SQL
ADDRESS	Parent cursor address
PIECE	Piece number (0-based)
SQL_TEXT	64-character chunk
COMMAND_TYPE	Statement type (3=SELECT, 6=UPDATE, etc.)

---

Reconstructing Full SQL Text

Using LISTAGG (12c+)

-- Reconstruct full SQL from pieces
SELECT
  SQL_ID,
  LISTAGG(SQL_TEXT, '') WITHIN GROUP (ORDER BY PIECE) AS full_sql
FROM V$SQLTEXT
WHERE SQL_ID = '&sql_id'
GROUP BY SQL_ID;

Using XMLAGG (Handles > 4000 chars)

-- For very long SQL statements
SELECT
  SQL_ID,
  RTRIM(XMLAGG(XMLELEMENT(e, SQL_TEXT) ORDER BY PIECE).EXTRACT('//text()').GETCLOBVAL(), ',') AS full_sql
FROM V$SQLTEXT
WHERE SQL_ID = '&sql_id'
GROUP BY SQL_ID;

Using SQL_FULLTEXT from V$SQL

-- Simpler approach using CLOB
SELECT SQL_ID, SQL_FULLTEXT
FROM V$SQL
WHERE SQL_ID = '&sql_id'
AND CHILD_NUMBER = 0;

---

Finding SQL Statements

Find SQL by Text Pattern

-- Search for SQL containing specific text
SELECT DISTINCT SQL_ID, HASH_VALUE
FROM V$SQLTEXT
WHERE UPPER(SQL_TEXT) LIKE '%EMPLOYEES%'
ORDER BY SQL_ID;

-- Get full statement for matching SQL
SELECT SQL_ID, SQL_TEXT
FROM V$SQLTEXT
WHERE SQL_ID IN (
  SELECT DISTINCT SQL_ID
  FROM V$SQLTEXT
  WHERE UPPER(SQL_TEXT) LIKE '%EMPLOYEES%'
)
ORDER BY SQL_ID, PIECE;

Find SQL by Object Name

-- Find all SQL referencing a specific table
SELECT DISTINCT
  s.SQL_ID,
  s.EXECUTIONS,
  s.ELAPSED_TIME/1000000 AS elapsed_sec,
  s.SQL_TEXT
FROM V$SQL s
WHERE s.SQL_ID IN (
  SELECT DISTINCT SQL_ID
  FROM V$SQLTEXT
  WHERE UPPER(SQL_TEXT) LIKE '%HR.EMPLOYEES%'
);

Currently Executing SQL

-- Get SQL text for active sessions
SELECT
  s.SID,
  s.SERIAL#,
  s.USERNAME,
  sq.SQL_ID,
  sq.SQL_FULLTEXT
FROM V$SESSION s
JOIN V$SQL sq ON s.SQL_ID = sq.SQL_ID
WHERE s.STATUS = 'ACTIVE'
  AND s.USERNAME IS NOT NULL;

---

SQL Text with Performance Metrics

Top SQL by Elapsed Time

SELECT
  s.SQL_ID,
  s.EXECUTIONS,
  ROUND(s.ELAPSED_TIME/1000000, 2) AS elapsed_sec,
  ROUND(s.ELAPSED_TIME/NULLIF(s.EXECUTIONS, 0)/1000000, 4) AS avg_sec,
  s.BUFFER_GETS,
  s.DISK_READS,
  DBMS_LOB.SUBSTR(s.SQL_FULLTEXT, 200, 1) AS sql_preview
FROM V$SQL s
WHERE s.ELAPSED_TIME > 1000000  -- > 1 second total
ORDER BY s.ELAPSED_TIME DESC
FETCH FIRST 20 ROWS ONLY;

SQL Text with Plan Hash

SELECT
  s.SQL_ID,
  s.PLAN_HASH_VALUE,
  s.EXECUTIONS,
  s.BUFFER_GETS,
  t.SQL_TEXT
FROM V$SQL s
JOIN V$SQLTEXT t ON s.SQL_ID = t.SQL_ID
WHERE s.SQL_ID = '&sql_id'
ORDER BY t.PIECE;

---

Historical SQL Text (AWR)

SQL Text from AWR Snapshots

-- Get historical SQL text
SELECT SQL_TEXT
FROM DBA_HIST_SQLTEXT
WHERE SQL_ID = '&sql_id';

-- SQL text with AWR statistics
SELECT
  h.SQL_ID,
  h.EXECUTIONS_DELTA,
  h.ELAPSED_TIME_DELTA/1000000 AS elapsed_sec,
  t.SQL_TEXT
FROM DBA_HIST_SQLSTAT h
JOIN DBA_HIST_SQLTEXT t ON h.SQL_ID = t.SQL_ID
WHERE h.SNAP_ID BETWEEN &begin_snap AND &end_snap
ORDER BY h.ELAPSED_TIME_DELTA DESC
FETCH FIRST 10 ROWS ONLY;

---

Practical Use Cases

Export Long SQL for Review

-- Create formatted output for SQL review
SET LONG 100000
SET LONGCHUNKSIZE 100000
SET LINESIZE 200
SET PAGESIZE 0
SET TRIMSPOOL ON

SPOOL sql_review.txt
SELECT SQL_FULLTEXT FROM V$SQL WHERE SQL_ID = '&sql_id';
SPOOL OFF

Compare SQL Across Environments

-- Generate SQL hash for comparison
SELECT
  SQL_ID,
  HASH_VALUE,
  EXACT_MATCHING_SIGNATURE,
  FORCE_MATCHING_SIGNATURE
FROM V$SQL
WHERE SQL_ID = '&sql_id';

Find SQL with Bind Variables

-- Get SQL text showing bind variable positions
SELECT
  s.SQL_ID,
  s.SQL_FULLTEXT,
  b.NAME AS bind_name,
  b.POSITION,
  b.DATATYPE_STRING,
  b.VALUE_STRING
FROM V$SQL s
JOIN V$SQL_BIND_CAPTURE b ON s.SQL_ID = b.SQL_ID
WHERE s.SQL_ID = '&sql_id';

---

Command Types Reference

COMMAND_TYPE	Statement
1	CREATE TABLE
2	INSERT
3	SELECT
6	UPDATE
7	DELETE
9	CREATE INDEX
26	LOCK TABLE
42	ALTER SESSION
47	PL/SQL EXECUTE
189	MERGE

-- Filter by command type
SELECT DISTINCT SQL_ID
FROM V$SQLTEXT
WHERE COMMAND_TYPE = 3  -- SELECT statements only
ORDER BY SQL_ID;

---

Troubleshooting

SQL Not Found in V$SQLTEXT

Possible causes:

• SQL aged out of shared pool
• Instance restart cleared shared pool
• SQL was explicitly flushed

Solutions:

-- Check AWR for historical SQL
SELECT SQL_TEXT FROM DBA_HIST_SQLTEXT WHERE SQL_ID = '&sql_id';

-- Check if SQL is still in cache
SELECT COUNT(*) FROM V$SQL WHERE SQL_ID = '&sql_id';

Incomplete SQL Text

-- Verify all pieces retrieved
SELECT
  SQL_ID,
  COUNT(*) AS piece_count,
  MAX(PIECE) + 1 AS expected_pieces
FROM V$SQLTEXT
WHERE SQL_ID = '&sql_id'
GROUP BY SQL_ID;

---

Required Privileges

-- Grant access to V$ views
GRANT SELECT ON V_$SQLTEXT TO &username;
GRANT SELECT ON V_$SQLTEXT_WITH_NEWLINES TO &username;
GRANT SELECT ON V_$SQL TO &username;

-- Or grant role
GRANT SELECT_CATALOG_ROLE TO &username;

---

Related Resources

• V$SESSION V$PROCESS Join Guide
• Performance Analysis Scripts
• AWR Report Guide
• ORA-01000: Maximum Open Cursors