分区表常用操作汇总
1.创建分区表
1.1.普通分区表
/* Formatted on 2021-11-01 上午 10:39:35 (QP5 v5.163.1008.3004) */
CREATE TABLE MONKEY.TEST_PART_NULL_NORMAL
(
ID NUMBER,
ADD_DATE DATE
)
PARTITION BY RANGE (ADD_DATE)
(
PARTITION DCS_P202111 VALUES LESS THAN (TO_DATE (' 2021-12-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION DCS_P202112 VALUES LESS THAN (TO_DATE (' 2022-01-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION DCS_P202201 VALUES LESS THAN (TO_DATE (' 2022-02-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')),
PARTITION DCS_MAX VALUES LESS THAN (MAXVALUE)
) ENABLE ROW MOVEMENT;
1.2.自增长分区表
CREATE TABLE MONKEY.TEST_PART_NULL
(
ID NUMBER,
ADD_DATE DATE
)
PARTITION BY RANGE(ADD_DATE)
INTERVAL ( NUMTOYMINTERVAL (1, 'MONTH') )
(
PARTITION DCS_P202111 VALUES LESS THAN (TO_DATE (' 2021-12-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))
)
2.修改分区表默认分区表空间
2.1.分区表默认分区表空间修改
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL MODIFY DEFAULT ATTRIBUTES TABLESPACE MYTBS;
SELECT 'ALTER TABLE '
|| OWNER
|| '.'
|| TABLE_NAME
|| ' MODIFY DEFAULT ATTRIBUTES TABLESPACE MYTBS;'
FROM DBA_PART_TABLES
WHERE OWNER = 'MONKEY';
SELECT DISTINCT
'ALTER TABLE '
|| A.OWNER
|| '.'
|| A.SEGMENT_NAME
|| ' MODIFY DEFAULT ATTRIBUTES TABLESPACE XXXXXX;',
SUM (BYTES) / 1024 / 1024 / 1024
FROM DBA_SEGMENTS A,
(SELECT DISTINCT OWNER, TABLE_NAME
FROM DBA_PART_TABLES
WHERE DEF_TABLESPACE_NAME = 'XXXXXX') B
WHERE A.OWNER = B.OWNER AND A.SEGMENT_NAME = B.TABLE_NAME
GROUP BY A.OWNER, A.SEGMENT_NAME
ORDER BY SUM (BYTES) / 1024 / 1024 / 1024 DESC;
2.2.分区索引默认分区表空间修改
ALTER INDEX MONKEY.TEST_PART_NULL_NORMAL_ID MODIFY DEFAULT ATTRIBUTES TABLESPACE MYTBS;
SELECT 'ALTER INDEX '
|| OWNER
|| '.'
|| INDEX_NAME
|| ' MODIFY DEFAULT ATTRIBUTES TABLESPACE MYTBS;'
FROM DBA_PART_INDEXES
WHERE OWNER = 'MONKEY';
3.分区表MOVE分区
3.1.MOVE分区表分区
MOVE分区表，原理是把记录插入另一个表空间，因此如果表有OGG，需要提前排除。
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL MOVE PARTITION DCS_P202111 TABLESPACE MYTBS;
3.2.MOVE分区索引分区
ALTER INDEX MONKEY.TEST_PART_NULL_NORMAL_ID REBUILD PARTITION DCS_P202111 TABLESPACE MYTBS;
4.分区表新增分区
4.1.不带MAX新增分区
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL ADD PARTITION DCS_P202202 VALUES LESS THAN (TO_DATE (' 2022-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'));
4.2.新增MAX分区
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL ADD PARTITION DCS_MAX VALUES LESS THAN (MAXVALUE);
4.3.带MAX新增分区
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL SPLIT PARTITION DCS_MAX AT ( (TO_DATE(' 2022-03-01 00:00:00','SYYYY-MM-DD HH24:MI:SS','NLS_CALENDAR=GREGORIAN'))) INTO ( PARTITION DCS_P202202 TABLESPACE USERS , PARTITION DCS_MAX TABLESPACE USERS);
新增分区最后可以加上UPDATE GLOBAL INDEXES，自动维护全局索引。
4.4.新增分区生成脚本
/* Formatted on 2021-12-12 上午 09:22:42 (QP5 v5.163.1008.3004) */
DECLARE
CUR_DATE DATE;
MAX_PARTITON_ROWNUM NUMBER;
V_SQL VARCHAR2 (2000);
V_PAR_NAME VARCHAR2 (2000);
V_FLAG VARCHAR2 (20);
V_NEXT_TIME VARCHAR2 (2000);
V_NEXT_PARTNAME VARCHAR2 (2000);
V_SPLIT_SQL VARCHAR2 (2000);
V_NEWPART_EXISTORNOT VARCHAR2 (200);
V_OWNER VARCHAR2 (200) := 'XXXXXX'; ------------把個用戶名改成你要作業的
v_mm NUMBER;
BEGIN
FOR R IN (SELECT OWNER, TABLE_NAME
FROM DBA_PART_TABLES
WHERE OWNER = V_OWNER AND INTERVAL IS NULL -------11G以上不註釋,10G及以下註釋掉
--AND table_name = 'XXXXXX' --------單表腳本產生
)
LOOP
V_FLAG := NULL;
FOR M IN (SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
V_DATE,
HIGH_VALUE,
V_ORDER
FROM (SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
V_DATE,
HIGH_VALUE,
ROW_NUMBER ()
OVER (PARTITION BY OWNER, SEGMENT_NAME
ORDER BY HIGH_VALUE DESC)
V_ORDER
FROM (SELECT *
FROM (WITH xml
AS (SELECT XMLTYPE (
DBMS_XMLGEN.GETXML (
'SELECT * FROM DBA_TAB_PARTITIONS WHERE table_OWNER='''
|| R.OWNER
|| ''' and '
|| 'table_name='''
|| R.TABLE_NAME
|| ''''))
AS xml
FROM DUAL),
parsed_xml
AS (SELECT EXTRACTVALUE (
xs.object_value,
'/ROW/TABLE_OWNER')
AS owner,
EXTRACTVALUE (
xs.object_value,
'/ROW/TABLE_NAME')
AS segment_name,
EXTRACTVALUE (
xs.object_value,
'/ROW/PARTITION_NAME')
AS PARTITION_NAME,
EXTRACTVALUE (
xs.object_value,
'/ROW/HIGH_VALUE')
AS HIGH_VALUE
FROM xml x,
TABLE (
XMLSEQUENCE (
EXTRACT (
x.xml,
'/ROWSET/ROW'))) xs)
SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
TO_DATE (
SUBSTR (HIGH_VALUE, 11, 19),
'yyyy/mm/dd hh24:mi:ss')
v_date,
HIGH_VALUE
FROM parsed_xml
WHERE OWNER = V_OWNER)))
WHERE HIGH_VALUE = 'MAXVALUE')
LOOP
V_FLAG := 'HAVE MAX';
V_SQL :=
'SELECT /*+ full(a) */ COUNT(*) FROM '
|| M.OWNER
|| '.'
|| M.SEGMENT_NAME
|| ' PARTITION('
|| M.PARTITION_NAME
|| ') a';
-- DBMS_OUTPUT.PUT_LINE (V_SQL);
EXECUTE IMMEDIATE V_SQL INTO MAX_PARTITON_ROWNUM;
IF MAX_PARTITON_ROWNUM = 0
THEN
FOR P IN (SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
V_DATE,
HIGH_VALUE,
V_ORDER
FROM (SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
V_DATE,
HIGH_VALUE,
ROW_NUMBER ()
OVER (PARTITION BY OWNER, SEGMENT_NAME
ORDER BY HIGH_VALUE DESC)
V_ORDER
FROM (SELECT *
FROM (WITH xml
AS (SELECT XMLTYPE (
DBMS_XMLGEN.GETXML (
'SELECT * FROM DBA_TAB_PARTITIONS WHERE table_OWNER='''
|| R.OWNER
|| ''' and '
|| 'table_name='''
|| R.TABLE_NAME
|| ''''))
AS xml
FROM DUAL),
parsed_xml
AS (SELECT EXTRACTVALUE (
xs.object_value,
'/ROW/TABLE_OWNER')
AS owner,
EXTRACTVALUE (
xs.object_value,
'/ROW/TABLE_NAME')
AS segment_name,
EXTRACTVALUE (
xs.object_value,
'/ROW/PARTITION_NAME')
AS PARTITION_NAME,
EXTRACTVALUE (
xs.object_value,
'/ROW/HIGH_VALUE')
AS HIGH_VALUE
FROM xml x,
TABLE (
XMLSEQUENCE (
EXTRACT (
x.xml,
'/ROWSET/ROW'))) xs)
SELECT OWNER,
SEGMENT_NAME,
PARTITION_NAME,
TO_DATE (
SUBSTR (HIGH_VALUE,
11,
19),
'yyyy/mm/dd hh24:mi:ss')
v_date,
HIGH_VALUE
FROM parsed_xml
WHERE OWNER = V_OWNER)))
WHERE V_ORDER = 1)
LOOP
IF P.V_DATE IS NULL
THEN
DBMS_OUTPUT.PUT_LINE (
'--'
|| P.OWNER
|| '.'
|| P.SEGMENT_NAME
|| ' LAST PARTITION ERROR PLEASE CONFIRM');
ELSIF P.V_DATE < TO_DATE ('20211101', 'YYYYMMDD') ---------当前开始作业的时间
THEN
DBMS_OUTPUT.PUT_LINE (
'--'
|| P.OWNER
|| '.'
|| P.SEGMENT_NAME
|| ' THE LAST PARTITION IS LESS THAN THE CURRENT TIME');
ELSE
CUR_DATE := P.V_DATE;
WHILE CUR_DATE < TO_DATE ('20230401', 'YYYYMMDD') ---------SPLIT到的最大分區時間
LOOP
SELECT ADD_MONTHS (CUR_DATE, 1) INTO CUR_DATE FROM DUAL;
SELECT TO_CHAR (ADD_MONTHS (CUR_DATE, -1), 'YYYYMMDD')
INTO V_PAR_NAME
FROM DUAL;
--DBMS_OUTPUT.PUT_LINE('DCS_P'||V_PAR_NAME);
SELECT TO_NUMBER (
DECODE (
TO_NUMBER (SUBSTR (V_PAR_NAME, 5, 2)) - 1,
0, 1,
TO_NUMBER (SUBSTR (V_PAR_NAME, 5, 2))))
INTO v_mm
FROM DUAL;
V_NEXT_PARTNAME :=
'DCS_P'
|| SUBSTR (V_PAR_NAME, 1, 4)
|| ''
|| LPAD (v_mm, 2, '0');
SELECT COUNT (*)
INTO V_NEWPART_EXISTORNOT
FROM DBA_TAB_PARTITIONS
WHERE TABLE_OWNER = P.OWNER
AND TABLE_NAME = P.SEGMENT_NAME
AND PARTITION_NAME = V_NEXT_PARTNAME;
SELECT TO_CHAR (CUR_DATE, 'YYYYMMDD')
INTO V_PAR_NAME
FROM DUAL;
IF V_NEWPART_EXISTORNOT = 0
THEN
V_NEXT_TIME :=
'TO_DATE('' '
|| SUBSTR (V_PAR_NAME, 1, 4)
|| '-'
|| SUBSTR (V_PAR_NAME, 5, 2)
|| '-'
|| SUBSTR (V_PAR_NAME, 7, 2)
|| ' 00:00:00'', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')';
V_SPLIT_SQL :=
'alter table '
|| P.OWNER
|| '.'
|| P.SEGMENT_NAME
|| ' split partition '
|| M.PARTITION_NAME
|| ' AT ('
|| V_NEXT_TIME
|| ') INTO ( PARTITION '
|| V_NEXT_PARTNAME
|| ' , PARTITION '
|| M.PARTITION_NAME
|| ');';
DBMS_OUTPUT.PUT_LINE (V_SPLIT_SQL);
ELSE
DBMS_OUTPUT.PUT_LINE (
'--'
|| P.OWNER
|| '.'
|| P.SEGMENT_NAME
|| 'NEW PARTITION_NAME <'
|| V_NEXT_PARTNAME
|| '> ERROR,THIS IS EXISTS,PLEASE CONFIRM');
END IF;
END LOOP;
END IF;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE (
'--'
|| M.OWNER
|| '.'
|| M.SEGMENT_NAME
|| ' MAX PARTITION have data,please split by manually');
END IF;
END LOOP;
IF V_FLAG IS NULL
THEN
DBMS_OUTPUT.PUT_LINE (
'--'
|| R.OWNER
|| '.'
|| R.TABLE_NAME
|| ' DO NOT HAVE MAXVALUE PARTITION');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE || SQLERRM);
END;
/
5.分区表删除分区
5.1.删除普通分区和MAX分区
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL DROP PARTITION DCS_P202204;
ALTER TABLE MONKEY.TEST_PART_NULL_NORMAL DROP PARTITION DCS_MAX;
删除分区最后可以加上UPDATE GLOBAL INDEXES，自动维护全局索引。
6.修改分区表分区时间间隔
-- 按天该为按月
ALTER TABLE MONKEY.T_T SET INTERVAL ();
ALTER TABLE t_t ADD PARTITION DCS_202309 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD'));
ALTER TABLE T_T SET INTERVAL (NUMTOYMINTERVAL (1, 'MONTH'));
-- 按月改为按每七天
ALTER TABLE MONKEY.T_T SET INTERVAL ();
ALTER TABLE T_T SET INTERVAL (NUMTODSINTERVAL(7, 'DAY'));
7.分区表索引重建
ALTER INDEX MONKEY.IX_MONKEY_ID REBUILD PARTITION DCS_P202310 TABLESPACE MYTBS online parallel 6;
ALTER INDEX MONKEY.IX_MONKEY_ID
NOPARALLEL;
