Just a quick note about a nice little enhancement that might have gone unnoticed so far - since 21c it's now possible to move all partitions and subpartitions of a partitioned heap table using a single ALTER TABLE MOVE command.
So before 21c - if you had a (sub)partitioned heap table and tried to do:
ALTER TABLE <PART_TABLE> MOVE ...
then you got the error message "ORA-14511: cannot perform operation on a partitioned object" and you had to move each physical segment separately - so for a composite partitioned table this was only allowed on subpartition level, for partitioned tables on partition level.
Now from 21c on this is possible and also officially documented here and here for example.
Note that it looks like that it is still not supported to move a partition in case the table is subpartitioned - this still throws the error message "ORA-14257: cannot move a partition which is a composite partition" - so the feature at present is limited to moving the entire table on global level.
However, what is still not possible - even in 23c (FREE) - rebuilding a (sub)partitioned index using a single command.
So running:
ALTER INDEX <PART_INDEX> REBUILD ...
on a (sub)partitioned index still throws the error message: "ORA-14086: a partitioned index may not be rebuilt as a whole", which is a pity - and leaves you again only with the option to perform the rebuilds on the lowest level.
Which might also explain the limitation of above new feature to heap tables only - index organized tables are not supported (and I haven't checked clustered tables).
And the very handy package DBMS_INDEX_UTL which allows very easy handling of (sub)partitioned index rebuilds is still not documented yet, which is even more a pity.
And I had actually built a PL/SQL Package to do the MOVEs and REBUILDs given schema, table_name and/or index_name as parameters.
ReplyDelete