分割槽表之自動增加分割槽（11G）
按季度、按周、按年
3.自動增加分割槽（11G）
----11g新特性，numtodsinterval/numtoyminterval
3.1 按月自動新增分割槽 (按季度)
----建立分割槽（每3個月新增一個分割槽）
create table t_mon
( seq number not null,
update_time date,
constraint t_mon_seq primary key(seq)
)
partition by range(update_time)
interval(numtoyminterval(3,'month'))
( partition t_mon1 values less than(to_date('2016-01-01','yyyy-mm-dd')) tablespace users);
----檢視T_MON表的分割槽情況
set linesize 999
set pagesize 999
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a10
col TABLESPACE_NAME for a10
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_MON T_MON1 TO_DATE(' 83 USERS
2016-01-01
SQL> insert into t_mon values(1,sysdate);
1 row created.
SQL> select * from t_mon;
SEQ UPDATE_TIME
---------- -------------------
1 2016-07-19 20:49:56
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_MON SYS_P43 TO_DATE(' 83 SYSTEM
2016-10-01
T_MON T_MON1 TO_DATE(' 83 USERS
2016-01-01
----批次新增資料，檢視分割槽表新增情況
/*
declare
a date;
begin
for i in 2..20 loop
select sysdate+i into a from dual;
dbms_output.put_line(a);
end loop;
end;
/
declare
a date;
begin
for i in 2..20 loop
select add_months(sysdate,i) into a from dual;
dbms_output.put_line(a);
end loop;
end;
/
*/
begin
for i in 2..14 loop
insert into t_mon values(i,add_months(sysdate,i));
end loop;
commit;
end;
/
SQL> select * from t_mon;
SEQ UPDATE_TIME
---------- -------------------
1 2016-07-19 20:55:39
2 2016-09-19 22:00:00
3 2016-10-19 22:00:00
4 2016-11-19 22:00:00
5 2016-12-19 22:00:00
6 2017-01-19 22:00:00
7 2017-02-19 22:00:00
8 2017-03-19 22:00:00
9 2017-04-19 22:00:00
10 2017-05-19 22:00:00
11 2017-06-19 22:00:00
12 2017-07-19 22:00:00
13 2017-08-19 22:00:00
14 2017-09-19 22:00:00
14 rows selected.
----再次檢視錶分割槽情況
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_MON';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_MON SYS_P43 TO_DATE(' 83 SYSTEM
2016-10-01
T_MON SYS_P61 TO_DATE(' 83 SYSTEM
2017-01-01
T_MON SYS_P62 TO_DATE(' 83 SYSTEM
2017-04-01
T_MON SYS_P63 TO_DATE(' 83 SYSTEM
2017-07-01
T_MON SYS_P64 TO_DATE(' 83 SYSTEM
2017-10-01
T_MON T_MON1 TO_DATE(' 83 USERS
2016-01-01
6 rows selected.
----現在有6個分割槽咯
SQL> select * from t_mon partition(sys_p61);
SEQ UPDATE_TIME
---------- -------------------
3 2016-10-19 22:00:00
4 2016-11-19 22:00:00
5 2016-12-19 22:00:00
----索引狀態
select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_MON';
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_TYPE STATUS
------------------------------ --------------------------- ---------- ----------- --------
T_MON_SEQ NORMAL SYSTEM TABLE VALID
3.2 按天自動新增分割槽 (按周)
----建立分割槽（每7天新增一個分割槽）
create table t_day
( seq number not null,
update_time date,
constraint t_day_seq primary key(seq)
)
partition by range(update_time)
interval(numtodsinterval(7,'day'))
( partition t_day1 values less than(to_date('2016-08-15','yyyy-mm-dd')) tablespace users);
----檢視T_DAY表的分割槽情況
set linesize 999
set pagesize 999
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a10
col TABLESPACE_NAME for a10
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_DAY';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_DAY T_DAY1 TO_DATE(' 83 USERS
2016-08-15
----批次新增資料，檢視分割槽表新增情況
begin
for i in 1..7 loop
insert into t_day values(i, sysdate+i*7);
end loop;
commit;
end;
/
SQL> select * from t_day;
SEQ UPDATE_TIME
---------- -------------------
1 2016-07-26 22:16:15
2 2016-08-02 22:16:15
3 2016-08-09 22:16:15
4 2016-08-16 22:16:15
5 2016-08-23 22:16:15
6 2016-08-30 22:16:15
7 2016-09-06 22:16:15
7 rows selected.
----再次檢視錶分割槽情況
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_DAY';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_DAY SYS_P83 TO_DATE(' 83 SYSTEM
2016-08-22
T_DAY SYS_P84 TO_DATE(' 83 SYSTEM
2016-08-29
T_DAY SYS_P85 TO_DATE(' 83 SYSTEM
2016-09-05
T_DAY SYS_P86 TO_DATE(' 83 SYSTEM
2016-09-12
T_DAY T_DAY1 TO_DATE(' 83 USERS
2016-08-15
SQL> select * from t_day partition(sys_p83);
SEQ UPDATE_TIME
---------- -------------------
4 2016-08-16 22:16:15
select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_DAY';
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_TYPE STATUS
------------------------------ --------------------------- ---------- ----------- --------
T_DAY_SEQ NORMAL SYSTEM TABLE VALID
3.3 按年自動新增分割槽 (按年)
----建立分割槽（每1年新增一個分割槽）
create table t_year
( seq number not null,
update_time date,
constraint t_year_seq primary key(seq)
)
partition by range(update_time)
interval(numtoyminterval(1,'year'))
( partition t_year1 values less than(to_date('2016-01-01','yyyy-mm-dd')) tablespace users);
----檢視T_YEAR表的分割槽情況
set linesize 999
set pagesize 999
col TABLE_NAME for a20
col PARTITION_NAME for a20
col HIGH_VALUE for a10
col TABLESPACE_NAME for a10
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_YEAR';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_YEAR T_YEAR1 TO_DATE(' 83 USERS
2016-01-01
----批次新增資料，檢視分割槽表新增情況
SQL> select sysdate,sysdate+numtoyminterval(1,'year') from dual;
SYSDATE SYSDATE+NUMTOYMINTE
------------------- -------------------
2016-07-19 22:33:27 2017-07-19 22:33:27
begin
for i in 1..5 loop
insert into t_year values(i,sysdate+numtoyminterval(i,'year'));
end loop;
commit;
end;
/
SQL> select * from t_year;
SEQ UPDATE_TIME
---------- -------------------
1 2017-07-19 22:35:00
2 2018-07-19 22:35:00
3 2019-07-19 22:35:00
4 2020-07-19 22:35:00
5 2021-07-19 22:35:01
----再次檢視錶分割槽情況
select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,HIGH_VALUE_LENGTH,TABLESPACE_NAME from dba_tab_partitions where TABLE_NAME='T_YEAR';
TABLE_NAME PARTITION_NAME HIGH_VALUE HIGH_VALUE_LENGTH TABLESPACE
-------------------- -------------------- ---------- ----------------- ----------
T_YEAR SYS_P87 TO_DATE(' 83 SYSTEM
2018-01-01
T_YEAR SYS_P88 TO_DATE(' 83 SYSTEM
2019-01-01
T_YEAR SYS_P89 TO_DATE(' 83 SYSTEM
2020-01-01
T_YEAR SYS_P90 TO_DATE(' 83 SYSTEM
2021-01-01
T_YEAR SYS_P91 TO_DATE(' 83 SYSTEM
2022-01-01
T_YEAR T_YEAR1 TO_DATE(' 83 USERS
2016-01-01
6 rows selected.
----現在有6個分割槽咯
SQL> select * from t_year partition(sys_p91);
SEQ UPDATE_TIME
---------- -------------------
5 2021-07-19 22:35:01
----索引狀態
select index_name,index_type,tablespace_name,table_type,status from user_indexes where table_name='T_YEAR';
INDEX_NAME INDEX_TYPE TABLESPACE TABLE_TYPE STATUS
------------------------------ --------------------------- ---------- ----------- --------
T_YEAR_SEQ NORMAL SYSTEM TABLE VALID
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28211342/viewspace-2153383/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Oracle分割槽表基礎運維-07增加分割槽(2 HASH分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(3列表分割槽)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(1範圍分割槽)Oracle運維
- oracle分割槽表和分割槽表exchangeOracle
- 移動分割槽表和分割槽索引的表空間索引
- oracle分割槽表和非分割槽表exchangeOracle
- 增加表分割槽時，為local分割槽索引指定不同表空間的方法索引
- PostgreSQL/LightDB 分割槽表之分割槽裁剪SQL
- Oracle12c：建立主分割槽、子分割槽，實現自動分割槽插入效果Oracle
- 非分割槽錶轉換成分割槽表
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- Oracle分割槽表基礎運維-07增加分割槽(4 RANGE_HASH)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(5RANGE_LIST)Oracle運維
- Oracle分割槽表基礎運維-07增加分割槽(6RANGE_RANGE)Oracle運維
- PG的非分割槽表線上轉分割槽表
- 【MYSQL】 分割槽表MySql
- oracle 分割槽表move和包含分割槽表的lob moveOracle
- Oracle 12.2之後ALTER TABLE .. MODIFY轉換非分割槽表為分割槽表Oracle
- MySql資料分割槽操作之新增分割槽操作MySql
- 【Linux】MBR磁碟分割槽表只能有四個分割槽？Linux
- Hive的靜態分割槽與動態分割槽Hive
- Oracle分割槽表基礎運維-04列表分割槽Oracle運維
- 分割槽表-實戰
- MySQL 分割槽表探索MySql
- Linux分割槽方案、分割槽建議Linux
- Oracle分割槽表基礎運維-09刪除分割槽Oracle運維
- Oracle分割槽表基礎運維-05組合分割槽Oracle運維
- Oracle分割槽表基礎運維-02範圍分割槽Oracle運維
- Oracle分割槽表基礎運維-03HASH分割槽Oracle運維
- Oracle分割槽表基礎運維-06分割槽表索引Oracle運維索引
- oracle 線上重新定義，普通表改變分割槽表，分割槽表可以更改型別、分割槽欄位等Oracle型別
- 非分割槽錶轉換成分割槽表以及注意事項
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- Oracle分割槽表基礎運維-01分割槽表分類Oracle運維
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- Linux 分割槽擴容（根分割槽擴容，SWAP 分割槽擴容，掛載新分割槽為目錄）Linux
- Hive中靜態分割槽和動態分割槽總結Hive
- Hive動態分割槽Hive