Exclude system-generated interval partitions from comparison and DDL
For background on Oracle's interval partitioning feature, see chapter 4 of Oracle Database Concept for release 12.2.
Our practice is to create interval-partitioned tables with the minimum number of partitions necessary to create the table (usually just one partition), and let Oracle create all others as data arrives requiring them. Between test, QA, and production databases, data arrives at different times, giving rise to automatic creation of different partitions. But because these differences are only a side-effect of different contents, they are not relevant, in the same way that differences in system-generated constraint names are not relevant. Changing the partition key or the interval size would require a rebuild.
Any time there is a CREATE TABLE command for an interval-partitioned table, the command should be limited to only those partitions that are needed to establish the table. In other words, exclude those partitions where DBATABPARTITIONS.INTERVAL='YES', because they are system-generated. The CREATE TABLE command should explicitly list only those partitions where INTERVAL='NO';
An an example, I have an interval-partitioned table named FMEMBERTENUREMMSNAPSHOT. These are the partitions in my test database:
C911233@TCRDS_SACRDSD2R.ssdc.kp.org> l
1 select partitionname, partitionposition, highvalue, interval
2 from dbatabpartitions
3 where tablename='FMEMBERTENUREMMSNAPSHOT'
4 and tableowner='COBIDM'
5* order by partitionposition
C911233@TCRDSSACRDSD2R.ssdc.kp.org> /
PARTITIONNAME PARTITIONPOSITION HIGH_VALUE INTERVAL
PARTEMPTY 1 190001 NO
SYSP937 2 201310 YES
SYSP968 3 201311 YES
SYSP966 4 201312 YES
SYSP973 5 201313 YES
SYSP933 6 201402 YES
SYSP970 7 201403 YES
SYSP967 8 201404 YES
SYSP931 9 201405 YES
SYSP932 10 201406 YES
SYSP935 11 201407 YES
SYSP977 12 201408 YES
<skipping many lines for brevity>
SYSP3318 63 201811 YES
SYSP3554 64 201812 YES
If there is a need to generate a CREATE TABLE command, all I really need is shown below, because it is sufficient to establish the table as indicated by INTERVAL='NO' in the query above:
CREATE TABLE cobidm.rgtemp11090455980 (
fmbrtenuremmsnpshtik NUMBER NOT NULL,
dmbrprmyhrnik NUMBER NOT NULL,
<many columns omitted>
crdsprcscntlupdid NUMBER NOT NULL,
CONSTRAINT RGTEMP11090455981 PRIMARY KEY (fmbrtenuremmsnpshtik) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455982 ON cobidm.rgtemp11090455980(fmbrtenuremmsnpshtik)
NOCOMPRESS
TABLESPACE cobimediumindex),
CONSTRAINT RGTEMP11090455983 UNIQUE (srcmbrprmyhrnid,srcsnpshtmmnb) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455984 ON cobidm.rgtemp11090455980(srcmbrprmyhrnid,srcsnpshtmmnb)
NOCOMPRESS
TABLESPACE cobimediumindex)
) TABLESPACE cobihcc
PARTITION BY RANGE (dsnpshtmmik) INTERVAL (1)
(PARTITION partempty VALUES LESS THAN (190001)
INDEXING ON
TABLESPACE cobi_hcc COLUMN STORE COMPRESS FOR QUERY LOW)
PARALLEL 4;
;
What I am getting today is shown below. This has the effect of forcing the creation of all of these partitions whether I have data that needs them or not, which therefore does not follow my model definition. I have to edit the script to remove the unnecessary partitions.
--
-- Script generated by Schema Compare for Oracle 4.0.10.528 on 16/11/2018 07:34:27
SET DEFINE OFF
SET SQLBLANKLINES ON
CREATE TABLE cobidm.rgtemp11090455980 (
fmbrtenuremmsnpshtik NUMBER NOT NULL,
dmbrprmyhrnik NUMBER NOT NULL,
<skipping columns for bevity>
crdsprcscntlupdid NUMBER NOT NULL,
CONSTRAINT RGTEMP11090455981 PRIMARY KEY (fmbrtenuremmsnpshtik) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455982 ON cobidm.rgtemp11090455980(fmbrtenuremmsnpshtik)
NOCOMPRESS
TABLESPACE cobimediumindex),
CONSTRAINT RGTEMP11090455983 UNIQUE (srcmbrprmyhrnid,srcsnpshtmmnb) USING INDEX (CREATE UNIQUE INDEX cobidm.rgtemp11090455984 ON cobidm.rgtemp11090455980(srcmbrprmyhrnid,srcsnpshtmmnb)
NOCOMPRESS
TABLESPACE cobimediumindex)
) TABLESPACE cobihcc
PARTITION BY RANGE (dsnpshtmmik) INTERVAL (1)
(PARTITION partempty VALUES LESS THAN (190001)
INDEXING ON
TABLESPACE cobihcc COLUMN STORE COMPRESS FOR QUERY LOW,
PARTITION VALUES LESS THAN (201310)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS,
PARTITION VALUES LESS THAN (201311)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS,
PARTITION VALUES LESS THAN (201312)
INDEXING ON
TABLESPACE cobimedium_data NOCOMPRESS,
<skipping many lines for brevity>
PARTITION VALUES LESS THAN (201811)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS,
PARTITION VALUES LESS THAN (201812)
INDEXING ON
TABLESPACE cobimediumdata NOCOMPRESS)
PARALLEL 4;
-
Richard commented
Select "system partitions" under the "ignore" section in project options.
System generated partitions won't be used in comparisons.