| UTL_FILE time out [message #360539] |
Fri, 21 November 2008 05:30  |
matspring
Messages: 11
Registered: May 2008
Location: UK
|
Junior Member |
|
|
I've recently upgraded form 8i to 10g.
I have a form that calls a pkg proc, uses UTL_FILE commands to extract data file to Sun unix box.
Initially I found that after 20 mins I got a FRM-92102 eror, I resolved this by adding SessionTimeOut = 180 to web.xml. However now after 1h 15mins (32MB) the file stops being written to, and the screen continues to hang(no error msg)!! All other Forms server and HTTP Server settings are set to 3 hours:
Env file:
FORMS_TIMEOUT = 180
HeartBeat = 1
web.xml:
Session-Timeout = 180
maxBlockTime = 10,800,000 (3hr)
httpd.conf:
Timeout = 10800 (3hr)
I also interestingly find that If I kick the pkg proc directly from the db the file stops being written to after 2 hours (50mb)!
Any ideas what the limitations of UTL_FILE are, and how it can be controlled?
any help appreciated?
|
|
|
|
| Re: UTL_FILE time out [message #360546 is a reply to message #360539] |
Fri, 21 November 2008 05:45  |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
You need to find out why the file "stops getting written". Does that mean that the procedure exits or that the procedure is still running, by the way?
The first things I would check, running the procedure from SQL*Plus to rule out any influence from other timeouts:
- Check in the procedure, if there are any "when others" error handlers or something like that that would stop an error from showing up.
- Check the alert log and the dump file directories
- Trace the session and see what it is doing.
- Check what the session is waiting for.
You can check the current waits of the session with :
select s.sid,s. SERIAL#, event, p1text, p1, p2text, p2, p3text, p3, wait_time, seconds_in_wait, state,
osuser, program,client_info, s.MODULE, sql_text
from v$session_wait e
join v$session s on s.sid = e.sid
join v$sql q ON q.hash_value = s.sql_hash_value
where s.sid = [SID OF THE SESSION]
|
|
|
|
| Re: UTL_FILE time out [message #360587 is a reply to message #360546] |
Fri, 21 November 2008 08:50 |
matspring
Messages: 11
Registered: May 2008
Location: UK
|
Junior Member |
|
|
thomasG
Thanks for your reply there is some helpful info there. In reply
From Forms its appears that the proc is still running as Completion message not displayed and screen hangs.
When run from PL/SQL developer the proc appears to be still running after 2 hours (tool session has running status) but there is no activity on the file.
It gets interesting now that I've just run the proc from SQL*PLUS as advised, after 1hr 15mins the activity on the file ends and the SQL*PLUS session hangs (Not Responding in Task Manager).
Whilst the proc was running I was monitoring the process using your SQL (and could see activity - a row was returned), however now there is now no activity (no data returned).
I believe that this suggests that the extract has completed however something somewhere prevents the proc ending...
your thoughts are appreciated...
|
|
|
|
| Re: UTL_FILE time out [message #360596 is a reply to message #360587] |
Fri, 21 November 2008 09:35 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Hi,
When something prevents the procedure from ending, then first we have to investigate what will cause the procedure to end.
When you use UTL_FILE to write data, I assume you have some sort of loop that goes through something, perhaps a cursor.
There have been changes in the handling of cursor loops, so perhaps the break condition that is used in the procedure doesn't work as it used to in 8.1.
You could put something like
dbms_application_info.set_client_info('Before Loop');
.....
loop
....
dbms_application_info.set_client_info('In Loop no. ' || v_i);
....
end loop;
.....
dbms_application_info.set_client_info('After loop');
into the loop for example, to write information about what part of the procedure the procedure is in right now into the client_info column of v$session.
You could also post the procedure here, maybe someone will spot something.
|
|
|
|
| Re: UTL_FILE time out [message #360604 is a reply to message #360587] |
Fri, 21 November 2008 10:34 |
matspring
Messages: 11
Registered: May 2008
Location: UK
|
Junior Member |
|
|
Thanks again,
The problem I have is that debugging is not an option as this is a production issue. Our non production DB's do not have the same volume of data and also run quicker due to less activity.
I appreciate the debug path is ideally the way to resolve this issue though
|
|
|
|
| Re: UTL_FILE time out [message #360655 is a reply to message #360604] |
Fri, 21 November 2008 19:53 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
One trick I use sometimes when I need to debug procedures or packages on production because I can't replicate the problem on developing or QA instances is that I create a copy of the package or procedure under a new name with the debug outputs I need and then run that one.
At least when there is only selecting and no updates involved, and the procedure in question can still be started by it's own from SQL*Plus.
You could also try the debug functionality in PL/SQL Developer while running the procedure with it.
|
|
|
|
| Re: UTL_FILE time out [message #360772 is a reply to message #360655] |
Mon, 24 November 2008 00:27 |
 |
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
|
Senior Member
Account Moderator |
|
|
What operating system are you running? Is 32MB a magic number on this operating system? Do you expect this much data? Are you doing 'putlines' as well as 'puts'?
David
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
