LOB Data Import Via DataPump

While using databases if you do have huge LOB stacks that need to be imported by using the DataPump but at this stage it does dawn upon users that the DataPump function takes forever to complete the process and most users do get stressed by it. The sad bit regarding this other than using DataPump is that users can use v$session_longops and a few others queries are of no help at this stage as well. Impdp can get you the required information but the information depends on how much time is spent while the process is being processed.

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

The best strategy at this stage is to check the size of the LOB segment and do monitor if there are any growth rates in it. Users do have the chance of comparing the size of the LOB segment size as well while importing Databases with a source Database to acquire a better estimate of how much work the DataPump will perform on it.

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

 More Oracle Articles, Database Articles and DBA Tips

  Database Security: Step by step guideline

  Performance Optimization by avoiding Table Locks!!

  Oracle DBMS: Fundamental Security Precautions

  DBA Tips for Oracle Regular Expressions!!

  Beware of Database Myths