Re: Oracle 8.05 hanging on batch inserts.

In article <3B2E983F.FE82EEFD_at_telusplanet.net>, JEFF says...
>
>Hi,
>
>I am trying to do a large batch insert procedure. (Several millions).
>The insert is accomplished by reading data from a file using the PL/SQL
>utl_file package. While this batch process has worked in the past the
>session that is doing the batch inserts hangs somewhere between 2600 and
>4000 rows. That is no further rows get inserted and the session just
>freezes. If the process is killed at this point it will remain in
>v$session with a status of "KILLED" until the database is restarted.
>

instruement your code with calls to utl_file to WRITE a log so you can see where in the process you are. A simple routine called "log" that does

o utl_file.fopen( in append mode )
o utl_file.put_line
o utl_file.fclose

will be of great assistance in monitoring this.

That aside for a moment -- PLSQL is not the tool i would personally use to load millions of records. SQLLDR (direct path) would be. Loading millions of records via utl_file+plsql isn't a great idea (loading hundreds to low thousands, sure)

>
>This is being done on Oracle 8.0.5 runing on Solaris 2.7. The batch
>process is implemented with PL/SQL. After each insert a commit is
>performed. The database is running with archivelog off. At the moment
>the user doing the batch inserts is the sole active user. The segment
>for the table that is being inserted to and related index segments are
>not anywhere near the maximum number of extents. Likewise, relevant
>datafiles have plenty of room left and have autoextend on. Finally,
>there does not seem to be any problem with disk space.
>
>I am stumped so if anyone has any suggestions it would be much
>appreciated.
>
>Regards,
>
>Jeff Bowes

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp