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
