技术标签: 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
版权声明:本文为博主原创文章,转载请附上博文链接!
文章浏览阅读2.4k次,点赞68次,收藏54次。随机链表的复制涉及到复制一个链表,该链表不仅包含普通的next指针,还包含random指针,该指针指向链表中的任意节点或空节点。
文章浏览阅读1.5k次,点赞38次,收藏28次。style。
文章浏览阅读318次,点赞5次,收藏5次。本课题研究的“基于PHP的旅游景点攻略网站”就是为了使游客更加方便、快捷的了解旅游景点以及攻略信息而设计的。采取Mysql作为后台数据的主要存储单元,采用Thinkphp框架、Redis技术进行业务系统的编码及其开发,实现了本系统的全部功能。该系统实现了网站内部的各种工作流程计算机管理化,其中包括网站简介、景点分析、景点信息、旅游攻略、旅游心得、作品获奖等功能。_php与mysql课题设计
文章浏览阅读988次。一、概念1.风险的两个属性:概率和影响2.风险管理是通过识别,分析和应对风险来提高正面机会的概率,降低负面机会的概率。3.风险敞口:未加保护的风险4.单个项目风险和整体项目风险5.变异性风险:项目所依赖的关键条件或制约因素出现异常改变,就会导致变异性风险。黑天鹅事件6.模糊性风险:未彻底确定的风险7.整合式风险管理二、规划风险管理1.风险分类已知风险和未知风险内部风险和外部风险商业保险和可保险风险未知风险又分已知-未知风险和未知-未知风险商业风险是指正常的经营,技_变异性风险
文章浏览阅读383次。系统:windows7旗舰版 64位。oracle数据库服务器版本:oracle11g。oracle数据库客户端版本:64位 Version 12.2.0.1.0。PL/SQL版本:【Version 12.0.7.1837(64 bit)】。一、下载: 1、官网下载: 官网下载速度慢,不推荐,官网网址【https://www.allroundautoma..._数据库设计与plsql开发和优化
文章浏览阅读1.1k次,点赞5次,收藏20次。根据Github Trendings的统计,今日(2024-04-06统计)共有10个项目上榜。_rust著名的开源项目
文章浏览阅读97次。这里写自定义目录标题欢迎使用Markdown编辑器新的改变功能快捷键合理的创建标题,有助于目录的生成如何改变文本的样式插入链接与图片如何插入一段漂亮的代码片生成一个适合你的列表创建一个表格设定内容居中、居左、居右SmartyPants创建一个自定义列表如何创建一个注脚注释也是必不可少的KaTeX数学公式新的甘特图功能,丰富你的文章UML 图表FLowchart流程图导出与导入导出导入欢迎使用Markdown编辑器你好! 这是你第一次使用 Markdown编辑器 所展示的欢迎页。如果你想学习如何使用Mar_.o编译后的目标文件汇编
文章浏览阅读329次。【代码】Linux安装Jdk11步骤。_linux jdk11
文章浏览阅读55次。1、如果需要创建全局 DBLink,则需要先确定用户有创建 dblink 的权限: [c-sharp] view plaincopyprint? select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); 如果没有,则需要使用 sysdba 角色给用..._oracle dblink创建
文章浏览阅读959次,点赞20次,收藏17次。而AppAgent采用了一种和RPA类似的方法,通过点击、滑动等模拟方式来操作APP,无需访问任何敏感数据,同时加上了大语言模型的理解和学习能力,使其效果更加出色以及增强适配性。也可以理解成大模型中的,人类反馈强化学习过程。据悉,AppAgent的功能与AutoGPT等智能代理类似,不依赖于操作系统的后台访问,而是通过UI界面进行点击、滑动等拟人化操作,与App进行交互操作。此外,视觉理解模块还可以利用机器视觉,进行图片对象和语义特征的提取,例如,识别图片内容、界面控件等,为后续的大脑、执行提供支持。_腾讯 手机 模型
文章浏览阅读1.2k次,点赞28次,收藏30次。为啥网络安全缺口这么大,还是这么缺网络安全工程师?
文章浏览阅读46次。小明来对这个字符串进行操作,他会从头到尾检查这个字符串,如果发现有两个相同的字母并排在一起,就会把这两个字符都删掉。给定一个长度为n的字符串s,字符串由小写字母a…输出最后处理完成的字符串,有可能是空串。接下来一行一个长度为n的字符串s。你需要给出处理完成的字符串。直到没有相邻的相同字母。_c语言入栈存字符