V$session program column value for Java Thin driver and OCI driver
Hi List Does anyone have an idea about the value in V$session program column value for Java Thin driver and OCI driver Any help would be really appreciated. TIA Sami __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Oracle DBA INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
MTS v$session
Title: MTS v$session I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115
Re: MTS v$session
did you look at the status column in v$session for the ones that don't match? IIRC, there can be a status of SNIPED where the session is not actually connected but has not been cleaned up. curiosity -- why is a user looking at v$session? --- Wiegand, Kurt [EMAIL PROTECTED] wrote: I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115 __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: MTS v$session
Title: MTS v$session Hi Kurt, Have you set the sqlnet.expire_time in the sqlnet.ora file? N. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: 01 August 2003 16:19To: Multiple recipients of list ORACLE-LSubject: MTS v$session I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115
RE: MTS v$session
Title: MTS v$session Yes; at least on the server side. # SQLNET.ORA Network Configuration File: /u01/app/oracle/product/817/network/admin/sqlnet.ora# Generated by Oracle configuration tools.#NAMES.DIRECTORY_PATH= (TNSNAMES)SQLNET.EXPIRE_TIME= 10 -Original Message-From: Nuala Cullen [mailto:[EMAIL PROTECTED]Sent: Friday, August 01, 2003 12:34 PMTo: Multiple recipients of list ORACLE-LSubject: RE: MTS v$session Hi Kurt, Have you set the sqlnet.expire_time in the sqlnet.ora file? N. -Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]On Behalf Of Wiegand, KurtSent: 01 August 2003 16:19To: Multiple recipients of list ORACLE-LSubject: MTS v$session I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115
RE: MTS v$session
Title: RE: MTS v$session No, they are not SNIPED; I've worked with those before. They are in fact all INACTIVE. I suspect they are waiting on the client for their next messages. As for your 2nd question: I've got 7 users with the DBA role on this database! Why? I'm too new here to ask. I view them as users/support personnel. Kurt -Original Message- From: Rachel Carmichael [mailto:[EMAIL PROTECTED]] Sent: Friday, August 01, 2003 11:34 AM To: Multiple recipients of list ORACLE-L Subject: Re: MTS v$session did you look at the status column in v$session for the ones that don't match? IIRC, there can be a status of SNIPED where the session is not actually connected but has not been cleaned up. curiosity -- why is a user looking at v$session? --- Wiegand, Kurt [EMAIL PROTECTED] wrote: I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115 __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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: MTS v$session
okay that sounds a lot like the connection got broken (PC turned off, sqlnet expire time, which I see you've set, etc) badly being a newbie is the perfect time to ask. You can sound totally innocent (I was just wondering how you have things set up here) --- Wiegand, Kurt [EMAIL PROTECTED] wrote: No, they are not SNIPED; I've worked with those before. They are in fact all INACTIVE. I suspect they are waiting on the client for their next messages. As for your 2nd question: I've got 7 users with the DBA role on this database! Why? I'm too new here to ask. I view them as users/support personnel. Kurt -Original Message- Sent: Friday, August 01, 2003 11:34 AM To: Multiple recipients of list ORACLE-L did you look at the status column in v$session for the ones that don't match? IIRC, there can be a status of SNIPED where the session is not actually connected but has not been cleaned up. curiosity -- why is a user looking at v$session? --- Wiegand, Kurt [EMAIL PROTECTED] wrote: I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115 __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: MTS v$session
Title: MTS v$session First question I would have is Why does a user have access to V$SESSIONS in the first place? Dick GouletSenior Oracle DBAOracle Certified 8i DBA -Original Message-From: Wiegand, Kurt [mailto:[EMAIL PROTECTED]Sent: Friday, August 01, 2003 11:19 AMTo: Multiple recipients of list ORACLE-LSubject: MTS v$session I've got an 8.1.7.4.0 instance running under Solaris 2.8. I've never worked with MTS before, but got a call from a user telling me that there were about 700 rows in v$session but they only had about 130 sessions open. The user believes that when the session is ended, v$session doesn't (or doesn't always) get cleaned up. Does anyone have any insight into this? Thanks. I checked v$process and had about 89 rows, which matched exactly to the 89 unix processes that I had on the box. I looked at paddr in v$session and saw that there were just over 100 rows pointing to each of the 6 dispatchers. The other rows each had their own dedicated process. I checked v$dispatcher and saw the 100+ values in the OWNED column, but also saw values between 300 - 450 in the CREATED column. I really don't know whether the application ends it's connections gracefully or nongracefully. Kurt Wiegand Cable Wireless Communications 703 292-2115
v$session question
Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL: http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$session question
Title: RE: v$session question grant select on sys.v_$session to xyuser / Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- From: Foelz.Frank [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 15, 2003 10:00 AM To: Multiple recipients of list ORACLE-L Subject: v$session question Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL : http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabe wie auch das Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Sie diese E-Mail irrtümlich erhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services - 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). *This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*1
v$session question
sorry, cut/copy/paste mismatch. I took the wrong error description. again.. anyone able to tell me, how to grant v$session select privileges to a user ? --- grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views --- ORA-02030 can only select from fixed tables/views Cause: An operation other than SELECT on a fixed dynamic performance table or view was attempted. It is only possible to select from fixed tables or views. Action: Remove the fixed table or view name from the SELECT statement. --- But I must know, what user is doing things to a definite table ! Frank --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$session question
v$session is a synonym. all the v$ are really v_$. go to dba_views... see for yourself. so grant on v_$. everyone gets nabbed by that one atleast once. From: Foelz.Frank [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 09:59:31 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: v$session question Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL: http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: [EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$session question
GRANT SELECT ON V_$SESSION TO user; Regards Nigel Bishop Snr. Oracle DBA ioko Tel DDI: +44 (0) 1904 435 458 Mobile: +44 (0) 7881 624 386 Fax: +44 (0) 1904 435 450 Email:[EMAIL PROTECTED] www.ioko.com -Original Message- Sent: 15 July 2003 15:00 To: Multiple recipients of list ORACLE-L Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. Action: Check syntax, insert keyword FILE as required, and try again. ORA-02030 can only select from fixed tables/views Frank Foelz Ó¿Ò _ Scheidt Bachmann GmbH Gestaltung Parkhaussysteme Breite Strasse 132 41238 Moenchengladbach Phone : ++49 2166 / 266 - 837 Fax: ++49 2166 / 266 - 615 e-mail : mailto:[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] URL: http://www.scheidt-bachmann.de http://www.scheidt-bachmann.de/ --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Nigel Bishop INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
AW: v$session question
that helped, tnx a lot ! Frank -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet am: Dienstag, 15. Juli 2003 16:15 An: Multiple recipients of list ORACLE-L Betreff: Re: v$session question v$session is a synonym. all the v$ are really v_$. go to dba_views... see for yourself. so grant on v_$. everyone gets nabbed by that one atleast once. From: Foelz.Frank [EMAIL PROTECTED] Date: 2003/07/15 Tue AM 09:59:31 EDT To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Subject: v$session question Hi all, anyone able to tell me, how to grant v$session select privileges to a user ? grant select on v$session to xyuser* ORA-02030: can only select from fixed tables/views Cause: The keyword FILE is required in this context. --- Confidentiality Note Diese E-Mail mit allen angehängten Dateien ist ausschließlich für die Person/Personen bestimmt, an die diese adressiert ist und könnte vertrauliche und/oder rechtlich geschützte Informationen enthalten. Sollten Sie nicht der für diese E-Mail bestimmte Adressat oder nicht zum Empfang berechtigt sein, ist Ihnen jeder Gebrauch, sowie jede Veröffentlichung, Vervielfältigung oder Weitergabewie auchdas Ergreifen oder Unterlassen von Maßnahmen im Vertrauen auf erlangte Information untersagt. Sollten Siediese E-Mail irrtümlicherhalten haben, benachrichtigen Sie bitte den Absender und löschen diese E-Mail von jedem Computer. E-Mail-Mitteilungen sind nicht notwendigerweise sicher. Scheidt Bachmann übernimmt keine Verantwortung für Veränderungen dieser Mitteilung, die nach dem Senden vorgenommen wurden. Herzlichen Dank für Ihre Unterstützung! This email and any files transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If you are not the authorised recipient, any use, disclosure, copying, distribution or any action taken or omitted to be taken in reliance on it, is prohibited. If you received this in error, please contact the sender and delete the material from any computer. E-mail messages are not necessarily secure. Scheidt Bachmann does not accept responsibility for any changes made to this message after it was sent. Thank you for your cooperation! --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Foelz.Frank INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
AW: Remove killed session from v$session
Thanks all, the time for the rollback to take place seems to explain why it takes so long before the session actually disappears from v$session. It's a development system running long data conversion procedures. Hence, there will be lots of rollback. ... and I guess I'll opt for orakill then. Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Arup Nanda [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 21. März 2003 15:39 An: Multiple recipients of list ORACLE-L Betreff: Re: Remove killed session from v$session Stefan, The session stays in the KILLED status because of a lot of reasons - the primary being the rollback being performed for all the changes the session did. At some point the session rollback will be complete and the session will be removed. Don't worry about the session being there. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 21, 2003 8:53 AM Hi list Is there any way to remove a killed session from v$session. Is it even necessary to do that ? I ran this: select spid, status, osuser, s.program from v$process p, v$session s where p.addr=s.paddr To check for the killed processes' spid in order to remove the thread (it's 9.2 on win2k) with orakill. But for the killed process, no process is shown. So, what's left ? Should I even bother or just wait till Oracle removes the killed session ? Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat
Re: AW: Remove killed session from v$session
Using orakill will not make the rollback happen any faster. My use of orakill and kill -9 is due to Oracle's sporadic habit of not cleaning up killed sessions, especially those holding a lock on a table. Using orakill won't hurt anything either, but if there is a big transaction taking place in the session you kill, it will still need time to rollback. Jared On Monday 24 March 2003 04:28, Stefan Jahnke wrote: Thanks all, the time for the rollback to take place seems to explain why it takes so long before the session actually disappears from v$session. It's a development system running long data conversion procedures. Hence, there will be lots of rollback. ... and I guess I'll opt for orakill then. Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -Ursprüngliche Nachricht- Von: Arup Nanda [mailto:[EMAIL PROTECTED] Gesendet: Freitag, 21. März 2003 15:39 An: Multiple recipients of list ORACLE-L Betreff: Re: Remove killed session from v$session Stefan, The session stays in the KILLED status because of a lot of reasons - the primary being the rollback being performed for all the changes the session did. At some point the session rollback will be complete and the session will be removed. Don't worry about the session being there. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 21, 2003 8:53 AM Hi list Is there any way to remove a killed session from v$session. Is it even necessary to do that ? I ran this: select spid, status, osuser, s.program from v$process p, v$session s where p.addr=s.paddr To check for the killed processes' spid in order to remove the thread (it's 9.2 on win2k) with orakill. But for the killed process, no process is shown. So, what's left ? Should I even bother or just wait till Oracle removes the killed session ? Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting
RE: AW: Remove killed session from v$session
Mladen, you really need to put a :) on these, lest some
newbie take you seriously.
:)
Jared
Gogala, Mladen [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/24/2003 09:18 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: Remove killed session from v$session
This is going to remove killed session from v$session:
ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m
Next time you connect to the database, the killed session will not
be visible in the v$session table.
-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L
Using orakill will not make the rollback happen any faster.
My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a
lock on a table.
Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.
Jared
On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
Thanks all, the time for the rollback to take place seems to explain why
it
takes so long before the session actually disappears from v$session.
It's
a
development system running long data conversion procedures. Hence, there
will be lots of rollback. ... and I guess I'll opt for orakill then.
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must
decline
all legal responsibility for the validity of the statements and comments
given above.
-Ursprüngliche Nachricht-
Von: Arup Nanda [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. März 2003 15:39
An: Multiple recipients of list ORACLE-L
Betreff: Re: Remove killed session from v$session
Stefan,
The session stays in the KILLED status because of a lot of reasons - the
primary being the rollback being performed for all the changes the
session
did. At some point the session rollback will be complete and the session
will be removed. Don't worry about the session being there.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, March 21, 2003 8:53 AM
Hi list
Is there any way to remove a killed session from v$session. Is it even
necessary to do that ?
I ran this:
select spid, status, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
To check for the killed processes' spid in order to remove the thread
(it's
9.2 on win2k) with orakill.
But for the killed process, no process is shown. So, what's left ?
Should
I
even bother or just wait
till Oracle removes the killed session ?
Regards,
Stefan
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail
an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask
for
your understanding that, for your own protection and ours, we must
decline all legal responsibility for the validity of the statements
and
comments given above.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stefan Jahnke
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538
RE: AW: Remove killed session from v$session
This is going to remove killed session from v$session:
ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m
Next time you connect to the database, the killed session will not
be visible in the v$session table.
-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L
Using orakill will not make the rollback happen any faster.
My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a
lock on a table.
Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.
Jared
On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
Thanks all, the time for the rollback to take place seems to explain why
it
takes so long before the session actually disappears from v$session. It's
a
development system running long data conversion procedures. Hence, there
will be lots of rollback. ... and I guess I'll opt for orakill then.
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must decline
all legal responsibility for the validity of the statements and comments
given above.
-Ursprüngliche Nachricht-
Von: Arup Nanda [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. März 2003 15:39
An: Multiple recipients of list ORACLE-L
Betreff: Re: Remove killed session from v$session
Stefan,
The session stays in the KILLED status because of a lot of reasons - the
primary being the rollback being performed for all the changes the session
did. At some point the session rollback will be complete and the session
will be removed. Don't worry about the session being there.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, March 21, 2003 8:53 AM
Hi list
Is there any way to remove a killed session from v$session. Is it even
necessary to do that ?
I ran this:
select spid, status, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
To check for the killed processes' spid in order to remove the thread
(it's
9.2 on win2k) with orakill.
But for the killed process, no process is shown. So, what's left ?
Should
I
even bother or just wait
till Oracle removes the killed session ?
Regards,
Stefan
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must
decline all legal responsibility for the validity of the statements and
comments given above.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Stefan Jahnke
INET: [EMAIL PROTECTED]
Fat City Network Services-- 858-538-5051 http://www.fatcity.com
San Diego, California-- Mailing list and web hosting services
-
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
RE: AW: Remove killed session from v$session
I agree. This is, essentially, a unix equivalent of shutdown abort.
It kills all oracle processes and removes any shared memory allocated to
oracle, even if there is more then one instance on the box.
-Original Message-
Sent: Monday, March 24, 2003 1:00 PM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Importance: High
Mladen, you really need to put a :) on these, lest some
newbie take you seriously.
:)
Jared
Gogala, Mladen [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/24/2003 09:18 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: Remove killed session from v$session
This is going to remove killed session from v$session:
ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m
Next time you connect to the database, the killed session will not
be visible in the v$session table.
-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L
Using orakill will not make the rollback happen any faster.
My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a
lock on a table.
Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.
Jared
On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
Thanks all, the time for the rollback to take place seems to explain why
it
takes so long before the session actually disappears from v$session.
It's
a
development system running long data conversion procedures. Hence, there
will be lots of rollback. ... and I guess I'll opt for orakill then.
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must
decline
all legal responsibility for the validity of the statements and comments
given above.
-Ursprüngliche Nachricht-
Von: Arup Nanda [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. März 2003 15:39
An: Multiple recipients of list ORACLE-L
Betreff: Re: Remove killed session from v$session
Stefan,
The session stays in the KILLED status because of a lot of reasons - the
primary being the rollback being performed for all the changes the
session
did. At some point the session rollback will be complete and the session
will be removed. Don't worry about the session being there.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, March 21, 2003 8:53 AM
Hi list
Is there any way to remove a killed session from v$session. Is it even
necessary to do that ?
I ran this:
select spid, status, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
To check for the killed processes' spid in order to remove the thread
(it's
9.2 on win2k) with orakill.
But for the killed process, no process is shown. So, what's left ?
Should
I
even bother or just wait
till Oracle removes the killed session ?
Regards,
Stefan
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail
an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask
for
your
RE: AW: Remove killed session from v$session
{innocently} but this *will* take care of the problem, right? :)
-Original Message-
Sent: Monday, March 24, 2003 12:59 PM
To: Multiple recipients of list ORACLE-L
Mladen, you really need to put a :) on these, lest some
newbie take you seriously.
:)
Jared
Gogala, Mladen [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/24/2003 09:18 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: Remove killed session from v$session
This is going to remove killed session from v$session:
ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m
Next time you connect to the database, the killed session will not
be visible in the v$session table.
-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L
Using orakill will not make the rollback happen any faster.
My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a
lock on a table.
Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.
Jared
On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
Thanks all, the time for the rollback to take place seems to explain why
it
takes so long before the session actually disappears from v$session.
It's
a
development system running long data conversion procedures. Hence, there
will be lots of rollback. ... and I guess I'll opt for orakill then.
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must
decline
all legal responsibility for the validity of the statements and comments
given above.
-Ursprüngliche Nachricht-
Von: Arup Nanda [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. März 2003 15:39
An: Multiple recipients of list ORACLE-L
Betreff: Re: Remove killed session from v$session
Stefan,
The session stays in the KILLED status because of a lot of reasons - the
primary being the rollback being performed for all the changes the
session
did. At some point the session rollback will be complete and the session
will be removed. Don't worry about the session being there.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, March 21, 2003 8:53 AM
Hi list
Is there any way to remove a killed session from v$session. Is it even
necessary to do that ?
I ran this:
select spid, status, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
To check for the killed processes' spid in order to remove the thread
(it's
9.2 on win2k) with orakill.
But for the killed process, no process is shown. So, what's left ?
Should
I
even bother or just wait
till Oracle removes the killed session ?
Regards,
Stefan
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail
an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask
for
your understanding that, for your own protection and ours, we must
decline all legal responsibility for the validity of the statements
and
comments given above
RE: AW: Remove killed session from v$session
Well, it will kill of your oracle processes and deallocate any
shared memory that has been allocated. It will transcend the problem
to the different plane of existence.
-Original Message-
Sent: Monday, March 24, 2003 2:05 PM
To: Multiple recipients of list ORACLE-L
{innocently} but this *will* take care of the problem, right? :)
-Original Message-
Sent: Monday, March 24, 2003 12:59 PM
To: Multiple recipients of list ORACLE-L
Mladen, you really need to put a :) on these, lest some
newbie take you seriously.
:)
Jared
Gogala, Mladen [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
03/24/2003 09:18 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: Remove killed session from v$session
This is going to remove killed session from v$session:
ps -fu oracle|grep -v PID|awk '{ print $2; }'|xargs kill -9
ipcs -m|grep oracle|awk '{ print $2; }'|xargs ipcrm -m
Next time you connect to the database, the killed session will not
be visible in the v$session table.
-Original Message-
Sent: Monday, March 24, 2003 11:14 AM
To: Multiple recipients of list ORACLE-L
Using orakill will not make the rollback happen any faster.
My use of orakill and kill -9 is due to Oracle's sporadic habit
of not cleaning up killed sessions, especially those holding a
lock on a table.
Using orakill won't hurt anything either, but if there is a big
transaction taking place in the session you kill, it will still
need time to rollback.
Jared
On Monday 24 March 2003 04:28, Stefan Jahnke wrote:
Thanks all, the time for the rollback to take place seems to explain why
it
takes so long before the session actually disappears from v$session.
It's
a
development system running long data conversion procedures. Hence, there
will be lots of rollback. ... and I guess I'll opt for orakill then.
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen!
Weitere Informationen unter +49 201/45 13-240 oder E-Mail an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails sent via the Internet can easily be
copied or manipulated by third parties. For this reason we would ask for
your understanding that, for your own protection and ours, we must
decline
all legal responsibility for the validity of the statements and comments
given above.
-Ursprüngliche Nachricht-
Von: Arup Nanda [mailto:[EMAIL PROTECTED]
Gesendet: Freitag, 21. März 2003 15:39
An: Multiple recipients of list ORACLE-L
Betreff: Re: Remove killed session from v$session
Stefan,
The session stays in the KILLED status because of a lot of reasons - the
primary being the rollback being performed for all the changes the
session
did. At some point the session rollback will be complete and the session
will be removed. Don't worry about the session being there.
HTH.
Arup Nanda
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Friday, March 21, 2003 8:53 AM
Hi list
Is there any way to remove a killed session from v$session. Is it even
necessary to do that ?
I ran this:
select spid, status, osuser, s.program from
v$process p, v$session s where p.addr=s.paddr
To check for the killed processes' spid in order to remove the thread
(it's
9.2 on win2k) with orakill.
But for the killed process, no process is shown. So, what's left ?
Should
I
even bother or just wait
till Oracle removes the killed session ?
Regards,
Stefan
Stefan Jahnke
Consultant
BOV Aktiengesellschaft
Voice: +49 201 - 4513-298
Fax: +49 201 - 4513-149
mailto: [EMAIL PROTECTED]
Please remove nospam to contact me via email.
visit our website: http://www.bov.de
subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp
Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient
schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail
an
mailto:[EMAIL PROTECTED]
Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht
unter
fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde
bitten
wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die
rechtliche Verbindlichkeit der vorstehenden Erklaerungen und
Aeusserungen
ausschliessen.
As you are probably aware, e-mails
V$SESSION Details Changed after upgrade Database to 8.1.7.4
I have just upgraded my database at the Weekend and I have noticed that PROGRAM field in V$SESSION no longer displays the program all the time i.e. SQLPLUS and Microsoft Access in particular. I use this fields in capturing illegal log ons etc.. Anybody have an idea before I try Support. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$SESSION Details Changed after upgrade Database to 8.1.7.4
Rabbit I haven't heard this reported and I did a quick Google search and couldn't find a mention. You might check Metalink. I think that Oracle Support will probably say that what gets posted to the PROGRAM field is the responsibility of the client program. Are you certain that the behavior hasn't changed, but you've just looked more closely since the upgrade? Dennis Williams DBA, 40%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Sunday, March 23, 2003 2:09 AM To: Multiple recipients of list ORACLE-L I have just upgraded my database at the Weekend and I have noticed that PROGRAM field in V$SESSION no longer displays the program all the time i.e. SQLPLUS and Microsoft Access in particular. I use this fields in capturing illegal log ons etc.. Anybody have an idea before I try Support. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Remove killed session from v$session
Hi list Is there any way to remove a killed session from v$session. Is it even necessary to do that ? I ran this: select spid, status, osuser, s.program from v$process p, v$session s where p.addr=s.paddr To check for the killed processes' spid in order to remove the thread (it's 9.2 on win2k) with orakill. But for the killed process, no process is shown. So, what's left ? Should I even bother or just wait till Oracle removes the killed session ? Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Remove killed session from v$session
Stefan, The session stays in the KILLED status because of a lot of reasons - the primary being the rollback being performed for all the changes the session did. At some point the session rollback will be complete and the session will be removed. Don't worry about the session being there. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, March 21, 2003 8:53 AM Hi list Is there any way to remove a killed session from v$session. Is it even necessary to do that ? I ran this: select spid, status, osuser, s.program from v$process p, v$session s where p.addr=s.paddr To check for the killed processes' spid in order to remove the thread (it's 9.2 on win2k) with orakill. But for the killed process, no process is shown. So, what's left ? Should I even bother or just wait till Oracle removes the killed session ? Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Arup Nanda INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Remove killed session from v$session
It should disappear eventually, it sometimes takes a while. Try the immediate clause next time, they don't usually hang around. alter system kill session 'sid,serial' immediate; -Original Message- From: Stefan Jahnke [mailto:[EMAIL PROTECTED] Sent: Friday, March 21, 2003 8:54 AM To: Multiple recipients of list ORACLE-L Subject: Remove killed session from v$session Hi list Is there any way to remove a killed session from v$session. Is it even necessary to do that ? I ran this: select spid, status, osuser, s.program from v$process p, v$session s where p.addr=s.paddr To check for the killed processes' spid in order to remove the thread (it's 9.2 on win2k) with orakill. But for the killed process, no process is shown. So, what's left ? Should I even bother or just wait till Oracle removes the killed session ? Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stefan Jahnke INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Anderson, Brian INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Remove killed session from v$session
Use orakill first, rather than using 'alter session kill', it's more reliable. You'll have to wait for Oracle to clean up the session. On occasion the only way to clean it up is bounce the database, which is why I always use kill -9 or orakill to start with. Jared On Friday 21 March 2003 05:53, Stefan Jahnke wrote: Hi list Is there any way to remove a killed session from v$session. Is it even necessary to do that ? I ran this: select spid, status, osuser, s.program from v$process p, v$session s where p.addr=s.paddr To check for the killed processes' spid in order to remove the thread (it's 9.2 on win2k) with orakill. But for the killed process, no process is shown. So, what's left ? Should I even bother or just wait till Oracle removes the killed session ? Regards, Stefan Stefan Jahnke Consultant BOV Aktiengesellschaft Voice: +49 201 - 4513-298 Fax: +49 201 - 4513-149 mailto: [EMAIL PROTECTED] Please remove nospam to contact me via email. visit our website: http://www.bov.de subscribe to our newsletter: http://www.bov.de/presse/newsletter.asp Sicherheitsluecken mit IT-Security-Konzepten von BOV effizient schliessen! Weitere Informationen unter +49 201/45 13-240 oder E-Mail an mailto:[EMAIL PROTECTED] Wie Sie wissen, koennen ueber das Internet versandte E-Mails leicht unter fremden Namen erstellt oder manipuliert werden. Aus diesem Grunde bitten wir um Verstaendnis dafuer, dass wir zu Ihrem und unserem Schutz die rechtliche Verbindlichkeit der vorstehenden Erklaerungen und Aeusserungen ausschliessen. As you are probably aware, e-mails sent via the Internet can easily be copied or manipulated by third parties. For this reason we would ask for your understanding that, for your own protection and ours, we must decline all legal responsibility for the validity of the statements and comments given above. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jared Still INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
v$sqlarea v$session
I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
v$session.sql_hash_value = v$sqlarea.hash_value or v$session.sql_address = v$sqlarea.address - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 10:18 AM I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Tim Gorman INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
Join sql_address from v$session to address from v$sqlarea . Regards, Denny Quoting [EMAIL PROTECTED]: I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
Perhaps this is what you're looking for? select sql_text ,sid, username, osuser, logon_time from v$sqlarea sa, v$session ss where sa.buffer_gets 1-- that's a lot of gets! and sa.hash_value = ss.sql_hash_value; HTH! GL! :) Rich Rich JesseSystem/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA p.s. West Bend, WI welcomes the new Home Depot to be built this summer!! If this SQL helps you, can I get a discount?? ;) -Original Message- [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 11:18 AM To: Multiple recipients of list ORACLE-L I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
How about: SELECT a.sid, a.username, c.disk_reads, c.buffer_gets, c.sorts, c.executions, c.rows_processed, c.sql_text FROM v$session a, v$sqlarea c WHERE a.sid = sessid AND a.sql_address = c.address -- AND a.SQL_HASH_VALUE = c.HASH_VALUE AND c.hash_value = DECODE( SIGN(a.sql_hash_value), -1, a.sql_hash_value + POWER(2,32), a.sql_hash_value ) / Not sure if there's still an issue with the hash_value or not. At 09:18 AM 3/12/2003 -0800, you wrote: I'm suffering from a senior moment. The question is at the every bottom. So exactly how do I join V$SQLAREA to V$SESSION? Andy Rivenes [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andy Rivenes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
charlie I think sqlarea and session has no direct relationship . Same sql can belong to many sessions and columns like users_opening, users_executing suggests this. There should be one entry in sqlarea per hashaddress I guess . may user v$open_cursors to join with addess and sid . -ak - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, March 12, 2003 9:18 AM I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: AK INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
From Window A
SQL /
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
CONSISTENT_CHANGES
-- -- --- -- -
--
169838526512700725 22864266596
18526
173 182492 110868621 87457118643
1836
225 2946412818444 267665 3624
1303
308 2985812805686 186559 3756
2982
671 2834712803899 130510 2910
1027
827 818906753 120904 4
1075
934 1407568243870 10341 12866
1501
103425255919580 359701 255
8129
106715018915634 12977520
976
1157 883210268240 157532 1310
5328
1331 818925581 13067998
7890
1334 4766877945 56560 183
8407
12 rows selected.
From Window B
1 SELECT address, hash_value, buffer_gets, sql_text
2 --,sid, username, osuser, logon_time
3 FROM v$sqlarea sa
4 --, v$session s
5 where buffer_gets 1
6 --sa.address = s.sql_address
7 --and sa.hash_value = s.sql_hash_value
8*--and s.sid = 173
[EMAIL PROTECTED] /
ADDRESS HASH_VALUE BUFFER_GETS
-- ---
SQL_TEXT
AA975444 3625081536 111745500
SELECT COUNT(*) FROM V_SB_PART V WHERE V.ID = :b1 AND V.VENDID = :b2
A41AB584 36165120 725314770
SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID
= :p1 AND P.ID = RP.PART AND
RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART
= PO.PARTID AND PO.TYP =
'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NVL(P.EFFDT,SYSDATE+1)) =
TRUNC(SYSDATE) ORDER BY 1
AA64EF0C 1943687711 105332438
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
A2114824 14023929 106680698
select codes.dsc, codes.parm2, codes.cdlng, count(distinct batchpl.cdhid),
count(batchpl.pkgid), dep
t.id, dept.depnm, to_char(sysdate - (dept.caseplfreq/24/60),'HH24:MI:SS'),
dept.caseplsz, dept.casef
inalprttm, dept.caseplsort,
to_char(new_time(sysdate,'PST',dept.tmzn),'HH24:MI:SS') from codes,dept
,batchpl where batchpl.shploc = dept.id and batchpl.status in('WPL','BD')
and batchpl.batchid is nul
l and dept.id = codes.cd and codes.id = 'BATCHPLPRT' and dept.caseplflg
= 'Y' group by dept.id,dept.
depnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm,
dept.caseplsort,codes.dsc,codes.parm2,codes
.cdlng,dept.tmzn order by
dept.id,dept.depnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm, dept
.caseplsort,codes.dsc,codes.parm2,codes.cdlng,dept.tmzn
Back in Window A after query from B returns
QL /
SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES
CONSISTENT_CHANGES
-- -- --- -- -
--
169840951912841564 23381267296
18808
173 183514 111385238 87477119354
1854
225 2946412818444 267665 3624
1303
308 2986812805797 186611 3764
2982
671 2836112803955 130513 2922
1027
827 818906753 120904 4
1075
934 1407568243870 10341 12866
1501
103425255919580 359701 255
8129
106715018915634 12977520
976
1157 883411017366 169782 1391
5745
1331 818925581 13067998
7890
1334 4772279674 60858 199
9414
12 rows selected.
A couple of the SID's show ever increasing CONSISTENT_GETS; such as
SID=173, 1334, etc.
I've NEVER, EVER gotten any rows returned when I attempt to join V$SESSION
V$SQLAREA.
FWIW - The is V7.3.4.5 on Solaris V2.6
Jesse, Rich
[EMAIL PROTECTED]To: '[EMAIL PROTECTED]'
[EMAIL PROTECTED]
rld.com cc:
Subject: RE: v$sqlarea v$session
03/12/2003 11:06
Re: v$sqlarea v$session
[EMAIL PROTECTED] wrote: I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? Charlie, I have noticed a number of (correct) replies about the (address, hash_value) join, however I am not sure that what those who answered and you have in mind is quite compatible. It looks like what you want is finding out who has issued this dreadful query, right ? The join with V$SESSION will only tell you who is still executing or has just executed the query. If somebody runs a cartesian join of death followed by a 'select null from dual', it's the latter you will join with. And no need to try to join on (PREV_SQL_ADDR, PREV_HASH_VALUE), it was buggy (meaning the same as the current pair) in all the versions I have checked. If you check FIRST_LOAD_TIME you will see that in most instances many statements stay for a long time in memory, so this terrible query may have been run a few hours ago. I think that for a delayed analysis the best you can use is PARSING_USER_ID in V$SQLAREA and join with USER_ID in DBA_USERS - only if PARSE_CALLS 0 (otherwise you will have 0, NOT meaning SYS) - which then you can possibly relate to a current session, but this is a different problem. Not ideal if everybody connects under the same account, nor in an OLTP environment where zillion users are repeatedly executing the same statements. But it can be useful in a DSS environment where what you fear is the haphazard terrible query which is executed only once. -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
How about: SELECT a.sid, a.username, c.disk_reads, c.buffer_gets, c.sorts, c.executions, c.rows_processed, c.sql_text FROM v$session a, v$sqlarea c WHERE a.sid = sessid AND a.sql_address = c.address -- AND a.SQL_HASH_VALUE = c.HASH_VALUE AND c.hash_value = DECODE( SIGN(a.sql_hash_value), -1, a.sql_hash_value + POWER(2,32), a.sql_hash_value ) / Not sure if there's still an issue with the hash_value or not. At 09:18 AM 3/12/2003 -0800, you wrote: I'm suffering from a senior moment. The question is at the every bottom. So exactly how do I join V$SQLAREA to V$SESSION? Andy Rivenes [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Andy Rivenes INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
Join sql_address from v$session to address from v$sqlarea . Regards, Denny Quoting [EMAIL PROTECTED]: I'm suffering from a senior moment. The question is at the every bottom. SQL select sql_text from v$sqlarea sa where buffer_gets 1 SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.saddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.paddr SQL / no rows selected 1 select sql_text 2 ,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 , v$session ss 5 where buffer_gets 1 6* and sa.address= ss.sql_address SQL / no rows selected 1 select sql_text 2 --,sid, username, osuser, logon_time 3 from v$sqlarea sa 4 --, v$session ss 5 where buffer_gets 1 6* --and sa.address= ss.sql_address SQL / SQL_TEXT -- -- SELECT RP.RELPART FROM OERELPART RP, PARTOFFERING PO, PART P WHERE P.ID = :p1 AN D P.ID = RP.PART AND RP.ACTIVE = 'Y' AND CAT_PKG.CATALOG_PART(RP.RELPART) = 'Y' AND RP.RELPART = PO.PARTID AND PO.TYP = 'WEB' AND PO.VAL = 'MWHCOM' AND TRUNC(NV L(P.EFFDT,SYSDATE+1)) = TRUNC(SYSDATE) ORDER BY 1 [EMAIL PROTECTED] select * from v$sess_io where consistent_gets 1000; SID BLOCK_GETS CONSISTENT_GETS PHYSICAL_READS BLOCK_CHANGES CONSISTENT_CHANGES -- -- --- -- - -- 173 11542278096265 30158 74924 954 308 2973912804854 186511 3614 2961 827 818906753 120904 4 1075 103415237413409 19540840 2913 106715018915634 12977520 976 So exactly how do I join V$SQLAREA to V$SESSION? -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Denny Koovakattu INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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$sqlarea v$session
THANKS AK!
1 select oc.sid, ss.serial#, user_name, osuser, sa.sql_text
2 from v$open_cursor oc, v$session ss, v$sqlarea sa
3 where oc.sid = ss.sid
4 and oc.address = sa.address
5 and oc.hash_value = sa.hash_value
6* and buffer_gets 1
SQL /
SIDSERIAL# USER_NAME OSUSER
-- -- -- ---
SQL_TEXT
173 65 OPS$MISOPS misops
select codes.dsc, codes.parm2, codes.cdlng, count(distinct batchpl.cdhid),
count
(batchpl.pkgid), dept.id, dept.depnm, to_char(sysdate -
(dept.caseplfreq/24/60),
'HH24:MI:SS'), dept.caseplsz, dept.casefinalprttm, dept.caseplsort,
to_char(new_
time(sysdate,'PST',dept.tmzn),'HH24:MI:SS') from codes,dept,batchpl where
batch
pl.shploc = dept.id and batchpl.status in('WPL','BD') and batchpl.batchid
is nul
l and dept.id = codes.cd and codes.id = 'BATCHPLPRT' and dept.caseplflg
= 'Y' gr
oup by
dept.id,dept.depnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm, dep
t.caseplsort,codes.dsc,codes.parm2,codes.cdlng,dept.tmzn order by
dept.id,dept.d
epnm,dept.caseplfreq,dept.caseplsz,dept.casefinalprttm,
dept.caseplsort,codes.ds
c,codes.parm2,codes.cdlng,dept.tmzn
341 40 DELS dels
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
479931 BTAYLORbtaylor
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
535230 JOSEAC joseac
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
563852 MICHAELK michaelk
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
600736 SBAKER sbaker
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
605289 TYTtyt
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
649149 RICKM rickm
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
727159 DREWH drewh
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
783327 BTAYLORbtaylor
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
934155 JCURTISjcurtis
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
978233 SBAKER sbaker
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1056196 MATTLA mattla
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1181 1303 MICHAELL michaell
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1237246 RITAK ritak
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1269 1903 MICHELLY michelly
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
1337 67 TOMS toms
UPDATE INVLOC SET QUOTE=:b1,QLOC=:b2,VENDID=:b3 WHERE ROWID = :b4
17 rows selected.
AK
[EMAIL PROTECTED]To: Multiple recipients of list
ORACLE-L [EMAIL PROTECTED]
.comcc:
Sent by: Subject: Re: v$sqlarea v$session
[EMAIL PROTECTED]
03/12/2003 10:39
AM
Please respond to
ORACLE-L
Re: v$sqlarea v$session
Just as a passing note - when you query v$sqlarea like this it thrashes the heck out of the library cache latch. (And v$sqlarea is also an aggregate view of x$kglob - so for a large shared pool you could end up with a massive sort and thrash of the temporary tablespace as the view is instantiated. You might be better off going for v$sql - it still thrashes the latch, but it may cost a lot less CPU and TEMP to run). Regards Jonathan Lewis http://www.jlcomp.demon.co.uk Now available One-day tutorials: Cost Based Optimisation Trouble-shooting and Tuning Indexing Strategies (see http://www.jlcomp.demon.co.uk/tutorial.html ) UK___April 8th UK___April 22nd Denmark May 21-23rd USA_(FL)_May 2nd Next dates for the 3-day seminar: (see http://www.jlcomp.demon.co.uk/seminar.html ) UK_(Manchester)_May USA_(CA, TX)_August The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: 12 March 2003 23:04 THANKS AK! 1 select oc.sid, ss.serial#, user_name, osuser, sa.sql_text 2 from v$open_cursor oc, v$session ss, v$sqlarea sa 3 where oc.sid = ss.sid 4 and oc.address = sa.address 5 and oc.hash_value = sa.hash_value 6* and buffer_gets 1 SQL / -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Blank username in v$session
These correspond to the Oracle background processes. Look at V$PROCESS for the specific information on what is executing. Thank You Stephen P. Karniotis Product Architect Compuware Corporation Direct: (248) 865-4350 Mobile: (248) 408-2918 Email: [EMAIL PROTECTED] Web:www.compuware.com -Original Message- Sent: Thursday, January 16, 2003 8:09 PM To: Multiple recipients of list ORACLE-L Subject:Blank username in v$session Hi, I have an instance that is showing null in username, and schemaname = 'sys' in v$session, these are jdbc client not background processes. Anyone know why this is the case? Rgds, Ken Heng -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ken Heng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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). The contents of this e-mail are intended for the named addressee only. It contains information that may be confidential. Unless you are the named addressee or an authorized designee, you may not copy or use it, or disclose it to anyone else. If you received it in error please notify us immediately and then destroy it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Karniotis, Stephen INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
Blank username in v$session
Hi, I have an instance that is showing null in username, and schemaname = 'sys' in v$session, these are jdbc client not background processes. Anyone know why this is the case? Rgds, Ken Heng -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ken Heng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Blank username in v$session
This is server activity not client activity. see program on v$session (it runs oracle.exe). -Original Message- From: Ken Heng [SMTP:[EMAIL PROTECTED]] Sent: Friday, January 17, 2003 8:09 AM To: Multiple recipients of list ORACLE-L Subject: Blank username in v$session Hi, I have an instance that is showing null in username, and schemaname = 'sys' in v$session, these are jdbc client not background processes. Anyone know why this is the case? Rgds, Ken Heng -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Ken Heng INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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.net -- Author: Sony kristanto INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: PROCESS column in V$SESSION
Charlie, I think Tom explained the machine column. For process column it is: = client PID eg start sqlplusw on your PC, connect to a DB and run query then start Taskmanager on your PC - = PID of sqlplusw.exe Not sure what (it's not the thread ID on the server - that is v$process.spid Run orakill with no parameters to see the join syntax for v$process and v$session Regards, Bruce Reardon -Original Message- Sent: Saturday, 12 October 2002 5:03 AM Charlie, = NT Domain = Machine Name Hope this helps. PS - do I get a Home Depot Discount Card for this? :) Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 11, 2002 11:59 AM To: Multiple recipients of list ORACLE-L For folks connecting to the DB server from another Unix box the PROCESS field is the Process ID on the host named in the MACHINE field. For folks connecting to the DB server from a PC the values are in the form of : So what do signify? WENDYC wendyc 1112:1116 HDSWIN\CSCSOPC034 WENDYC wendyc 1172:1140 HDSWIN\CSCSOPC034 MWH williamd22968 pan MWH williamd26653 pan OPS$WILLIAMD williamd26974 titan OPS$WKLINE wkline 22717 titan OPS$WSPENCER wspencer24664 titan OPS$WSPENCER wspencer4824 titan YSULLIVA ysulliva728:300 HDSWIN\CSCCSPC105 YSULLIVA ysulliva1104:1076 HDSWIN\CSCCSPC105 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
PROCESS column in V$SESSION
For folks connecting to the DB server from another Unix box the PROCESS field is the Process ID on the host named in the MACHINE field. For folks connecting to the DB server from a PC the values are in the form of : So what do signify? WENDYC wendyc 1112:1116 HDSWIN\CSCSOPC034 WENDYC wendyc 1172:1140 HDSWIN\CSCSOPC034 MWH williamd22968 pan MWH williamd26653 pan OPS$WILLIAMD williamd26974 titan OPS$WKLINE wkline 22717 titan OPS$WSPENCER wspencer24664 titan OPS$WSPENCER wspencer4824 titan YSULLIVA ysulliva728:300 HDSWIN\CSCCSPC105 YSULLIVA ysulliva1104:1076 HDSWIN\CSCCSPC105 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: PROCESS column in V$SESSION
Charlie, = NT Domain = Machine Name Hope this helps. PS - do I get a Home Depot Discount Card for this? :) Tom Mercadante Oracle Certified Professional -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Friday, October 11, 2002 11:59 AM To: Multiple recipients of list ORACLE-L For folks connecting to the DB server from another Unix box the PROCESS field is the Process ID on the host named in the MACHINE field. For folks connecting to the DB server from a PC the values are in the form of : So what do signify? WENDYC wendyc 1112:1116 HDSWIN\CSCSOPC034 WENDYC wendyc 1172:1140 HDSWIN\CSCSOPC034 MWH williamd22968 pan MWH williamd26653 pan OPS$WILLIAMD williamd26974 titan OPS$WKLINE wkline 22717 titan OPS$WSPENCER wspencer24664 titan OPS$WSPENCER wspencer4824 titan YSULLIVA ysulliva728:300 HDSWIN\CSCCSPC105 YSULLIVA ysulliva1104:1076 HDSWIN\CSCCSPC105 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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: Mercadante, Thomas F INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services - 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).
strange behaviour of status column in v$session
Hi all, OS: solaris DB: 8173 when i logged into db when I check the column status for some of the users, its status is blank. I checked metalink. In that I found, it is a bug on clinet versions 817. But I logged into the server as telnet (bequeth). I logged into the server from my PC as 2 sessions. 1. tty/20 2. tty/01 both as system user. But the program column is sqlplus for tty/20 but null for tty/01. I tried issuing different queries from both sessions, but no use. can somebody through somelight on this, why the strange behaviour. thanks and regards, srinivas __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: strange behaviour of status column in v$session
The answer is very simple: if you're using MTS and if you're not executing a SQL command or Java, you don't have a server assigned. In order to see both sessions have a server assigned, you'd need them both to be executing something simultaneously. -Original Message- From: kommareddy sreenivasa [mailto:[EMAIL PROTECTED]] Sent: Monday, August 26, 2002 3:38 AM To: Multiple recipients of list ORACLE-L Subject: strange behaviour of status column in v$session Hi all, OS: solaris DB: 8173 when i logged into db when I check the column status for some of the users, its status is blank. I checked metalink. In that I found, it is a bug on clinet versions 817. But I logged into the server as telnet (bequeth). I logged into the server from my PC as 2 sessions. 1. tty/20 2. tty/01 both as system user. But the program column is sqlplus for tty/20 but null for tty/01. I tried issuing different queries from both sessions, but no use. can somebody through somelight on this, why the strange behaviour. thanks and regards, srinivas __ Do You Yahoo!? Yahoo! Finance - Get real-time stock quotes http://finance.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: kommareddy sreenivasa 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: Gogala, Mladen 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: AW: OSUSER in V$SESSION capture in procedure?
Thanks Jared. Works great.
-Original Message-
Sent: Thursday, May 23, 2002 9:55 PM
To: Multiple recipients of list ORACLE-L
you can also do:
select osuser
fromv$session s
where sys_context('userenv', 'SESSIONID') = s.audsid;
This requires a direct grant to v_$session only.
Jared
Denham Eva [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/22/2002 11:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: OSUSER in V$SESSION capture in procedure?
Wow, this worked exactly as I hoped.
Many Thanks.
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 8:49 PM
To: Multiple recipients of list ORACLE-L
Try:
select osuser
fromv$session
where sid in (select sid
from v$mystat);
Chk
[EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Hi Eva,
1. If there is always the same osuser, why don't you use a default value.
2. Your proc couldn't work, because
Select uid from dual;
UID Gives you the USER_ID (number) from all_users.
Then you compare OSUSER (char) with UID(number)?
This should work:
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT USERNAME
INTO V_OSUSER
FROM ALL_USERS
WHERE USER_ID := V_UID;
END
rest of procedure.Includes insert etc
END TEST
HTH
Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-
Von: Denham Eva [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 22. Mai 2002 17:34
An: Multiple recipients of list ORACLE-L
Betreff: OSUSER in V$SESSION capture in procedure?
Hello Listers,
I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough. But I want to capture the OSUSER value from v$session so
that
the there is a history of changes to the table and by whom. Problem with
using USER function is that all the users access the server via a third
party app and therefore have one username. Pretty pointless for this
effort
then, as I could update the column in the history table with that user and
be done with it. But the use of UID also does not work because that brings
back a whole list of all the OSUSER value.
ie
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
WHERE OSUSER := V_UID;
END
rest of procedure.Includes insert etc
END TEST
Now obviously this returns more than one row as all the users use the same
username through the app. Any suggestion?
Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.
#
This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal
For more information please visit www.marshalsoftware.com
#
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
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).
--
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
RE: AW: OSUSER in V$SESSION capture in procedure?
you can also do:
select osuser
fromv$session s
where sys_context('userenv', 'SESSIONID') = s.audsid;
This requires a direct grant to v_$session only.
Jared
Denham Eva [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
05/22/2002 11:23 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Subject:RE: AW: OSUSER in V$SESSION capture in procedure?
Wow, this worked exactly as I hoped.
Many Thanks.
-Original Message-
[mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 22, 2002 8:49 PM
To: Multiple recipients of list ORACLE-L
Try:
select osuser
fromv$session
where sid in (select sid
from v$mystat);
Chk
[EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM
Please respond to [EMAIL PROTECTED]
Sent by:[EMAIL PROTECTED]
To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
cc:
Hi Eva,
1. If there is always the same osuser, why don't you use a default value.
2. Your proc couldn't work, because
Select uid from dual;
UID Gives you the USER_ID (number) from all_users.
Then you compare OSUSER (char) with UID(number)?
This should work:
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT USERNAME
INTO V_OSUSER
FROM ALL_USERS
WHERE USER_ID := V_UID;
END
rest of procedure.Includes insert etc
END TEST
HTH
Volker Schoen
E-Mail: mailto:[EMAIL PROTECTED]
http://www.inplan.de
-Ursprüngliche Nachricht-
Von: Denham Eva [mailto:[EMAIL PROTECTED]]
Gesendet: Mittwoch, 22. Mai 2002 17:34
An: Multiple recipients of list ORACLE-L
Betreff: OSUSER in V$SESSION capture in procedure?
Hello Listers,
I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough. But I want to capture the OSUSER value from v$session so
that
the there is a history of changes to the table and by whom. Problem with
using USER function is that all the users access the server via a third
party app and therefore have one username. Pretty pointless for this
effort
then, as I could update the column in the history table with that user and
be done with it. But the use of UID also does not work because that brings
back a whole list of all the OSUSER value.
ie
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
WHERE OSUSER := V_UID;
END
rest of procedure.Includes insert etc
END TEST
Now obviously this returns more than one row as all the users use the same
username through the app. Any suggestion?
Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.
#
This e-mail message has been scanned for Viruses and Content and cleared
by MailMarshal
For more information please visit www.marshalsoftware.com
#
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Denham Eva
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).
--
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
OSUSER in V$SESSION capture in procedure?
Hello Listers, I have what I hope is challenging problem. I am trying to create a procedure that execs from a trigger on a table. Simple enough. But I want to capture the OSUSER value from v$session so that the there is a history of changes to the table and by whom. Problem with using USER function is that all the users access the server via a third party app and therefore have one username. Pretty pointless for this effort then, as I could update the column in the history table with that user and be done with it. But the use of UID also does not work because that brings back a whole list of all the OSUSER value. ie CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT OSUSER INTO V_OSUSER FROM V$SESSION WHERE OSUSER := V_UID; END rest of procedure.Includes insert etc END TEST Now obviously this returns more than one row as all the users use the same username through the app. Any suggestion? Many TIA Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva 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: OSUSER in V$SESSION capture in procedure?
I, too, have a situation whereby all users access the db via third party app with single credentials - haven't found a way around it, other then application-side logging -bill -Original Message- Sent: Wednesday, May 22, 2002 11:34 AM To: Multiple recipients of list ORACLE-L Hello Listers, I have what I hope is challenging problem. I am trying to create a procedure that execs from a trigger on a table. Simple enough. But I want to capture the OSUSER value from v$session so that the there is a history of changes to the table and by whom. Problem with using USER function is that all the users access the server via a third party app and therefore have one username. Pretty pointless for this effort then, as I could update the column in the history table with that user and be done with it. But the use of UID also does not work because that brings back a whole list of all the OSUSER value. ie CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT OSUSER INTO V_OSUSER FROM V$SESSION WHERE OSUSER := V_UID; END rest of procedure.Includes insert etc END TEST Now obviously this returns more than one row as all the users use the same username through the app. Any suggestion? Many TIA Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva 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: Magaliff, Bill 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).
AW: OSUSER in V$SESSION capture in procedure?
Hi Eva, 1. If there is always the same osuser, why don't you use a default value. 2. Your proc couldn't work, because Select uid from dual; UID Gives you the USER_ID (number) from all_users. Then you compare OSUSER (char) with UID(number)? This should work: CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT USERNAME INTO V_OSUSER FROM ALL_USERS WHERE USER_ID := V_UID; END rest of procedure.Includes insert etc END TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Denham Eva [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 22. Mai 2002 17:34 An: Multiple recipients of list ORACLE-L Betreff: OSUSER in V$SESSION capture in procedure? Hello Listers, I have what I hope is challenging problem. I am trying to create a procedure that execs from a trigger on a table. Simple enough. But I want to capture the OSUSER value from v$session so that the there is a history of changes to the table and by whom. Problem with using USER function is that all the users access the server via a third party app and therefore have one username. Pretty pointless for this effort then, as I could update the column in the history table with that user and be done with it. But the use of UID also does not work because that brings back a whole list of all the OSUSER value. ie CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT OSUSER INTO V_OSUSER FROM V$SESSION WHERE OSUSER := V_UID; END rest of procedure.Includes insert etc END TEST Now obviously this returns more than one row as all the users use the same username through the app. Any suggestion? Many TIA Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva 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: AW: OSUSER in V$SESSION capture in procedure?
Try: select osuser fromv$session where sid in (select sid from v$mystat); Chk [EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Eva, 1. If there is always the same osuser, why don't you use a default value. 2. Your proc couldn't work, because Select uid from dual; UID Gives you the USER_ID (number) from all_users. Then you compare OSUSER (char) with UID(number)? This should work: CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT USERNAME INTO V_OSUSER FROM ALL_USERS WHERE USER_ID := V_UID; END rest of procedure.Includes insert etc END TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Denham Eva [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 22. Mai 2002 17:34 An: Multiple recipients of list ORACLE-L Betreff: OSUSER in V$SESSION capture in procedure? Hello Listers, I have what I hope is challenging problem. I am trying to create a procedure that execs from a trigger on a table. Simple enough. But I want to capture the OSUSER value from v$session so that the there is a history of changes to the table and by whom. Problem with using USER function is that all the users access the server via a third party app and therefore have one username. Pretty pointless for this effort then, as I could update the column in the history table with that user and be done with it. But the use of UID also does not work because that brings back a whole list of all the OSUSER value. ie CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT OSUSER INTO V_OSUSER FROM V$SESSION WHERE OSUSER := V_UID; END rest of procedure.Includes insert etc END TEST Now obviously this returns more than one row as all the users use the same username through the app. Any suggestion? Many TIA Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva 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). -- 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: OSUSER in V$SESSION capture in procedure?
Denham Eva wrote:
Hello Listers,
I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough.
But I want to capture the OSUSER value from v$session so that the there is a
history of changes to the table and by whom.
Problem with using USER function is that all the users access the server via
a third party app and therefore have one username.
Pretty pointless for this effort then, as I could update the column in the
history table with that user and be done with it.
But the use of UID also does not work because that brings back a whole list
of all the OSUSER value.
ie
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE)
IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
WHERE OSUSER := V_UID;
END
rest of procedure.Includes insert etc
END TEST
Now obviously this returns more than one row as all the users use the same
username through the app.
Any suggestion?
Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.
Matching AUDSID with SYS_CONTEXT('USERENV', 'SESSIONID') will return a
single row (and the good one). Intuitive enough, isn't it.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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: OSUSER in V$SESSION capture in procedure?
One thing that is possible to get a unique system id you can allways do
the following:
When the user logs into your application, place a record in a table stating
all the pertinent info like application, machine id, date/time, etc.
Allowing with this info use the DBMS_SESSION package and get the UNIQUE
IDENTIFIER for the user. You can do:
select dbms_session.unique_session_id into unique_identifier from dual;
For all further updates/etc use this variable in the update of an IDENTIFIER
column.
If you want to know who did the update then, take the IDENTIFIER column and
match it up to the information in the LOGIN table you added to at the
begining of the session.
-Original Message-
Sent: Wednesday, May 22, 2002 2:52 PM
To: Multiple recipients of list ORACLE-L
Denham Eva wrote:
Hello Listers,
I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough.
But I want to capture the OSUSER value from v$session so that the there is
a
history of changes to the table and by whom.
Problem with using USER function is that all the users access the server
via
a third party app and therefore have one username.
Pretty pointless for this effort then, as I could update the column in the
history table with that user and be done with it.
But the use of UID also does not work because that brings back a whole
list
of all the OSUSER value.
ie
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE)
IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
WHERE OSUSER := V_UID;
END
rest of procedure.Includes insert etc
END TEST
Now obviously this returns more than one row as all the users use the same
username through the app.
Any suggestion?
Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.
Matching AUDSID with SYS_CONTEXT('USERENV', 'SESSIONID') will return a
single row (and the good one). Intuitive enough, isn't it.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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: Kevin Lange
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: OSUSER in V$SESSION capture in procedure?
you can also get the sid directly (if you don't want to use any packages)
from the steve adams script:
select
p.pid,
s.sid,
s.serial#
from
sys.v_$session s,
sys.v_$process p
where
s.sid = (select sid from sys.v_$mystat where rownum = 1) and
p.addr = s.paddr
/
from this you can lookup the pertinent info in the v$session table.
-Original Message-
Sent: Wednesday, May 22, 2002 2:52 PM
To: Multiple recipients of list ORACLE-L
Denham Eva wrote:
Hello Listers,
I have what I hope is challenging problem.
I am trying to create a procedure that execs from a trigger on a table.
Simple enough.
But I want to capture the OSUSER value from v$session so that the there is
a
history of changes to the table and by whom.
Problem with using USER function is that all the users access the server
via
a third party app and therefore have one username.
Pretty pointless for this effort then, as I could update the column in the
history table with that user and be done with it.
But the use of UID also does not work because that brings back a whole
list
of all the OSUSER value.
ie
CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE)
IS
V_UID NUMBER;
V_OSUSER
BEGIN
BEGIN
SELECT UID
INTO V_UID
FROM DUAL;
END;
BEGIN
SELECT OSUSER
INTO V_OSUSER
FROM V$SESSION
WHERE OSUSER := V_UID;
END
rest of procedure.Includes insert etc
END TEST
Now obviously this returns more than one row as all the users use the same
username through the app.
Any suggestion?
Many TIA
Denham Eva
Oracle DBA
In UNIX Land
On a quiet Night, you can hear the Windows machines reboot.
Matching AUDSID with SYS_CONTEXT('USERENV', 'SESSIONID') will return a
single row (and the good one). Intuitive enough, isn't it.
--
Regards,
Stephane Faroult
Oriole Software
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Stephane Faroult
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: Kevin Lange
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: AW: OSUSER in V$SESSION capture in procedure?
Wow, this worked exactly as I hoped. Many Thanks. -Original Message- [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 8:49 PM To: Multiple recipients of list ORACLE-L Try: select osuser fromv$session where sid in (select sid from v$mystat); Chk [EMAIL PROTECTED]@fatcity.com on 05/22/2002 01:14:52 PM Please respond to [EMAIL PROTECTED] Sent by:[EMAIL PROTECTED] To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Hi Eva, 1. If there is always the same osuser, why don't you use a default value. 2. Your proc couldn't work, because Select uid from dual; UID Gives you the USER_ID (number) from all_users. Then you compare OSUSER (char) with UID(number)? This should work: CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT USERNAME INTO V_OSUSER FROM ALL_USERS WHERE USER_ID := V_UID; END rest of procedure.Includes insert etc END TEST HTH Volker Schoen E-Mail: mailto:[EMAIL PROTECTED] http://www.inplan.de -Ursprüngliche Nachricht- Von: Denham Eva [mailto:[EMAIL PROTECTED]] Gesendet: Mittwoch, 22. Mai 2002 17:34 An: Multiple recipients of list ORACLE-L Betreff: OSUSER in V$SESSION capture in procedure? Hello Listers, I have what I hope is challenging problem. I am trying to create a procedure that execs from a trigger on a table. Simple enough. But I want to capture the OSUSER value from v$session so that the there is a history of changes to the table and by whom. Problem with using USER function is that all the users access the server via a third party app and therefore have one username. Pretty pointless for this effort then, as I could update the column in the history table with that user and be done with it. But the use of UID also does not work because that brings back a whole list of all the OSUSER value. ie CREATE OR REPLACE PROCEDURE TEST(TST_HIST IN TEST_TBL%ROWTYPE) IS V_UID NUMBER; V_OSUSER BEGIN BEGIN SELECT UID INTO V_UID FROM DUAL; END; BEGIN SELECT OSUSER INTO V_OSUSER FROM V$SESSION WHERE OSUSER := V_UID; END rest of procedure.Includes insert etc END TEST Now obviously this returns more than one row as all the users use the same username through the app. Any suggestion? Many TIA Denham Eva Oracle DBA In UNIX Land On a quiet Night, you can hear the Windows machines reboot. # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Denham Eva 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). -- 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). # This e-mail message has been scanned for Viruses and Content
Zero-term'd machine in V$SESSION from Winders
So, there I am, creating a special kill user script (don't ask) for one of our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output rows were getting truncated on the output. Here's the proc I had started: CREATE OR REPLACE PROCEDURE Euthanize AS v_printline VARCHAR2(140); CURSOR c_sessions IS SELECT vs.username, vs.osuser, -- --REPLACE(vs.machine,CHR(0),NULL) MACHINE, vs.machine, -- , vs.logon_time, vs.last_call_et, vp.SPID FROM v$session vs, v$process vp WHERE vs.username IS NOT NULL AND vs.paddr = vp.addr ORDER BY vs.last_call_et DESC; BEGIN FOR rsess IN c_sessions LOOP v_printline := rsess.username||'|'|| rsess.osuser||'|'||rsess.machine||'|'|| rsess.logontime||'|'||rsess.idletime||'|'|| rsess.spid||'|'||rsess.logon_time; dbms_output.put_line(v_printline); END LOOP; END Euthanize; After compiling this, I called it from SQL*Plus using execute euthanize;. I noticed that all the DBMS_OUTPUT lines that were truncated were sessions from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION showed that all of the MACHINE columns from Windohs sessions were zero-terminated. No other client (Solaris) was. Since the output from DBMS_OUTPUT is being prematurely truncated by this zero-term'd field, the workaround is to either move the problem field to the end of the output line, or to use the REPLACE function, as I've commented out in the above code. The latter is necessary if there is more than one bastardized field like this or if the order of the columns in the output is important. Can anyone reproduce this? The particular clients I found with the problem are 8.1.7. TIA! 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).
RE: Zero-term'd machine in V$SESSION from Winders
Rich, We do have Win clients, and V$SESSION does show proper values for machine and terminal. Are these sessions for background processes and/or slave processes? Jay -Original Message- Sent: Tuesday, May 14, 2002 4:23 PM To: Multiple recipients of list ORACLE-L So, there I am, creating a special kill user script (don't ask) for one of our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output rows were getting truncated on the output. Here's the proc I had started: CREATE OR REPLACE PROCEDURE Euthanize AS v_printline VARCHAR2(140); CURSOR c_sessions IS SELECT vs.username, vs.osuser, -- --REPLACE(vs.machine,CHR(0),NULL) MACHINE, vs.machine, -- , vs.logon_time, vs.last_call_et, vp.SPID FROM v$session vs, v$process vp WHERE vs.username IS NOT NULL AND vs.paddr = vp.addr ORDER BY vs.last_call_et DESC; BEGIN FOR rsess IN c_sessions LOOP v_printline := rsess.username||'|'|| rsess.osuser||'|'||rsess.machine||'|'|| rsess.logontime||'|'||rsess.idletime||'|'|| rsess.spid||'|'||rsess.logon_time; dbms_output.put_line(v_printline); END LOOP; END Euthanize; After compiling this, I called it from SQL*Plus using execute euthanize;. I noticed that all the DBMS_OUTPUT lines that were truncated were sessions from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION showed that all of the MACHINE columns from Windohs sessions were zero-terminated. No other client (Solaris) was. Since the output from DBMS_OUTPUT is being prematurely truncated by this zero-term'd field, the workaround is to either move the problem field to the end of the output line, or to use the REPLACE function, as I've commented out in the above code. The latter is necessary if there is more than one bastardized field like this or if the order of the columns in the output is important. Can anyone reproduce this? The particular clients I found with the problem are 8.1.7. TIA! 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). This electronic message contains information from CTIS, Inc., which may be company sensitive, proprietary, privileged or otherwise protected from disclosure. The information is intended to be used solely by the recipients named above. If you are not an intended recipient, be aware that any review, disclosure, copying, distribution or use of this transmission or its contents is prohibited. If you have received this transmission in error, please notify us immediately at [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jay Mehta 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: Zero-term'd machine in V$SESSION from Winders
Jesse, Our database is 81714 on NT4. Our clients are a mix of 816 OCI, 817 sqlplus and forms 4.5. The forms 4.5 clients include only the PC name in machine column and it is not chr(0) terminated. Their terminal field is set to 'Windows NT PC' Our 816 817 clients have machine set to 'domain\pc_name' and it is chr(0) terminated. Their terminal field has just the PC name and it is not chr(0) terminated. Maybe you can use something like decode ( a.terminal , 'Windows NT PC' , a.machine , a.terminal ) Terminal Regards, Bruce Reardon -Original Message- Sent: Wednesday, 15 May 2002 6:23 So, there I am, creating a special kill user script (don't ask) for one of our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output rows were getting truncated on the output. Here's the proc I had started: CREATE OR REPLACE PROCEDURE Euthanize AS v_printline VARCHAR2(140); CURSOR c_sessions IS SELECT vs.username, vs.osuser, -- --REPLACE(vs.machine,CHR(0),NULL) MACHINE, vs.machine, -- , vs.logon_time, vs.last_call_et, vp.SPID FROM v$session vs, v$process vp WHERE vs.username IS NOT NULL AND vs.paddr = vp.addr ORDER BY vs.last_call_et DESC; BEGIN FOR rsess IN c_sessions LOOP v_printline := rsess.username||'|'|| rsess.osuser||'|'||rsess.machine||'|'|| rsess.logontime||'|'||rsess.idletime||'|'|| rsess.spid||'|'||rsess.logon_time; dbms_output.put_line(v_printline); END LOOP; END Euthanize; After compiling this, I called it from SQL*Plus using execute euthanize;. I noticed that all the DBMS_OUTPUT lines that were truncated were sessions from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION showed that all of the MACHINE columns from Windohs sessions were zero-terminated. No other client (Solaris) was. Since the output from DBMS_OUTPUT is being prematurely truncated by this zero-term'd field, the workaround is to either move the problem field to the end of the output line, or to use the REPLACE function, as I've commented out in the above code. The latter is necessary if there is more than one bastardized field like this or if the order of the columns in the output is important. Can anyone reproduce this? The particular clients I found with the problem are 8.1.7. TIA! 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: Reardon, Bruce (CALBBAY) 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: Zero-term'd machine in V$SESSION from Winders
Title: RE: Zero-term'd machine in V$SESSION from Winders Rich, I tested on 8.1.7.2 on HP. For Windows client (7.3, 8.0, 8.1) machine has null terminator, terminal does not. For HP client neither does. For backgrounds machine does not, but terminal set to UNKNOWN with null terminator. HTH. Alex. -Original Message- From: Jesse, Rich [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 14, 2002 1:23 PM To: Multiple recipients of list ORACLE-L Subject: Zero-term'd machine in V$SESSION from Winders So, there I am, creating a special kill user script (don't ask) for one of our instances, 8.1.6.0.0 on Solaris. The problem is that some of the output rows were getting truncated on the output. Here's the proc I had started: CREATE OR REPLACE PROCEDURE Euthanize AS v_printline VARCHAR2(140); CURSOR c_sessions IS SELECT vs.username, vs.osuser, -- --REPLACE(vs.machine,CHR(0),NULL) MACHINE, vs.machine, -- , vs.logon_time, vs.last_call_et, vp.SPID FROM v$session vs, v$process vp WHERE vs.username IS NOT NULL AND vs.paddr = vp.addr ORDER BY vs.last_call_et DESC; BEGIN FOR rsess IN c_sessions LOOP v_printline := rsess.username||'|'|| rsess.osuser||'|'||rsess.machine||'|'|| rsess.logontime||'|'||rsess.idletime||'|'|| rsess.spid||'|'||rsess.logon_time; dbms_output.put_line(v_printline); END LOOP; END Euthanize; After compiling this, I called it from SQL*Plus using execute euthanize;. I noticed that all the DBMS_OUTPUT lines that were truncated were sessions from Windohs workstations. Examining a SELECT DUMP(machine) FROM V$SESSION showed that all of the MACHINE columns from Windohs sessions were zero-terminated. No other client (Solaris) was. Since the output from DBMS_OUTPUT is being prematurely truncated by this zero-term'd field, the workaround is to either move the problem field to the end of the output line, or to use the REPLACE function, as I've commented out in the above code. The latter is necessary if there is more than one bastardized field like this or if the order of the columns in the output is important. Can anyone reproduce this? The particular clients I found with the problem are 8.1.7. TIA! Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
RE: V$SESSION
1. The user has connected, but not issued a SQL statement. 2. The user has been connected a while, issued a statement some time ago, but that statement has been flushed via the buffer under the LRU algorithm. Any more? HTH Mark === Mark Leith | T: +44 (0)1905 330 281 Sales Marketing | F: +44 (0)870 127 5283 Cool Tools UK Ltd | E: [EMAIL PROTECTED] === http://www.cool-tools.co.uk Maximising throughput performance -Original Message- Mengler Sent: 11 April 2002 21:29 To: Multiple recipients of list ORACLE-L If there is an entry in V$SESSION, does it imply that user has a current session within the instance? If not, how do I determine which users have logged out of the DB? Under what conditions would I not be able to obtain any SQL from V$SQLAREA for any given SID? 1 select username, osuser, sql_text 2 from v$session ss, v$sqlarea sa 3 where ss.sid = 861 4 and ss.sql_address = sa.address 5* and ss.sql_hash_value = sa.hash_value [EMAIL PROTECTED] / no rows selected [EMAIL PROTECTED] select * from v$session where sid = 861; SADDR SIDSERIAL# AUDSID PADDR USER# USERNAME COMMAND TADDR -- -- -- -- -- -- LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS - -- -- -- - - MACHINE TERMINAL - PROGRAM TYPE SQL_ADDR SQL_HASH_VALUE PREV_SQL -- --- --- PREV_HASH_VALUE MODULE MODULE_HASH --- --- ACTION ACTION_HASH --- CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# --- - - ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET -- --- - - AF4518E4861680 47669205 AF289B1C 2494 OPS$RCAMPBEL 0 INACTIVE DEDICATED 2494 OPS$RCAMPBEL rcampbel25645 titanpts/200 runform30@titan (TNS interface) USER 00 0 A54EFF6C -2.129E+09 frmula_inq1635528872 from menu_driver 306861083 683581-1 14 13785328 11-APR-02 117 -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Daylight Savings Time means that everybody gets up 1 hour earlier. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: Mark Leith 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).
V$SESSION
If there is an entry in V$SESSION, does it imply that user has a current session within the instance? If not, how do I determine which users have logged out of the DB? Under what conditions would I not be able to obtain any SQL from V$SQLAREA for any given SID? 1 select username, osuser, sql_text 2 from v$session ss, v$sqlarea sa 3 where ss.sid = 861 4 and ss.sql_address = sa.address 5* and ss.sql_hash_value = sa.hash_value [EMAIL PROTECTED] / no rows selected [EMAIL PROTECTED] select * from v$session where sid = 861; SADDR SIDSERIAL# AUDSID PADDR USER# USERNAME COMMAND TADDR -- -- -- -- -- -- LOCKWAIT STATUS SERVER SCHEMA# SCHEMANAME OSUSER PROCESS - -- -- --- - MACHINE TERMINAL - PROGRAM TYPE SQL_ADDR SQL_HASH_VALUE PREV_SQL -- -- PREV_HASH_VALUE MODULE MODULE_HASH --- --- ACTION ACTION_HASH --- CLIENT_INFO FIXED_TABLE_SEQUENCE ROW_WAIT_OBJ# - ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# LOGON_TIM LAST_CALL_ET -- --- - - AF4518E4861680 47669205 AF289B1C 2494 OPS$RCAMPBEL0 INACTIVE DEDICATED 2494 OPS$RCAMPBEL rcampbel 25645 titanpts/200 runform30@titan (TNS interface) USER 000 A54EFF6C -2.129E+09 frmula_inq1635528872 from menu_driver 306861083 683581 -1 14 13785328 11-APR-02 117 -- Charlie Mengler Maintenance Warehouse [EMAIL PROTECTED] 10641 Scripps Summit Ct. 858-831-2229 San Diego, CA 92131 Daylight Savings Time means that everybody gets up 1 hour earlier. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Charlie Mengler 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: very interesting problem with V$SESSION and web applications....
Hello Bunyamin We have the same problem. I think that you can solve it with LDAP and enterprise users. Yechiel Adar Mehish - Original Message - From: Bunyamin K. Karadeniz To: Multiple recipients of list ORACLE-L Sent: Friday, April 05, 2002 11:15 PM Subject: very interesting problem with V$SESSION and web applications Dear Gurus , I have a comic question . ? We have a db and ias and portal . users log in by using portal login page . The problem is : because application server connects to db , in v$session the machines are all the application server machine . Although the users are db users , when you login from portal , the usernames are portal30 and portal30_sso .. So how will I know which user is which session ? V$session gives no help ... May be comic :) But can not find an answer .. Investigating portal for writing into v$session as the real username ..But no other thing comes into my mind Any idea please ... Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA.
very interesting problem with V$SESSION and web applications....
Dear Gurus , I have a comic question . ? We have a db and ias and portal . users log in by using portal login page . The problem is : because application server connects to db , in v$session the machines are all the application server machine . Although the users are db users , when you login from portal , the usernames are portal30 and portal30_sso .. So how will I know which user is which session ? V$session gives no help ... May be comic :) But can not find an answer .. Investigating portal for writing into v$session as the real username ..But no other thing comes into my mind Any idea please ... Bunyamin K. Karadeniz Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S. Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 / 1217Mobile : +90 535 3357729 The degree of normality in a database is inversely proportional to that of its DBA.
RE: very interesting problem with V$SESSION and web applications.
Assuming that the application "knows" the real username, let the first
thing that the app does is call dbms_application_info.set_client_info passing
the real username as a parameter. This sets v$session.client_info to the
real username.
T10-PARTS select client_info from v$session 2 where
sid = (select sid from v$mystat where rownum = 1) ;
CLIENT_INFO
T10-PARTS execute
dbms_application_info.set_client_info('REAL_USER_NAME');
PL/SQL procedure successfully
completed.
T10-PARTS select client_info from
v$session 2 where sid = (select sid from v$mystat where rownum =
1) ;
CLIENT_INFOREAL_USER_NAME
T10-PARTS select
userenv('CLIENT_INFO') from dual;
USERENV('CLIENT_INFO')REAL_USER_NAME
T10-PARTS select
sys_context('userenv', 'client_info') from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO')REAL_USER_NAME
Hope this helps
Paul-Original Message-From:
Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]]Sent: Friday,
April 05, 2002 4:16 PMTo: Multiple recipients of list
ORACLE-LSubject: very interesting problem with V$SESSION and web
applications
Dear Gurus , I have a comic question .
?
We have a db and ias and portal . users log in by
using portal login page .
The problem is : because application server
connects to db , in v$session the machines are all the application server
machine .
Although the users are db users , when you login
from portal , the usernames are portal30 and portal30_sso ..
So how will I know which user is which session ?
V$session gives no help ...
May be comic :) But can not find an answer ..
Investigating portal for writing into v$session as the real username ..But no
other thing comes into my mind
Any idea please ...
Bunyamin K.
Karadeniz
Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S.
Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 /
1217Mobile : +90 535 3357729
The degree of normality in a database is
inversely proportional to that of its
DBA.
Re: very interesting problem with V$SESSION and web applications.
ThANK YOU SO MUCH
. ...
I will enter this code to login code of portal
and test it. thank you again .
Assuming that the application "knows" the real username, let the first
thing that the app does is call dbms_application_info.set_client_info passing
the real username as a parameter. This sets v$session.client_info to the
real username.
T10-PARTS select client_info from v$session 2 where
sid = (select sid from v$mystat where rownum = 1) ;
CLIENT_INFO
T10-PARTS execute
dbms_application_info.set_client_info('REAL_USER_NAME');
PL/SQL procedure successfully
completed.
T10-PARTS select client_info
from v$session 2 where sid = (select sid from v$mystat where
rownum = 1) ;
CLIENT_INFOREAL_USER_NAME
T10-PARTS select
userenv('CLIENT_INFO') from dual;
USERENV('CLIENT_INFO')REAL_USER_NAME
T10-PARTS select
sys_context('userenv', 'client_info') from dual;
SYS_CONTEXT('USERENV','CLIENT_INFO')REAL_USER_NAME
Hope this helps
Paul-Original Message-From:
Bunyamin K. Karadeniz [mailto:[EMAIL PROTECTED]]Sent:
Friday, April 05, 2002 4:16 PMTo: Multiple recipients of list
ORACLE-LSubject: very interesting problem with V$SESSION and web
applications
Dear Gurus , I have a comic question .
?
We have a db and ias and portal . users log in
by using portal login page .
The problem is : because application server
connects to db , in v$session the machines are all the application server
machine .
Although the users are db users , when you
login from portal , the usernames are portal30 and portal30_sso ..
So how will I know which user is which session
? V$session gives no help ...
May be comic :) But can not find an answer ..
Investigating portal for writing into v$session as the real username ..But
no other thing comes into my mind
Any idea please ...
Bunyamin K.
Karadeniz
Oracle DBA / DeveloperCivilian IT DepartmentHavelsan A.S.
Eskisehir yolu 7.km Ankara TurkeyPhone: +90 312 2873565 /
1217Mobile : +90 535 3357729
The degree of normality in a database is
inversely proportional to that of its
DBA.
V$SESSION with no osuser and no username
Hi all, I'm using Sun Solaris 7 with Sun Clustering cluster 2.2 I have 4 strange users connected in this machine Oracle 8.1.6.0.0 (Dedicated) Characteristic of unknown users (from v$session) : - 4 of them connected without USERNAME, OSUSER, PROGRAM, TERMINAL, MACHINE (they are NULLl) - Logon_time keep changing to the current time, - user# = 0 (SYS according to dba_users) - status = active - type = user - Last_call_et values are the same for four of them (20) Can someone explain who are those users Thanks Sinardy -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Sinard Xing 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$SESSION with no osuser and no username
Sinard Xing wrote: Hi all, I'm using Sun Solaris 7 with Sun Clustering cluster 2.2 I have 4 strange users connected in this machine Oracle 8.1.6.0.0 (Dedicated) Characteristic of unknown users (from v$session) : - 4 of them connected without USERNAME, OSUSER, PROGRAM, TERMINAL, MACHINE (they are NULLl) - Logon_time keep changing to the current time, - user# = 0 (SYS according to dba_users) - status = active - type = user - Last_call_et values are the same for four of them (20) Can someone explain who are those users Thanks Sinardy Sinardy, Can be processes for jobs (most likely), Intermedia-style processes, etc. Join on V$PROCESS or V$BGPROCESS to have the fuller picture. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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$session question
Thanks for the info about module. I've got a script myself that show's info like GUI/character users, connect times etc... The piece I was now missing was the module field. Thanks for the info. Joe On Fri, 25 Jan 2002, Catherine LeBlanc wrote: Yes, that will do it, but module does not have ifrun60, it has the actual Oracle form name that the user is running. Joe, I have a script to show all active connections and all that stuff if you want it. Catherine LeBlanc DBA, Bates College, Lewiston, ME At 07:36 AM 1/25/02 -0800, you wrote: Hi, Try module in v$session (just a guess) Jack Joe LaCascio [EMAIL PROTECTED]@fatcity.com on 25-01-2002 15:35:24 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Catherine LeBlanc 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: Joe LaCascio 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$session question
Hi, There is a bug (1237128, http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=BUGp_id=1237128) that might be related to this. The title of the bug is V$SESSION.PROGRAM HAS NO VALUE USING WINNT ORACLE8/8I CLIENT CONNECTION In our case, we see this when 816 NT OCI apps connect to our NT 817 database - we did not see it with the 816 OCI clients connecting to NT 815 database. HTH, Bruce Reardon -Original Message- Sent: Tuesday, 29 January 2002 1:35 Thanks for the info about module. I've got a script myself that show's info like GUI/character users, connect times etc... The piece I was now missing was the module field. Thanks for the info. Joe On Fri, 25 Jan 2002, Catherine LeBlanc wrote: Yes, that will do it, but module does not have ifrun60, it has the actual Oracle form name that the user is running. Joe, I have a script to show all active connections and all that stuff if you want it. Catherine LeBlanc DBA, Bates College, Lewiston, ME At 07:36 AM 1/25/02 -0800, you wrote: Hi, Try module in v$session (just a guess) Jack Joe LaCascio [EMAIL PROTECTED]@fatcity.com on 25-01-2002 15:35:24 cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Reardon, Bruce (CALBBAY) 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).
v$session question
In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe LaCascio 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$session question
Hi, Try module in v$session (just a guess) Jack Joe LaCascio [EMAIL PROTECTED]@fatcity.com on 25-01-2002 15:35:24 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe LaCascio 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). == De informatie verzonden in dit e-mailbericht is vertrouwelijk en is uitsluitend bestemd voor de geadresseerde. Openbaarmaking, vermenigvuldiging, verspreiding en/of verstrekking van deze informatie aan derden is, behoudens voorafgaande schriftelijke toestemming van Ernst Young, niet toegestaan. Ernst Young staat niet in voor de juiste en volledige overbrenging van de inhoud van een verzonden e-mailbericht, noch voor tijdige ontvangst daarvan. Ernst Young kan niet garanderen dat een verzonden e-mailbericht vrij is van virussen, noch dat e-mailberichten worden overgebracht zonder inbreuk of tussenkomst van onbevoegde derden. Indien bovenstaand e-mailbericht niet aan u is gericht, verzoeken wij u vriendelijk doch dringend het e-mailbericht te retourneren aan de verzender en het origineel en eventuele kopieën te verwijderen en te vernietigen. Ernst Young hanteert bij de uitoefening van haar werkzaamheden algemene voorwaarden, waarin een beperking van aansprakelijkheid is opgenomen. De algemene voorwaarden worden u op verzoek kosteloos toegezonden. = The information contained in this communication is confidential and is intended solely for the use of the individual or entity to whom it is addressed. You should not copy, disclose or distribute this communication without the authority of Ernst Young. Ernst Young is neither liable for the proper and complete transmission of the information contained in this communication nor for any delay in its receipt. Ernst Young does not guarantee that the integrity of this communication has been maintained nor that the communication is free of viruses, interceptions or interference. If you are not the intended recipient of this communication please return the communication to the sender and delete and destroy all copies. In carrying out its engagements, Ernst Young applies general terms and conditions, which contain a clause that limits its liability. A copy of these terms and conditions is available on request free of charge. === -- 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$session question
What platform? On HP-UX 11.0 and Oracle 8.1.6.2.0 and w/ Clients on Win/NT 4 Workstations, here is what I get: (last few lines) SQL select username, program from v$session; USERNAMEPROGRAM --- --- X468Y02 C:\orant\bin\ifrun60.exe X0C0AJF C:\orant\bin\ifrun60.exe X020C7P C:\orant\bin\ifrun60.exe XFZGBMX C:\orant\bin\ifrun60.exe X225D64 C:\orant\bin\ifrun60.exe XDTF9GR C:\orant\bin\ifrun60.exe - Kirti -Original Message- Sent: Friday, January 25, 2002 8:35 AM To: Multiple recipients of list ORACLE-L In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Joe LaCascio 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: Deshpande, Kirti 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$session question
Yes, that will do it, but module does not have ifrun60, it has the actual Oracle form name that the user is running. Joe, I have a script to show all active connections and all that stuff if you want it. Catherine LeBlanc DBA, Bates College, Lewiston, ME At 07:36 AM 1/25/02 -0800, you wrote: Hi, Try module in v$session (just a guess) Jack Joe LaCascio [EMAIL PROTECTED]@fatcity.com on 25-01-2002 15:35:24 Please respond to [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc:(bcc: Jack van Zanen/nlzanen1/External/MEY/NL) In the past, I was running Oracle 8.1.5 and the clients were running Oracle Forms 4.5. When I queried v$session and looked at the program field I could see what clients where running f45run32.exe. Now we are on Oracle 8.1.6 and the clients are running Forms60. When I now query v$session the program filed is null? What view could I query in 8.1.6 to see which users are running ifrun60.exe? Thanks, Joe -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Catherine LeBlanc 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).
inconsistency between v$session_wait and v$session?
I must be interpreting something incorrectly here, but I can't figure out what. I was following a session which was waiting (as seen in v$session_wait) for 'SQL*Net message from dblink'. The sequence# wasn't incrementing so I assumed it was pulling back a bunch of data. So next I went to look at the SQL in v$sql querying on the sql_address value in v$session. The query I found had only one table, and it was local. Going back to v$session_wait, I still found the same event and seq#. What's up? Any ideas here? Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Henry Poras 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).
Program field in v$session
This column is 64 characters in length - if for example the shortcut to a program is longer than this, it can't all be stored, so is there any way of getting this info? Thanks, Steven H. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington 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: Program field in v$session
DBMS_APPLICATION_INFO Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes. Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Friday, October 05, 2001 7:40 AM To: Multiple recipients of list ORACLE-L This column is 64 characters in length - if for example the shortcut to a program is longer than this, it can't all be stored, so is there any way of getting this info? Thanks, Steven H. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Steven Hovington 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: Christopher Spence 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$session
Title: RE: V$session
It's in v$process. Here's a script that displays it that I got off this list a while ago
set linesize 2000
column logon_time format a17
column username format a16
column unix_pid format a8
column machine format a20
column client_Program format a20
set pagesize 200
select
s.username,
s.sid,
s.serial#,
to_char(p.spid) unix_pid,
s.status,
to_char(logon_time, 'mm/dd/yy hh24:mi:ss') logon_time,
-- idle time
-- days added to hours
--( trunc(LAST_CALL_ET/86400) * 24 ) || ':' ||
-- days separately
substr('0'||trunc(LAST_CALL_ET/86400),-2,2) || ':' ||
-- hours
substr('0'||trunc(mod(LAST_CALL_ET,86400)/3600),-2,2) || ':' ||
-- minutes
substr('0'||trunc(mod(mod(LAST_CALL_ET,86400),3600)/60),-2,2) || ':' ||
--seconds
substr('0'||mod(mod(mod(LAST_CALL_ET,86400),3600),60),-2,2) idle_time,
substr(s.program,1,20) client_program,
s.machine,
s.process client_process,
s.osuser,
substr(p.program,1,20) server_program
from v$session s, v$process p
where s.username is not null
and p.addr = s.paddr
order by username, 2
/
Lisa Rutland Koivu
Oracle Database Administrator
Certified Self-Important Database Deity
Slayer of Unix Administrators
Wanton Kickboxing Goddess
[EMAIL PROTECTED]
NeoMedia
2201 Second St., Suite 600
Fort Myers, FL 33901, USA
Phone: 941-337-3434
Fax: 941-337-3668
www.neom.com http://www.neom.com
www.paperclick.com http://www.paperclick.com
www.qode.com http://www.qode.com
P a p e r C l i c k . c o m http://www.paperclick.com/home.htm
Enter Your PaperClick Code Here!
-Original Message-
From: Seema Singh [mailto:[EMAIL PROTECTED]]
Sent: Wednesday, May 02, 2001 12:01 PM
To: Multiple recipients of list ORACLE-L
Subject: V$session
Hi
Which column in v$session indicate os process?
Thanks
-seema
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Seema Singh
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$session
The column named PROCESS. [EMAIL PROTECTED] 05/02/01 12:00PM Hi Which column in v$session indicate os process? Thanks -seema _ Get your FREE download of MSN Explorer at http://explorer.msn.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Seema Singh 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: Tim Sawmiller 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: Tracking JDBC clients in V$SESSION
On Unix I run 'netstat -a', count the number of SQLNet connections per remote host, then compare that with the number of connections per username in v$session. With that you can kinda deduce what hosts are connected to what username. Maybe there is a better way... Jesse, Rich wrote: So, there I am, trying to find out what client a connection to an 8.1.7 DB on Solaris is coming from. Naturally, I SELECT USERNAME, MACHINE, TERMINAL FROM V$SESSION; Lo and behold, there is a generic login for the username, column MACHINE in all rows contains jdbcclient and TERMINAL contains unknown. With SQLNet and Net8 these two columns are populated as one would expect. Also, the OSUSER column is generic, so I'm not able to use that info. Never having used JDBC (yet), is there a setting in the JDBC client to set these? More importantly, if there is, WHY would there be such a setting? And how am I supposed to track this down, aside from SHUTDOWN ABORT (ick)? TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA -- Name: InterScan_Disclaimer.txt InterScan_Disclaimer.txtType: Plain Text (text/plain) Encoding: 7bit -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: SuzyV 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).
Tracking JDBC clients in V$SESSION
So, there I am, trying to find out what client a connection to an 8.1.7 DB on Solaris is coming from. Naturally, I SELECT USERNAME, MACHINE, TERMINAL FROM V$SESSION; Lo and behold, there is a generic login for the username, column MACHINE in all rows contains jdbcclient and TERMINAL contains unknown. With SQLNet and Net8 these two columns are populated as one would expect. Also, the OSUSER column is generic, so I'm not able to use that info. Never having used JDBC (yet), is there a setting in the JDBC client to set these? More importantly, if there is, WHY would there be such a setting? And how am I supposed to track this down, aside from SHUTDOWN ABORT (ick)? TIA, Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA --- This message has been scanned for viruses with Trend Micro's Interscan VirusWall.
v$session and v$process -- Simple one
Hello List, I've a simple question. Why do I see this output when I execute the following query: SQL l 1 select a.USERNAME,a.SPID "unix pid",b.SID,b.serial#,b.osuser,b.program,a.program 2 from v$process a, v$session b 3 where a.spid='unixpid' 4* and a.addr=b.paddr SQL USERNAMEunix pidSID SERIAL# OSUSER --- - - - --- PROGRAM PROGRAM oracle 18463 102 1954 OraUser C:\ORAWIN\BIN\F45RUN.EXE oracle@fincons (TNS V1-V2) oracle 18463 142 1263 OraUser C:\ORAWIN\BIN\F45RUN.EXE oracle@fincons (TNS V1-V2) Why does it show me 2 oracle SIDs ??? This happens only on some unixpids. TIA ___ Do You Yahoo!? Enva mensajes instantneos y recibe alertas de correo con Yahoo! Messenger - http://messenger.yahoo.es -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: =?iso-8859-1?q?Pablo=20ksksksk?= 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: Confused with v$session and v$process
Mitchell, By hostname I take it you mean the hostname of the server where oracle is installed. I believe that you can call the server anything you want because the ODBC driver looks up the hostname in the tnsnames.ora and gets the ip address from the host file you are calling from. If you use Oracle Names that might be a whole different ballgame. List correct me if I'm in error!!! ROR mm [EMAIL PROTECTED] 04/02/01 03:55PM Hi DBAs what is impact if I change the host name on unix with oracle installed. Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ron Rogers 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).
Confused with v$session and v$process
Hi, DBAs, I think that I might ask a simple question but I have been confused sometimes for a long time. Could someone help me to clarify these? First, is " v$session.paddr=v$process.addr" the only way to join these two tables" Second,I am confused about the Username, Osuser in v$session with the Username in v$process. "select a.program, b.program,a.username,b.username,b.osuser from v$process a, v$session b where a.addr=b.paddr and sid in (18,33,68,115,144,150)" One of the results is as follows: a.program :oracle@vantive (TNS V1-V3) b.username: iwserver@vantive (TNS V1-V3) b.username: oracle a.username:SWBAPPS a.username: vantive Third: I am confused with Spid,pid in v$process and sid in v$session. Is pid (oracle process id)oracle server process id? what is it used for? Thanks very much for your help. Chuan
