Tuesday 14 August 2007

Partitioning Limitations

I was trying to reverse engineer the schema using Visual Paradigm for UML 6.0 Professional Edition and identified that it doesn't work with partitioned tables. However, this is a restriction / bug in Visual Paradigm.
To list out few limitations of Partitioning :


  • In 9i, you can not create index organized table with List Partitioning (however in 10g, you can)
  • In 9i, you can not Fast Refresh materialized views after truncating partitions on base table. You have to have range or composite (implies range) especially in this case. But In 10g, you can i.e. range OR list (or composite)
  • You have to disable referential integrity between partitioned tables if you want to drop old partitions. Even if you drop child partition first, followed by its parent table partition, currently results in Oracle error ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
  • We can not move partitions to tablespace with different block size using ALTER TABLE MOVE command

1 comment:

Amelia said...

Thanks a lot for listing all the limitations of partitioning. Its good to know in advance about all these aspects to make use of this scheme in a better way and also to avoid problems.
sap upgrade tool