Completion time of RMAN / Data Pump

Sometime , we want to know how long RMAN or DATA PUMP export / import will take. Here are the sample SQLs to get to know:
Note: PER_DONE — So far work done in %

For RMAN:

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, (sofar/totalwork) * 100 per_done,start_time, sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and lower(opname) not like ‘%aggregate%’
and lower(opname) like ‘rman%’;

SID START_TIME         TOTALWORK      SOFAR       PER_DONE END_AT
—– —————– ———- ———- ———- —————– —————–
491 18/08/10 20:13:45                   1213440    1165439   96.04   18/08/10 20:18:06
480 18/08/10 20:14:42                    486816     172329     35.39    18/08/10 20:23:50

For Export:
SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, round((sofar/totalwork) * 100) done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like ‘%EXPORT%’;

SID START_TIME         TOTALWORK      SOFAR       PER_DONE END_AT
———- —————– ———- ———- ———- —————–
—————–
113 18/08/10 21:03:48                  3864        892            23.08    18/08/10 21:33:21

For Import:

SQL>alter session set nls_date_format=’dd/mm/yy hh24:mi:ss’;
SQL>select sid, totalwork, sofar, round((sofar/totalwork) * 100) per_done,start_time,sysdate + time_remaining/3600/24 end_at
from v$session_longops
where totalwork > sofar
and upper(message) like ‘%IMPORT%’;

SID START_TIME         TOTALWORK      SOFAR   PER_DONE END_AT
———- —————– ———- ———- ———- —————– —————–
121 19/08/10 11:02:54                3004            532       17.70 19/08/10 11:33:05

Advertisements

About deepakguptadba

10 years of experience in Database Administrator for Oracle Database 10g//9i/8i/7.x with total 15 years’ comprehensive experience in IT , Pharma / Biotech , Manufacturing, Construction , Auto & Electronics.
This entry was posted in SQL Script and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s