You Asked
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
and Tom said...
1) only if you are HAPPY with the current utl_file_dir init.ora setting. Otherwise, if you need to add a path and you are using a version of oracle BEFORE 9i Release 2 -- you will need to not only modify the init.ora (or spfile in 9i) you will need to restart the database.
In 9i Release 2, UTL_FILE uses a "directory" object instead of the utl_file_dir init.ora parameter and these directory objects are created when needed, without touching the parameter files, without restarting.
2) you seem to be missing "set serveroutput on size 1000000". without that, dbms_output goes into the great bit bucket in the sky.
3) umm, that'll collect quite a bit of data don't you think? What you could do would be to set up some database tables and using dbms_job, schedule a stored procedure that inserts into these tables relevant information that you want to collect (don't run a report every 5 minutes, gather some data every 5 minutes). Then, report on the data whenever you want, however you want.
My solution -- don't utl_file every 5 minutes, stuff the data into a table somewhere and report on that.
Rating
(5 ratings)
Is this answer out of date? If it is, please let us know via a Comment