Can you see load progress when running sqlldr with DIRECT = TRUE?

Question

I am loading a large file (~22 GB) to Oracle with sqlldr, using the direct = TRUE option (otherwise it would probably take months). So it's been sitting there for several hours, loading as best I can tell, but I have no way of knowing how much longer it's going to take. Is there any way to check progress with this kind of load?

Answer 1

You can monitor the growth of the table where you are inserting in. Check dba_segments.

Answer 2

UPDATE: The answer by ik_zelf is the correct one, but I'm leaving this one up since setting the rows parameter to force periodic saves is still a useful thing to do.

One way I have found to track the progress of a direct path load is to set the ROWS option in sqlldr

rows -- number of rows in conventional path bind array or between direct path data saves (Default: Conventional path 64, Direct path all)

If this parameter is set, sqlldr will print a message such as

Save data point reached - logical record count 10000

each time a save point is reached. However, as Oracle points out and my own testing confirms, these saves are very expensive:

A data save is an expensive operation. The value for ROWS should be set high enough so that a data save occurs once every 15 minutes or longer. The intent is to provide an upper boundary (high-water mark) on the amount of work that is lost when an instance failure occurs during a long-running direct path load. Setting the value of ROWS to a small number adversely affects performance and data block space utilization.

Reassurance that the thing is still running every 15 minutes of an 8 hour load is worth a bit of a performance hit.

http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_modes.htm#i1007779

Note that querying SYS.V_$LOADTSTAT or SYS.V_$LOADPSTAT does not work. Again, per the Oracle documentation:

Any SELECT against this table results in "no rows returned" since you cannot load data and do a query at the same time.