使用exchange普通表模式被切换到分区表-白红宇

使用exchange普通表模式被切换到分区表

阅读量：5284 次

发布时间：2019-06-14

本文共 5806 字，大约阅读时间需要 19 分钟。

随着数据库的不断增长的数据量。有些表需要转换的普通堆表分区表模式。

有几种不同的方式来执行此操作，如出口数据表，区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描写叙述的是使用EXCHANGE PARTITION方式来实现。以下是详细的操作演示样例。

有关详细的dbms_redefinition在线重定义表的原理及步骤可參考：

有关使用DBMS_REDEFINITION在线重定义分区表可參考：有关分区表的描写叙述请參考：

1、主要步骤

a、为新的分区表准备对应的表空间

b、基于源表元数据创建分区表以及相关索引、约束等

c、使用exchange方式将普通表切换为分区表

d、更正相关索引及约束名等(可省略)

e、使用split依据须要将分区表切割为多个不同的分区

f、收集统计信息

2、准备环境

--创建用户SQL> create user leshami identified by xxx;SQL> grant dba to leshami;--创建演示须要用到的表空间SQL> create tablespace tbs_tmp datafile '/u02/database/SYBO2/oradata/tbs_tmp.dbf' size 10m autoextend on;SQL> alter user leshami default tablespace tbs_tmp;SQL> create tablespace tbs1 datafile '/u02/database/SYBO2/oradata/tbs1.dbf' size 10m autoextend on;SQL> create tablespace tbs2 datafile '/u02/database/SYBO2/oradata/tbs2.dbf' size 10m autoextend on;SQL> create tablespace tbs3 datafile '/u02/database/SYBO2/oradata/tbs3.dbf' size 10m autoextend on;SQL> conn leshami/xxx-- 创建一个lookup表CREATE TABLE lookup (  id            NUMBER(10),  description   VARCHAR2(50));--加入主键约束ALTER TABLE lookup ADD (  CONSTRAINT lookup_pk PRIMARY KEY (id));--插入数据INSERT INTO lookup (id, description) VALUES (1, 'ONE');INSERT INTO lookup (id, description) VALUES (2, 'TWO');INSERT INTO lookup (id, description) VALUES (3, 'THREE');COMMIT;--创建一个用于切换到分区的大表CREATE TABLE big_table (  id            NUMBER(10),  created_date  DATE,  lookup_id     NUMBER(10),  data          VARCHAR2(50));--填充数据到大表DECLARE  l_lookup_id    lookup.id%TYPE;  l_create_date  DATE;BEGIN  FOR i IN 1 .. 10000 LOOP    IF MOD(i, 3) = 0 THEN      l_create_date := ADD_MONTHS(SYSDATE, -24);      l_lookup_id   := 2;    ELSIF MOD(i, 2) = 0 THEN      l_create_date := ADD_MONTHS(SYSDATE, -12);      l_lookup_id   := 1;    ELSE      l_create_date := SYSDATE;      l_lookup_id   := 3;    END IF;        INSERT INTO big_table (id, created_date, lookup_id, data)    VALUES (i, l_create_date, l_lookup_id, 'This is some data for ' || i);  END LOOP;  COMMIT;END;/--为大表加入主、外键约束，索引。以及加入触发器等.ALTER TABLE big_table ADD (  CONSTRAINT big_table_pk PRIMARY KEY (id));CREATE INDEX bita_created_date_i ON big_table(created_date);CREATE INDEX bita_look_fk_i ON big_table(lookup_id);ALTER TABLE big_table ADD (  CONSTRAINT bita_look_fk  FOREIGN KEY (lookup_id)  REFERENCES lookup(id));CREATE OR REPLACE TRIGGER tr_bf_big_table   BEFORE UPDATE OF created_date   ON big_table   FOR EACH ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table;/--收集统计信息EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'LOOKUP', cascade => TRUE);EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);

3、创建分区表

CREATE TABLE big_table2 (     id            NUMBER(10),     created_date  DATE,     lookup_id     NUMBER(10),     data          VARCHAR2(50)   )   PARTITION BY RANGE (created_date)   (PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);  ALTER TABLE big_table2 ADD (  CONSTRAINT big_table_pk2 PRIMARY KEY (id));CREATE INDEX bita_created_date_i2 ON big_table2(created_date) LOCAL;CREATE INDEX bita_look_fk_i2 ON big_table2(lookup_id) LOCAL;ALTER TABLE big_table2 ADD (  CONSTRAINT bita_look_fk2  FOREIGN KEY (lookup_id)  REFERENCES lookup(id));--触发器也须要单独加入到分区表CREATE OR REPLACE TRIGGER tr_bf_big_table2              --Author: Leshami   BEFORE UPDATE OF created_date                        --Blog   : http://blog.csdn.net/leshami   ON big_table2   FOR EACH ROWBEGIN   :new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');END tr_bf_big_table2;/

4、使用exchange切换为分区表

--以下的这个命令就是通过exchange方式来直接将普通表来切换为分区表ALTER TABLE big_table2  EXCHANGE PARTITION big_table_2014  WITH TABLE big_table  WITHOUT VALIDATION  UPDATE GLOBAL INDEXES;  SQL> select count(*) from big_table2;  COUNT(*)----------     10000DROP TABLE big_table;RENAME big_table2 TO big_table;ALTER TABLE big_table RENAME CONSTRAINT big_table_pk2 TO big_table_pk;ALTER TABLE big_table RENAME CONSTRAINT bita_look_fk2 TO bita_look_fk;ALTER INDEX big_table_pk2 RENAME TO big_table_pk;ALTER INDEX bita_look_fk_i2 RENAME TO bita_look_fk_i;ALTER INDEX bita_created_date_i2 RENAME TO bita_created_date_i;ALTER TRIGGER tr_bf_big_table2 RENAME TO tr_bf_big_table;

5、使用split方式切割分区表

ALTER TABLE big_table  SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2012 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))  INTO (PARTITION big_table_2012 tablespace tbs1 ,        PARTITION big_table_2014)  UPDATE GLOBAL INDEXES;ALTER TABLE big_table  SPLIT PARTITION big_table_2014 AT (TO_DATE('31-DEC-2013 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))  INTO (PARTITION big_table_2013 tablespace tbs2,        PARTITION big_table_2014)  UPDATE GLOBAL INDEXES;--收集统计信息，假设表非常大的话，须要考虑使用并行度。採样值--对于上述的操作中，本地分区索引和数据存储在指定的表空间，存在混用情形；对于全局索引则保存在缺省表空间。--上面提到的2种情形。能够依据须要作对应调整EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE', cascade => TRUE);--验证结果SQL> col HIGH_VALUE format a45 wrappedSQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions  2  where table_name='BIG_TABLE';TABLE_NAME      PARTITION_NAME       HIGH_VALUE                                      NUM_ROWS--------------- -------------------- --------------------------------------------- ----------BIG_TABLE       BIG_TABLE_2012       TO_DATE(' 2012-12-31 23:59:59', 'SYYYY-MM-DD        3333                                     HH24:MI:SS', 'NLS_CALENDAR=GREGORIABIG_TABLE       BIG_TABLE_2013       TO_DATE(' 2013-12-31 23:59:59', 'SYYYY-MM-DD        3334                                     HH24:MI:SS', 'NLS_CALENDAR=GREGORIABIG_TABLE       BIG_TABLE_2014       MAXVALUE                                            3333

本文參考：

很多其它參考

有关Oracle RAC请參考

有关Oracle 网络配置相关基础以及概念性的问题请參考：

有关基于用户管理的备份和备份恢复的概念请參考

有关RMAN的备份恢复与管理请參考

有关ORACLE体系结构请參考

转载于:https://www.cnblogs.com/mfrbuaa/p/4673214.html

你可能感兴趣的文章

百度地图Api进阶教程-地图鼠标左右键操作实例和鼠标样式6.html