Just aborted an Drop Partition/Add Partition Alter statement on a table with about 100M rows and 15 populated partitions (27 partitions in all). It's taking ages to abort, and is easily re-created, so I thought it might be a good idea to Cancel the Rollback.
fyi : the data on the table isn't terribly skewed, but the No Range partition contains about 60M rows.
Sadly, Recovery Manager fails to recognize the Table Id I supply in the CANCEL ROLLBACK command (I got the tableid from TVM). Yes, I know I'm supposed to use List Rollback Tables to get the table id, but when I try that, Recovery Manager just hangs and I have to End the Task in windows :-(
This doesn't happen with non-PPI tables.
Any clues as to which basic mistake I've made this time and how to fix it ?
You incorrectly assumed that the request was abortable and was rolling back.
ALTER is not a normal DML operation like DELETE. ALTER does not use Transient Journal, so once it begins processing you can't abort it or roll it back. It will continue to run to completion even across database restarts.
It doesn't have anything to do with PPI specifically; maybe you've just never tried a long-running ALTER on a non-PPI table...
ALTER in conjunction with adding/dropping partitions on a PPI table is perhaps a bit different to 'normal' DDL. There's a substantial amount of data deleted and inserted (and TJ'd), and the 'aborted' request really does get aborted and really does (eventually) get rolled back (even the DDL component !).
Whilst the request is aborting, a LIST ROLLBACK TABLES command in recovery manager simply hangs. However, once the request abort has successfully completed, Recover Manager suddenly comes back to life and belatedly shows details of the table that has just been rolled-back.
You're right though, I've never done an ALTER on a non-PPI table, I'm more of a drop/recreate kind of bloke :-)