How to Find Partition Range Already Defined


How to Find Partition Range Already Defined


 I have Created a Range partition on a table basis of date, suppose from 01-01-2001 to 31-12-2001 Each interval month and did not use NO RANGE, UNKNOWN.

Now the situation is , i got some new data, and there is some new date ranges, which can be prior/post from above partition range. 

1- First of all, is there any way to find out what exectly partition range has been already difined on table (min to max date). In this regard i found a post to get ConstraintText using partitioningconstraintsvx view. Buth it is hard to get min and max value from this long text.

2- What is the best practice to add new range , suppose i want to increase range  from 01-01-2001 to 31-12-2002 with out recreaction of existing range( just to increase one and other way).

3- What if, By defining some range overlaps to the existing ranges?

Note: i want perform these task from the client application.



Senior Apprentice

Re: How to Find Partition Range Already Defined

Hi Ahmad,

you could use REGEXP_SUBSTR or INSTR to extract the last date found in PartitioningConstraintsV.

Regarding Q2 & Q3, there's ADD RANGE and ranges can't overlap, but the manuals fully cover that.