Trying to copy data from one environment to other.
Created partitions and sub partitions as per the source table but still getting error : Inserted partition key does not map to any partition.
Thanks for your help.
Here are some steps to try:
Double and triple check the partition and subpartition definitions and boundaries. They are easy to mess up, especially with subpartitioning. You have to actually check the subpartitioning layout in all the partitions. Just because a subpartition exists in one partition doesn't mean it has to exist in another. The partition names are irrelavent.. what matter is the boundaries (HIGH_VALUE in DBA_TAB_PARTITIONS/SUBPARTITIONS, which is unfortunately a LONG and therefore requires a PL/SQL workaround to query programmatically).
Check your insert statement and verify that you are putting the right source columns in the right target columns. Use explicit column lists both the INSERT and SELECT clauses, don't rely on default column order. It may be sticking the wrong column in your partitioning key.
If all the above checks out, find out if partition exchanges ever happen on the source table. It is possible for the source table owner to have done partition exchanges with the "WITHOUT VALIDATION" clause in order to speed things up. This can result in the wrong data being in the wrong partition, which won't throw an error until you try to select this data out and insert it into another similar partitioned table, at which point it is doing the validation and can throw the error if it doesn't map to some other partition.
Remember that subpartitions can vary from partition to partition. You may have taken the definition from the latest one, or the first one, or the subpartition template, none of which guarantees that's what you need for all the partitions. One easy to test if this is the problem is to make sure there is a (DEFAULT) subpartition defined (assuming it's LIST) to catch any outliers.
If it's a RANGE partition (e.g. some date column) you can create a distant future date partition (e.g. VALUES LESS THAN (TO_DATE('12/31/9999','MM/DD/YYYY')) ), then attempt the insert. If there's rogue future date it will go into that future partition and you can then query it to find out what date it was.
Lastly, consider not defining your partitions at all, but using INTERVAL (if RANGE) or automatic list (if LIST) so that Oracle automatically creates the partitions as you insert data (e.g. PARTITION BY RANGE(datecol) INTERVAL NUMTOYMINTERVAL(1,'MONTH'). You'll still have to define a subpartition template so your subpartitions gets created with each one. I find interval partitioning to be desirable any time it's possible (with a few outlying exceptions). It's your friend and saves a lot of typing and error-prone partition definitions.