RE: transaction ???

Raja:

The view you're thinking of with a sql_text column of 1000 characters is v$sqlarea, not v$sqltext. In v$sqltext, the column sql_text is varchar2(64) but the text of the statement is there completely, broken over various lines. These lines can be ordered by the column "piece". DBA_SOURCE, on the other hand, is a view showing all the source code in the database, such as that for packages, procedures and triggers. So, it is not currently running SQL, but SQL that has been compiled into the database for later use. I most often use the owner column to join that view with, say, dba_users. The column "line". in this case, orders the various code fragments contained in the column "text".  

Jon Walthour

-----Original Message-----
Luthra
Sent: Sunday, August 19, 2001 9:50 PM
To: Multiple recipients of list ORACLE-L

Hello,

The sql_text that is there in v$sqltext is only 1000 chars wide, where as the text field in dba_source is 4000 chars. long. How do I get the text from dba_source, as I am not able to join that table with any other suitable table.

Any help/comments?

rgds,

raja

--

On Sun, 19 Aug 2001 17:15:30  
 Jon Walthour wrote:


>To find currently running sql, run the following query:

>

>SELECT   a.username AS username

>       , a.sid AS sid

>       , a.serial# AS ser#

>       , b.sql_text AS statement

>    FROM v$session a

>       , v$sqltext b

>   WHERE a.username IS NOT NULL

>     AND a.status = 'ACTIVE'

>     AND a.sql_address = b.address

>     AND a.sql_hash_value = b.hash_value

>     AND a.audsid != USERENV('sessionid')

>ORDER BY sid

>       , ser#

>       , b.piece;

>

>To check out lots of scripts for tuning, look into Steve Adams' website




>at http://www.ixora.com.au.

>

>Jon Walthour

>-----Original Message-----

>Linsy

>Sent: Sunday, August 19, 2001 6:41 PM

>To: Multiple recipients of list ORACLE-L

>

>Hi, all

>

>How to find the currently running transaction,

>including user, sql_text, etc?

>

>Do you have any scripts for monitoring, tuning

>transactions?

>

>Thank you!

>

>Janet

>

>__________________________________________________

>Do You Yahoo!?

>Make international calls for as low as $.04/minute with Yahoo! 

>Messenger http://phonecard.yahoo.com/

>--

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>-- 

>Author: Janet Linsy

>  INET: janetlinsy_at_yahoo.com

>

>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: ListGuru_at_fatcity.com (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: Jon Walthour

>  INET: jonw_at_fuse.net

>

>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: ListGuru_at_fatcity.com (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).

>



Get 250 color business cards for FREE!
http://businesscards.lycos.com/vp/fastpath/
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Viraj Luthra
  INET: viraj999_at_lycos.com

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: ListGuru_at_fatcity.com (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: Jon Walthour
  INET: jonw_at_fuse.net

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: ListGuru_at_fatcity.com (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).