Problem with Hash sub-partitioning

Hi,
I have a table X(id number, name varchar2(20), location varchar2(20), deptid number). This is partitioned by range on LOCATION and further by DEPTID. I add a partition and a default subpartition to this table. I then get a non-partitioned table T , all DEPTID values of which are unique with value=1. The table has exactly the same schema as the X table. To get the data from this new table into X, I add a subpartition and exchange it with the table T, using the ALTER TABLE .... EXCHANGE SUBPARTION .... command.
The table T is then dropped. I get another table S having the DEPTID value of 2 in all its rows. I follow the same process as above and exchange it with another subpartition. This works fine. But if the table S is exchanged with a subpartition first, and table T later with another, I get an Oracle error saying that rows from the table did not qualify for being in the subpartition.

The order in which I will have to exchage the tables is not fixed. My requirement is that I should be able to add a sub-partition and exchange with a table immediately after the table is created. How do I get around the problem? I am bent on using HASH subpartitioning as I might have to use multiple columns as the subpartitioning key.

Please help
Yash Received on Fri Sep 06 2002 - 04:57:15 CDT