Table Level Export Import in 9i Oracle Database-Most Time Effective Way
From Source database copy the table creation script using below command:
select dbms_metadata.get_ddl('TABLE','BB_CONNECTION_HISTORY_12AUG','LMSOGS') from dual;
Create the table structure in destination database under same user using the script taken from source:
CREATE TABLE "LMSOGS"."BB_CONNECTION_HISTORY_12AUG"
( "KAR_ACC_ORG" NUMBER(3,0) NOT NULL ENABLE,
"KAR_ACC_ACCT" VARCHAR2(19) NOT NULL ENABLE,
"KAR_REC_TYPE" VARCHAR2(1) NOT NULL ENABLE,
"KAR_CURRENCY" VARCHAR2(3) NOT NULL ENABLE,
"KAR_ACC_INSERT_DT" DATE NOT NULL ENABLE,
"KAR_REC_STATUS" VARCHAR2(1),
"KAR_LOGO" NUMBER(3,0),
"KAR_FIAT_OCCURRENCE_1" NUMBER(3,0),
"KAR_FIAT_DATE_1" DATE,
"KAR_FIAT_CYCLE_DUE_1" NUMBER(1,0),
"KAR_FIAT_RECENCY_1" NUMBER(1,0),
"KAR_FIAT_REVERSAL_IND_1" NUMBER(1,0),
"KAR_FIAT_PMT_AMT_1" NUMBER(23,6),
"KAR_FIAT_PRE_PMT_AMT_1" NUMBER(23,6),
"KAR_FIAT_AMT_TOT_DUE_1" NUMBER(23,6),
"KAR_FIAT_REL_PMT_AMT_1" NUMBER(23,6),
"KAR_FIAT_ORIG_PMT_AMT_1" NUMBER(23,6),
"KAR_FIAT_ORIG_LM_1" NUMBER(2,0),
"KAR_FIAT_DPT_AMT_1_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_2_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_3_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_4_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_5_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_6_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_7_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_8_1" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_9_1" NUMBER(23,6),
"KAR_FIAT_OCCURRENCE_2" NUMBER(3,0),
"KAR_FIAT_DATE_2" DATE,
"KAR_FIAT_CYCLE_DUE_2" NUMBER(1,0),
"KAR_FIAT_RECENCY_2" NUMBER(1,0),
"KAR_FIAT_REVERSAL_IND_2" NUMBER(1,0),
"KAR_FIAT_PMT_AMT_2" NUMBER(23,6),
"KAR_FIAT_PRE_PMT_AMT_2" NUMBER(23,6),
"KAR_FIAT_AMT_TOT_DUE_2" NUMBER(23,6),
"KAR_FIAT_REL_PMT_AMT_2" NUMBER(23,6),
"KAR_FIAT_ORIG_PMT_AMT_2" NUMBER(23,6),
"KAR_FIAT_ORIG_LM_2" NUMBER(2,0),
"KAR_FIAT_DPT_AMT_1_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_2_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_3_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_4_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_5_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_6_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_7_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_8_2" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_9_2" NUMBER(23,6),
"KAR_FIAT_OCCURRENCE_3" NUMBER(3,0),
"KAR_FIAT_DATE_3" DATE,
"KAR_FIAT_CYCLE_DUE_3" NUMBER(1,0),
"KAR_FIAT_RECENCY_3" NUMBER(1,0),
"KAR_FIAT_REVERSAL_IND_3" NUMBER(1,0),
"KAR_FIAT_PMT_AMT_3" NUMBER(23,6),
"KAR_FIAT_PRE_PMT_AMT_3" NUMBER(23,6),
"KAR_FIAT_AMT_TOT_DUE_3" NUMBER(23,6),
"KAR_FIAT_REL_PMT_AMT_3" NUMBER(23,6),
"KAR_FIAT_ORIG_PMT_AMT_3" NUMBER(23,6),
"KAR_FIAT_ORIG_LM_3" NUMBER(2,0),
"KAR_FIAT_DPT_AMT_1_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_2_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_3_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_4_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_5_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_6_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_7_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_8_3" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_9_3" NUMBER(23,6),
"KAR_FIAT_OCCURRENCE_4" NUMBER(3,0),
"KAR_FIAT_DATE_4" DATE,
"KAR_FIAT_CYCLE_DUE_4" NUMBER(1,0),
"KAR_FIAT_RECENCY_4" NUMBER(1,0),
"KAR_FIAT_REVERSAL_IND_4" NUMBER(1,0),
"KAR_FIAT_PMT_AMT_4" NUMBER(23,6),
"KAR_FIAT_PRE_PMT_AMT_4" NUMBER(23,6),
"KAR_FIAT_AMT_TOT_DUE_4" NUMBER(23,6),
"KAR_FIAT_REL_PMT_AMT_4" NUMBER(23,6),
"KAR_FIAT_ORIG_PMT_AMT_4" NUMBER(23,6),
"KAR_FIAT_ORIG_LM_4" NUMBER(2,0),
"KAR_FIAT_DPT_AMT_1_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_2_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_3_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_4_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_5_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_6_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_7_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_8_4" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_9_4" NUMBER(23,6),
"KAR_FIAT_OCCURRENCE_5" NUMBER(3,0),
"KAR_FIAT_DATE_5" DATE,
"KAR_FIAT_CYCLE_DUE_5" NUMBER(1,0),
"KAR_FIAT_RECENCY_5" NUMBER(1,0),
"KAR_FIAT_REVERSAL_IND_5" NUMBER(1,0),
"KAR_FIAT_PMT_AMT_5" NUMBER(23,6),
"KAR_FIAT_PRE_PMT_AMT_5" NUMBER(23,6),
"KAR_FIAT_AMT_TOT_DUE_5" NUMBER(23,6),
"KAR_FIAT_REL_PMT_AMT_5" NUMBER(23,6),
"KAR_FIAT_ORIG_PMT_AMT_5" NUMBER(23,6),
"KAR_FIAT_ORIG_LM_5" NUMBER(2,0),
"KAR_FIAT_DPT_AMT_1_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_2_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_3_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_4_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_5_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_6_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_7_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_8_5" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_9_5" NUMBER(23,6),
"KAR_FIAT_OCCURRENCE_6" NUMBER(3,0),
"KAR_FIAT_DATE_6" DATE,
"KAR_FIAT_CYCLE_DUE_6" NUMBER(1,0),
"KAR_FIAT_RECENCY_6" NUMBER(1,0),
"KAR_FIAT_REVERSAL_IND_6" NUMBER(1,0),
"KAR_FIAT_PMT_AMT_6" NUMBER(23,6),
"KAR_FIAT_PRE_PMT_AMT_6" NUMBER(23,6),
"KAR_FIAT_AMT_TOT_DUE_6" NUMBER(23,6),
"KAR_FIAT_REL_PMT_AMT_6" NUMBER(23,6),
"KAR_FIAT_ORIG_PMT_AMT_6" NUMBER(23,6),
"KAR_FIAT_ORIG_LM_6" NUMBER(2,0),
"KAR_FIAT_DPT_AMT_1_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_2_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_3_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_4_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_5_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_6_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_7_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_8_6" NUMBER(23,6),
"KAR_FIAT_DPT_AMT_9_6" NUMBER(23,6),
"KAR_UPDATE_DT" DATE,
"KAR_CREATE_DT" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 81920 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TS_KAR_HIST_DATA";
Export the table data only from source database:
exp USERNAME TABLES=LMSOGS.BB_CONNECTION_HISTORY_12AUG GRANTS=N INDEXES=N ROWS=Y TRIGGERS=N CONTRAINTS=N IGNORE=Y FILE=BB_CONNECTION_HISTORY_12AUG.dmp LOG=exp_BB_CONNECTION_HISTORY_12AUG.log;
SCP the dump file to destination database:
Import the table to Destination database Schema:
imp LMSOGS/********* FROMUSER=LMSOGS TOUSER=LMSOGS TABLES=BB_CONNECTION_HISTORY_12AUG GRANTS=N INDEXES=N rows=y CONSTRAINTS=N IGNORE=Y FILE=BB_CONNECTION_HISTORY_12AUG.dmp LOG=imp_BB_CONNECTION_HISTORY_12AUG.log
Create Index manually or do structure export import. Create primary key. referential constraints etc as per source.
No comments:
Post a Comment