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: [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: Jon Walthour
> 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).
>
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: [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: Jon Walthour
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).
