oracle分区表的迁移

原创

jokehst 2012-12-17 23:49:25 ©著作权

文章标签 oracle count 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者jokehst的原创作品，请联系作者获取转载授权，否则将追究法律责任

使用exp/imp导出导入分区表数据

1.分区表状态
SQL> select count(*) from test123;

COUNT(*)
----------
262145-----------------------------表的总条目

SQL> select count(1) from test123 partition(p1);

COUNT(1)
----------
262144-----------------------------p1分区所占条目

SQL> select count(1) from test123 partition(p2);

COUNT(1)
----------
1-----------------------------p2分区所占条目

SQL> select table_name,partition_name,tablespace_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
TEST123 P1
TS01

TEST123 P2
TS02

TEST123 P3
TS03

TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TABLESPACE_NAME
------------------------------
TEST123 PMAX
TS04

2.导入、导出目录的设置

SQL> select directory_name,directory_path from dba_directories where DIRECTORY_NAME='DUMP_DIR';

DIRECTORY_NAME DIRECTORY_PATH
------------------------------ --------------------
DUMP_DIR /home/oracle/dump

3.导出整个表

[oracle@jokesql2 admin]$ exp \'sys/123 as sysdba\' file='/home/oracle/dump' tables=test123

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 22:32:54 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)

About to export specified tables via Conventional Path ...
. . exporting table                        TEST123
. . exporting partition                             P1     262144 rows exported
. . exporting partition                             P2          1 rows exported
. . exporting partition                             P3          0 rows exported
. . exporting partition                           PMAX          0 rows exported
Export terminated successfully without warnings.

4.导出所有分区

[oracle@jokesql2 ~]$ exp \'sys/123 as sysdba\' file='/home/oracle/test123_p1' tables='(test123:p1,test123:p2,test123:p3,test123:pmax)'

Export: Release 10.2.0.1.0 - Production on Mon Dec 17 22:47:21 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

5.将导出文件拷贝到目标主机目录
[oracle@jokesql2 ~]$ scp test* oracle@192.168.18.116:/home/oracle
oracle@192.168.18.116's password:
test123.dmp 100% 3856KB 3.8MB/s 00:00
test123_p1.dmp 100% 3856KB 3.8MB/s 00:01

6.导入所有分区（前提要有相应的表空间，我是采用手工方式建立的）

[oracle@jokesql admin]$ imp \'sys/123 as sysdba\' tables='(test123:p1,test123:p2,test123:p3,test123:pmax)' rows=y file='/home/oracle/test123_p1.dmp'

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 23:19:22 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

能够看到分区条目了
SQL> select count(1) from test123 partition(p1);

COUNT(1)
----------
262144

7.导入整个表

[oracle@jokesql admin]$ imp \'sys/123 as sysdba\' tables=test123 rows=y file='/home/oracle/test123.dmp'

Import: Release 10.2.0.1.0 - Production on Mon Dec 17 23:24:13 2012

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses WE8ISO8859P1 character set (possible charset conversion)
. importing SYS's objects into SYS
. importing SYS's objects into SYS
IMP-00015: following statement failed because the object already exists:
"CREATE TABLE "TEST123" ("ID" NUMBER, "CREATEDATE" DATE) PCTFREE 10 PCTUSED"
" 40 INITRANS 1 MAXTRANS 255 TABLESPACE "SYSTEM" LOGGING PARTITION BY RANGE "
"("CREATEDATE" ) (PARTITION "P1" VALUES LESS THAN (TO_DATE(' 2001-01-01 00:"
"00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PC"
"TUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 9437184 FREELISTS 1 FREELI"
"ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS01" LOGGING NOCOMPRESS, PART"
"ITION "P2" VALUES LESS THAN (TO_DATE(' 2002-01-01 00:00:00', 'SYYYY-MM-DD H"
"H24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MA"
"XTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL "
"DEFAULT) TABLESPACE "TS02" LOGGING NOCOMPRESS, PARTITION "P3" VALUES LESS T"
"HAN (TO_DATE(' 2003-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA"
"R=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITI"
"AL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS0"
"3" LOGGING NOCOMPRESS, PARTITION "PMAX" VALUES LESS THAN (MAXVALUE) PCTFRE"
"E 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 F"
"REELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "TS04" LOGGING NOCOMPRESS "
")"
Import terminated successfully with warnings.

查看
SQL> select count(*) from test123;

COUNT(*)
----------
262145