Re: Running import datapump over a database link.
Date: Thu, 7 May 2015 11:36:06 -0400
Message-ID: <CAAaXtLBQW0M7zJqvATxORAHydSer8P562=hy0Gt_iSAWy4Ehnw_at_mail.gmail.com>
The locking idea sounds a little extreme in any case. It would avoid the ORA-1555 issue, but perhaps at an unacceptably high price.
There are of course the more traditional ways of avoiding ORA-1555 errors. You said that you did not want to manipulate UNDO on the primary database, though, so that probably leaves the old tried-and-true "try re-running the job when the database is less busy" method.
What about a standby database? Do you have one? If so, you could suspend replication, open the standby READ ONLY, do the export, and then resume replication.
On Thu, May 7, 2015 at 11:05 AM, 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, 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 DBAhttp://mgogala.freehostia.com
>
>
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Thu May 07 2015 - 17:36:06 CEST