Select the full Text of an SQL Statement

Posted on 2011-03-07
Last Modified: 2012-05-11
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
Question by:Alex Matzinger
  • 2
  • 2

Expert Comment

ID: 35064020
You will have to query the CLOB column in v$sqlstats.sql_fulltext

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?  

Accepted Solution

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

set long 100000


Author Closing Comment

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

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

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

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

773 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