Batch hang caused by unprocessed sqlldr standard output
1 problem
A batch processing in the production environment was not completed.
2 Analysis
Batch processing logic:
Java- > shell- > sqlldr
Check the database session and find that the corresponding session wait event is SQL*Net message from client and the corresponding v$session.program is sqlldr@xxx,v$session.seq#, which lasts more than 5 hours (6:45-12:20).
Query the table loaded in the database and find that part of the data has been loaded, but the corresponding sqlldr log is empty.
The corresponding sqlldr command is as follows:
Sqlldr user/pwd data=a.txt control=a.ctl log=a.log bad=a.bad discard=a.dis errors=9999999 rows=1000
It is verified in the test environment that when sqlldr runs directly, all data can be loaded smoothly; when running in java- > shell- > sqlldr mode, the problem can be reproduced if it stops after a specific number of rows are loaded.
At the time of the above test, when sqlldr runs directly, the current window will output that the xx lines have been loaded. In fact, this is where the problem lies. When running in java- > shell- > sqlldr mode, the standard output is not read by the program, and when the amount of data to be loaded reaches a certain level (exceeding the default buffer size), the loading process will stop.
The verification process is referred to the appendix.
3 solution
Handle the standard output and error output of sqlldr, optional method:
Method one: sqlldr... Silent= (ALL)
Method 2: sqlldr. 1 > std.log 2 > err.log
Attached, test script
-- RunShell.java
Import java.util.Date
Import java.text.SimpleDateFormat
Import java.io.BufferedReader
Import java.io.InputStreamReader
Public class RunShell {
Public static void main (String [] args) {
Try {
SimpleDateFormat df = new SimpleDateFormat ("yyyy-MM-dd HH:mm:ss")
String shpath= "/ home/oracle/java/test.sh"
System.out.println ("- 1muri -" + df.format (new Date ()
Process ps = Runtime.getRuntime () .exec (shpath)
System.out.println ("--2muri -" + df.format (new Date ()
/ / ps.waitFor ()
BufferedReader br = new BufferedReader (new InputStreamReader (ps.getInputStream ()
String loop = args [0]
System.out.println (loop)
String line
While ("0" .equals (loop) & & (line = br.readLine ())! = null) {
System.out.println (line)
}
System.out.println ("--3muri -" + df.format (new Date ()
Ps.waitFor ()
System.out.println ("--4mura -" + df.format (new Date ()
}
Catch (Exception e) {
E.printStackTrace ()
}
}
}
-- test.sh
Seq-w 1 100000 | awk'{print $0 "xxxxxxxxxxxxxx"}'
Test 1 Mel-pass in parameter 0, and the main thread actively reads the standard output of shell
Java RunShell 0
-1 Murray 2018-04-20 13:23:25
-2murmur2018-04-20 13:23:25
0
...
...
-3murmur2018-04-20 13:23:34
-4murmur2018-04-20 13:23:34
= = > can be completed successfully
Test 2 Mel-pass in parameter 1, and the main thread does not read the standard output of shell
Java RunShell 1
-1 Murray 2018-04-20 13:28:30
-2murmur2018-04-20 13:28:30
one
-3murmur2018-04-20 13:28:30
= = > suspended for a long time
Ctrl-C
^ Cmurmur4 Muffin 2018-04-20 16:37:08
