Oracle 8.1.6 Exp / Imp (multiple dump files)

The table export is split into multiple dump files because there are too many records.

Export data from SOURCE DB
Make sure machine can be connected to both databases ( SOURCE and LOCAL)
Start SQL*PLUS session for SOURCE
Run:
SQL>@C:\Exp_Data\Exp_script.sql

Where Exp_script.sql is
---------------------------
/*
How to run under SQL*Plus:

SQL>@C:\Exp_Data\Exp_script.sql

**separated by "rec_creation_date"**

*/


host del C:\Exp_Data\ExpSpool.log
SPOOL C:\Exp_Data\\ExpSpool.log

--Cleanup existing dump files
host del C:\Exp_Data\record_in_year_2001.dmp
host del C:\Exp_Data\record_in_year_2002.dmp
host del C:\Exp_Data\record_in_year_2003.dmp
host del C:\Exp_Data\record_in_year_2004.dmp

--Cleanup existing log files
host del C:\Exp_Data\record_in_year_2001.log
host del C:\Exp_Data\record_in_year_2002.log
host del C:\Exp_Data\record_in_year_2003.log
host del C:\Exp_Data\record_in_year_2004.log


--create 2001 dump file
drop table .record_in_year_2001;
create table .record_in_year_2001 as select * from .table_name where rec_creation_date < to_date('01-jan-2002');
host exp username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2001.dat
drop table .record_in_year_2001;


--create 2002 dump file
drop table .record_in_year_2002;
create table .record_in_year_2002 as select * from .table_name where rec_creation_date < to_date('01-jan-2003') and rec_creation_date >= to_date('01-jan-2002');
host exp username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2002.dat
drop table .record_in_year_2002;

--create 2003 dump file
drop table .record_in_year_2003;
create table .record_in_year_2003 as select * from .table_name where rec_creation_date < to_date('01-jan-2004') and rec_creation_date >= to_date('01-jan-2003');
host username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2003.dat
drop table .record_in_year_2003;

--create 2004 dump file
drop table .record_in_year_2004;
create table .record_in_year_2004 as select * from .table_name where rec_creation_date < to_date('01-jan-2005') and rec_creation_date >= to_date('01-jan-2004');
host exp username/password@SOURCE parfile=C:\Exp_Data\record_in_year_2004.dat
drop table .record_in_year_2004;


SPOOL OFF
---------------------------------
dat file example:
------------------------
CONSTRAINTS=N
DIRECT=Y
FEEDBACK=0
FILE=C:\Exp_Data\record_in_year_2001.dmp
GRANTS=N
INDEXES=N
LOG=C:\Exp_Data\record_in_year_2001.log
TRIGGERS=N
ROWS=Y
TABLES=.table2001
--------------------------

Import data into LOCAL database
Run “cmd” to get command prompt

Run following commands sequentially:
imp username/password file=record_in_year_2001.dmp full=yes
imp username/password file=record_in_year_2002.dmp full=yes
imp username/password file=record_in_year_2003.dmp full=yes
imp username/password file=record_in_year_2004.dmp full=yes

Note: If “ORACLE error 12560 encountered”, try to add the tnsname:
imp username/password@SOURCE file=<***>.dmp full=yes


Consolidate data into one table

Start SQL*PLUS session for local database (GEMSORCL)
Run following scripts:
create table .record_in_year_ as select * from .record_in_year_2001;
insert into .record_in_year_ select * from .record_in_year_2002;
insert into .record_in_year_ select * from .record_in_year_2003;
insert into .record_in_year_ select * from .record_in_year_2004;

drop table .record_in_year_2001;
drop table .record_in_year_2002;
drop table .record_in_year_2003;
drop table .record_in_year_2004;


Verify restored data against SOURCE DB
Start SQL*PLUS session for both databases (SOURCE and LOCAL)
Run following scripts for PE0047:
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2003') and rec_creation_date >= to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2004') and rec_creation_date >= to_date('01-jan-2003');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2005') and rec_creation_date >= to_date('01-jan-2004');
select count(*) from .record_in_year_ where rec_creation_date is NULL;
select count(*) from .record_in_year_;

Run following scripts for GEMSORCL:
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2003') and rec_creation_date >= to_date('01-jan-2002');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2004') and rec_creation_date >= to_date('01-jan-2003');
select count(*) from .record_in_year_ where rec_creation_date < to_date('01-jan-2005') and rec_creation_date >= to_date('01-jan-2004');
select count(*) from .record_in_year_ where rec_creation_date is NULL;
select count(*) from .record_in_year_;

All counts should match accordingly.