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?

Dunc

don't know about the sqlldr but you could use a pipe as a data source. for sqlldr, and write data to this pipe using some sort of script or program that would tell you how much has been written. how many lines has been written so far, or count the percentage of source file. This way you'd get the progress +/- the buffering of sqlldr – Rustle

Answer 1

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

Distillery

While loading, blocks of data are appended in the segment's extents. This data is not visible until the load completes but the size of the segment is growing during the load. – Distillery

You're right - I can see the bytes and blocks increasing. – Dunc

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.

Dunc