技术标签: oracle
随着数据库数据量的不断增长,有些表需要由普通的堆表转换为分区表的模式。有几种不同的方法来对此进行操作,诸如导出表数据,然后创建分区表再导入数据到分区表;使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描述的是使用DBMS_REDEFINITION来实现,下面是具体的操作示例。
有关具体的dbms_redefinition在线重定义表的原理及步骤可参考:基于 dbms_redefinition 在线重定义表
有关分区表的描述请参考:Oracle 分区表
1、准备环境
--创建用户
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 .. 1000000 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 ROW
BEGIN
: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);
--表上的索引相关信息
SQL> @idx_info
TABLE_NAME INDEX_NAME CL_NAM STATUS PAR
------------------------- ------------------------------ -------------------- -------- ---
LOOKUP LOOKUP_PK ID VALID NO
BIG_TABLE BIG_TABLE_PK ID VALID NO
BIG_TABLE BITA_CREATED_DATE_I CREATED_DATE VALID NO
BIG_TABLE BITA_LOOK_FK_I LOOKUP_ID VALID NO
--创建一个中间表(分区表)
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_2012 VALUES LESS THAN (TO_DATE('01/01/2013', 'DD/MM/YYYY')) tablespace tbs1,
PARTITION big_table_2013 VALUES LESS THAN (TO_DATE('01/01/2014', 'DD/MM/YYYY')) tablespace tbs2,
PARTITION big_table_2014 VALUES LESS THAN (MAXVALUE) tablespace tbs3);
2、切换普通表到分区表
----校验表能否被重定义
EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('LESHAMI', 'BIG_TABLE');
--开始重定义
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'LESHAMI',
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
--可选步骤,用于同步重定义表与临时表
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'LESHAMI',
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
--为中间表添加相应的索引,约束等
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
BEFORE UPDATE OF created_date
ON big_table2
FOR EACH ROW
BEGIN
:new.created_date := TO_CHAR (SYSDATE, 'yyyymmdd hh24:mi:ss');
END tr_bf_big_table2;
/
-- Gather statistics on the new table.
EXEC DBMS_STATS.gather_table_stats('LESHAMI', 'BIG_TABLE2', cascade => TRUE);
--完成在线重定义
BEGIN
dbms_redefinition.finish_redef_table( --Author : Leshami
uname => 'LESHAMI', --Blog : http://blog.csdn.net/leshmai
orig_table => 'BIG_TABLE',
int_table => 'BIG_TABLE2');
END;
/
--可以看到新的表BIG_TABLE上的索引已经成为分区索引,如最后一列所示
SQL> @idx_info
TABLE_NAME INDEX_NAME CL_NAM STATUS PAR
------------------------------ ------------------------------ ------------------------------ -------- ---
BIG_TABLE BIG_TABLE_PK2 ID VALID NO
BIG_TABLE BITA_LOOK_FK_I2 LOOKUP_ID N/A YES
BIG_TABLE BITA_CREATED_DATE_I2 CREATED_DATE N/A YES
BIG_TABLE2 BITA_CREATED_DATE_I CREATED_DATE VALID NO
BIG_TABLE2 BIG_TABLE_PK ID VALID NO
BIG_TABLE2 BITA_LOOK_FK_I LOOKUP_ID VALID NO
LOOKUP LOOKUP_PK ID VALID NO
--校验分区表
SQL> select table_name, partition_name,high_value,num_rows from user_tab_partitions where table_name='BIG_TABLE';
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- ------------------------------ ------------------------------- ----------
BIG_TABLE BIG_TABLE_2012 TO_DATE(' 2013-01-01 00:00:00', 333793
BIG_TABLE BIG_TABLE_2013 TO_DATE(' 2014-01-01 00:00:00', 332025
BIG_TABLE BIG_TABLE_2014 MAXVALUE 333128
3、切换后的收尾工作
--移除中间表
DROP TABLE big_table2;
--根据需要可以重命名索引及约束名
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;
4、其它
--在使用DBMS_REDEFINITION期间,如果我们使用COPY_TABLE_DEPENDENTS过程,其索引依旧为非分区索引
--需要删除原来的索引之后,然后重新创建为新的本地分区索引
/*SQL> set timing off
SQL> variable num_errors number
BEGIN
DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS
(uname=>'LESHAMI',
orig_table=>'BIG_TABLE',
int_table=>'BIG_TABLE2',
num_errors=>:num_errors);
END;
/
SQL> print num_errors
NUM_ERRORS
----------
0 */
--------------------------------------------------------
--如下为使用COPY_TABLE_DEPENDENTS依赖对象之后表BIG_TABLE2多出了几个TMP$$开头的索引,但是索引依旧为普通索引,而非分区索引
SQL> @idx_info2
TABLE_NAME INDEX_NAME CL_NAM CL_POS STATUS PAR
------------------------- ------------------------------ -------------------- ------ -------- ---
LOOKUP LOOKUP_PK ID 1 VALID NO
BIG_TABLE BIG_TABLE_PK ID 1 VALID NO
BIG_TABLE BITA_CREATED_DATE_I CREATED_DATE 1 VALID NO
BIG_TABLE BITA_LOOK_FK_I LOOKUP_ID 1 VALID NO
BIG_TABLE2 TMP$$_BIG_TABLE_PK0 ID 1 VALID NO
BIG_TABLE2 TMP$$_BITA_CREATED_DATE_I0 CREATED_DATE 1 VALID NO
BIG_TABLE2 TMP$$_BITA_LOOK_FK_I0 LOOKUP_ID 1 VALID NO
5 、参考:http://www.oracle-base.com/articles/misc/partitioning-an-existing-table.php
---------------------
作者:Leshami
来源:CSDN
原文:https://blog.csdn.net/leshami/article/details/30041813
版权声明:本文为博主原创文章,转载请附上博文链接!
文章浏览阅读2k次。windows下的pytorch-gpu安装_cuda必须安装在c盘吗
文章浏览阅读38次。springboot基于小程序的临沂大学非机动车车辆管理系统。开发软件:eclipse/myeclipse/idea。springboot基于微信小程序的线下剧本杀管理系统。springboot微信小程序的代驾系统的设计与实现。springboot一品萫茶馆管理系统的设计与实现。springboot交通违章处理系统的设计与实现。springboot洛阳地铁信息查询与管理系统。ssm基于微信小程序的多多母婴购物商城的设计与。springboot企业客户信息反馈平台。springboot校园篮球联赛管理系统。
文章浏览阅读1.1w次,点赞3次,收藏16次。第一步、打开任务方式一、控制面板中打开控制面板 >所有控制面板项 > 管理工具 ,任务计划程序方式二、运行中打开【Win+R】输入taskschd.msctaskschd.msc第二步、创建任务选中并右击【任务计划程序库】,点击【新文件夹】,命名为【Rsszy】。点击【操作】-【创建基本任务…】输入【名称】和【描述】,点击【下一步..._任务计划延时启动程序
文章浏览阅读5.2k次,点赞2次,收藏26次。数据安全与信息安全的关系,是包含的关系,信息安全包括了数据安全与网络安全,数据安全主要是数据使用的安全。_信息安全的指标有什么
文章浏览阅读828次。2022 年MathorCup 高校数学建模挑战赛——大数据竞赛赛道A:“58 到家”家政服务订单分配问题“58 到家”是“58 同城”旗下高品质、高效率的上门家政服务平台,平台向用户提供家政保洁、保姆、月嫂、搬家、维修等众多生活领域的服务。在家政保洁场景中,用户在平台下单购买服务后,平台会将订单分配给一个保洁阿姨,阿姨接到订单后按照用户指定的服务时间上门,进行保洁服务。_家政服务分配数学建模
文章浏览阅读2.9k次,点赞12次,收藏75次。如何为Java面试准备项目经验_java项目经验面试
文章浏览阅读455次。Redis在3.0版正式引入了集群这个特性。Redis集群是一个分布式(distributed)、容错(fault-tolerant)的 Redis内存K/V服务, 集群可以使用的功能是普通单机 Redis 所能使用的功能的一个子集(subset),比如Redis集群并不支持处理多个keys的命令,因为这需要在不同的节点间移动数据,从而达不到像Redis那样的性能,在高负载的情况下可能会导致不可预_myself,master - 0 0 2 connected 5492-10922 [5492->-cb3a1c46679dfa5211a6d9843
文章浏览阅读8.6w次,点赞91次,收藏396次。报错:Torch not compiled with CUDA enabled看这一篇就足够了_torch not compiled with cuda enabled
文章浏览阅读780次,点赞2次,收藏3次。Linux内核Thermal框架详解六、Thermal Core(5)_thermal_zone_get_temp
文章浏览阅读901次。Unity3D的MonoBehaviour与Component与Tranform与GameObjectC#的修饰符internal,default,virtual,sealed从一个空类说起private:不加scope修饰符则为private(不建议这样使用)internal:internal的作用域为一个工程virtual与sealed:virtual表示子类可以override,sealed..._transform 是 monobehaviour 类的属性么?
文章浏览阅读148次。http://code4app.com/ios/535651f2933bf0647d8b570f介绍:原创即时通讯系统,拥有和微信一样强大的通讯功能。具体特色有:1、首家支持聊天室群聊2、支持和微信一样的语音聊天,可以显示时长、未读状态,自动轮播未读语音;3、支持发送图片、表情、GIF动画表情等;..._tcp群聊系统1
文章浏览阅读194次。SQL Server中TOP子句可能导致的问题以及解决办法_.microsoft.sqlserver.jdbc.sqlserverexception: 为 top 或 fetch 子句行计数参数