分区表的行迁移

摘录自：https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2570428600346670727

ops$tkyte%ORA10GR2> set serveroutput on
ops$tkyte%ORA10GR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(30)
  6  )
  7  enable row movement
  8  PARTITION BY RANGE (dt)
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('01-jan-2008','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('01-jan-2009','dd-mon-yyyy'))
 12  )
 13  /

Table created.

ops$tkyte%ORA10GR2> insert into t values ( to_date( '01-jun-2007', 'dd-mon-yyyy' ), 1, 'hello' );

1 row created.

ops$tkyte%ORA10GR2> create or replace trigger update_trigger
  2  after update on t for each row
  3  begin
  4          dbms_output.put_line( 'old key = ' || to_char( :old.dt, 'dd-mon-yyyy' ) ||
  5                                ' new key = ' || to_char( :new.dt, 'dd-mon-yyyy' ) );
  6          dbms_output.put_line( 'old rowid = ' || rowidtochar( :old.rowid ) ||
  7                                ' new rowid = ' || rowidtochar( :new.rowid ) );
  8  end;
  9  /

Trigger created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select 'part1', t.* from t partition(part1) union all select 'part2', t.* from t partition(part2);

'PART DT                 X Y
----- --------- ---------- ------------------------------
part1 01-JUN-07          1 hello

ops$tkyte%ORA10GR2> update t set dt = add_months(dt,12);
old key = 01-jun-2007 new key = 01-jun-2008
old rowid = AAA4GjAAEAAABNEAAA new rowid = AAA4GkAAEAAABNMAAA

1 row updated.

ops$tkyte%ORA10GR2> select 'part1', t.* from t partition(part1) union all select 'part2', t.* from t partition(part2);

'PART DT                 X Y
----- --------- ---------- ------------------------------
part2 01-JUN-08          1 hello

ops$tkyte%ORA10GR2