Skip to Main Content
  • Questions
  • Failed operation while altering the non partitined table to a partitioned table.

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Priyank.

Asked: February 09, 2023 - 10:32 am UTC

Last updated: March 28, 2023 - 2:24 pm UTC

Version: Oracle 19c

Viewed 1000+ times

You Asked

Hi,

I am planning to partition some of the very large tables in our data warehouse (some exceeding 600GB size).
And considering their size, I thought of not duplicating the entire table data into another partitioned table. But that old approach makes rollback very simple, just rename back the tables and move back the delta data and its done.

Oracle 19c has support to do this partition operation online and I used it and it works great but then I thought of rollback scenario or if for our largest table the operation just keeps running, and we end up cancelling or killing it; so I tested following.

ALTER TABLE partition_test MODIFY
  PARTITION BY RANGE (created_date) INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')) 
  ( PARTITION partition_test_1 VALUES LESS THAN (TO_DATE('01-DEC-2021','dd-MON-yyyy')),
    PARTITION partition_test_2 VALUES LESS THAN (TO_DATE('01-JAN-2022','dd-MON-yyyy')));


I simply killed the above session and table was looking fine - it did not get partitioned as expected but i noticed 2 new tables.

SYS_JOURNAL_679587
SYS_RMTAB$$_H679587

As I understood that above mentioned tables are Oracle internal tables which are required when partitions are moved etc and Journal table is capturing the changes and RMTAB hold ROWIDs details. When I tried dropping these tables, Journal got dropped but RMTAB refused with folowing error

ORA-14473: Mapping table cannot be dropped while there are failed online partition maintenance operations that must be cleaned up.

I got from Oracle documentation that this cannot be dropped directly and need following

DBMS_PART.CLEANUP_ONLINE_OP (
   schema_name       IN   VARCHAR2 DEFAULT NULL,
   table_name        IN   VARCHAR2 DEFAULT NULL, 
   partition_name    IN   VARCHAR2 DEFAULT NULL);


But I dont have a partition_name because its just the first attempt to covert a non-partition table in to the partition table.

How can I removed RMTAB table?
Can this approach potentially corrupt the table I am trying to partition? I would appreciate any suggestions including completely moving away from doing the partition using online operation.

Regards,
Priyank

and Chris said...

You don't need to know the partition name!

Call the procedure with the table name alone and it removes the temporary objects:

create table t as 
  select level c1, rpad ( 'stuff', 100, 'f' ) c2,
         sysdate - ( level / 512 ) c3
  from   dual
  connect by level <= 1000000;
    
alter table t modify
  partition by range (c3) interval ( interval '1' day ) 
  ( partition partition_test_1 values less than ( date'2000-12-01' ));
/*
alter table t modify
            *
ERROR at line 1:
ORA-00028: your session has been killed
*/

conn your_user@your_db

select object_name
from   user_objects
where  created > trunc ( sysdate )
order  by created desc;
/*
OBJECT_NAME         
-------------------
SYS_JOURNAL_191945  
SYS_RMTAB$$_H191945 
SYS_IOT_TOP_191946  
T
*/

exec dbms_part.cleanup_online_op ( user, 't' );

select object_name
from   user_objects
where  created > trunc ( sysdate )
order  by created desc;
/*
OBJECT_NAME
-----------
T    
*/


You can then rerun the alter table to partition the table.

If you're concerned about this process taking a long time, you could place all existing data in one partition. This can be significantly faster than splitting it into all the desired partitions.

Continuing the example above, splitting into daily partitions takes ~25s. Whereas placing everything in one partition takes ~6s:

set timing on
alter table t modify
  partition by range (c3) interval ( interval '1' day ) 
  ( partition partition_test_1 values less than ( date'2000-12-01' ));

--Elapsed: 00:00:24.77

select count(*) from user_tab_partitions
where  table_name = 'T';
/*
  COUNT(*)
----------
      1955
*/
drop table t 
  cascade constraints purge;
  
create table t as 
  select level c1, rpad ( 'stuff', 100, 'f' ) c2,
         sysdate - ( level / 512 ) c3
  from   dual
  connect by level <= 1000000;
  
alter table t modify
  partition by range (c3) interval ( numtodsinterval(1, 'day') )
  ( partition partition_test_1 values less than ( date'2023-12-01' ) );
    
--Elapsed: 00:00:06.15

select count(*) from user_tab_partitions
where  table_name = 'T';  
/*
  COUNT(*)
----------
         1
*/


This also gives a relatively quick rollback option:

Exchange the partition with an empty non-partitioned copy of the table. Ensure you include any grants, triggers, etc. on the empty table. Then rename the tables to switch the partitioned with the non-partitioned table. You'll need an outage to complete this safely.

Obviously, this only works as long as there's only one partition in the current table!

Assuming you stick with partitioning, you can split the existing data into separate partitions as needed.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

online partitioning

A reader, March 28, 2023 - 12:19 pm UTC

Thanks a lot chris.
just to conclude this, we were able to partition the table using online clause. It worked without any issues for tables with heavy transactions and FK relations etc.

For large tables, we did not face any issues till about 250 GB (no lob columns)... post this size, I used your suggestion of reducing the number of partitions and also we ran parallel query for this and largest table ~ 700GB took about 6-7 hours to finish with 16 threads.
Chris Saxon
March 28, 2023 - 2:24 pm UTC

Thanks for letting us know; glad you got this resolved!

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database