Home » SQL & PL/SQL » SQL & PL/SQL » How to get complete SQL given the hash_value
How to get complete SQL given the hash_value [message #273464] Wed, 10 October 2007 09:59 Go to next message
pkirangi
Messages: 74
Registered: August 2005
Member
I have this sql:
select sql_text from v$sqlarea where hash_value='203448873'

The sql_text returned by this is only 100 characters, and so has only part of the sql.
Where would I get the complete SQL text.
This sql is not stored in the database, but rather executed from an reporting tool.

Thanks
PHK
Re: How to get complete SQL given the hash_value [message #273465 is a reply to message #273464] Wed, 10 October 2007 10:09 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Did you "set wrap off"?

By
Vamsi
Re: How to get complete SQL given the hash_value [message #273467 is a reply to message #273464] Wed, 10 October 2007 10:10 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
SQL> desc v$sqlarea
Re: How to get complete SQL given the hash_value [message #273471 is a reply to message #273464] Wed, 10 October 2007 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In addition, use v$sql instead.

Regards
Michel
Re: How to get complete SQL given the hash_value [message #273473 is a reply to message #273464] Wed, 10 October 2007 10:26 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks for your responses.

V$SQL and V$SQLAREA have a column which is
SQL_TEXT VARCHAR2(1000)

So obviously the sql is truncated to 1000 characters.
Are there any metadata tables which would hold the entire sql, maybe in a CLOB column or something?

Thanks
PHK
Re: How to get complete SQL given the hash_value [message #273476 is a reply to message #273464] Wed, 10 October 2007 10:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Are you blind?

SQL_FULLTEXT                                       CLOB




SQL> desc v$sqlarea
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SQL_TEXT                                           VARCHAR2(1000)
 SQL_FULLTEXT                                       CLOB
 SQL_ID                                             VARCHAR2(13)
 SHARABLE_MEM                                       NUMBER
 PERSISTENT_MEM                                     NUMBER
 RUNTIME_MEM                                        NUMBER
 SORTS                                              NUMBER
 VERSION_COUNT                                      NUMBER
 LOADED_VERSIONS                                    NUMBER
 OPEN_VERSIONS                                      NUMBER
 USERS_OPENING                                      NUMBER
 FETCHES                                            NUMBER
 EXECUTIONS                                         NUMBER
 PX_SERVERS_EXECUTIONS                              NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 USERS_EXECUTING                                    NUMBER
 LOADS                                              NUMBER
 FIRST_LOAD_TIME                                    VARCHAR2(76)
 INVALIDATIONS                                      NUMBER
 PARSE_CALLS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 BUFFER_GETS                                        NUMBER
 APPLICATION_WAIT_TIME                              NUMBER
 CONCURRENCY_WAIT_TIME                              NUMBER
 CLUSTER_WAIT_TIME                                  NUMBER
 USER_IO_WAIT_TIME                                  NUMBER
 PLSQL_EXEC_TIME                                    NUMBER
 JAVA_EXEC_TIME                                     NUMBER
 ROWS_PROCESSED                                     NUMBER
 COMMAND_TYPE                                       NUMBER
 OPTIMIZER_MODE                                     VARCHAR2(10)
 OPTIMIZER_COST                                     NUMBER
 OPTIMIZER_ENV                                      RAW(818)
 OPTIMIZER_ENV_HASH_VALUE                           NUMBER
 PARSING_USER_ID                                    NUMBER
 PARSING_SCHEMA_ID                                  NUMBER
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 KEPT_VERSIONS                                      NUMBER
 ADDRESS                                            RAW(8)
 HASH_VALUE                                         NUMBER
 OLD_HASH_VALUE                                     NUMBER
 PLAN_HASH_VALUE                                    NUMBER
 MODULE                                             VARCHAR2(64)
 MODULE_HASH                                        NUMBER
 ACTION                                             VARCHAR2(64)
 ACTION_HASH                                        NUMBER
 SERIALIZABLE_ABORTS                                NUMBER
 OUTLINE_CATEGORY                                   VARCHAR2(64)
 CPU_TIME                                           NUMBER
 ELAPSED_TIME                                       NUMBER
 OUTLINE_SID                                        VARCHAR2(40)
 LAST_ACTIVE_CHILD_ADDRESS                          RAW(8)
 REMOTE                                             VARCHAR2(1)
 OBJECT_STATUS                                      VARCHAR2(19)
 LITERAL_HASH_VALUE                                 NUMBER
 LAST_LOAD_TIME                                     DATE
 IS_OBSOLETE                                        VARCHAR2(1)
 CHILD_LATCH                                        NUMBER
 SQL_PROFILE                                        VARCHAR2(64)
 PROGRAM_ID                                         NUMBER
 PROGRAM_LINE#                                      NUMBER
 EXACT_MATCHING_SIGNATURE                           NUMBER
 FORCE_MATCHING_SIGNATURE                           NUMBER
 LAST_ACTIVE_TIME                                   DATE
 BIND_DATA                                          RAW(2000)

SQL> 

[Updated on: Wed, 10 October 2007 10:36] by Moderator

Report message to a moderator

Re: How to get complete SQL given the hash_value [message #273477 is a reply to message #273473] Wed, 10 October 2007 10:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
V$SQL.SQL_FULLTEXT

Regards
Michel

Oups! Ana beats me.

[Updated on: Wed, 10 October 2007 10:35]

Report message to a moderator

Re: How to get complete SQL given the hash_value [message #273480 is a reply to message #273476] Wed, 10 October 2007 10:47 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Ana,
Cool down... Cool
PHK may be on 9i not on 10g.

By
Vamsi
Re: How to get complete SQL given the hash_value [message #273483 is a reply to message #273480] Wed, 10 October 2007 10:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
By default, if no version is given, last one is assumed.
If it is not the case, this is OP fault, he has to read the guide and follow it.

Regards
Michel
Re: How to get complete SQL given the hash_value [message #273500 is a reply to message #273464] Wed, 10 October 2007 12:45 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
When I do a describe of v$sqlarea
it actually brings in v_$sqlarea.
v_$sqlarea DOES NOT have SQL_FULL_TEXT
Re: How to get complete SQL given the hash_value [message #273501 is a reply to message #273500] Wed, 10 October 2007 12:50 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
pkirangi wrote on Wed, 10 October 2007 12:45

When I do a describe of v$sqlarea
it actually brings in v_$sqlarea.
v_$sqlarea DOES NOT have SQL_FULL_TEXT



You still did not answer the question of what version you are on, though it does appear to be 9i.
Re: How to get complete SQL given the hash_value [message #273503 is a reply to message #273500] Wed, 10 October 2007 12:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't read the previous posts.
Please do it.

Regards
Michel

[Updated on: Wed, 10 October 2007 12:58]

Report message to a moderator

Re: How to get complete SQL given the hash_value [message #273508 is a reply to message #273464] Wed, 10 October 2007 13:42 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
This is the Oracle version we have
Oracle9i Enterprise Edition Release 9.2.0.8.0
Re: How to get complete SQL given the hash_value [message #273510 is a reply to message #273508] Wed, 10 October 2007 13:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64137
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why didn't you say it in your first post? Why?

v$sqltext

Regards
Michel
Re: How to get complete SQL given the hash_value [message #273511 is a reply to message #273464] Wed, 10 October 2007 14:09 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
OK Thanks Michael.
Sorry for not including the version inclusing, which threw everyone off for some time.

PHK
Re: How to get complete SQL given the hash_value [message #273522 is a reply to message #273511] Wed, 10 October 2007 14:33 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Did you try the following?
Quote:

set wrap on
By
Vamsi
Re: How to get complete SQL given the hash_value [message #273524 is a reply to message #273464] Wed, 10 October 2007 14:43 Go to previous messageGo to next message
pkirangi
Messages: 74
Registered: August 2005
Member
That doesnt help Vamsi. The problem is not about wrapping here.
Re: How to get complete SQL given the hash_value [message #273535 is a reply to message #273524] Wed, 10 October 2007 14:56 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Quote:

The sql_text returned by this is only 100 characters, and so has only part of the sql.
Is that 100 or 1000?

By
Vamsi
Re: How to get complete SQL given the hash_value [message #273623 is a reply to message #273535] Thu, 11 October 2007 02:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
For 9i, the best way I ever found of getting the whole SQL was either v$sqltext or v$sqltext_with_newlines.

You'll need to stitch the bits together yourself though.
Re: How to get complete SQL given the hash_value [message #273812 is a reply to message #273464] Thu, 11 October 2007 14:36 Go to previous message
pkirangi
Messages: 74
Registered: August 2005
Member
Thanks everyone.
ANd yes it is 1000, Vamsi. Sorry for the typo in my earlier message.
Previous Topic: Formatting NULL and regular numbers
Next Topic: index using PL/SQL
Goto Forum:
  


Current Time: Thu Dec 08 06:32:11 CST 2016

Total time taken to generate the page: 0.10064 seconds