?
Solved

Select the full Text of an SQL Statement

Posted on 2011-03-07
4
Medium Priority
?
465 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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

862 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