AI 助理
备案控制台
开发者社区 数据库 文章 正文

Oracle 通过脚本一键生成按月分区表

2021-12-12 684
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Oracle 通过脚本一键生成按月分区表

前言


分区表作为Oracle三大组件之一，在Oracle数据库中，起着至关重要的作用。


分区表有什么优点？


  • 普通表转分区表：应用程序无感知，DML 语句无需修改即可访问分区表。
  • 高可用性：部分分区不可用不影响整个分区表使用。
  • 方便管理：可以单独对分区进行DDL操作，列入重建索引或扩展分区，不影响分区表的使用。
  • 减少OLTP系统资源争用：因为DML分布在很多段上进行操作。
  • 增强数据仓库的查询性能：可按时间进行分区查询，加速查询。


在运维开发过程中，发现有部分应用厂商在建表之初并未考虑到数据体量的问题，导致很多大表都没有建成分区表。在系统运行过程中，这些表的数据量一直在增大，当达到一定体量时，我们就需要考虑对其进行分区表转换，以提高数据库的性能。那么，如何操作呢？


一、介绍


普通表转换为分区表，Oracle给我们提供了哪些方式呢？


  • 数据泵导入
  • 子查询方式插入
  • 分区交换
  • 在线重定义
  • ALTER TABLE…MODIFY…方式（12.2之后支持）


以上几种方式中，我比较常用的是：数据泵导入，子查询插入，在线重定义。这三种方式的共同点都是 需要提前创建分区表结构的中间表或者目标表。

二、脚本


在长时间的重复性工作中，“懒癌”发作的我就想着是否能通过自动化的方式构建分区表的建表语句呢？然后我发现了 梁敬彬大佬的 普通表自动转化为按月分区表的脚本。


经加工和提炼，将以上脚本修改为契合自己使用的脚本：


  • 用于生成CTAS完整分区表建表语句：ctas_par.prc
  • 用于CTAS直接转换为分区表：par_tab_deal.pkg点我跳转文末 可以获取 分区表脚本 以及博主的联系方式


par_tab_deal.pkg 的使用方式为：

--创建日志表 PART_TAB_LOG
create table PART_TAB_LOG
(
 TAB_NAME     VARCHAR2(200),
 DEAL_MODEL   VARCHAR2(200),
 SQL_TEXT     clob,
 DEAL_TIME    DATE,
 remark       VARCHAR2(4000),
 exec_order1  number,
 exec_order2  number
);
--执行分区表转换
BEGIN
  pkg_deal_part_tab.p_main(p_tab            => 't1',
                           p_deal_flag      => 1,
                           p_parallel       => 8,
                           p_part_colum     => 'created_date',
                           p_part_nums      => 24,
                           p_tab_tablespace => 'users',
                           p_idx_tablespace => 'users');
END;
--查看日志
select DBMS_LOB.SUBSTR(sql_text,1000)||';' from part_tab_log t where tab_name='T1' order by exec_order1,exec_order2;
select sql_text||';' from part_tab_log t where tab_name='T1' order by exec_order1,exec_order2;


ctas_par.prc 的使用方式：

--直接执行输出即可
BEGIN
  ctas_par(p_tab        => 't1',
           p_part_colum => 'created_date',
           p_part_nums  => 24,
           p_tablespace => 'users');
END;


三、实战应用


1、创建测试表T1

由于实验需要一张基础表，因此通过下方步骤创建表 T1：

--删除t1表
DROP TABLE t1 PURGE;
--创建t1表
CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id)
);
--创建索引
CREATE INDEX t1_created_date_idx ON t1(created_date);
--插入数据
INSERT INTO t1
SELECT level,
       'Description for ' || level,
       ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)
FROM   dual
CONNECT BY level <= 10000;
COMMIT;


2、创建procedure


执行脚本创建procedure：

image.png

image.png

select  'alter  '||object_type||'   '||owner||'.'||object_name||'   compile;'
from  dba_objects t
where t.object_type='PROCEDURE'
and t.object_name='CTAS_PAR' ;

image.png


📢 注意：确认procedure已成功创建。


3 执行procedure

执行procedure生成CTAS创建分区表语句：


  • 表名：T1
  • 分区键：CREATED_DATE
  • 建立分区月数：24
  • 分区所在表空间：USERS
alter session set nls_date_format="yyyy-mm-dd hh24:mi:ss";
BEGIN
  ctas_par(p_tab        => 'T1',
           p_part_colum => 'CREATED_DATE',
           p_part_nums  => 24,
           p_tablespace => 'USERS');
END;


执行脚本如下：

--分区表获取分区列最小记录日期：2015-01-01 00:00:00
--分区表ctas创建的完整语句如下： 
create table T1
partition BY RANGE(CREATED_DATE)(
partition T1_P201501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201507 values less than (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201508 values less than (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201509 values less than (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201510 values less than (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201511 values less than (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201512 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201601 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201602 values less than (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201603 values less than (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201604 values less than (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201605 values less than (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201606 values less than (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201607 values less than (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201611 values less than (TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201612 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_MAX values less than (maxvalue) tablespace USERS)
             nologging
             parallel 4
             enable row movement
             tablespace USERS
             as select /*+parallel(t,8)*/ * from T1_01 t where 1 = 2;
--附加日志和取消并行
alter table T1 logging;
alter table T1 noparallel;


如果只是需要分区表的建表语句，这里已经可以很简单的拼接出来：

create table T1
(
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE
)
partition BY RANGE(CREATED_DATE)(
partition T1_P201501 values less than (TO_DATE(' 2015-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201502 values less than (TO_DATE(' 2015-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201503 values less than (TO_DATE(' 2015-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201504 values less than (TO_DATE(' 2015-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201505 values less than (TO_DATE(' 2015-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201506 values less than (TO_DATE(' 2015-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201507 values less than (TO_DATE(' 2015-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201508 values less than (TO_DATE(' 2015-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201509 values less than (TO_DATE(' 2015-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201510 values less than (TO_DATE(' 2015-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201511 values less than (TO_DATE(' 2015-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201512 values less than (TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201601 values less than (TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201602 values less than (TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201603 values less than (TO_DATE(' 2016-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201604 values less than (TO_DATE(' 2016-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201605 values less than (TO_DATE(' 2016-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201606 values less than (TO_DATE(' 2016-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201607 values less than (TO_DATE(' 2016-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201608 values less than (TO_DATE(' 2016-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201609 values less than (TO_DATE(' 2016-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201610 values less than (TO_DATE(' 2016-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201611 values less than (TO_DATE(' 2016-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_P201612 values less than (TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace USERS,
partition T1_MAX values less than (maxvalue) tablespace USERS)
ENABLE ROW MOVEMENT
TABLESPACE USERS;


4、CTAS创建分区表

实际执行前，需要先将原表T1进行 rename 操作：

alter table lucifer.T1 RENAME TO T1_01;

image.png


📢 注意： 如需创建分区表结构，无需修改以上脚本；如需直接创建分区表包含数据，需要将 where 1 = 2 修改为 where 1 = 1。


确保当前表T1已经rename为T1_01，执行CTAS创建分区表：

image.png

查看分区表结构：

image.png

📢 注意： 由于CTAS不会继承 注释，默认值，因此需要手动比对是否缺失。

COMMENT ON TABLE T1 IS '';
COMMENT ON COLUMN T1.ID IS '';
COMMENT ON COLUMN T1.CREATED_DATE IS '';
COMMENT ON COLUMN T1.DESCRIPTION IS '';


重命名原表的索引和约束：

--重命名索引
ALTER INDEX T1_CREATED_DATE_IDX RENAME TO T1_CREATED_DATE_IDX_01;
ALTER INDEX T1_PK RENAME TO T1_PK_01;
--重命名唯一约束
ALTER TABLE T1_01 RENAME CONSTRAINT T1_PK TO T1_PK_01;

image.png


分区表新建本地索引：

create index T1_CREATED_DATE_IDX on T1 (CREATED_DATE) tablespace users;
alter table T1 add constraint T1_PK primary key (ID) using index  tablespace users;


查询分区表：


通过以下查询可以发现，数据已被按月分到对应分区下。

SELECT COUNT(1) FROM t1;
SELECT COUNT(1) FROM t1 PARTITION(T1_P201501);
SELECT COUNT(1) FROM t1 PARTITION(T1_P201601);
SELECT COUNT(1) FROM t1 PARTITION(T1_MAX);


image.png

至此，脚本已经介绍完毕。


文章标签：
SQL
OLTP
关系型数据库
索引
运维
Oracle
数据库
关键词：
Oracle数据库一键上云脚本
Oracle数据库一键上云分区表
Lucifer三思而后行
目录
相关文章
dreaming317
|
存储 SQL Oracle
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
Oracle数据库批量删除表、视图、序列、存储过程、函数脚本
dreaming317
144 0
pbeskoyq7ffm4
|
5月前
|
机器学习/深度学习 Oracle 关系型数据库
Oracle 19c单机一键安装脚本分享
Oracle 19c单机一键安装脚本分享
pbeskoyq7ffm4
258 2
以山向海
|
6月前
|
Oracle 安全 关系型数据库
关系型数据库Oracle执行RMAN脚本权限验证
【7月更文挑战第24天】
以山向海
122 10
以山向海
|
6月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle执行RMAN脚本环境检查
【7月更文挑战第24天】
以山向海
114 4
以山向海
|
6月前
|
存储 Oracle 关系型数据库
关系型数据库Oracle运行RMAN脚本
【7月更文挑战第23天】
以山向海
58 4
以山向海
|
6月前
|
监控 Oracle 算法
关系型数据库Oracle执行RMAN脚本注意事项
【7月更文挑战第23天】
以山向海
75 3
pbeskoyq7ffm4
|
5月前
|
Oracle 关系型数据库 数据库
Oracle数据库备份脚本分享-Python
Oracle数据库备份脚本分享-Python
pbeskoyq7ffm4
139 0
pbeskoyq7ffm4
|
5月前
|
Oracle 安全 关系型数据库
Oracle安装部署再也不用头疼了，分享一个实用的一键部署脚本，建议收藏！
Oracle安装部署再也不用头疼了，分享一个实用的一键部署脚本，建议收藏！
pbeskoyq7ffm4
179 0
以山向海
|
6月前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle编写RMAN脚本
【7月更文挑战第23天】
以山向海
48 2
以山向海
|
6月前
|
Oracle 关系型数据库 数据库
关系型数据库Oracle执行RMAN脚本
【7月更文挑战第22天】
以山向海
100 2

热门文章

最新文章

  • 1
    数据库数据恢复—ORACLE常见故障的数据恢复方案
  • 2
    Oracle数据恢复—Oracle数据库文件有坏快损坏的数据恢复案例
  • 3
    Oracle 从 DMP 文件中恢复指定表的步骤
  • 4
    从ORACLE源进行批量数据迁移到GBase8a参考示例
  • 5
    服务器数据恢复—华为S5300存储Oracle数据库恢复案例
  • 6
    企业的建库脚本（oracle10g）
  • 7
    ORACLE 9i归档之ARCn: Media recovery disabled
  • 8
    Aix 6.1下安装Oracle11g详细文档
  • 9
    Oracle提供两种使用嵌套表
  • 10
    oracle 分页sql 分段查数据和分段求和 sql语句 和java代码
  • 1
    oracle 19c 密码字段
    72
  • 2
    实时计算 Flink版操作报错合集之在连接Oracle 19c时报错如何解决
    138
  • 3
    实时计算 Flink版操作报错合集之flink cdc xstream采集oracle报错如何解决
    91
  • 4
    实时计算 Flink版操作报错合集之写Oracle时出现的缺失等号错误如何解决
    94
  • 5
    实时计算 Flink版操作报错合集之采集oracle的时候报ORA-65040:不允许从可插入数据库内部执行该操作如何解决
    310
  • 6
    实时计算 Flink版产品使用合集之可以通过配置Oracle数据库的schema注册表来监测表结构的变化吗
    62
  • 7
    Oracle 部署及基础使用，字节跳动资深面试官亲述
    63
  • 8
    Oracle WebLogic Server远程代码执行漏洞 CVE-2020-14750 已亲自复现_cve-2020-14750漏洞复现
    186
  • 9
    Oracle dataguard 容灾技术实战（笔记），教你一种更清晰的Linux运维架构
    135
  • 10
    实时计算 Flink版产品使用合集之在读取Oracle归档日志时出现日志数量大幅增加的情况如何解决
    120

    • 相关课程

    更多
  • 数据库及SQL/MySQL基础

    • 相关电子书

    更多
  • PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践
  • PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移
  • Oracle云上最佳实践

    • 相关实验场景

    更多
  • SQLServer数据库的创建

    • 推荐镜像

    更多
  • oracle
  • mysql
  • postgresql
    • 下一篇
    DataWorks智能交互式数据开发与分析之旅