Hi Tom and his team!

I have question about online partition move and constraints in status disabled, validated.
But it requires some prehistory.

We have huge database with many tables with thousands of partitions.
Sometimes we decide to reorganize one of tablespaces to reclaim free space in it.

For this purpose we generate some script like many of such commands:

alter table tblowner.tblname move partition partname tablespace tblspace_new online;

we generating these commands by queries to dba_tables, dba_indexes, dba_tab_partitions, dba_ind_partitions.
something like this

select 'ALTER TABLE '||table_owner||'.'||table_name||' MOVE PARTITION '||partition_name||' TABLESPACE TBS_NEW online;' from dba_tab_partitions
where tablespace_name = 'TBS_OLD'

Most of segments will be moved online with such commands.
For several cases, online move will not succeed.
For example, this script will not move base table of matherialized view if we try to move it just as a table, it will not move online indexes with too long values, it will not move tables with flashback archive enabled etc.
In most cases, if move is not possible, oracle will not start move or will start move, fail and cleanup itself automatically.
Also, if cleanup required, it will be mostly dependent on moved partition size, and not dependent on entire table size.

However (and here interesting part starting) we faced some case, when, after online partition move try, manual cleanup required and moreover - this cleanup requires read of not just one partition, but read entire table.

I'm talking about table with non indexed unique constraints.

Look at this example:

CREATE TABLE TEST_TABLE
(
  SESS_ID                NUMBER,
  MM                     NUMBER
)
PARTITION BY RANGE (SESS_ID)
(  
PARTITION P_1 VALUES LESS THAN (2),  
PARTITION P_2 VALUES LESS THAN (3)
);               

Usually, exchange partition used for data addition to such tables at our environment, but for testcase we will use simple insert before constraint creation.

insert into TEST_TABLE values (1,1);
insert into TEST_TABLE values (2,2);
commit;

ALTER TABLE TEST_TABLE ADD (
  CONSTRAINT PK_NOIDX_TST
  PRIMARY KEY
  (SESS_ID)
  DISABLE VALIDATE);

Now let's try to move it online:

ALTER TABLE TEST_TABLE move partition P_1 ONLINE;

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-25128: No insert/update/delete on table with constraint (NG.PK_NOIDX_TST)
disabled and validated
ORA-00604: error occurred at recursive SQL level 1
ORA-25128: No insert/update/delete on table with constraint (NG.PK_NOIDX_TST)
disabled and validated

Ok, now let's check partitions of our table:

select table_name, partition_name from user_tab_partitions where table_name = 'TEST_TABLE';

TEST_TABLE P_1
TEST_TABLE P_2

All looks good.

But let's check another view:

column object_name format a20
column subobject_name format a20
column object_type format a20
select object_name, subobject_name, object_type from user_objects where object_name = 'TEST_TABLE';

OBJECT_NAME          SUBOBJECT_NAME       OBJECT_TYPE
-------------------- -------------------- --------------------
TEST_TABLE                                TABLE
TEST_TABLE           P_1                  TABLE PARTITION
TEST_TABLE           P_2                  TABLE PARTITION
TEST_TABLE           SYS_P28119           TABLE PARTITION           <--------!

Here it is!
We get additional partition, which will not be cleaned automatically.

CLEANUP_ONLINE_PMO job will try to drop it, but without success:

exec dbms_scheduler.run_job('CLEANUP_ONLINE_PMO');
BEGIN dbms_scheduler.run_job('CLEANUP_ONLINE_PMO'); END;

*
ERROR at line 1:
ORA-25128: No insert/update/delete on table with constraint (NG.PK_NOIDX_TST)
disabled and validated
ORA-06512: at "SYS.DBMS_PDB", line 85
ORA-06512: at line 4
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1

Also, this partition is identified by healthcheck:

HCKE-0009: OBJ$-PARTOBJ$-TABPART$ mismatch

Yes, I understand, that fixup is pretty straightforward:

alter table NG.TEST_TABLE modify constraint PK_NOIDX_TST novalidate;
exec dbms_scheduler.run_job('CLEANUP_ONLINE_PMO');
alter table NG.TEST_TABLE modify constraint PK_NOIDX_TST validate;

But validating constraint will read entire table, and if we have table with thousand partitions, oracle will have to read them all and it's can be time consuming operation. (even with good parallelism)

Another workaround (if move online was not executed yet), we can just run

ALTER TABLE TEST_TABLE move partition P_1;

without "online" keyword.


We have many DBA's, so one of them eventually can forget about such tables in our database and will run

ALTER TABLE ... move partition ... online

on such table and it will again require disable and enable constraint for fixup, and validating pk constraint on this table without index can be very time consuming.

Is there some method to drop such stuck partition without disabling/enabling constraint?

Is it possible, that Oracle will add some foolproof check for such cases?
(so that online move will not even start for such tables)

SQL> create table test_table
  2  (
  3    sess_id                number,
  4    mm                     number
  5  );

Table created.

SQL>
SQL> insert into test_table values (1,1);

1 row created.

SQL> insert into test_table values (2,2);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> alter table test_table add constraint pk_noidx_tst  primary key  (sess_id)  disable validate;

Table altered.

SQL>
SQL> alter table test_table move online;

Table altered.

SQL>