RE: V$SQLTEXT text length
Jesse, You could access the v$sqltext. It contains the whole query in 64 character sections. it is not as organized as sql_text in v$sqlarea, but if you put all the rows with sql_text field ordered by piece column you will have the full query. Bryan Rodrigues -Original Message- Sent: Wednesday, June 12, 2002 4:05 PM To: Multiple recipients of list ORACLE-L While researching some shared_pool problems on an 8.1.6 instance, I happen to notice that the SQL_TEXT column of the V$SQLAREA view is "only" 1000 bytes. What happens when the statement is longer than that? Does it just not show all of it in that view? Just curious. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Rodrigues, Bryan INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: V$SQLTEXT text length
Following query might help: select piece, sql_text from v$sqltext where (address,hash_value) in (select sql_address,sql_hash_value from v$session where paddr = (select addr from v$process where spid = '&1')) order by piece / set linesize 120 select substr(vs.username,1,10)username, vs.osuser, vs.sid, vs.serial#, vs.LOGON_TIME, substr(vs.machine,1,15)machine, vs.process, vp.spid, vs.last_call_et from v$session vs, v$process vp where vs.paddr = vp.addr and vs.username is not null and vs.status = 'ACTIVE' / HTH, Regards Rafiq To get SPID: Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Wed, 12 Jun 2002 13:59:52 -0800 PIECE column? By joining V$SQLAREA to V$SQLTEXT? (on ADDRESS) So, then, having the text truncated to 1000 chars in V$SQLAREA is no big deal, but it could be confusing if you had multiple rows in V$SQLAREA with the first 1000 chars of the SQL statement exactly the same, correct? The theory being that you could join to V$SQLTEXT by ADDRESS to get the entire text if needed. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 12, 2002 2:51 PM > To: [EMAIL PROTECTED] > Cc: Jesse, Rich > Subject: Re: V$SQLTEXT text length > > > Please notice the PIECE column. > > Jared > > "Jesse, Rich" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 06/12/2002 01:04 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:V$SQLTEXT text length > > > While researching some shared_pool problems on an > 8.1.6 instance, > I > happen to notice that the SQL_TEXT column of the V$SQLAREA > view is "only" > 1000 bytes. What happens when the statement is longer than > that? Does it > just not show all of it in that view? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). _ Chat with friends online, try MSN Messenger: http://messenger.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Mohammad Rafiq INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: V$SQLTEXT text length
D'oh! I'm 2 for 2 today! I had "V$SQLTEXT" in the subject, but "V$SQLAREA" in the body... Looks like I'm stopping at the liquor store on the way home... ;) Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 12, 2002 2:51 PM > To: [EMAIL PROTECTED] > Cc: Jesse, Rich > Subject: Re: V$SQLTEXT text length > > > Please notice the PIECE column. > > Jared > > "Jesse, Rich" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 06/12/2002 01:04 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:V$SQLTEXT text length > > > While researching some shared_pool problems on an > 8.1.6 instance, > I > happen to notice that the SQL_TEXT column of the V$SQLAREA > view is "only" > 1000 bytes. What happens when the statement is longer than > that? Does it > just not show all of it in that view? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
RE: V$SQLTEXT text length
PIECE column? By joining V$SQLAREA to V$SQLTEXT? (on ADDRESS) So, then, having the text truncated to 1000 chars in V$SQLAREA is no big deal, but it could be confusing if you had multiple rows in V$SQLAREA with the first 1000 chars of the SQL statement exactly the same, correct? The theory being that you could join to V$SQLTEXT by ADDRESS to get the entire text if needed. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -Original Message- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Wednesday, June 12, 2002 2:51 PM > To: [EMAIL PROTECTED] > Cc: Jesse, Rich > Subject: Re: V$SQLTEXT text length > > > Please notice the PIECE column. > > Jared > > "Jesse, Rich" <[EMAIL PROTECTED]> > Sent by: [EMAIL PROTECTED] > 06/12/2002 01:04 PM > Please respond to ORACLE-L > > > To: Multiple recipients of list ORACLE-L > <[EMAIL PROTECTED]> > cc: > Subject:V$SQLTEXT text length > > > While researching some shared_pool problems on an > 8.1.6 instance, > I > happen to notice that the SQL_TEXT column of the V$SQLAREA > view is "only" > 1000 bytes. What happens when the statement is longer than > that? Does it > just not show all of it in that view? -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: V$SQLTEXT text length
Please notice the PIECE column. Jared "Jesse, Rich" <[EMAIL PROTECTED]> Sent by: [EMAIL PROTECTED] 06/12/2002 01:04 PM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> cc: Subject:V$SQLTEXT text length While researching some shared_pool problems on an 8.1.6 instance, I happen to notice that the SQL_TEXT column of the V$SQLAREA view is "only" 1000 bytes. What happens when the statement is longer than that? Does it just not show all of it in that view? Just curious. Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: V$SQLTEXT text length
You can use the "piece" fields, as in the following script: select a.sid, a.username, s.sql_text from v$session a, v$sqltext s wherea.sql_address = s.address and a.sql_hash_value = s.hash_value order by a.username, a.sid, s.piece; Hope This Helps, Bill Gentry - DBA Allina Health System Mpls., MN [EMAIL PROTECTED] -- On Wed, 12 Jun 2002 12:04:37 Jesse, Rich wrote: >While researching some shared_pool problems on an 8.1.6 instance, I >happen to notice that the SQL_TEXT column of the V$SQLAREA view is "only" >1000 bytes. What happens when the statement is longer than that? Does it >just not show all of it in that view? > >Just curious. > >Rich Jesse System/Database Administrator >[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA >-- >Please see the official ORACLE-L FAQ: http://www.orafaq.com >-- >Author: Jesse, Rich > INET: [EMAIL PROTECTED] > >Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 >San Diego, California-- Public Internet access / Mailing Lists > >To REMOVE yourself from this mailing list, send an E-Mail message >to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in >the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may >also send the HELP command for other information (like subscribing). > ___ WIN a first class trip to Hawaii. Live like the King of Rock and Roll on the big Island. Enter Now! http://r.lycos.com/r/sagel_mail/http://www.elvis.lycos.com/sweepstakes -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Bill Gentry INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Re: V$SQLTEXT text length
Hi, Rich, Look in V$SQLTEXT - there is the whole sql statement, split in chunks of 64 characters max each. HTH, Lubo - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, June 12, 2002 1:04 PM > While researching some shared_pool problems on an 8.1.6 instance, I > happen to notice that the SQL_TEXT column of the V$SQLAREA view is "only" > 1000 bytes. What happens when the statement is longer than that? Does it > just not show all of it in that view? > > Just curious. > > Rich Jesse System/Database Administrator > [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California-- Public Internet access / Mailing Lists > > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in > the message BODY, include a line containing: UNSUB ORACLE-L > (or the name of mailing list you want to be removed from). You may > also send the HELP command for other information (like subscribing). > > -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Lyubomir Petrov INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
