1

I wanted to know if DBMA_REDEFINITON package allows a WHERE clause to filter contents before migration.

I have a partitioned table and wants to copy data and constraint to another table using DBMA_REDEFINITON but while copying contents, I do not want to copy a particular partition from the original table. Is it possible to drop this partition using WHERE clause.

The question came from following information given on Oracle Tips site

Online table redefinition: You can drop large numbers of rows from a table by adding a WHERE clause predicate to filter-out unwanted rows when you copy the table.

My requirement is from the example below, can a new table have only partitions Part_2 and Part3. (Dropping partition Part_1 using WHERE clause)

-- Existing Table
CREATE TABLE ORIGINAL
(
  ID                INT NOT NULL PRIMARY KEY,
  TEXT              VARCHAR2(255 CHAR) NOT NULL
)PARTITION BY RANGE
  (
    ID
  )
  (
    PARTITION part_1 VALUES LESS THAN(2),
    PARTITION part_2 VALUES LESS THAN(3),
    PARTITION part_3 VALUES LESS THAN(MAXVALUE)
  )
/
-- some data in the table
insert into ORIGINAL values (1, 'test');

-- some index on the table
create index idx_ORIGINAL on ORIGINAL(TEXT);

--INTERIM table for REDEFINITION (has all partitions right now)
CREATE TABLE ORIGINAL_INTERIM
(
  ID                INT NOT NULL PRIMARY KEY,
  TEXT              VARCHAR2(255 CHAR) NOT NULL
)
PARTITION BY RANGE
  (
    ID
  )
  (
    PARTITION part_1 VALUES LESS THAN(2),
    PARTITION part_2 VALUES LESS THAN(3),
    PARTITION part_3 VALUES LESS THAN(MAXVALUE)
  )
/

-- Start Redefinition
BEGIN
  DBMS_REDEFINITION.START_REDEF_TABLE('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/


-- Copy objects
DECLARE
  num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM',
    DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors);
END;
/


-- Sync
BEGIN
  DBMS_REDEFINITION.SYNC_INTERIM_TABLE ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- finish
BEGIN
  DBMS_REDEFINITION.FINISH_REDEF_TABLE ('TSPACE1','ORIGINAL','ORIGINAL_INTERIM');
END;
/

-- Drop interim table
drop table ORIGINAL_INTERIM cascade constraints purge;
0

You can use part_name parameter of DBMS_REDEFINITION.START_REDEF_TABLE procedure. Here is the complete example of online table redefinition. Online Table Redefinition Examples from Oracle Docs.
Methods to move a partition to another tablespace

|improve this answer|||||
  • Hi thanks for the comments, but unfortunately it looks like with PART_NAME I can only redefine with single partition. My requirement was to skip a single partition and copy rest of the partitions. Is there any work around for this? – Sauchin Feb 25 '16 at 4:42
  • @Sauchin If you want to move selected partition then use ALTER TABLE MOVE PARTITION else if you want to redefine table with selected partitions then use ALTER TABLE ... DROP PARTITION. – JSapkota Feb 25 '16 at 5:18
  • Yes I should be using either move or drop partition statements, but both involve rebuilding global indexes which currently takes a lot of time due to TB of data. Also within that period depending upon the option chosen I risk either locking the tables until index is rebuilt (ALTER TABLE DROP PARTITION REBUILD GLOBAL INDEXES) or failing DML with UNUSABLE indexes issue (ALTER TABLE DROP PARTITION; ALTER INDEX REBUILD ONLINE). This was my desperate attempt to avoid both :) ... I appreciate your help. – Sauchin Feb 25 '16 at 8:00
  • Though this answer does not solve my problem completely, I am accepting this answer as this is the closest solution you can get as far as the question is concerned. – Sauchin Feb 25 '16 at 8:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.