晴风

创建连续日期分区

-- ========================================
-- Author name: 晴风
-- Create date: 2022-06-23 14:41:28

-- 批量创建连续日期分区,再DM_ODS空间内可直接调用,其它空间内需要加空间前缀
-- TABLE_OWNER:表空间
-- TABLE_NAME:表名
-- START_DATE:开始日期,包含
-- END_DATE:结束日期,包含
-- ========================================
-- DROP PROCEDURE CREATE_CD_PT;
CREATE OR REPLACE PROCEDURE CREATE_CD_PT(TABLE_OWNER IN VARCHAR,TABLE_NAME IN VARCHAR, START_DATE DATE ,END_DATE IN DATE)
AS SQL VARCHAR(200);
DATE_STR VARCHAR(50);
BEGIN
    WHILE START_DATE<=END_DATE LOOP
        DATE_STR :=  DATE_FORMAT(START_DATE,'yyyyMMdd');
        SQL := 'ALTER TABLE ' ||  TABLE_OWNER || '.' || TABLE_NAME || ' ADD PARTITION  P' || DATE_STR || ' VALUES(''' ||  DATE_STR || ''');';
        EXECUTE IMMEDIATE SQL;
        SET START_DATE = ADD_DAYS(START_DATE,1);
    END LOOP;
END;
-- 表空间,表名,开始日期,结束日期
DM_ODS.CREATE_CD_PT('DM_ODS','test_20220526_pt',TO_DATE('20220501'),TO_DATE('20220630'));

-- 查询所有分区
SELECT
PARTITION_NAME 
,HIGH_VALUE
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME = UPPER('test_20220526_pt')
ORDER BY REGEXP_REPLACE(PARTITION_NAME,'^[pP]','') DESC

表占用空间大小统计

-- DROP TABLE TABLE_OCCUPANCY_SIZE;
-- CREATE TABLE IF NOT EXISTS TABLE_OCCUPANCY_SIZE(
-- OWNER varchar(50),
-- TABLE_NAME varchar(100),
-- SIZE_MB bigint,
-- SIZE_GB NUMERIC(20,2)
-- );
-- COMMENT ON TABLE TABLE_OCCUPANCY_SIZE IS '表占用空间大小统计'; 
-- COMMENT ON COLUMN TABLE_OCCUPANCY_SIZE.OWNER IS 'OWNER'; 
-- COMMENT ON COLUMN TABLE_OCCUPANCY_SIZE.TABLE_NAME IS '表名';
-- COMMENT ON COLUMN TABLE_OCCUPANCY_SIZE.SIZE_MB IS '大小(MB)';
-- COMMENT ON COLUMN TABLE_OCCUPANCY_SIZE.SIZE_GB IS '大小(GB)';

-- 开始计算
Declare
    -- 定义游标
    Cursor myCur is  SELECT OWNER,TABLE_NAME FROM DBA_TAB_comments WHERE TABLE_TYPE='TABLE' AND OWNER IN ('DM_ODS','DM_STD','DM_DWS','SCRK_DM','YCZC_DM');
Begin
    -- 清空目标表
    EXECUTE IMMEDIATE 'TRUNCATE TABLE TABLE_OCCUPANCY_SIZE';
    -- 遍历游标
    for varA in myCur
    loop
        -- 插入到结果表
        INSERT INTO TABLE_OCCUPANCY_SIZE
        SELECT varA.OWNER,varA.TABLE_NAME
        ,TABLE_USED_SPACE(varA.OWNER, varA.TABLE_NAME) * PARA_VALUE / 1024 / 1024 SIZE_MB 
        ,TABLE_USED_SPACE(varA.OWNER, varA.TABLE_NAME) * PARA_VALUE / 1024 / 1024 / 1024  SIZE_GB 
        FROM V$DM_INI WHERE PARA_NAME LIKE 'GLOBAL_PAGE_SIZE';
    end loop;
End;

-- SELECT * FROM DM_STD.TABLE_OCCUPANCY_SIZE ORDER BY SIZE_MB DESC;

清空或删除表分区,起始日期为时间节点

-- ========================================
-- Author name: 晴风
-- Create date: 2022-07-14 14:29:58
-- 清空或删除表分区,起始日期为时间节点
-- OPERATION:DROP或TRUNCATE
-- TABLE_NAME:表名,不能带前缀,请到对应表空间下执行
-- START_DATE:起始日期，小于等于该日期的分区会被删除
-- ========================================


-- DROP PROCEDURE CLEAR_CD_PT;
CREATE OR REPLACE PROCEDURE CLEAR_CD_PT(OPERATION IN VARCHAR,T_NAME IN VARCHAR, START_DATE DATE)
AS 
  SQL VARCHAR(200);
  CURSOR MYCUR IS (
   SELECT PARTITION_NAME FROM DBA_TAB_PARTITIONS
   WHERE TABLE_NAME = UPPER(T_NAME) AND LENGTH(PARTITION_NAME)=9 AND SUBSTRING(PARTITION_NAME,2)<=TO_CHAR(START_DATE,'YYYYMMDD')
   ORDER BY SUBSTRING(PARTITION_NAME,2) DESC
  );
BEGIN
    FOR VARA IN MYCUR
    LOOP
      SQL := 'ALTER TABLE ' || T_NAME || ' ' || OPERATION || ' PARTITION '|| VARA.PARTITION_NAME;
      EXECUTE IMMEDIATE SQL; 
    END LOOP;
END;
-- 操作,表名,起始日期
DM_ODS.CLEAR_CD_PT('TRUNCATE','test_20220714_pt',TO_DATE('20220612'));