间隔分区：每日一个示例

1

drop table ACCT;
CREATE TABLE ACCT
(
  user_id NUMBER,
  user_name VARCHAR2(20),
  insertdate DATE
)
tablespace users
PARTITION BY RANGE(insertdate)
INTERVAL (NUMTODSINTERVAL(1,'day')) STORE IN (users)
(
  PARTITION part20220820 VALUES LESS THAN(to_date('2022-08-21 00:00:00','yyyy-mm-dd hh24:mi:ss'))
);

insert into acct values (1,'aaa',to_date('2022-08-10','yyyy-mm-dd'));
insert into acct values (2,'bbb',to_date('2022-08-25','yyyy-mm-dd'));
insert into acct values (3,'ccc',to_date('2022-08-26','yyyy-mm-dd'));
insert into acct values (4,'ddd',to_date('2022-09-11','yyyy-mm-dd'));
commit;
select * from ACCT;

set lin 200
col TABLESPACE_NAME for a12
col table_name for a10
col PARTITION_NAME for a20
col HIGH_VALUE for a85
col PARTITION_POSITION for 999
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,PARTITION_POSITION,TABLESPACE_NAME,NUM_ROWS from DBA_tab_partitions where TABLE_NAME='ACCT';

如果想要改这些间隔分区的名称，可以尝试一下：

set serverout on

create FUNCTION DailyPartition(tableName IN VARCHAR2) RETURN BOOLEAN IS

    EXPRESSION_IS_OF_WRONG_TYPE EXCEPTION;
    PRAGMA EXCEPTION_INIT(EXPRESSION_IS_OF_WRONG_TYPE, -6550);

    ds INTERVAL DAY TO SECOND;
    ym INTERVAL YEAR TO MONTH;
    str VARCHAR2(1000);

BEGIN
    SELECT INTERVAL INTO str
    FROM USER_PART_TABLES
    WHERE TABLE_NAME = tableName;

    EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT ym;
    RETURN FALSE;
EXCEPTION
    WHEN EXPRESSION_IS_OF_WRONG_TYPE THEN
        EXECUTE IMMEDIATE 'BEGIN :ret := '||str||'; END;' USING OUT ds;
        RETURN TRUE;
END DailyPartition;
/



create PROCEDURE RenamePartitions IS

    ts TIMESTAMP;
    newName VARCHAR2(30);

    CURSOR TabPartitions IS
    SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE
    FROM USER_TAB_PARTITIONS
    WHERE TABLE_NAME IN ('ACCT', 'TEST') --这里改为你想要改分区名称的间隔分区表
        AND PARTITION_NAME <> 'P_INITIAL'
    ORDER BY 1,2;

BEGIN

    EXECUTE IMMEDIATE 'ALTER SESSION SET DDL_LOCK_TIMEOUT = 180';

    FOR aPart IN TabPartitions LOOP
        EXECUTE IMMEDIATE 'BEGIN :ret := '||aPart.HIGH_VALUE||'; END;' USING OUT ts;
        IF DailyPartition(aPart.TABLE_NAME) THEN
            ts := ts - INTERVAL '1' DAY;
            newName := 'P_'||TO_CHAR(ts,'yyyy_mm_dd');
        ELSE
            ts := ADD_MONTHS(ts, -1);
            newName := 'P_'||TO_CHAR(ts,'yyyy_mm');
        END IF;
        IF aPart.PARTITION_NAME <> newName THEN
            EXECUTE IMMEDIATE 'ALTER TABLE '||aPart.TABLE_NAME||' RENAME PARTITION '||aPart.PARTITION_NAME||' TO '||newName;
        END IF;
    END LOOP;

END RenamePartitions;
/

引自

https://stackoverflow.com/questions/41443756/oracle-automatic-partitioning-name-pattern-for-partitions

修改后效果如下：
看着清晰多了吧。