Re: Running import datapump over a database link.

From: Zabair Ahmed <roon987_at_yahoo.co.uk>
Date: Thu, 07 May 2015 15:05:25 +0000
Message-ID: <1546349641.3370671.1431011124660.JavaMail.yahoo_at_mail.yahoo.com>



That would be a neat idea locking the table -  it's a heavily accessed table in the application, so I guess it would have a big impact on the application - I wouldn't want to do this on a production system. Currently the table has 1239087660 rows. I like the idea of finding some suitable ranges and sticking this in the query clause - still working on coming up with some suitable ranges for the job_id. 

     On Thursday, 7 May 2015, 16:00, Zabair Ahmed <roon987_at_yahoo.co.uk> wrote:    

 That would be a neat idea locking the table -  it's a heavily accessed table in the application, so I guess it would have a big impact on the application - I wouldn't want to do this on a production system. Currently the table has 1239087660 rows. I like the idea of finding some suitable ranges and sticking this in the query clause - still working on coming up with some suitable ranges for the job_id.    

     On Thursday, 7 May 2015, 2:58, Mladen Gogala <dmarc-noreply_at_freelists.org> wrote:    

  Open another session on the source system and execute the following statement:  LOCK TABLE ADT_HL7_JOB_SEGMENT IN EXCLUSIVE MODE;    When the lock is granted, start your import. When it finishes, commit the session with the lock. That will guarantee that you will not receive ORA-01555 on the source database. There may also be some minor effects to concurrent updates of that table.      

 On 05/06/2015 06:27 AM, Zabair Ahmed wrote:   

  Hello
  Oracle 11.2.0.4 on Linux.
  Am trying to import a large table (176gb) from our Production database to a test database using a database link. This is test exercise and I want to know the timings the import for when we do this for live later on in the month.   I've created a database link and and using the network_link parameter in my impdp - this all works fine.   But I got the ORA-01555 on the source database.    I want to eliminate this but I can't change any of the undo parameters on the source (production) system.    This is the par file am using...
  JOB_NAME=IMP_ADT_HL7_JOB_SEGMENT NETWORK_LINK=REMOTE_FFT DIRECTORY=DP_DIR LOGFILE=ADT_HL7_JOB_SEGMENT.log REMAP_SCHEMA=MQ1DN4LIVE:ZAHMED CONTENT=DATA_ONLY TABLES=(MQ1DN4LIVE.ADT_HL7_JOB_SEGMENT) TABLE_EXISTS_ACTION=APPEND PARALLEL=4 QUERY=ADT_HL7_JOB_SEGMENT:"where job_id>=1 and mod(job_id,4)=1"   This is the structure of the ADT_HL7_JOB_SEGMENT table..

  SQL> desc ADT_HL7_JOB_SEGMENT  Name                                      Null?    Type  ----------------------------------------- -------- ----------------------------  JOB_ID                                    NOT NULL NUMBER(10)  ADT_HL7_COMPONENT_SPEC_ID                 NOT NULL NUMBER(10)  COMPONENT_CONTENT                                  VARCHAR2(240)  CREATED_BY                                         VARCHAR2(240)  CREATED_DATE                                       DATE  SOURCE                                             VARCHAR2(1) 
  
  

  These are the min(204832016) and max(654137385) values of the job_id column.   Am not sure my query in the above parfile is going to help me here. What I would like to do is take the job_id column and specify a range and break it down to 4 ranges.   Any ideas much appreciated.              

 --
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com         

--
http://www.freelists.org/webpage/oracle-l
Received on Thu May 07 2015 - 17:05:25 CEST

Original text of this message