Home » RDBMS Server » Server Administration » Estimation time for Import job (Oracle 10.2.0.2.0 , Windows)
Estimation time for Import job [message #616114] Thu, 12 June 2014 06:48 Go to next message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Hi ,

I am running one datapump schema import in windows with network link parameter and it is running for more than 12 hours.
It is importing schema with data from source to target database.

SQL> SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
  2  ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
  3  FROM   V$SESSION_LONGOPS
  4  WHERE
  5  TOTALWORK != 0
  6  AND    SOFAR != TOTALWORK
  7  order by 1;

       SID    SERIAL# OPNAME                              SOFAR  TOTALWORK USERNAME                CONTEXT   COMPLETE
---------- ---------- ------------------------------ ---------- ---------- -------------------- ---------- ----------
       139        527 Table Scan                         324695     515422 P10                  0             63
       142        581 Table Scan                         324695     515422 P10                  0             63
       [b]150          5 SYS_IMPORT_SCHEMA_02                    0      87383 P10                  0             0[/b]       153       2767 Gather Schema Statistics              206        948 P10               1989405        21.73

As per the above output , complete time for import is showing 0 but log files are getting updated.
Can you please tell me how to find the remaining time left to complete the import job ?

Schema size is 120 GB

Thanks,
Mathew

Re: Estimation time for Import job [message #616136 is a reply to message #616114] Thu, 12 June 2014 08:39 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Balaji/Ajit/Jack/Mohan/Mathew,

This mean import is done but there is a lag to fill the log or to gather the statistics (see line sid 153).
Just check the state of your job in USER/DBA_DATAPUMP_JOBS and V$DATAPUMP_JOB.

[Updated on: Thu, 12 June 2014 08:45]

Report message to a moderator

Re: Estimation time for Import job [message #616143 is a reply to message #616136] Thu, 12 June 2014 09:13 Go to previous message
srikanth02
Messages: 49
Registered: June 2014
Location: India
Member
Quote:
This mean import is done but there is a lag to fill the log or to gather the statistics (see line sid 153).

Import is still running and log file is getting updated.
Quote:
Just check the state of your job in USER/DBA_DATAPUMP_JOBS and V$DATAPUMP_JOB

SQL>
SQL> SELECT SID, SERIAL#, opname, SOFAR, TOTALWORK,username,context,
  2  ROUND(SOFAR/TOTALWORK*100,2) COMPLETE
  3  FROM   V$SESSION_LONGOPS
  4  WHERE
  5  TOTALWORK != 0
  6  AND    SOFAR != TOTALWORK
  7  order by 1;

       SID    SERIAL# OPNAME                              SOFAR  TOTALWORK USERNAME                CONTEXT   COMPLETE
---------- ---------- ------------------------------ ---------- ---------- -------------------- ---------- ----------
       150          5 SYS_IMPORT_SCHEMA_02                    0      87383 P10                         0     0
       153       2767 Gather Schema Statistics              386        948 P10                      1989725      40.72


From USER/DBA_DATAPUMP_JOBS and V$DATAPUMP_JOB views I could see import job is running.

Also I see schema stats is gathered for the entire P10 schema from below query and stats are up to date.
SQL> SELECT OWNER,TABLE_NAME,LAST_ANALYZED
  2  FROM   DBA_TABLES
  3  where OWNER IN ('P10')
  4  order by 2;

Thanks,
Mathew

[Updated on: Thu, 12 June 2014 09:17]

Report message to a moderator

Previous Topic: Checking swap space 0Mb available ,150 required
Next Topic: Oracle Database Upgrade from 11.2.0.3 to 11.2.0.4
Goto Forum:
  


Current Time: Thu Mar 28 09:13:24 CDT 2024