Oracle 数据库在线重定义功能表分区以及分区前的准备工作

最新推荐文章于 2023-10-10 14:21:03 发布

想要做个心机男

最新推荐文章于 2023-10-10 14:21:03 发布

阅读量572

点赞数 2

分类专栏： Oracle 文章标签： Oracle 表分区在线重定义数据库

本文链接：https://blog.csdn.net/Heart_machine_boy/article/details/96729649

版权

Oracle 专栏收录该内容

1 篇文章 1 订阅

订阅专栏

前段时间完成了对已存在的Oracle数据表进行分区。由于是第一次接触Oracle数据库表分区的概念，在网上查阅了大量的博客文章，但是感觉绝大部分文章博客的内容几乎雷同，而且在实际生产过程的应用当中，会出现各种“意外的情况”，很难找到一篇详细完整的关于表分区的博客，所以想将我此次作业的过程遇到的“意外的情况”记录下来，其实就是希望各位第一次接触表分区的同僚，在执行各种进行表分区的操作时提前做好完整的检查，确保表分区的各个步骤都能正确执行，望各位大佬批评指正，也希望对跟我有同样需求的同学有所帮助。

首先对表分区做一个简单的介绍（这里偷个懒，附上链接就行啦。）：

https://blog.csdn.net/fighting_tobe_better/article/details/80881221 （新建分区表）

https://blog.csdn.net/wanglilin/article/details/7177338 （将已存在的表进行表分区）

我此次使用的是在线重定义的方法进行的表分区。大概知晓了表分区的执行步骤，但是，我们还不能放心大胆的执行分区的操作。在正式开始执行操作步骤之前，我们应当做如下检查：

1. 查看用户拥有的权限和角色：

调用DBMS_REDEFINITION包需要EXECUTE_CATALOG_ROLE角色，除此之外，还需要

CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE、SELECT ANY TABLE的权限。

查看权限：

select privilege from dba_sys_privs where grantee='C##LTS'

union

select privilege from dba_sys_privs where grantee in

(select granted_role from dba_role_privs where grantee='C##LTS' );

其中 C##LTS 是用户名。

如果用户权限缺失了表分区所需要的权限，需要提前给用户添加权限。

2. 使用在线重定义的方法进行表分区，需要数据库表空间有两倍大的分区表的大小空间。

说句题外话，我此次进行分区的表大小有44G，上亿条的数据我感觉是很大了。我在执行过程中执行的好几步花费的时间都很长，所以少走点弯路能节约不少时间，这也是我写这篇博客的初衷。

查看表的大小：

Select round(BYTES/1024/1024,2)||'M' from user_segments where segment_name='TEMP_RESULTS';

TEMP_RESULTS是表名。

查看数据库表空间的大小：

SELECT TABLESPACE_NAME "表空间",

       To_char(Round(BYTES / 1024, 2), '99990.00')

       || ''           "实有",

       To_char(Round(FREE / 1024, 2), '99990.00')

       || 'G'          "现有",

       To_char(Round(( BYTES - FREE ) / 1024, 2), '99990.00')

       || 'G'          "使用",

       To_char(Round(10000 * USED / BYTES) / 100, '99990.00')

       || '%'          "使用比例"

FROM   (SELECT A.TABLESPACE_NAME TABLESPACE_NAME,

Floor(A.BYTES / ( 1024 * 1024 )) BYTES,

Floor(B.FREE / ( 1024 * 1024 )) FREE,

Floor(( A.BYTES - B.FREE ) / ( 1024 * 1024 )) USED

FROM   (SELECT TABLESPACE_NAME TABLESPACE_NAME,

Sum(BYTES)  BYTES

FROM   DBA_DATA_FILES

GROUP  BY TABLESPACE_NAME) A,

(SELECT TABLESPACE_NAME TABLESPACE_NAME,

Sum(BYTES)      FREE

FROM   DBA_FREE_SPACE

GROUP  BY TABLESPACE_NAME) B

WHERE  A.TABLESPACE_NAME = B.TABLESPACE_NAME)

--WHERE TABLESPACE_NAME LIKE 'CDR%' --这一句用于指定表空间名称

ORDER  BY Floor(10000 * USED / BYTES) DESC;

此段代码可以直接复制粘贴。

查看待分区表所在表空间的剩余大小，满足条件即可。

当表空间不能满足分区所需要的空间时，可以对表空间进行扩容。

在此，使用增加数据文件的方式增大表空间。（oracle单个文件大小最大不超过32g）

关于表空间的详情可咨询：

https://www.cnblogs.com/yx007/p/6651919.html#_label1_2

https://www.cnblogs.com/kerrycode/p/4006840.html

查找数据文件指标及路径：

select b.file_id　　文件ID,

　　b.tablespace_name　　表空间,

　　b.file_name　　　　　物理文件名,

　　b.bytes　　　　　　　总字节数,

　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,

　　sum(nvl(a.bytes,0))　　　　　　　　剩余,

　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩余百分比

　　from dba_free_space a,dba_data_files b

　　where a.file_id=b.file_id

　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes

　　order by b.tablespace_name

我这里为表空间扩容是采取的增加数据文件的方法：

ALTER TABLESPACE HDGCDATA ADD DATAFILE

' /home/oracle/oracle18c/app/dbs/XXXX.DBF' 

size 7167M autoextend on ;

这里是为表空间HDGCDATA增加一个大小为7167M的名为 XXXX.DBF 的数据文件。其中具体路径最好与原来的数据文件相同。

到这里，权限也有了，空间也满足了，是时候开始正式进行表分区了。（就是前面的一些情况我之前没有考虑到，导致我在执行过程中出错，多走了十八条弯路。）

正式开始：

1. 检查原始表是否可以进行分区：

BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('C##LTS','RESULTS',DBMS_REDEFINITION.CONS_USE_PK);
END;

这一步是表分区的开始，用以检验带分区表是否满足分区的条件，如果不满足，将会提示相关错误，权限不足或者原始表没有主键等错误原因。（不包括表空间之类的）

其中 C##LTS 是用户名，RESULTS 是表名。

大家可能注意到我这里的写法与上面链接（将已存在的表进行表分区）的写法有所不同，是因为我在执行的过程中不知道哪里出了问题，使用 EXEC 来执行后面的语句总是提示不认识这个语句，后来查阅其他的博客时发现使用 BEGIN .. END 是同样的功能，所以我就放弃追寻答案了，有知道的同学请多多留言指导。

2.建立临时表：

这是非常重要的一步，有很多方面需要注意。

建立的临时表最好和原始表的建表结构相同，也可增加字段。

我们先来查看原表的建表结构:

select dbms_metadata.get_ddl('TABLE','RESULTS') from dual;

其中 RESULTS是原始表。运行这条代码之后，可能会得到一大串的代码，因为得到的不只是表的字段结构，还有原表的一些索引和触发器或者一些其他的东西。

我们先截取其中建表的部分出来，我的建表语句如下，大家可以参考：

CREATE TABLE "C##LTS"."TEMP_RESULTS" 
   (	"ID" NUMBER(20)NOT NULL, 
	"PLATENAME" NVARCHAR2(40), 
	"GASWELLNAME" NVARCHAR2(40), 
	"SAMPLETIME" DATE,  
	"ACCWATERPRODUCT" NUMBER(38,2))
partition by range (SAMPLETIME) interval (numtoyMinterval (3,'MONTH'))
(
PARTITION P1 VALUES LESS THAN (TO_DATE('2018-12-01 00:00:00', 'YYYY-MM-DD hh24-mi-ss')) TABLESPACE HDGCDATA,
PARTITION P2 VALUES LESS THAN (TO_DATE('2019-01-01 00:00:00', 'YYYY-MM-DD hh24-mi-ss')) TABLESPACE HDGCDATA,
PARTITION P3 VALUES LESS THAN (TO_DATE('2019-04-01 00:00:00', 'YYYY-MM-DD hh24-mi-ss')) TABLESPACE HDGCDATA
);

其中HDGCDATA是表空间名。

我这是时间范围分区的建表方法，同时是按季度自动增加表分区，详情请咨询：

https://www.cnblogs.com/weimengjiacan/p/8275023.html

但是注意，在这里建分区临时表的时候，最好不要设置主键。因为主键相当于是一条索引，存在索引会影响数据插入的速度，当你表的数据量比较大的时候，多一条索引，执行下面一条步骤的时间就会大大增加。

3.开始执行在线重定义：

BEGIN
DBMS_REDEFINITION.START_REDEF_TABLE('C##LTS','RESULTS','TEMP_RESULTS'); 
END;

其中 TEMP_RESULTS 是临时表。

这一步的执行时间会比较长，通俗讲这一步就是把原表的数据插入到新表里。像我知道张表44G，执行时间大约是2500s左右。

这个具体时间还要根据具体情况来。在这一步漫长的执行过程中，也可能会出现各种情况。

当在这一步之后的任何一步的执行过程中，都有可能执行过程出错，这是就需要我们中止步骤，重新开始执行在线重定义。

如果执行过程出错，释放快照的语句：

BEGIN
DBMS_REDEFINITION.ABORT_REDEF_TABLE('C##LTS','RESULTS','TEMP_RESULTS');
END;

我在执行过程中，可能是网络的问题（因为我是客户端，远程控制数据库），导致我这一步执行一直不结束，最长到20000秒。而且执行释放快照语句也不能成功，一直处在等待状态（释放快照只要几十秒），其实是因为这一步执行一直锁住了临时表，一直不会释放，要等到时间过长数据库自动释放（如果到了这一步，建议你第二天再来执行这一条吧！。。）。

当这一步执行成功时，恭喜你，离成功只有十万八千米了。

4. 给临时表添加与原表相同的依赖：

这个要按照不同的情况来，我亮亮我的：

增加主键

ALTER TABLE "C##LTS"."TEMP_RESULTS" ADD CONSTRAINT "SYS_C0048134" PRIMARY KEY ("ID");

这是给表添加主键，SYS_C0048134是主键索引的名字，这个我是直接通过Navicat Preminu直接导出原始表结构来的。

此时可以查看所有分区：

SELECT TABLE_NAME,PARTITION_NAME
FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='TEMP_RESULTS';

我们可以给表添加分区索引：

具体可以提前看看这：

https://blog.csdn.net/linux__xu/article/details/80957783

然后我的语句如下：

给表建立分区索引，分区索引的数量要与分区数相同

create index TEMP_RESULTS_MIX_INDEX on TEMP_RESULTS(SAMPLETIME,GASWELLNAME)
LOCAL
(
partition IDX_1 TABLESPACE HDGCDATA,
partition IDX_2 TABLESPACE HDGCDATA,
partition IDX_3 TABLESPACE HDGCDATA,
partition IDX_4 TABLESPACE HDGCDATA,
partition IDX_5 TABLESPACE HDGCDATA,
partition IDX_6 TABLESPACE HDGCDATA,
partition IDX_7 TABLESPACE HDGCDATA
)

查看表所有索引

select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and
t.table_name='TEMP_RESULTS'

接下来是创建触发器，或者其他的什么。。

比如创建触发器实现id自增

CREATE OR REPLACE TRIGGER TEMP_UPDATE_TRIGGER_ID BEFORE INSERT
ON TEMP_RESULTS
FOR EACH ROW
begin
    SELECT RESULTS_ID.nextval into :new.id from dual;
end;

这里我都是手动添加依赖，但是好像有能直接从原表复制全部依赖的方法，有些博客里面有些类似这样的代码：

DECLARE
   num_errors  PLS_INTEGER;
BEGIN
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS(
      ‘OXX_XXT_OWNER‘,
      ‘ID_OXXXDX_MX‘,
      ‘ID_OXXXDX_MX_TMP‘,
      DBMS_REDEFINITION.CONS_ORIG_PARAMS,
      TRUE,
      TRUE,
      TRUE,
      TRUE,
      num_errors);
END;

有些地方我不太懂，执行起来一直缺少权限，后来又看到这一步执行有风险，所以放弃了，改成了手动添加，这里有了解的大佬请多多指教。

如果你的原始表一直处在更新状态，一直有新的数据在往里添加，则需要执行下面一步。

5. 同步更新数据：

BEGIN
DBMS_REDEFINITION.sync_interim_table('C##LTS','RESULTS','TEMP_RESULTS');
END;

如果原始表新插入数据越多，那么这一步时间就会越长。但是执行这一步会大大减少下一步 完成在线重定义 的执行时间，这是非常重要的。

6. 完成在线重定义：

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('C##LTS','RESULTS','TEMP_RESULTS');
END;

这一步执行过程中，会锁住原始表，导致其他不能与原始表会话。所以第五步同步更新数据就会减少锁住表的时间。

如果在执行了同步更新数据之后立刻执行这一步，这一步也就会瞬间完成。

这一步执行成功，就说明你已经走完这十万八千里啦！恭喜！恭喜！

接下来我们来看看我们的成果：

SELECT TABLE_NAME,PARTITION_NAME
FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='RESULTS';

查看原始表，会发现已经变成了一张分区表了。

我们可以直接查某个分区下的数据：

SELECT DISTINCT SAMPLETIME FROM RESULTS PARTITION (SYS_P4260) WHERE SAMPLETIME>TO_DATE('2019-07-03 16:17:00','yyyy-mm-dd hh24:mi:ss')

为了完成这个表分区，我看了很多相关的资料博客，感谢各位博主的知识分享。我代码中有很多是直接摘的原博主的代码，由于查阅的太多我实在找不出来原作了，冒昧“盗用”代码我很抱歉，如有侵权行为，请跟我联系，侵权立删！

第一次写博客，很多不足还请各位大佬多多指导，你们的建议会成为我宝贵的经验。

前人栽树，我来乘凉~ 心机boy 在此谢过！

想要做个心机男

关注

2
点赞
踩
3

收藏

觉得还不错? 一键收藏
1
评论
Oracle 数据库在线重定义功能表分区以及分区前的准备工作

前段时间完成了对已存在的Oracle数据表进行分区。由于是第一次接触Oracle数据库表分区的概念，在网上查阅了大量的博客文章，但是感觉绝大部分文章博客的内容几乎雷同，而且在实际生产过程的应用当中，会出现各种“意外的情况”，很难找到一篇详细完整的关于表分区的博客，所以想将我此次作业的过程遇到的“意外的情况”记录下来，其实就是希望各位第一次接触表分区的同僚，在执行各种进行表分区的操作时提前做好完整的...
复制链接

扫一扫