資料庫遷移之資料泵實驗
environment
source host：
os system:server2008x64
oracle version:10.0.2.0.5 32bit
destination host:
os system:server2008x64
oracle version:10.0.2.0.5 64bit
對資料庫中的表進行拆分匯出匯入，減少停機時間，首先將表比較大而不變化的資料先匯出，匯入，最好在匯出變化的資料
source host:
C:\Users\Administrator>sqlplus /@orcl as sysdba
SQL> create tablespace loneboo datafile 'D:\oracle\product\10.2.0\oradata\orcl\loneboo01.dbf' size 200m;
表空間已建立。
SQL> create user loneboo identified by loneboo default tablespace loneboo temporary tablespace temp;
使用者已建立。
SQL> grant connect,dba to loneboo;
SQL> exit
tables: A partition table
B partition blob table
C
create table A
(
ID NVARCHAR2(10) not null,
CREATEDATE DATE,
NAME NVARCHAR2(20)
)
partition by range (CREATEDATE)
(
partition R_CREATE1501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table A
add constraint PK_A_ID primary key (ID)
using index
tablespace LONEBOO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table B
(
ID NVARCHAR2(10) not null,
CREATEDATE DATE,
NAME NVARCHAR2(20),
DATA BLOB
)
partition by range (CREATEDATE)
(
partition R_CREATE1501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table B
add constraint PK_B_ID primary key (ID)
using index
tablespace LONEBOO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table C
(
ID nvarchar2(20),
NAME nvarchar2(50),
AGE number
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table C
add constraint pk_c_id primary key (ID);
INSERT INTO a VALUES ('1',to_date('2015-01-01','yyyy-mm-dd'),'hello');
INSERT INTO a VALUES ('2',to_date('2015-02-01','yyyy-mm-dd'),'world');
INSERT INTO a VALUES ('3',to_date('2015-03-01','yyyy-mm-dd'),'join');
INSERT INTO a VALUES ('4',to_date('2015-04-01','yyyy-mm-dd'),'syn');
INSERT INTO a VALUES ('5',to_date('2015-05-01','yyyy-mm-dd'),'koko');
INSERT INTO B VALUES ('1',to_date('2015-01-01','yyyy-mm-dd'),'hello',NULL);
INSERT INTO B VALUES ('2',to_date('2015-02-01','yyyy-mm-dd'),'world',NULL);
INSERT INTO B VALUES ('3',to_date('2015-03-01','yyyy-mm-dd'),'join',NULL);
INSERT INTO B VALUES ('4',to_date('2015-04-01','yyyy-mm-dd'),'syn',NULL);
INSERT INTO B VALUES ('5',to_date('2015-05-01','yyyy-mm-dd'),'koko',NULL);
透過sql development將圖片加入到data欄位中
INSERT INTO C VALUES ('1','hello',20);
INSERT INTO C VALUES ('2','world',30);
INSERT INTO C VALUES ('3','join',40);
建立目錄
C:\Users\Administrator>sqlplus loneboo/loneboo@orcl
SQL*Plus: Release 10.2.0.5.0 - Production on 星期一 10月 12 11:59:56 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory dirloneboo as 'D:\dirloneboo';
1.匯出後設資料
expdp loneboo/loneboo@orcl schemas=loneboo directory='dirloneboo' exclude=statistics content=metadata_only dumpfile=loneboometadata.dmp logfile=loneboometadata.log
2.匯出分割槽表部分資料
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=A,B query=\"where createdate <to_date('2015-03-01','yyyy-mm-dd')\" dumpfile=loneboombigtable%U.dmp logfile=loneboombigtable.log
3.匯出小表全部資料
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=C dumpfile=loneboosmalltable%U.dmp logfile=loneboosmalltable.log
4.匯出分割槽表剩餘資料
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=A,B query=\"where createdate >= to_date('2015-03-01','yyyy-mm-dd')\" dumpfile=lonebootableother%U.dmp logfile=lonebootableother.log
destination host 步驟：
C:\Users\Administrator>sqlplus /@orcl as sysdba
SQL> create tablespace loneboo datafile 'D:\oracle\product\10.2.0\oradata\orcl\loneboo01.dbf' size 200m;
表空間已建立。
SQL> create user loneboo identified by loneboo default tablespace loneboo temporary tablespace temp;
使用者已建立。
SQL> grant connect,dba to loneboo;
SQL> exit
C:\Users\Administrator>sqlplus loneboo/loneboo@orcl
SQL> create directory dirloneboo as 'D:\dirloneboo';
目錄已建立。
複製匯出的檔案到destination host 的 D:\dirloneboo
1.匯入後設資料
impdp loneboo/loneboo@orcl schemas=loneboo directory='dirloneboo' dumpfile=loneboometadata.dmp logfile=imploneboometadata.log
2.匯入大表的部分資料
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=loneboombigtable%U.dmp logfile=imploneboodata.log
3.匯入小表的全部資料
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=loneboosmalltable%U.dmp logfile=imploneboosmalltable.log
4.匯入大表的剩餘資料
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=lonebootableother%U.dmp logfile=imploneboopardata.log
source host：
os system:server2008x64
oracle version:10.0.2.0.5 32bit
destination host:
os system:server2008x64
oracle version:10.0.2.0.5 64bit
對資料庫中的表進行拆分匯出匯入，減少停機時間，首先將表比較大而不變化的資料先匯出，匯入，最好在匯出變化的資料
source host:
C:\Users\Administrator>sqlplus /@orcl as sysdba
SQL> create tablespace loneboo datafile 'D:\oracle\product\10.2.0\oradata\orcl\loneboo01.dbf' size 200m;
表空間已建立。
SQL> create user loneboo identified by loneboo default tablespace loneboo temporary tablespace temp;
使用者已建立。
SQL> grant connect,dba to loneboo;
SQL> exit
tables: A partition table
B partition blob table
C
create table A
(
ID NVARCHAR2(10) not null,
CREATEDATE DATE,
NAME NVARCHAR2(20)
)
partition by range (CREATEDATE)
(
partition R_CREATE1501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table A
add constraint PK_A_ID primary key (ID)
using index
tablespace LONEBOO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table B
(
ID NVARCHAR2(10) not null,
CREATEDATE DATE,
NAME NVARCHAR2(20),
DATA BLOB
)
partition by range (CREATEDATE)
(
partition R_CREATE1501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
partition R_CREATE1506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
tablespace LONEBOO
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
)
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table B
add constraint PK_B_ID primary key (ID)
using index
tablespace LONEBOO
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create table
create table C
(
ID nvarchar2(20),
NAME nvarchar2(50),
AGE number
)
;
-- Create/Recreate primary, unique and foreign key constraints
alter table C
add constraint pk_c_id primary key (ID);
INSERT INTO a VALUES ('1',to_date('2015-01-01','yyyy-mm-dd'),'hello');
INSERT INTO a VALUES ('2',to_date('2015-02-01','yyyy-mm-dd'),'world');
INSERT INTO a VALUES ('3',to_date('2015-03-01','yyyy-mm-dd'),'join');
INSERT INTO a VALUES ('4',to_date('2015-04-01','yyyy-mm-dd'),'syn');
INSERT INTO a VALUES ('5',to_date('2015-05-01','yyyy-mm-dd'),'koko');
INSERT INTO B VALUES ('1',to_date('2015-01-01','yyyy-mm-dd'),'hello',NULL);
INSERT INTO B VALUES ('2',to_date('2015-02-01','yyyy-mm-dd'),'world',NULL);
INSERT INTO B VALUES ('3',to_date('2015-03-01','yyyy-mm-dd'),'join',NULL);
INSERT INTO B VALUES ('4',to_date('2015-04-01','yyyy-mm-dd'),'syn',NULL);
INSERT INTO B VALUES ('5',to_date('2015-05-01','yyyy-mm-dd'),'koko',NULL);
透過sql development將圖片加入到data欄位中
INSERT INTO C VALUES ('1','hello',20);
INSERT INTO C VALUES ('2','world',30);
INSERT INTO C VALUES ('3','join',40);
建立目錄
C:\Users\Administrator>sqlplus loneboo/loneboo@orcl
SQL*Plus: Release 10.2.0.5.0 - Production on 星期一 10月 12 11:59:56 2015
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
連線到:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory dirloneboo as 'D:\dirloneboo';
1.匯出後設資料
expdp loneboo/loneboo@orcl schemas=loneboo directory='dirloneboo' exclude=statistics content=metadata_only dumpfile=loneboometadata.dmp logfile=loneboometadata.log
2.匯出分割槽表部分資料
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=A,B query=\"where createdate <to_date('2015-03-01','yyyy-mm-dd')\" dumpfile=loneboombigtable%U.dmp logfile=loneboombigtable.log
3.匯出小表全部資料
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=C dumpfile=loneboosmalltable%U.dmp logfile=loneboosmalltable.log
4.匯出分割槽表剩餘資料
expdp loneboo/loneboo@orcl directory='dirloneboo' exclude=statistics content=data_only parallel=10 tables=A,B query=\"where createdate >= to_date('2015-03-01','yyyy-mm-dd')\" dumpfile=lonebootableother%U.dmp logfile=lonebootableother.log
destination host 步驟：
C:\Users\Administrator>sqlplus /@orcl as sysdba
SQL> create tablespace loneboo datafile 'D:\oracle\product\10.2.0\oradata\orcl\loneboo01.dbf' size 200m;
表空間已建立。
SQL> create user loneboo identified by loneboo default tablespace loneboo temporary tablespace temp;
使用者已建立。
SQL> grant connect,dba to loneboo;
SQL> exit
C:\Users\Administrator>sqlplus loneboo/loneboo@orcl
SQL> create directory dirloneboo as 'D:\dirloneboo';
目錄已建立。
複製匯出的檔案到destination host 的 D:\dirloneboo
1.匯入後設資料
impdp loneboo/loneboo@orcl schemas=loneboo directory='dirloneboo' dumpfile=loneboometadata.dmp logfile=imploneboometadata.log
2.匯入大表的部分資料
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=loneboombigtable%U.dmp logfile=imploneboodata.log
3.匯入小表的全部資料
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=loneboosmalltable%U.dmp logfile=imploneboosmalltable.log
4.匯入大表的剩餘資料
impdp loneboo/loneboo@orcl directory='dirloneboo' content=data_only parallel=10 dumpfile=lonebootableother%U.dmp logfile=imploneboopardata.log
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28282660/viewspace-1814505/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- 【實驗】【外部表】以資料泵檔案格式抽取and遷移資料演示
- 資料泵實現資料遷移到異地庫
- ORM實操之資料庫遷移ORM資料庫
- 海量資料處理_資料泵分批資料遷移
- Mongo資料遷移實驗Go
- 資料遷移（1）——通過資料泵表結構批量遷移
- Oracle使用資料泵 (expdp/impdp)實施遷移Oracle
- 使用impdp,expdp資料泵進入海量資料遷移
- 資料庫升級之-資料泵資料庫
- 資料庫遷移資料庫
- 資料庫-oracle-資料庫遷移資料庫Oracle
- 使用資料泵遷移遇到的問題
- Laravel 學習之資料庫遷移Laravel資料庫
- 【遷移】使用rman遷移資料庫資料庫
- Oracle資料庫資料遷移流程Oracle資料庫
- 資料庫遷移 ：理解資料庫
- laravel資料庫遷移Laravel資料庫
- Odoo遷移資料庫Odoo資料庫
- redis資料庫遷移Redis資料庫
- Cacti 遷移資料庫資料庫
- 資料庫遷移方案資料庫
- ORACLE資料庫遷移Oracle資料庫
- 遷移資料庫成功！資料庫
- 建立資料庫遷移資料庫
- oracle遷移資料經驗Oracle
- 雲資料庫管理與資料遷移資料庫
- 資料泵全庫遷移，排除一個大表【kingsql作品】SQL
- 海量資料遷移之資料抽取流程
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）Oracle
- 資料庫遷移神器——Flyway資料庫
- 遷移MySQL 5.7資料庫MySql資料庫
- 用rman遷移資料庫資料庫
- 資料庫遷移手記資料庫
- django資料庫遷移-15Django資料庫
- SQL Server資料庫遷移SQLServer資料庫