【原】Oracle 通過腳本一鍵生成按月分區(qū)表

LuciferLiu 2021-12-10

展開全文

前言

分區(qū)表作為Oracle三大組件之一，在Oracle數(shù)據(jù)庫中，起著至關(guān)重要的作用。

分區(qū)表有什么優(yōu)點？

普通表轉(zhuǎn)分區(qū)表：應用程序無感知，DML 語句無需修改即可訪問分區(qū)表。
高可用性：部分分區(qū)不可用不影響整個分區(qū)表使用。
方便管理：可以單獨對分區(qū)進行DDL操作，列入重建索引或擴展分區(qū)，不影響分區(qū)表的使用。
減少OLTP系統(tǒng)資源爭用：因為DML分布在很多段上進行操作。
增強數(shù)據(jù)倉庫的查詢性能：可按時間進行分區(qū)查詢，加速查詢。

在運維開發(fā)過程中，發(fā)現(xiàn)有部分應用廠商在建表之初并未考慮到數(shù)據(jù)體量的問題，導致很多大表都沒有建成分區(qū)表。在系統(tǒng)運行過程中，這些表的數(shù)據(jù)量一直在增大，當達到一定體量時，我們就需要考慮對其進行分區(qū)表轉(zhuǎn)換，以提高數(shù)據(jù)庫的性能。那么，如何操作呢？

一、介紹

普通表轉(zhuǎn)換為分區(qū)表，Oracle給我們提供了哪些方式呢？

數(shù)據(jù)泵導入
子查詢方式插入
分區(qū)交換
在線重定義
ALTER TABLE…MODIFY…方式（12.2之后支持）

以上幾種方式中，我比較常用的是：數(shù)據(jù)泵導入，子查詢插入，在線重定義。這三種方式的共同點都是 需要提前創(chuàng)建分區(qū)表結(jié)構(gòu)的中間表或者目標表。

二、腳本

在長時間的重復性工作中，“懶癌”發(fā)作的我就想著是否能通過自動化的方式構(gòu)建分區(qū)表的建表語句呢？然后我發(fā)現(xiàn)了 梁敬彬大佬的普通表自動轉(zhuǎn)化為按月分區(qū)表的腳本。

經(jīng)加工和提煉，將以上腳本修改為契合自己使用的腳本：

用于生成CTAS完整分區(qū)表建表語句：ctas_par.prc
用于CTAS直接轉(zhuǎn)換為分區(qū)表：par_tab_deal.pkg

可以獲取 分區(qū)表腳本 以及博主的聯(lián)系方式

par_tab_deal.pkg 的使用方式為：

--創(chuàng)建日志表 PART_TAB_LOGcreate 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);--執(zhí)行分區(qū)表轉(zhuǎn)換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 的使用方式：

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

三、實戰(zhàn)應用

1、創(chuàng)建測試表T1

由于實驗需要一張基礎(chǔ)表，因此通過下方步驟創(chuàng)建表 T1：

--刪除t1表DROP TABLE t1 PURGE;--創(chuàng)建t1表CREATE TABLE t1 (
  id           NUMBER,
  description  VARCHAR2(50),
  created_date DATE,
  CONSTRAINT t1_pk PRIMARY KEY (id));--創(chuàng)建索引CREATE INDEX t1_created_date_idx ON t1(created_date);--插入數(shù)據(jù)INSERT INTO t1SELECT level,   'Description for ' || level,   ADD_MONTHS(TO_DATE('01-JAN-2017', 'DD-MON-YYYY'), -TRUNC(DBMS_RANDOM.value(1,4)-1)*12)FROM   dualCONNECT BY level <= 10000;COMMIT;

2、創(chuàng)建procedure

執(zhí)行腳本創(chuàng)建procedure：

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

?? 注意：確認procedure已成功創(chuàng)建。

3 執(zhí)行procedure

執(zhí)行procedure生成CTAS創(chuàng)建分區(qū)表語句：

表名：T1
分區(qū)鍵：CREATED_DATE
建立分區(qū)月數(shù)：24
分區(qū)所在表空間：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;

執(zhí)行腳本如下：

--分區(qū)表獲取分區(qū)列最小記錄日期：2015-01-01 00:00:00--分區(qū)表ctas創(chuàng)建的完整語句如下： create table T1partition 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;

如果只是需要分區(qū)表的建表語句，這里已經(jīng)可以很簡單的拼接出來：

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 MOVEMENTTABLESPACE USERS;

4、CTAS創(chuàng)建分區(qū)表

實際執(zhí)行前，需要先將原表T1進行 rename 操作：

alter table lucifer.T1 RENAME TO T1_01;

rename原表T1
?? 注意： 如需創(chuàng)建分區(qū)表結(jié)構(gòu)，無需修改以上腳本；如需直接創(chuàng)建分區(qū)表包含數(shù)據(jù)，需要將 where 1 = 2 修改為 where 1 = 1。

確保當前表T1已經(jīng)rename為T1_01，執(zhí)行CTAS創(chuàng)建分區(qū)表：

查看分區(qū)表結(jié)構(gòu)：

?? 注意： 由于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;

重命名原表索引
分區(qū)表新建本地索引：

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;

查詢分區(qū)表：

通過以下查詢可以發(fā)現(xiàn)，數(shù)據(jù)已被按月分到對應分區(qū)下。

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);

至此，腳本已經(jīng)介紹完畢。