This is something that happened in one of my project which I would like to share and also have your views.
I came across an unexpected situation whereby in a range partitioned table there were rows in a partition which were not matching the partition key.

E.g. In a table partition by year, where I was supposed to have all rows with partition key column between 201401 and 201412, I was having rows for other periods other than the year 2014.

Let's see how this may happen..

CREATE TABLE TMP_FACT_ACCOUNT_GL
(
FCT_MTH_NKEY NUMBER(18) NOT NULL,
FCT_AMT NUMBER(18),
DIM_CMPNY_KEY NUMBER(18) NOT NULL,
DIM_ACCT_KEY NUMBER(18) NOT NULL,
DIM_BUS_UNT_KEY NUMBER(18) NOT NULL,
BATCH_ID NUMBER(18) NOT NULL,
BATCH_DTS TIMESTAMP(6) NOT NULL
)
PARTITION BY RANGE (FCT_MTH_NKEY)
(
PARTITION P2014 VALUES LESS THAN (201500),
PARTITION P2015 VALUES LESS THAN (201600)
);

INSERT INTO TMP_FACT_ACCOUNT_GL
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201501,1000,10,160100,1,999,SYSDATE);

COMMIT;

INSERT INTO TMP_FACT_ACCOUNT_GL
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201401,2000,10,125000,1,999,SYSDATE);

COMMIT;


-- Let's see the data by partition

SELECT * FROM TMP_FACT_ACCOUNT_GL PARTITION (P2015)

201501 1000 10 160100 1 999 2015-10-01 10:10:48,000000


SELECT * FROM TMP_FACT_ACCOUNT_GL PARTITION (P2014)

201401 2000 10 125000 1 999 2015-10-01 10:11:36,000000

-- This is consistent up to now.

Now let's try to insert a row in partition 2014 where the period does not match

INSERT INTO TMP_FACT_ACCOUNT_GL PARTITION (P2014)
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201502,5000,10,420200,1,999,SYSDATE);

--ORA-14401: inserted partition key is outside specified partition
--This insert will return an Error and ensure that the integrity of the partition is maintained.



INSERT INTO TMP_FACT_ACCOUNT_GL PARTITION (P2015)
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201403,7500,10,520000,1,999,SYSDATE);

--ORA-14401: inserted partition key is outside specified partition
--Again we cannot force a row with wrong partition key value to a partition


..now let's see how integrity is broken while using exchange partition to insert data to the partitioned table


It's a usual practice in ETL and Data Warehouse to load the target data in a temporary table and perform all the validations including foreign keys constraints before moving the data into the right partition in the final target table.

This method is definitely many times faster than inserting directly to target table where we have a huge data volumes, foreign key constraints, indexes etc.

In the example below I'm using exchange table partition to move data from a work table to a partitioned table


CREATE TABLE TMP_FACT_ACCOUNT_GL_WRK
AS SELECT * FROM TMP_FACT_ACCOUNT_GL WHERE 1=2


INSERT INTO TMP_FACT_ACCOUNT_GL_WRK
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201411,100,10,120000,1,999,SYSDATE);

INSERT INTO TMP_FACT_ACCOUNT_GL_WRK
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201410,200,10,120000,1,999,SYSDATE);

INSERT INTO TMP_FACT_ACCOUNT_GL_WRK
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201506,300,10,120000,1,999,SYSDATE);

INSERT INTO TMP_FACT_ACCOUNT_GL_WRK
(FCT_MTH_NKEY, FCT_AMT, DIM_CMPNY_KEY, DIM_ACCT_KEY, DIM_BUS_UNT_KEY, BATCH_ID, BATCH_DTS)
VALUES
(201509,800,10,120000,1,999,SYSDATE);



SELECT * FROM TMP_FACT_ACCOUNT_GL_WRK

201411 100 10 120000 1 999 2015-10-01 10:18:24,000000
201410 200 10 120000 1 999 2015-10-01 10:18:26,000000
201506 300 10 120000 1 999 2015-10-01 10:18:27,000000
201509 800 10 120000 1 999 2015-10-01 10:18:30,000000



ALTER TABLE TMP_FACT_ACCOUNT_GL EXCHANGE PARTITION P2014 WITH TABLE TMP_FACT_ACCOUNT_GL_WRK

This DDL statements will fail with the error below in this case because we are trying to put rows into a partition where the partition key does not match.

ORA-14099: all rows in table do not qualify for specified partition

It may fails also if we are trying to insert a row with a foreign key which does not exist on the parent table, assuming that this constraint has status ENABLED and VALIDATE on the target table.



..now to let's try to move the data using WITHOUT VALIDATION clause because (let's assume) we have validated all foreign keys on the work table and we want to move the data very fast, within a second to the target table.

ALTER TABLE TMP_FACT_ACCOUNT_GL EXCHANGE PARTITION P2014 WITH TABLE TMP_FACT_ACCOUNT_GL_WRK WITHOUT VALIDATION

Table altered

Now let's see the data on the Partition 2014

SELECT * FROM TMP_FACT_ACCOUNT_GL PARTITION (P2014)

201411 100 10 120000 1 999 2015-10-01 10:22:41,000000
201410 200 10 120000 1 999 2015-10-01 10:22:43,000000
201506 300 10 120000 1 999 2015-10-01 10:22:45,000000
201509 800 10 120000 1 999 2015-10-01 10:22:48,000000


As a business analytic user of this table knowing that it is partitioned by year, you would be surprised to see inconsistent data in the partitions.

Yes, we have an argument when saying that, we must validate the partition key values also before doing the exchange table partition, but nothing prevent us for not doing it.

Conclusion:

As for constraints on a table (through the data dictionary) you can know if the integrity constraint is maintained and if the data is consistent by looking at the Enabled, Disabled , Validate or No_Validate properties.

However, for a partitioned table as far as I know there no such status that can tell you if the data is consistent within the partitions.
So, we should be careful when using Oracle Table Partition because Oracle Database does not guarantee that the data is consistent within the partition.