oracle Interval 分区维护与管理要点

2019独角兽企业重金招聘Python工程师标准>>>

本文取自oracle网官网，以在日常工作中备查

Interval Partitioning Essentials - Common Questions - Top Issues (Doc ID 1479115.1

 

In this Document   Purpose Questions and Answers What is interval partitioning? Where can i find examples? Can i specify interval partitioning at subpartition level? Are there any restrictions for the partition key (e.g. data type)? Can i have Domain Index on an interval partitioned table? How to specify/change the tablespace storage for the interval partitions What will be the names of the automatically created interval partitions? What happens if i want to set a new interval for the table? How to perform Partition Maintenance Operations (PMOP)? Exchanging a Partition of an Interval Partitioned Table Dropping interval partition Merging partitions Splitting interval partition Renaming interval partition How to disable / turn off interval partitioning for a table? Partition Count/Pruning Can i specify NULL value for the partitioning key column? Why the system generated interval partitions are missing from the DDL of the table when i retrieve it with the function DBMS_METADATA.GET_DDL? Can interval partitioned table be a parent table for reference partitioning? References   Applies to: Oracle Database - Enterprise Edition - Version 11.1.0.6 to 12.1.0.2 [Release 11.1 to 12.1] Information in this document applies to any platform. Purpose To answer common questions, to provide directions to readers to the relevant information related to interval partitioning. Ask Questions, Get Help, And Share Your Experiences With This Article Would you like to explore this topic further with other Oracle Customers, Oracle Employees, and Industry Experts?   Click here to join the discussion where you can ask questions, get help from others, and share your experiences with this specific article. Discover discussions about other articles and helpful subjects by clicking here to access the main My Oracle Support Community page for Database Datawarehousing.   Questions and Answers What is interval partitioning? Interval partitioning is new feature introduced in version 11.1. This feature makes it possible to get the corresponding partitions created automatically as data comes in for the corresponding partition. Before the interval partition feature became available often a maxvalue partition was created to avoid the ORA-14400: inserted partition key does not map to any partition, and time to time the max partition was split into 2 partition to reduce the catch all maxvalue partition. As an extension of range partitioning the interval partitioning instructs the database to automatically create partitions of a specified interval when data inserted into the table exceeds all of the existing range partitions. You must specify at least one range partition. The range partition key value determines the high value of the range partitions, which is called the transition point, and the database creates interval partitions for data with values that are beyond that transition point. The lower boundary of every interval partition is the non-inclusive upper boundary of the previous range or interval partition. See also Document 805976.1 11g New Features:INTERVAL PARTITIONING     Where can i find examples? Interval partitioning can be specified at creation time of the table or it can be set for an existing range partitioned table. CREATE TABLE ... PARTITION BY RANGE (<partition key>)      INTERVAL(<number or interval expression>) [STORE IN (<tablespace list>)] ... (       PARTITION <partition name> ...          ---> definition of range partition(s) ... ); In order to turn an existing range partitioned table into interval partitioned table, execute the following command: ALTER TABLE <table_name> SET INTERVAL (<number or interval expression>); You can find examples for interval partitioned tables in the following sources. Knowledge documents: Document 854332.1 How To Introduce Interval Partitioning into a Range Partitioned Table Document 757754.1 Interval Partitioning By Week Document 1514047.1 How to Build an INTERVAL PARTIONED Table using a NUMBER datatype for the INTERVAL Partition. Document 466352.1 11g Feature: Interval Partitioning Example Oracle by Example (ObE) documents: Understand and Use Interval Partitioning Using Interval Partitioning Manipulating Partitions in Oracle Database 11g - Interval partitioning MOS Database DataWarehousing document: Example for Interval partitioning - new feature in 11g See also Interval Literals in the Oracle Database SQL Language Reference.     Can i specify interval partitioning at subpartition level? No, interval partitioning cannot be specified at subpartition level. There is an enhancement logged to allow this feature at subpartition level. If you attempt doing that you may get error ORA-00922: missing or invalid option.                 This is documented in