间隔分区：每日一个示例-凯发app官方网站

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

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

阅读(407) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们