Dear Tom,

Your website is very useful for those developers that work on
oracle. Please Keep it on.

Now, I have below questions to ask you:

1. can i use utl_file.put_line without modifying the init.ora file?
since I have no rights to access the production server.

2. I want to use spo command in below codes, but it does not
work, can u give me some suggestion on that?

**************************************************
spo c:\abc.txt

DECLARE
l_start integer;
BEGIN
l_start := dbms_utility.get_time;
<<mk_loop>>
while (dbms_utility.get_time-l_start < 1000)
loop
dbms_output.put_line('test');
end loop mk_loop;

END;
spo off
***************************************************

My expect result for abc.txt is that, it contains many
"test" in the file.

3. Your showsql.sql is very useful. Now I have modified the script
which use utl_file to suit my case (I will loop the
My_showsql.sql for about 5 minutes to monitor the server
activities.) But, our company policies do not allow me to modify
the init.ora file. Instead, i believe i can use
dbms_output.put_line to replace the utl_file, but it seems it
does not work and the buffer size is a consideration. Do u have
any suggestion on my coding?

Below is the code of My_showsql.sql:
**************************************************************

declare

file_handle UTL_FILE.FILE_TYPE;
file_directory varchar2(30) := '&1';
file_name varchar2(30) := '&2';
v_mode varchar2(5) := '&3';
error_message varchar2(60);
v_now long;
v_username v$session.username%type;
v_sid v$session.sid%type;
v_serial v$session.serial#%type;
v_process v$session.process%type;
v_status v$session.status%type;
l_str long;
v_user long;
v_program long;
v_last_call_et long;
v_logon_time long;
v_current_time long;
v_sql_text long;
l_str2 long;

CURSOR p_cur IS
select username, sid, serial#, process, status
from v$session
where username is not null;

cursor p_cur2 IS
select username||'('||sid||','||serial#||')' user_name,
s.program,
to_char(round(s.LAST_CALL_ET/60,2)) LAST_CALL_ET,
to_char(s.LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
max(decode(v.piece,0,sql_text,' ')) ||
max(decode(v.piece,1,sql_text,' ')) ||
max(decode(v.piece,2,sql_text,' ')) ||
max(decode(v.piece,3,sql_text,' ')) ||
max(decode(v.piece,4,sql_text,' ')) ||
max(decode(v.piece,5,sql_text,' ')) ||
max(decode(v.piece,6,sql_text,' ')) ||
max(decode(v.piece,7,sql_text,' ')) ||
max(decode(v.piece,8,sql_text,' ')) ||
max(decode(v.piece,9,sql_text,' '))
sql_text
from v$session s, v$sqltext_with_newlines v
where s.status in ('ACTIVE', 'INACTIVE')
and rawtohex(s.sql_address) <> '00'
and s.username is not null
and s.sql_address=v.address
group by username||'('||sid||','||serial#||')',
s.program, s.LAST_CALL_ET,
to_char(s.LOGON_TIME,' Day HH24:MI'),
to_char(sysdate,' Day HH24:MI')
order by s.last_call_et;

BEGIN
file_handle := UTL_FILE.FOPEN(file_directory,file_name, v_mode);

SELECT '* '||rpad(TO_CHAR(SYSDATE, 'MM-DD-YYYY 4:MI:SS'),47,' ')
||'*' into v_now FROM DUAL ;

UTL_FILE.PUT_LINE(file_handle, v_now );
UTL_FILE.PUT_LINE(file_handle, '*****');

UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, 'Current Connections: ');
UTL_FILE.PUT_LINE(file_handle, ' ');

l_str := null;

UTL_FILE.PUT_LINE(file_handle, 'User SID_and_Serial Process Status ');
UTL_FILE.PUT_LINE(file_handle, '-------------------- -------------------- --------------- --------');
open p_cur;
LOOP
fetch p_cur into v_username, v_sid, v_serial, v_process, v_status;
l_str:= rpad(v_username,20,' ')||' '||rpad(v_sid||','||v_serial,20,' ')||' '||rpad(v_process,15,' ')||' '||rpad(v_status,8,' ');
UTL_FILE.PUT_LINE(file_handle, rtrim(l_str,chr(10)) );
exit when (p_cur%notfound);
END LOOP;
close p_cur;

UTL_FILE.PUT_LINE(file_handle, ' ');
UTL_FILE.PUT_LINE(file_handle, 'User(s) Details: ');
UTL_FILE.PUT_LINE(file_handle, ' ');

l_str2 := null;

UTL_FILE.PUT_LINE(file_handle, 'User Program Idle Time Logon Time Current Time SQL Statement ');
UTL_FILE.PUT_LINE(file_handle, '-------------------- -------------------- ------------ -------------------- -------------------- ----------------------------------------------------------------');
open p_cur2;
LOOP
fetch p_cur2 into v_user, v_program, v_last_call_et, v_logon_time, v_current_time, v_sql_text;
l_str2:= rpad(v_user,20,' ')||' '||rpad(v_program,20,' ')||' '||rpad(v_last_call_et,12,' ')||' '||rpad(rtrim(v_logon_time,chr(10)),20,' ')||' '||rpad(v_current_time,20,' ')||' '||v_sql_text;
UTL_FILE.PUT_LINE(file_handle, rtrim(l_str2,chr(10)) );
exit when (p_cur2%notfound);
END LOOP;
close p_cur2;

UTL_FILE.PUT_LINE(file_handle, ' ');

UTL_FILE.FCLOSE(file_handle);

exception
when utl_file.invalid_mode then ......barbarbar...
******************************************************************

I hope you can understand what i am asking and Thank you Very much for your help.

Regards,
Lok