oracle 分区转移,oracle分区表的迁移
使用exp/imp导出导入分区表数据1.分区表状态SQL> select count(*) from test123;COUNT(*)----------262145-----------------------------表的总条目SQL> select count(1) from test123 partition(p1);COUNT(1)----------262144-----
使用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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
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.
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
. . importing partition "TEST123":"P1" 262144 rows imported
. . importing partition "TEST123":"P2" 1 rows imported
. . importing partition "TEST123":"P3" 0 rows imported
. . importing partition "TEST123":"PMAX" 0 rows imported
Import terminated successfully without warnings.
能够看到分区条目了
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
Copyright (c) 1982, 2005, Oracle. All rights reserved.
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
魔乐社区（Modelers.cn) 是一个中立、公益的人工智能社区，提供人工智能工具、模型、数据的托管、展示与应用协同服务，为人工智能开发及爱好者搭建开放的学习交流平台。社区通过理事会方式运作，由全产业链共同建设、共同运营、共同享有，推动国产AI生态繁荣发展。
更多推荐
0
0- 0
已为社区贡献5条内容
所有评论(0)