Re: Running import datapump over a database link.

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Wed, 06 May 2015 21:56:23 -0400
Message-ID: <554AC647.6030405_at_yahoo.com>

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 - 03:56:23 CEST