步骤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/