oracle 创建分区表-每10天一个分区

最新推荐文章于 2023-09-09 08:00:00 发布

ksf泡面

最新推荐文章于 2023-09-09 08:00:00 发布

阅读量583

点赞数

分类专栏： Oracle 分区表文章标签： oracle

本文链接：https://blog.csdn.net/u013737399/article/details/125562460

版权

Oracle 分区表专栏收录该内容

5 篇文章 0 订阅

订阅专栏

oracle 创建分区表-每10天一个分区

create table test_part
(
  ID NUMBER(20) not null,
  REMARK VARCHAR2(1000),
  create_time DATE
)
PARTITION BY RANGE (CREATE_TIME) INTERVAL (NUMTODSINTERVAL(10, 'day'))
(partition part_t01 values less than(to_date('2022-06-30', 'yyyy-mm-dd')));

insert into test_part values(1,'a',to_date('2022-06-24 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-06-25 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-06-26 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-06-27 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-06-28 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-06-29 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-06-30 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-01 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-02 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-03 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-04 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-05 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-06 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-07 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-08 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-09 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-10 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-11 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-12 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-13 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-14 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-15 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-16 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-17 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-18 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-19 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-20 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-21 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-22 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-23 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-24 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-25 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-26 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-27 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-28 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-29 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-30 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-07-31 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-08-01 12:00:00','yyyy-mm-dd hh24:mi:ss'));
insert into test_part values(1,'a',to_date('2022-08-02 12:00:00','yyyy-mm-dd hh24:mi:ss'));
commit ;


--收集统计信息
begin
dbms_stats.gather_table_stats(
	ownname		=>'SCOTT',
	tabname		=>'TEST_PART',
	degree		=>4, 
	estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, 
	cascade		=>true,
	method_opt=>'for all columns size repeat'
);
end;
/

--检查分区表信息
set lines 200 pages 200
col table_name for a25
col table_owner for a10
col partition_name for a15
col partition_position  heading "partition|position"for 99999
col tablespace_name for a10
col column_name for a15
col high_value for a83
select a.table_owner ,a.table_name ,a.partition_name ,a.partition_position,a.tablespace_name ,b.column_name ,high_value
from dba_tab_partitions a ,DBA_PART_KEY_COLUMNS b
where a.table_owner=b.owner 
and a.table_name = b.name 
and a.table_name=upper('TEST_PART');

                                                     partition
TABLE_OWNE TABLE_NAME                PARTITION_NAME   position TABLESPACE COLUMN_NAME     HIGH_VALUE
---------- ------------------------- --------------- --------- ---------- --------------- -----------------------------------------------------------------------------------
SCOTT      TEST_PART                 PART_T01                1 SH_DATA    CREATE_TIME     TO_DATE(' 2022-06-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SCOTT      TEST_PART                 SYS_P847                2 SH_DATA    CREATE_TIME     TO_DATE(' 2022-07-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SCOTT      TEST_PART                 SYS_P848                3 SH_DATA    CREATE_TIME     TO_DATE(' 2022-07-20 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SCOTT      TEST_PART                 SYS_P849                4 SH_DATA    CREATE_TIME     TO_DATE(' 2022-07-30 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
SCOTT      TEST_PART                 SYS_P850                5 SH_DATA    CREATE_TIME     TO_DATE(' 2022-08-09 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA