TIP 88#: Progress of DataPump import with LOB data

If you have a big LOB to be imported through DataPump, you may realize that datapump session stays for a long time and you wonder if it is working and the client also may ask you how much more time is needed to be completed.
The sad news is that v$session_longops and query like the following can not help you that much.Also you can get too much info on how much longer impdp run from datapump views in database or datapump commands (like status).

select sid, serial#, context, sofar, totalwork,round(sofar/totalwork*100,2) "%_complete"
opname like '%%' and opname not like '%aggregate%' and totalwork != 0 and sofar <> totalwork;

The best way is to check the size of LOB segment and see if it grows. Also you could compare the LOB segment size in a import DB with a source DB to get a better estimate of how much more work DataPump has to do :

select sum(bytes)/1024/1024,sysdate from dba_segments where segment_name in (select segment_name from dba_lobs where table_name='&table_name');

No comments: