Skip to content

SQL Text by ID (sqltext.sql)

What This Script Does

Retrieves and displays the complete SQL statement text for a specific SQL_ID from the shared SQL area.

The Script

rem vsortusage.sql
rem
set pagesize 140
set linesize 80
rem
ttitle 'SQL Text by SQLID'
rem
col sql_text format a80 word_wrapped
rem
select sql_text
from v$sqltext
where sql_id = '&sqlid'
order by piece
/

set pagesize 50

Usage

-- Basic usage
@sqltext.sql

Parameters

The script prompts for:

&sqlid - **: The SQL_ID of the statement you want to view

Required Privileges

SELECT ANY DICTIONARY
-- OR --
```## Sample Output

                                SQL Text by SQLID

SQL_TEXT

SELECT e.employee_id, e.first_name, e.last_name, d.department_name FROM employ ees e JOIN departments d ON e.department_id = d.department_id WHERE e.hire_dat e > TO_DATE(‘2020-01-01’, ‘YYYY-MM-DD’) ORDER BY e.hire_date DESC

## Key Output Information

- **SQL_TEXT**: The complete SQL statement text, formatted with word wrapping for readability
- Text is assembled in order from multiple pieces if the SQL is longer than 1000 characters

## Common Use Cases

**Performance Analysis**
```sql
-- View the actual SQL text when investigating slow queries
@sqltext.sql
-- Enter: [appropriate values]

Query Identification

-- Understand what a SQL_ID represents when found in AWR or ASH reports
@sqltext.sql

ep - Display execution plans for SQL statements
db - Basic database information
dtable - Table analysis for objects referenced in SQL