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.