Re: Running import datapump over a database link.

From: Mladen Gogala <mgogala_at_yahoo.com>
Date: Thu, 07 May 2015 11:52:31 -0400
Message-ID: <554B8A3F.2070705_at_yahoo.com>

How about creating a materialized view and use it to populate the table across the link?

On 05/07/2015 11:05 AM, Zabair Ahmed 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, 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://mgogala.freehostia.com/>
>
>
>
>

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


--
http://www.freelists.org/webpage/oracle-l

Received on Thu May 07 2015 - 17:52:31 CEST