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
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.