Solved

Select the full Text of an SQL Statement

Posted on 2011-03-07
4
456 Views
Last Modified: 2012-05-11
Hello,
I have an oracle 11 database.  I was wondering if there is some way to select the complete text of sql statements from the v$sql... tables.  i have tried looking at sql, sqltext, sqlarea, sql_plan,  and sqltext_with_newlines, but the most the tables contain(by themselves) is 1000 characters of an sql statement.  I would like to be able to type:

Select sql_text from something...and get the complete text of all sql statements in my database no matter how long it is.  Is there a way to do this?  

Thank you
0
Comment
Question by:Alex Matzinger
  • 2
  • 2
4 Comments
 
LVL 7

Expert Comment

by:MrNed
ID: 35064020
You will have to query the CLOB column in v$sqlstats.sql_fulltext
0
 
LVL 4

Author Comment

by:Alex Matzinger
ID: 35064457
yes i have and the column returns even less text than the reg varchar2(1000) text fields.  It appears to be showing me maybe 64 chars.  Is there a way to expand how much it is showing me?  
0
 
LVL 7

Accepted Solution

by:
MrNed earned 500 total points
ID: 35064955
If you're using sqlplus, try:

set long 100000

0
 
LVL 4

Author Closing Comment

by:Alex Matzinger
ID: 35066633
Wow...thank you for the help.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now