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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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).