Oracle 11g允许数据库之间单分区传输，这可以满足一些并不需要整张表数据都传输的需求。好，我们在下面就简单测试一下，传给大家大体思路。
首先我们用SYS用户创建两个表空间，然后创建一个测试用户
[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 19 08:47:43 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
sys@SA7N67B11G>
sys@SA7N67B11G> CREATE TABLESPACE transport_tbs_1
2 DATAFILE '/u01/app/oracle/oradata/SA7N67B11G/transport_tbs_1.dbf'
3 SIZE 5M AUTOEXTEND ON NEXT 128K;
Tablespace created.
sys@SA7N67B11G> CREATE TABLESPACE transport_tbs_2
2 DATAFILE '/u01/app/oracle/oradata/SA7N67B11G/transport_tbs_2.dbf'
3 SIZE 5M AUTOEXTEND ON NEXT 128K;
Tablespace created.
创建测试用户transport
sys@SA7N67B11G> CREATE USER transport IDENTIFIED BY oracle;
User created.
sys@SA7N67B11G> GRANT RESOURCE, CONNECT TO transport;
Grant succeeded.
sys@SA7N67B11G> ALTER USER transport
2 QUOTA UNLIMITED ON transport_tbs_1
3 QUOTA UNLIMITED ON transport_tbs_2;
User altered.
用测试用户连接并创建一个分区表，并导入数据：
[oracle@localhost ~]$ sqlplus transport/oracle
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 19 08:48:09 2012
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
transport@SA7N67B11G>
transport@SA7N67B11G> CREATE TABLE transport_test (
2 id NUMBER NOT NULL,
3 username VARCHAR2(30) NOT NULL,
4 login_date DATE,
5 CONSTRAINT transport_test_pk PRIMARY KEY (id)
6 )
7 PARTITION BY RANGE (login_date)
8 (
9 PARTITION part_2012 VALUES LESS THAN (TO_DATE('01-10-2012','DD-MM-YYYY'))
10 TABLESPACE transport_tbs_1,
11 PARTITION part_2013 VALUES LESS THAN (TO_DATE('01-10-2013','DD-MM-YYYY'))
12 TABLESPACE transport_tbs_2
13 );
Table created.
transport@SA7N67B11G> INSERT INTO transport_test VALUES (1, 'LUOCS', SYSDATE);
1 row created.
transport@SA7N67B11G> INSERT INTO transport_test VALUES (2, 'XLZHGJ', SYSDATE);
1 row created.
transport@SA7N67B11G> INSERT INTO transport_test VALUES (3, 'ORACLE', ADD_MONTHS(SYSDATE,-12));
1 row created.
transport@SA7N67B11G> INSERT INTO transport_test VALUES (4, 'LINUX', ADD_MONTHS(SYSDATE,-12));
1 row created.
transport@SA7N67B11G> commit;
Commit complete.
重新收集表统计信息：
transport@SA7N67B11G> EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSPORT_TEST');
PL/SQL procedure successfully completed.
查看分区信息：
transport@SA7N67B11G> COLUMN table_name FORMAT A20
transport@SA7N67B11G> COLUMN partition_name FORMAT A20
transport@SA7N67B11G> COLUMN tablespace_name FORMAT A20
transport@SA7N67B11G>
transport@SA7N67B11G> SELECT table_name, partition_name, tablespace_name, num_rows
2 FROM user_tab_partitions WHERE table_name = 'TRANSPORT_TEST';
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
TRANSPORT_TEST PART_2012 TRANSPORT_TBS_1 2
TRANSPORT_TEST PART_2013 TRANSPORT_TBS_2 2
transport@SA7N67B11G> select * from transport_test partition(part_2012);
ID USERNAME LOGIN_DATE
---------- ------------------------------------------------------------ -----------------------
3 ORACLE 19-OCT-2011 08:49:07
4 LINUX 19-OCT-2011 08:49:12
我们再回到SYS用户致第一个表空间为只读，待会我们把该表空间里的数据传输过去
sys@SA7N67B11G> show user
USER is "SYS"
sys@SA7N67B11G> ALTER TABLESPACE transport_tbs_1 READ ONLY;
Tablespace altered.
sys@SA7N67B11G> create or replace directory dmp_dir as '/home/oracle';
Directory created.
导出transport_tbs_1表空间对应的分区part_2012的内容：
[oracle@localhost ~]$ expdp system/oracle directory=dmp_dir dumpfile=part_2012.dmp tables=transport.transport_test:part_2012 transportable=always
Export: Release 11.2.0.1.0 - Production on Fri Oct 19 08:55:36 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** directory=dmp_dir dumpfile=part_2012.dmp tables=transport.transport_test:part_2012 transportable=always
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/home/oracle/part_2012.dmp
******************************************************************************
Datafiles required for transportable tablespace TRANSPORT_TBS_1:
/u01/app/oracle/oradata/SA7N67B11G/transport_tbs_1.dbf
Datafiles required for transportable tablespace USERS:
/u01/app/oracle/oradata/SA7N67B11G/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 08:55:48
我这里在同一个服务器上操作，为模拟先进行删除一下，对象为表和表空间，因为第一个表空间的数据文件我们要用到，所以不能删除
sys@SA7N67B11G> DROP TABLE transport.transport_test PURGE;
Table dropped.
sys@SA7N67B11G> DROP TABLESPACE transport_tbs_1 INCLUDING CONTENTS;
Tablespace dropped.
sys@SA7N67B11G> DROP TABLESPACE transport_tbs_2 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
我们通过IMPDP命令进行导入
[oracle@localhost ~]$ impdp system/oracle directory=dmp_dir dumpfile=part_2012.dmp partition_options=departition
> transport_datafiles='$ORACLE_BASE/oradata/SA7N67B11G/transport_tbs_1.dbf'
Import: Release 11.2.0.1.0 - Production on Fri Oct 19 09:13:41 2012
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** directory=dmp_dir dumpfile=part_2012.dmp partition_options=departition transport_datafiles=$ORACLE_BASE/oradata/SA7N67B11G/transport_tbs_1.dbf
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE UNIQUE INDEX "TRANSPORT"."TRANSPORT_TEST_PK" ON "TRANSPORT"."TRANSPORT_TEST" ("ID") PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 5890 OBJNO_REUSE 88526 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TRANSPORT"."TRANSPORT_TEST" ADD CONSTRAINT "TRANSPORT_TEST_PK" PRIMARY KEY ("ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 5890 OBJNO_REUSE 88526 INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENAB
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type INDEX:"TRANSPORT"."TRANSPORT_TEST_PK" creation failed
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at 09:13:44
切换到transport用户，查询一下导入情况：
transport@SA7N67B11G> show user
USER is "TRANSPORT"
transport@SA7N67B11G> exec dbms_stats.gather_schema_stats(user);
PL/SQL procedure successfully completed.
transport@SA7N67B11G> COLUMN table_name FORMAT A30
transport@SA7N67B11G> COLUMN tablespace_name FORMAT A20
transport@SA7N67B11G> SELECT table_name, tablespace_name, partitioned, num_rows
2 FROM user_tables;
TABLE_NAME TABLESPACE_NAME PARTIT NUM_ROWS
------------------------------ -------------------- ------ ----------
TRANSPORT_TES_PART_2012 TRANSPORT_TBS_1 NO 2
到这里我们发现PART_2012分区数据成功导入进来了。
Online Table Redefinition，在先重定义表
Oracle 在9i R2之后，提供了一个新的在线重新构造和重新定义表结构的功能：DBMS_REDEFINITION。该技术在几乎不中断业务的情况下，通过创建一个中间表，并通过内部机制，保证原表与中间表的数据同步，最后通过一个切换操作，完成表结构的在线重定义。下面就演示DBMS_REDEFINITION来在先重定义（普通表到分区表）的过程。
luocs@SA7N67B11G> show user
USER is "LUOCS"
我们先清理测试数据
luocs@SA7N67B11G> drop table login_aud purge;
Table dropped.
新创建一张login_aud表，这时候是普通表。
sys@SA7N67B11G> CREATE TABLE login_aud (
2 id NUMBER,
3 loginname VARCHAR2(30),
4 logindate date,
5 CONSTRAINT login_aud_pk PRIMARY KEY (id)
6 );
Table created.
再创建视图：
luocs@SA7N67B11G> CREATE VIEW login_aud_v AS
2 SELECT * FROM login_aud;
View created.
创建序列：
luocs@SA7N67B11G> CREATE SEQUENCE login_aud_seq cache 100;
Sequence created.
创建存储过程：
luocs@SA7N67B11G> CREATE OR REPLACE PROCEDURE get_name (
2 p_id IN login_aud.id%TYPE,
3 p_name OUT login_aud.loginname%TYPE) AS
4 BEGIN
5 SELECT loginname
6 INTO p_name
7 FROM login_aud
8 WHERE id = p_id;
9 END;
10 /
Procedure created.
创建触发器：
luocs@SA7N67B11G> CREATE OR REPLACE TRIGGER login_aud_tri
2 BEFORE INSERT ON login_aud
3 FOR EACH ROW
4 WHEN (new.id IS NULL)
5 BEGIN
6 :new.id := login_aud_seq.NEXTVAL;
7 END;
8 /
Trigger created.
然后我们查看一下这些对象的状态，确认都为VALID：
luocs@SA7N67B11G> COLUMN object_name FORMAT A20
luocs@SA7N67B11G> SELECT object_name, object_type, status FROM user_objects
2 where object_name in ('LOGIN_AUD','LOGIN_AUD_PK','LOGIN_AUD_V','LOGIN_AUD_SEQ','GET_NAME','LOGIN_AUD_TRI');
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- -------------------------------------- --------------
GET_NAME PROCEDURE VALID
LOGIN_AUD TABLE VALID
LOGIN_AUD_PK INDEX VALID
LOGIN_AUD_SEQ SEQUENCE VALID
LOGIN_AUD_TRI TRIGGER VALID
LOGIN_AUD_V VIEW VALID
6 rows selected.
我们灌一些数据：
luocs@SA7N67B11G> DECLARE
2 l_name VARCHAR2(10);
3 BEGIN
4 FOR i IN 1 .. 100 LOOP
5 IF MOD(i, 2) = 0 THEN
6 l_name := 'LUOCS';
7 ELSE
8 l_name := 'EXADATA';
9 END IF;
10
11 INSERT INTO LOGIN_AUD (id, loginname, logindate)
12 VALUES (i, l_name, add_months(SYSDATE,-1));
13 END LOOP;
14 COMMIT;
15 END;
16 /
PL/SQL procedure successfully completed.
好，我们开始在线重定义表：
创建一个临时分区表，表的结构要和Login_aud一样。
sys@SA7N67B11G> create tablespace part_login_aud_1 datafile '/u01/app/oracle/oradata/SA7N67B11G/part_login_aud_1.dbf' size 5M;
Tablespace created.
sys@SA7N67B11G> create tablespace part_login_aud_2 datafile '/u01/app/oracle/oradata/SA7N67B11G/part_login_aud_2.dbf' size 5M;
Tablespace created.
luocs@SA7N67B11G> show user
USER is "LUOCS"
luocs@SA7N67B11G> CREATE TABLE p_login_aud (
2 id NUMBER,
3 loginname VARCHAR2(30),
4 logindate date,
5 CONSTRAINT p_login_aud_pk PRIMARY KEY (id)
6 )
7 partition by hash(id)
8 partitions 4 store in (part_login_aud_1,part_login_aud_2)
9 /
Table created.
sys@SA7N67B11G> show user
USER is "SYS"
先检测下能否重定义
sys@SA7N67B11G> EXEC DBMS_REDEFINITION.can_redef_table('LUOCS', 'LOGIN_AUD');
PL/SQL procedure successfully completed.
启动在线重定义,该过程会复制原始表的数据到中间表
luocs@SA7N67B11G> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE(
3 uname => 'LUOCS',
4 orig_table => 'LOGIN_AUD',
5 int_table => 'P_LOGIN_AUD',
6 col_mapping => NULL,
7 options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
8 END;
9 /
PL/SQL procedure successfully completed.
这一步的本质是建立一个build immediate，基于rowid的增量刷新的物化视图。
开始同步数据：
luocs@SA7N67B11G> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE(
3 uname => 'LUOCS',
4 orig_table => 'LOGIN_AUD',
5 int_table => 'P_LOGIN_AUD');
6 END;
7 /
PL/SQL procedure successfully completed.
结束在线重定义
luocs@SA7N67B11G> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE(
3 uname => 'LUOCS',
4 orig_table => 'LOGIN_AUD',
5 int_table => 'P_LOGIN_AUD');
6 END;
7 /
PL/SQL procedure successfully completed.
现在我们可以删除中间分区表：
luocs@SA7N67B11G> drop table p_login_aud;
Table dropped.
查看分区信息：
luocs@SA7N67B11G> COLUMN PARTITION_NAME FOR A30
luocs@SA7N67B11G> COLUMN TABLESPACE_NAME FOR A30
luocs@SA7N67B11G> select partition_name, tablespace_name from user_tab_partitions
2 where table_name = 'LOGIN_AUD';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
SYS_P25 PART_LOGIN_AUD_1
SYS_P26 PART_LOGIN_AUD_2
SYS_P27 PART_LOGIN_AUD_1
SYS_P28 PART_LOGIN_AUD_2
luocs@SA7N67B11G> COLUMN object_name FORMAT A20
luocs@SA7N67B11G> SELECT object_name, object_type, status FROM user_objects;
LOGIN_AUD_V VIEW VALID
LOGIN_AUD_SEQ SEQUENCE VALID
GET_NAME PROCEDURE VALID
P_LOGIN_AUD_PK INDEX VALID
LOGIN_AUD TABLE VALID
LOGIN_AUD TABLE PARTITION VALID
LOGIN_AUD TABLE PARTITION VALID
LOGIN_AUD TABLE PARTITION VALID
LOGIN_AUD TABLE PARTITION VALID
9 rows selected.
这时我们发现触发器消失了，其实触发器在在线重定义之后和中间表（P_LOGIN_AUD）关联，当我们删除中间表的时候，触发器也跟着一起删掉了。
最后我们可以做一些创建本地分区索引或全局分区索引之类操作，当然根据需求去做
luocs@SA7N67B11G> CREATE INDEX IDX_LOGIN_AUD ON LOGIN_AUD(id,loginname) LOCAL;
Index created.
luocs@SA7N67B11G> SELECT partition_name, NUM_ROWS from user_tab_partitions
2 where table_name = 'LOGIN_AUD';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
SYS_P25
SYS_P26
SYS_P27
SYS_P28
重新收集统计信息：
luocs@SA7N67B11G> exec dbms_stats.gather_table_stats(user,'LOGIN_AUD');
PL/SQL procedure successfully completed.
luocs@SA7N67B11G> SELECT partition_name, NUM_ROWS from user_tab_partitions
2 where table_name = 'LOGIN_AUD';
PARTITION_NAME NUM_ROWS
------------------------------ ----------
SYS_P25 20
SYS_P26 28
SYS_P27 25
SYS_P28 27
当然，我们还可以反过来操作，即分区表向非分区表的转换，或者已分区表向另一种分区表的转换等等。
参考Oracle联机文档：http://docs.oracle.com/cd/B28359_01/server.111/b28310/tables007.htm
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
