使用TTS传输分区表

步骤1：
首先建立所需的表空间和建立基表
CREATE TABLESPACE ttsdat1 DATAFILE '/u01/app/o acle/oradata/oltpdb/ttsdat1.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE 50M;
CREATE TABLESPACE  ttsidx1 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsidx1.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE&n sp;50M;

CREATE TABLESPACE ttsdat2 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsdat2.dbf' SIZE&nbs

;1M AUTOEXTEND ON MAXSIZE 50M;
CREATE TABLESPACE ttsidx2 DATAFILE '/u01/app/oracle/or data/oltpdb/ttsidx2.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE 50M;

CREATE TABLESPACE tts at3 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsdat3.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE  0M;
CREATE TABLESPACE ttsidx3 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsidx3.dbf' SIZE 1M&nbs

;AUTOEXTEND ON MAXSIZE 50M;

CREATE TABLESPACE ttsdat4 DATAFILE '/u01/app/oracle/oradat /oltpdb/ttsdat4.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE 50M;
CREATE TABLESPACE ttsidx4&nbs ;DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsidx4.dbf' SIZE

1M AUTOEXTEND ON MAXSIZE 50M;

CREATE TABLESPACE ttsdat5 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsdat5.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE 50M;
CREATE TABLESPACE ttsidx5 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsidx5.dbf' SIZE 1M

AUTOEXTEND ON MAXSIZE 50M;

检查所建的表空间
SELECT tablespace_name, status, plugged_in FROM dba_tablespaces;

建立基表
CREATE TABLE txns (
  trans_id  NUMBER(12),
   trans_dt  DATE,
   from_acct CHAR(10),
   to_acct   CHAR(10),
   amount    NUMBER(12,2))
   PARTITION BY RANGE (trans_dt)
      ( PARTITION fy2004 VALUES LESS THAN (to_date('2005-01-01','yyyy-mm-dd') )
           TABLESPACE ttsdat1,
        PARTITION fy2005 VALUES LESS THAN (to_date('2006-01-01','yyyy-mm-dd') )
           TABLESPACE ttsdat2,
        PARTITION fy2006 VALUES LESS THAN (to_date('2007-01-01','yyyy-mm-dd') )
           TABLESPACE ttsdat3,
        PARTITION fy2007 VALUES LESS THAN (to_date('2008-01-01','yyyy-mm-dd') )
           TABLESPACE ttsdat4,
        PARTITION fy2008 VALUES LESS THAN (to_date('2009-01-01','yyyy-mm-dd') )
           TABLESPACE ttsdat5 );

建索引

CREATE UNIQUE INDEX txns_pk_idx ON txns(trans_id,trans_dt) LOCAL
   ( PARTITION fy2004 TABLESPACE ttsidx1,
     PARTITION fy2005 TABLESPACE ttsidx2,
     PARTITION fy2006 TABLESPACE ttsidx3,
     PARTITION fy2007 TABLESPACE ttsidx4,
     PARTITION fy2008 TABLESPACE ttsidx5 );

建主键

ALTER TABLE txns ADD CONSTRAINT txns_pk PRIMARY KEY (trans_id, trans_dt)
   USING INDEX LOCAL
   ( PARTITION fy2004,
     PARTITION fy2005,
     PARTITION fy2006,
     PARTITION fy2007,
     PARTITION fy2008 );

检查数据存在

SELECT * FROM txns WHERE rownum < 25;
SELECT trunc(trans_dt,'YEAR'), count(*) FROM txns GROUP BY trunc(trans_dt,'YEAR');

分析表及索引

exec dbms_stats.gather_table_stats('&','TXNS',cascade=> True);
-- OR --
ANALYZE TABLE txns COMPUTE STATISTICS;
ANALYZE INDEX txns_pk_idx COMPUTE STATISTICS;

-- 检查每个分区行数

SELECT partition_name, num_rows FROM user_tab_partitions;

-- 检查每个索引的行数

SELECT partition_name, num_rows, status FROM user_ind_partitions;

-- 检查整个表的行数

SELECT table_name, num_rows FROM user_Tables;

-- 检查每个段

SELECT segment_type, segment_name, partition_name, tablespace_name, blocks, extents FROM

user_segments ORDER BY segment_Type, segment_name, partition_name;

步骤2：准备剥离分区表

首先建立一个与要删除分区相同的独立的表

CREATE TABLE txns_fy2004 TABLESPACE ttsdat1 AS SELECT * FROM txns WHERE 1=0;
ALTER TABLE txns_fy2004 ADD CONSTRAINT txns_fy2004_pk PRIMARY KEY (trans_id, trans_dt)

USING INDEX TABLESPACE ttsidx1;

使用EXCHANGE PARTITION的方法，实际上数据并没有移动只是内部指针发生了变化。

ALTER TABLE txns EXCHANGE PARTITION fy2004  WITH TABLE txns_fy2004 INCLUDING INDEXES;

检查数据是否一致

SELECT COUNT(*) FROM txns PARTITION (fy2004);
SELECT COUNT(*) FROM txns_fy2004;

因为现在旧的分区表是空的，如果不在需要现在可以将它删除了

ALTER TABLE txns DROP PARTITION fy2004;

将需要TTS的表空间设置为READ ONLY

ALTER TABLESPACE ttsdat1 READ ONLY;
ALTER TABLESPACE ttsidx1 READ ONLY;

使用DBMS_TTS工具检查是否满足TTS

EXECUTE sys.DBMS_TTS.TRANSPORT_SET_CHECK('ttsdat1,ttsidx1', TRUE);
SELECT * FROM sys.TRANSPORT_SET_VIOLATIONS;

建立DIRECTORY
CREATE OR REPLACE DIRECTORY trans_dir AS '/u01/app/oracle/oradata/oltpdb';
GRANT READ, WRITE ON DIRECTORY trans_dir TO ...;

使用EXPDP导出
cd /u01/app/oracle/oradata/oltpdb

expdp /@tnsname \
   DUMPFILE=ttsfy1.dmp \
   DIRECTORY=trans_dir \
   TRANSPORT_TABLESPACES = ttsdat1,ttsidx1

ls -al

进行表空间的转换

rman target oltpdb
CONVERT TABLESPACE ttsdat1, ttsidx1
   TO PLATFORM. 'Microsoft Windows IA (32-bit)'
   FORMAT='$ORACLE_BASE/oradata/oltpdb/%N_win.dbf';
exit

ls -al

使表空间READ WRITTE状态。如果不在需要可以直接删除。

DROP TABLESPACE ttsdat1 INCLUDING CONTENTS;
DROP TABLESPACE ttsidx1 INCLUDING CONTENTS;

现在为准备下一年的数据和索引分区

CREATE TABLESPACE ttsdat9 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsdat6.dbf'
   SIZE 1M AUTOEXTEND ON MAXSIZE 50M;
CREATE TABLESPACE ttsidx9 DATAFILE '/u01/app/oracle/oradata/oltpdb/ttsidx6.dbf'
   SIZE 1M AUTOEXTEND ON MAXSIZE 50M;

ALTER TABLE txns ADD PARTITION fy2009
   VALUES LESS THAN (to_date('2010-01-01','yyyy-mm-dd') )
   TABLESPACE ttsdat6
   UPDATE INDEXES (txns_pk_idx (PARTITION fy2009 TABLESPACE ttsidx6));

步骤3：准备导入TTS

CONNECT arch/arch@BING
SELECT * FROM v$nls_parameters WHERE parameter IN

('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

---确认一个ORACLE data pump目录有必须的权限

CREATE OR REPLACE DIRECTORY trans_dir AS 'd:/oradata/oltpdb';

GRANT READ, WRITE ON DIRECTORY trans_dir TO ...;

准备导入：
impdp arch/arch@BING parfile=ttsfy1.par
DUMPFILE=ttsfy1.dmp
DIRECTORY=trans_dir
REMAP_SCHEMA=oltp:arch
TRANSPORT_DATAFILES='d:\oradata\oltpdb\TTSDAT1_WIN.dbf',
'd:\oradata\oltpdb\TTSIDX1_WIN.dbf'

默认新的表空间是READ ONLY，需要将它设置为READ WRITE。

ALTER TABLESPACE ttsdat1 READ WRITE;
ALTER TABLESPACE ttsidx1 READ WRITE;

创建新的归档表

CREATE TABLE arch
PARTITION BY RANGE (trans_dt)
( PARTITION fy2004 VALUES LESS THAN (to_date('2005-01-01','yyyy-mm-dd') )
TABLESPACE ttsdat1 )
AS SELECT * FROM txns_fy2004 WHERE 1=0;

建立索引和约束

CREATE UNIQUE INDEX arch_pk_idx ON arch(trans_id,trans_dt) LOCAL ( PARTITION fy2004

TABLESPACE ttsidx1 );

ALTER TABLE arch ADD CONSTRAINT arch_pk PRIMARY KEY (trans_id, trans_dt)
USING INDEX LOCAL ( PARTITION fy2004 );

desc arch

同样采用EXCHANGE PARTITION的方法

ALTER TABLE arch EXCHANGE PARTITION fy2004
WITH TABLE txns_fy2004 INCLUDING INDEXES;

删除表txns_fy2004

DROP TABLE txns_fy2004 PURGE;

继续处理后面的分区表

ALTER TABLE arch ADD PARTITION fy2005
VALUES LESS THAN (to_date('2006-01-01','yyyy-mm-dd') )
TABLESPACE ttsdat2
UPDATE INDEXES (arch_pk_idx (PARTITION fy2005 TABLESPACE ttsidx2));

ALTER TABLE arch EXCHANGE PARTITION fy2005 WITH TABLE txns_fy2005 INCLUDING INDEXES;

DROP TABLE txns_fy2005 PURGE;

用以上的办法，分别将所有的表空间传输到新的库中。

以上参考METALINK Note:731559.1

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7882490/viewspace-468213/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7882490/viewspace-468213/