Oracle-L: RE: Zero-term'd machine in V$SESSION from Winders

From: Jay Mehta <Jmehta_at_ctisinc.com>
Date: Tue, 14 May 2002 13:53:31 -0800
Message-ID: <F001.00460823.20020514135331@fatcity.com>

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
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

--

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

Author: Jesse, Rich
INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

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 MIS_at_ctisinc.com.

--

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

Author: Jay Mehta
INET: Jmehta_at_ctisinc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------