Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

How to get the SQL queries based on SQL_ID.

976208Jun 27 2013 — edited Jun 27 2013

Hi Experts,

I want to get the SQL queries based on SQL_ID.

I have tried the following query,but I am not getting full query.

[code]SET linesize 132 pagesize 999

column sql_fulltext format a60 word_wrap

break on sql_text skip 1

SELECT   REPLACE (TRANSLATE (sql_text, '0123456789', '999999999'), '9', ''),sql_id

FROM   dba_hist_sqltext s

WHERE   s.sql_id = '7tvurftg8zryb';[/code]

One of my friend said use grid to get full query text.

Can you please help me how to use grid ,else any other method to get the full query based on SQL_ID.

Please help me.

Thanks in advance.

Comments

Ashu_Neo

Checking for SQL library cache. for sql_id?

-- conn sys as sysdba

select * from v$sql

/

Pavel

Hello!

may be this will  help You

https://forums.oracle.com/forums/thread.jspa?messageID=10201676&#10201676

regards,

Pavel

976208

I am checking for SQL code based on SQL ID.

Thanks.

Hemant K Chitale

SQL_TEXT is a CLOB (not a VARCHAR2).

Querying in SQLPlus, you'd have to SET LONG n  (where n is the max number of characters you expect to retrieve for the column in one row).

Hemant K Chitale

Ashu_Neo 
976208 wrote:


I am checking for SQL code based on SQL ID.


Thanks.

Okay; Even v$sql also stores Sql Text (SQL code) with SQL_ID. Use tool (like TOAD), then query to v$sql and copy the text contains as per specific sql_id and paste it on editor panel then right click and choose formatting tool option then do format.

You have a better readability on editor if you have TOAD.

Thanks!

976208

Hi,

Thanks for your reply.

I am using TOAD.

How to do it TOAD.

Thanks.

976208

I have tried SET LONG 10000 in SQL*PLUS

Still I am not getting full query.

Thanks

Ashu_Neo

You have these many options to set, if sql_text is really huge. But better use a tool(TOAD) as it's really helpful and easy to use instead! (See my previous comment).

column sql_text format A10000

set echo off

set head off

set feed off

set verify off

set termout off

set lines 10000

set long 1000000

set trimspool on

set pages 0

Thanks!

1 - 8
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jul 25 2013
Added on Jun 27 2013
8 comments
79,478 views