Shulou(Shulou.com)05/31 Report--

This article is about how to change the monthly partition table to the day partition table in the database. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

Generally speaking, the tables of monthly partitions are all range partitions. Here's how to change monthly partitions to day partitions:

The situation of 1.range interval partition

The interval partition cannot be manually add partition, and the following error occurs:

ORA-14760 ADD PARTITION is not permitted on Interval partitioned objects

The idea is to first change it to non-interval (Interval) partition, then manually add partition by day, and it is recommended to change it to daily interval. For more information, please see the following list:

Create table tmp_test_range

(

Id number

Name varchar2 (30)

Int_date date

)

PARTITION BY RANGE (int_date)

Interval (NUMTOYMINTERVAL (1)

(

Partition P201511 values less than (to_date ('201512018)

Partition P201512 values less than (to_date ('20160101))

ALTER TABLE tmp_test_range SET INTERVAL ()

Alter table tmp_test_range add partition P20160101 values less than (to_date (20160102)

ALTER TABLE tmp_test_range SET INTERVAL (NUMTODSINTERVAL (1)

The case of 2.range non-spaced partitions

The idea is to delete some unused monthly partitions, then manually add partition by day, and suggest changing it to daily intervals.

Create table tmp_test_range

(

Id number

Name varchar2 (30)

Int_date date

)

PARTITION BY RANGE (int_date)

(

Partition P201511 values less than (to_date ('201512018)

Partition P201512 values less than (to_date (20160101)

Partition P201601 values less than (to_date ('201602018))

Alter table tmp_test_range drop partition P201601

Alter table tmp_test_range add partition P20160101 values less than (to_date ('20160102 recording recording yyyymmdd`));-- add a new starting partition

ALTER TABLE tmp_test_range SET INTERVAL (NUMTODSINTERVAL (1)

Thank you for reading! This is the end of the article on "how to change the monthly partition table in the database to day partition". I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.