it企业网站模板,了解wordpress,基层建设网站,网站外链应该怎么做☘️博主介绍☘️#xff1a; ✨又是一天没白过#xff0c;我是奈斯#xff0c;DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux#xff0c;也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章#xff0c;并且也会默默的点赞收藏加关注❣… ☘️博主介绍☘️ ✨又是一天没白过我是奈斯DBA一名✨ ✌✌️擅长Oracle、MySQL、SQLserver、Linux也在积极的扩展IT方向的其他知识面✌✌️ ❣️❣️❣️大佬们都喜欢静静的看文章并且也会默默的点赞收藏加关注❣️❣️❣️ 哈喽各位小伙伴经过前面几篇文章的介绍我详细地介绍了分区表和分区索引的基本概念、创建方法、分区索引的重建和管理以及一些常用的检查语句。通过这些知识的学习我们为将普通表迁移到分区表奠定了坚实的基础。 分区表是一个非常强大的功能它允许我们将数据映射到不同的物理磁盘上从而分散 IO提高数据的可管理性、可用性和性能。通过合理地使用分区表我们可以更有效地管理和查询大规模数据同时还能提高数据库的性能和稳定性。 现在让我们探索如何将现有的普通表数据迁移到分区表中。这个过程可能会涉及到数据转换、索引重建等一系列复杂操作。但是只要我们掌握了正确的方法和技巧就能够顺利地完成这个任务。 因为分区技术需要介绍的太多那么我将分成五篇来进行介绍以便大家因为篇幅过长而感到阅读疲惫。五篇的内容分别如下
第一篇分区表和分区索引的介绍和分类
第二篇分区表的管理
第三篇分区索引的重建和管理
第四篇分区表和分区索引常用的检查语句
第五篇普通表迁移到分区表当前篇 目录
方式一通过expdp/impdp将普通表数据迁移到分区表非在线方式需要停止相关表的业务
案例将非分区表table_m5转为hash分区表
方式二通过SQL语句insert into...select插入的方式将普通表数据迁移到分区表非在线方式需要停止相关表的业务
案例将非分区表table_m5转为间隔分区表
方式三重点通过dbms_redefinition在线重定义的方式将普通表数据迁移到分区表在线方式不需要停止相关表的业务可以进行在线迁移
案例将非分区表table_m10转为range分区表 普通表数据迁移到分区表的方式今天我介绍三种三种方式各有优势我将一一详细介绍。 方式一通过expdp/impdp将普通表数据迁移到分区表。这种属于非在线方式需要停止相关表的业务 方式二通过SQL语句insert into...select插入的方式将普通表数据迁移到分区表。这种属于非在线方式需要停止相关表的业务 方式三重点通过dbms_redefinition在线重定义的方式将普通表数据迁移到分区表。这种属于在线方式不需要停止相关表的业务可以进行在线迁移 废话不多说
one
two
three
开始今天的内容
先回顾一下分区表的优点、缺点、特性和什么时候用分区表设计原则 分区表的优点、缺点、特性 1由于将数据分散到各个分区中减少了数据损坏的可能性 2可以对单独的分区进行备份和恢复 3可以将分区映射到不同的物理磁盘上来分散 IO 4提高可管理性、可用性和性能。 5缺点已经存在的表没有方法可以直接转化为分区表。不过Oracle提供了在线重定义表的功能。 6特殊性含有LONG、LONGRAW数据类型的表不能进行分区一般分区类型为varchar,varchar2,number,date 7每个表的分区或子分区数的总数不能超过1023个。 什么时候用分区表设计原则 1单表过大当表的大小超过2GB或对于OLTP系统表的记录超过1000万都应考虑对表进行分区。 2历史数据据需要剥离的表中包含历史数据新的数据被增加到新的分区中。 3查询特征非常明显比如是按整年、整月或者按某个范围 4基于表的大部分查询应用只访问表中少量的数据。 5按时间段删除成批的数据例如按月删除历史数据。 6按时间周期进行表空间的备份时将分区与表空间建立对应关系。 7如果一个表中大部分数据都是只读数据通过对表进行分区可将只读数据存储在只读表空间中对于大数据库的备份是非常有益的。 8对于经常执行并行查询的表应考虑进行分区。 9当对表的部分数据可用性要求很高时应考虑进行表分区。 方式一通过expdp/impdp将普通表数据迁移到分区表非在线方式需要停止相关表的业务 10g引入了最新的数据泵(Data Dump)技术。它可以通过使用并行在效率上要比exp/imp要高。可以跨平台迁移能够在不同硬件平台上的oracle之间传递数据支持全库、用户、表级的备份与恢复。对于数据泵(Data Dump)的相关参数这篇我不做具体介绍如果想了解的小伙伴可以私聊我我安排一篇文章来专门去讲数据泵 案例将非分区表table_m5转为hash分区表
1查看表数据和索引
SQL select count(*) from table_m5; SQL select * from dba_indexes where table_nameTABLE_M5; ---有索引 2通过expdp导出表table_m5。为了保证数据一致性需要对table_m5加锁防止对该表的操作或者业务去停止相关该表的操作 创建数据泵directory并赋权 SQL create directory bk as /home/oracle ; SQL grant all on directory bk to public; 导出数据 [oracle11g ~]$ expdp \/ as sysdba\ directorybk dumpfileexpdp_orcl_table_%U.dmp tablesusername.table_m5 parallel2 clustern 3重命名原表切记不要删除便于分区错误后恢复并创建分区表与旧表的结构一致
SQL alter table table_m5 rename to table_m5_old; ---重命名原表
4将表结构、索引、约束的DDL语句全部复制
SQL select dbms_metadata.get_ddl(TABLE,TABLE_M5_OLD,USERNAME) from dual;SQL select TABLE_NAME,TABLE_TYPE,INDEX_NAME from dba_indexes where table_nameTABLE_M5_OLD;
SQL select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_nameTABLE_M5_OLD;
SQL select dbms_metadata.get_ddl(INDEX,索引名,USERNAME) from dual;
SQL select dbms_metadata.get_ddl(CONSTRAINT,约束名,USERNAME) from dual;
5创建hash分区表
SQL
CREATE TABLE USERNAME.TABLE_M5 (ID NUMBER(20,0) NOT NULL ENABLE, NAME VARCHAR2(20) NOT NULL ENABLE, AGE NUMBER(10,0) NOT NULL ENABLE, SEX VARCHAR2(10) NOT NULL ENABLE, CARDID NUMBER(30,0) NOT NULL ENABLE, JOINDATE DATE NOT NULL ENABLE, REGION VARCHAR2(12) NOT NULL ENABLE, TEL VARCHAR2(12) NOT NULL ENABLE, EMAIL VARCHAR2(30) NOT NULL ENABLE, RECOMMEND VARCHAR2(10), IDENTIFIER VARCHAR2(100), PRIMARY KEY (ID)
)
partition by hash(cardid)
partitions 2;SQL alter table TABLE_M5 add primary key (ID); ---通过plsql查看的旧表结构但主键创建在最下面一定要创建
6导入表验证数据 [oracle11g ~]$ impdp \/ as sysdba\ directorybk dumpfileexpdp_orcl_table_01.dmp,expdp_orcl_table_02.dmp table_exists_actionappend SQL select * from dba_part_tables where table_nameTABLE_M5; ---记录分区的表的信息
SQL select * from DBA_tab_partitions where table_nameTABLE_M5; ---记录表的分区的信息
SQL select * from dba_part_indexes where table_nameTABLE_M5; ---查询分区的索引信息原普通索引还在 SQL select count(*) from TABLE_M5;SQL select count(*) from TABLE_M5 partition(SYS_P221); ---hash分区名自动分配通过DBA_tab_partitions
SQL select count(*) from TABLE_M5 partition(SYS_P222); ---hash分区名自动分配通过DBA_tab_partitions
7创建分区索引。
SQL create index table_m5_index on table_m5(cardid) local;
SQL select index_name,status from dba_indexes where table_nameTABLE_M5; ---N/A分区索引。分区索引有无有效需要查看dba_ind_partitions视图的status列usable有效unusable无效SQL select * from dba_part_indexes where table_nameTABLE_M5;
SQL select * from dba_ind_partitions where index_nameTABLE_M5_INDEX;
SQL select index_name,partition_name,status from dba_ind_partitions where index_nameTABLE_M5_INDEX; 方式二通过SQL语句insert into...select插入的方式将普通表数据迁移到分区表非在线方式需要停止相关表的业务 INSERT INTO...SELECT语句在SQL中用于将一个表中的数据插入到另一个表中。它结合了INSERT语句和SELECT语句的功能允许从源表中检索数据并将其插入到目标表中。但是这种方式不适合数据量很大的表插入对于几百万的小表或者机器性能很强上千万的表也是适合的。 案例将非分区表table_m5转为间隔分区表
1查看表数据和索引
SQL select count(*) from table_m5; SQL select * from dba_indexes where table_nameTABLE_M5; ---有索引 2重命名原表切记不要删除便于分区错误后恢复并创建分区表与旧表的结构一致
SQL alter table table_m5 rename to table_m5_old; ---重命名原表
3将表结构、索引、约束的DDL语句全部复制
SQL select dbms_metadata.get_ddl(TABLE,TABLE_M5_OLD,USERNAME) from dual;SQL select TABLE_NAME,TABLE_TYPE,INDEX_NAME from dba_indexes where table_nameTABLE_M5_OLD;
SQL select TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from dba_constraints where table_nameTABLE_M5_OLD;
SQL select dbms_metadata.get_ddl(INDEX,索引名,USERNAME) from dual;
SQL select dbms_metadata.get_ddl(CONSTRAINT,约束名,USERNAME) from dual;
4创建间隔分区表
SQL
CREATE TABLE USERNAME.TABLE_M5 (ID NUMBER(20,0) NOT NULL ENABLE, NAME VARCHAR2(20) NOT NULL ENABLE, AGE NUMBER(10,0) NOT NULL ENABLE, SEX VARCHAR2(10) NOT NULL ENABLE, CARDID NUMBER(30,0) NOT NULL ENABLE, JOINDATE DATE NOT NULL ENABLE, REGION VARCHAR2(12) NOT NULL ENABLE, TEL VARCHAR2(12) NOT NULL ENABLE, EMAIL VARCHAR2(30) NOT NULL ENABLE, RECOMMEND VARCHAR2(10), IDENTIFIER VARCHAR2(100), PRIMARY KEY (ID)
)
partition by range(joindate)
interval (numtoyminterval(1,month) )
(
partition p_001 values less than (to_date(2015-01-01,YYYY-MM-DD)),
partition p_002 values less than (to_date(2015-02-01,YYYY-MM-DD)),
partition p_003 values less than (to_date(2015-03-01,YYYY-MM-DD))
);SQL alter table TABLE_M5 add primary key (ID); ---通过plsql查看的旧表结构但主键创建在最下面一定要创建
5insert导入表验证数据。为了保证数据一致性需要对table_m5加锁防止对该表的操作或者业务去停止相关该表的操作
SQL insert into table_m5 select * from table_m5_old;
SQL commit; SQL select * from dba_part_tables where table_nameTABLE_M5; ---记录分区的表的信息 SQL select * from DBA_tab_partitions where table_nameTABLE_M5; ---记录表的分区的信息。根据分区字段joindate的时间自动创建按1个月创建分区 SQL select count(*) from TABLE_M5; SQL select count(*) from TABLE_M5 partition(P_001); ---间隔分区名自动分配通过DBA_tab_partitions SQL select count(*) from TABLE_M5 partition(P_002); ---间隔分区名自动分配通过DBA_tab_partitions 6创建分区索引。
SQL create index table_m5_index on table_m5(cardid) local;
SQL select index_name,status from dba_indexes where table_nameTABLE_M5; ---N/A分区索引。分区索引有无有效需要查看dba_ind_partitions视图的status列usable有效unusable无效SQL select * from dba_part_indexes where table_nameTABLE_M5;
SQL select * from dba_ind_partitions where index_nameTABLE_M5_INDEX;
SQL select index_name,partition_name,status from dba_ind_partitions where index_nameTABLE_M5_INDEX; 方式三重点通过dbms_redefinition在线重定义的方式将普通表数据迁移到分区表在线方式不需要停止相关表的业务可以进行在线迁移 在高可用系统中改变表的定义是一件比较棘手事尤其是对于7×24系统。Oracle提供的基本语法基本可以满足一般性修改但是对于把普通堆表改为分区表把索引组织表修改为堆表等操作就无法完成了。而且对于被大量DML语句访问的表9i版本开始提供了在线重定义表功能通过调用DBMS_REDEFINITION包可以在修改表结构的同时允许DML操作。 dbms_redefinition在线重定义能保证数据的一致性在大部分时间内表都可以正常进行DML操作。只在切换的瞬间锁表具有很高的可用性这种方法具有很强的灵活性对各种不同的需要都能满足。而且可以在切换前进行相应的授权并建立各种约束可以做到切换完成后不再需要任何额外的管理操作。 dbms_redefinition进行分表转换可以实现真正意义上的0秒不停机切换而且完全保证数据一致性只是在dbms_redefinition.finish_redef_table时有短暂的独占表锁定 在线重定义表具有以下功能 1修改表的存储参数 2将表转移到其他表空间 3增加并行查询选项 4增加或删除分区 5重建表以减少碎片 6将堆表改为索引组织表或相反的操作 7增加或删除一个列 注调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色除此之外还需要CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、 LOCKANY TABLE和SELECT ANY TABLE的权限。 使用在线重定义的一些限制条件 (1)必须有足够的空间容纳表的两个副本。 (2)不能修改主键列。 (3)表必须有主键(ORA-12089: cannot online redefine table with no primary key) (4)必须在同一用户中进行重新定义跨用户不行。 (5)在重新定义操作完成之前添加的新列不能变为非NULL。 (6)表不能包含long、bfile或用户定义的类型。 (7)不能重新定义群集表。 (8)不能重新定义SYS或系统模式中的表。 (9)不能重定义带有物化视图日志或在其上定义物化视图的表 (10)重新定义时无法进行数据的水平子设置。 (11)数据库若是rac只需在其中一个节点的数据库上实施。 (12)实施中部分命令执行时间较长命令一旦执行后请不要中断命令的执行一条命令执行完毕后不能再次重复执行该命令。 (13)执行命令的数据库用户需要为sys或者system用户 在线重定义的操作流程如下建议在10.2.0.5/11.2.0.1上操作BUG少 1创建基础表A如果存在就不需要操作。 2创建中间的分区表B。 3开始重定义将基表A的数据导入中间分区表B。 4结束重定义此时在DB的Name Directory里已经将2个表进行了交换。即此时基表A成了分区表我们创建的中间分区表B成了普通表。此时我们可以删除我们创建的中间表B。它已经是普通表。 案例将非分区表table_m10转为range分区表 1查看表数据和索引
SQL select count(*) from table_m10; SQL select * from dba_indexes where table_nameTABLE_M10; ---有索引 2创建中间range分区表这里的中间表不是传统意义上的临时表而是真真实实存在的分区表为了将普通表的数据过渡 SQL select dbms_metadata.get_ddl(TABLE,TABLE_M10,USERNAME) from dual; 只需要查看表结构因为可以通过DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS包的形式将索引、触发器、约束、权限复制在下面操作 SQL CREATE TABLE USERNAME.TABLE_M10_LS(ID NUMBER(20,0) NOT NULL ENABLE,NAME VARCHAR2(20) NOT NULL ENABLE,AGE NUMBER(10,0) NOT NULL ENABLE,SEX VARCHAR2(10) NOT NULL ENABLE,CARDID NUMBER(30,0) NOT NULL ENABLE,JOINDATE DATE NOT NULL ENABLE,REGION VARCHAR2(12) NOT NULL ENABLE,TEL VARCHAR2(12) NOT NULL ENABLE,EMAIL VARCHAR2(30) NOT NULL ENABLE,RECOMMEND VARCHAR2(10),IDENTIFIER VARCHAR2(100),PRIMARY KEY (ID))
partition by range (ID)
(
partition P10000 values less than (10010000),
partition P20000 values less than (10020000),
partition P30000 values less than (10030000),
partition P40000 values less than (10040000),
partition P50000 values less than (10050000),
partition P_MAX values less than (MAXVALUE)
);
3进行表重定义操作 一、检查table_m10是否可以进行重定义只是验证原表是否可以通过dbms_redefinition进行重定义操作的条件 SQL begindbms_redefinition.can_redef_table(USERNAME,TABLE_M10);end;/ 二、对table_m10进行重定义同步原表数据到中间分区表 SQL begindbms_redefinition.start_redef_table(uname USERNAME, ---表所属的用户orig_table TABLE_M10, ---要重新组织表的名称int_table TABLE_M10_LS, ---中间表的名称options_flag dbms_redefinition.cons_use_pk); ---使用主键约束来重定义表按照这个标准即可。options_flag有两个选项
--dbms_redefinition.cons_use_pk在重定义时创建的物化视图是基于主键的刷新
--dbms_redefinition.cons_use_rowid在重定义时创建的物化视图是基于rowid刷新。如果表启用了行迁移数据会乱掉不建议使用end;/ 4复制原表的上的依赖关系包括索引、触发器、约束、权限等
SQL declarenum_errors pls_integer;beginDBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(uname USERNAME,orig_table TABLE_M10,int_table TABLE_M10_LS,copy_indexes dbms_redefinition.cons_orig_params, --copy索引copy_triggers TRUE, ---copy触发器copy_constraints TRUE, ---copy约束copy_privileges TRUE, ---copy表的权限ignore_errors TRUE, num_errors num_errors);end;/
5同步原表与中间表的数据dbms_redefinition.start_redef_table操作时如果表中有数据那么会消耗很长的时间因此原表还在DML操作中间分区表不是最新的数据所以要同步最新的数据
SQL begindbms_redefinition.sync_interim_table( uname USERNAME,orig_table TABLE_M10,int_table TABLE_M10_LS);end;/
6结束重定义此操作就是将原表和中间表进行表名转换原普通表变为了分区表中间分区表变为了普通表。注转换只能进行一次不能回切会报错
注finish_redef_table过程中原始表被短暂锁定独占锁定。如果在第五步sync_interim_table后原表还有DML操作insert插入那么第六步就是先独占锁定表然后再执行一次sync_interim_table数据同步到中间表保证两表之间的数据完全一致0秒不停机切换而且数据也会保证一致性只是有短暂的锁表之后进行表名转换
SQL begindbms_redefinition.finish_redef_table( uname USERNAME,orig_table TABLE_M10,int_table TABLE_M10_LS);end;/
7验证table_m10数据并验证是否分区。在dbms_redefinition.finish_redef_table结束重定义的时候原表的结构直接继承了中间分区表的结构中间分区表则变成了普通表分区操作在秒级
SQL select count(*) from TABLE_M10; ---并对比TABLE_M10_LS数据查看是否缺少 SQL select * from dba_part_tables where table_nameTABLE_M10; ---记录分区的表的信息
SQL select * from DBA_tab_partitions where table_nameTABLE_M10; ---记录表的分区的信息
SQL select * from dba_part_indexes where table_nameTABLE_M10; ---查询分区的索引信息原普通索引还在 SQL select count(*) from TABLE_M10 partition(P10000); ---range分区名通过DBA_tab_partitions
SQL select count(*) from TABLE_M10 partition(P20000); ---range分区名通过DBA_tab_partitions 8创建分区索引。
SQL create index table_m5_index on table_m5(cardid) local;
SQL select index_name,status from dba_indexes where table_nameTABLE_M5; ---N/A分区索引。分区索引有无有效需要查看dba_ind_partitions视图的status列usable有效unusable无效SQL select * from dba_part_indexes where table_nameTABLE_M5;
SQL select * from dba_ind_partitions where index_nameTABLE_M5_INDEX;
SQL select index_name,partition_name,status from dba_ind_partitions where index_nameTABLE_M5_INDEX;
9删除table_m10_ls中间表已经由分区表变为了普通表
SQL drop table TABLE_M10_LS;
