1、创建临时表
ZXB@racc1>create table test_part
2 (id number,
3 create_time date)
4 partition by range(create_time) interval(numtodsinterval(1,'day'))
5 (partition part_t001 values less than(to_date('2020-03-28','yyyy-mm-dd')));
ZXB@racc1>alter table test_part add name varchar2(100);
2、创建索引
ZXB@racc1>create index idx_testpart_fh on test_part(id,name) local;
3、插入数据
insert into test_part(id,name,create_time)
values(1,'zxb',to_date('2020-03-28','yyyy-mm-dd'));
insert into test_part(id,name,create_time)
values(1,'hql',to_date('2020-03-29','yyyy-mm-dd'));
insert into test_part(id,name,create_time)
values(1,'zjy',to_date('2020-03-30','yyyy-mm-dd'));
insert into test_part(id,name,create_time)
values(1,'hll',to_date('2020-04-05','yyyy-mm-dd'));
insert into test_part(id,name,create_time)
values(2,'lzq',to_date('2020-05-05','yyyy-mm-dd'));
4、查询分区情况
ZXB@racc1>select TABLE_OWNER,partition_name,num_rows from dba_tab_partitions where table_name='TEST_PART';
TABLE_OWNER PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
ZXB PART_T001
ZXB SYS_P41
ZXB SYS_P42
ZXB SYS_P43
ZXB SYS_P44
5、查询索引情况
ZXB@racc1>SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME ,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='IDX_TESTPART_FH';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
ZXB IDX_TESTPART_FH PART_T001 USABLE
ZXB IDX_TESTPART_FH SYS_P41 USABLE
ZXB IDX_TESTPART_FH SYS_P42 USABLE
ZXB IDX_TESTPART_FH SYS_P43 USABLE
ZXB IDX_TESTPART_FH SYS_P44 USABLE
6、再次插入数据
begin
while 2>1
loop
insert into test_part(id,name,create_time) values(2,'lzq',to_date('2020-05-05','yyyy-mm-dd'));
commit;
dbms_lock.sleep(5);
end loop;
end;
/
7、删除分区
ZXB@racc1>alter table test_part drop partition SYS_P41;
Table altered.
ZXB@racc1>alter table test_part drop partition SYS_P42;
Table altered.
8、删除分区后的状态
ZXB@racc1>select TABLE_OWNER,partition_name,num_rows from dba_tab_partitions where table_name='TEST_PART';
TABLE_OWNER PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
ZXB PART_T001
ZXB SYS_P43
ZXB SYS_P44
ZXB SYS_P45
ZXB@racc1>SELECT INDEX_OWNER,INDEX_NAME,PARTITION_NAME ,STATUS FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='IDX_TESTPART_FH';
INDEX_OWNER INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
ZXB IDX_TESTPART_FH PART_T001 USABLE
ZXB IDX_TESTPART_FH SYS_P43 USABLE
ZXB IDX_TESTPART_FH SYS_P44 USABLE
ZXB IDX_TESTPART_FH SYS_P45 USABLE
ZXB@racc1>select * from test_part;
ID CREATE_TI NAME
---------- --------- ------------------------------
1 30-MAR-20 zjy
1 05-APR-20 hll
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
2 05-MAY-20 lzq
