1 rows processed
4：在线重定义分区表
准备基表，创建表依附对象，位图索引和触发器
SQL> create table hr.t04310_e as select * from dba_source;
Table created.
SQL> create bitmap index hr.i_t04310_e on hr.t04310_e(type);
Index created.
SQL> create or replace trigger hr.trg_t04310_e
2 after update of line on hr.t04310_e
3 referencing new as new old as old for each row
4 begin
5 if :old.line <= :new.line
6 then
7 raise_application_error('-20001','new values must big than old');
8 end if;
9* end;
Trigger created
SQL> select * from user_errors;
no rows selected
构造长查询语句，模拟表正在被访问
SQL> select * from hr.t04310_e a,hr.t04310_e b;
开始在线重定义分区表
SQL> CREATE TABLE "HR"."T04310_E$REORG"
2 ( OWNER VARCHAR2(30),NAME VARCHAR2(30), TYPE VARCHAR2(12),LINE NUMBER,TEXT VARCHAR2(4000) )
3 PARTITION BY RANGE ( LINE )
4 (PARTITION line_P1 VALUES LESS THAN (5000) TABLESPACE USERS,
5 PARTITION line_P2 VALUES LESS THAN (MAXVALUE) TABLESPACE EXAMPLE);
Table created.
SQL> BEGIN
2 DBMS_REDEFINITION.START_REDEF_TABLE('"HR"', '"T04310_E"', '"T04310_E$REORG"', NULL, DBMS_REDEFINITION.CONS_USE_ROWID);
3* END;
PL/SQL procedure successfully completed.
SQL> CREATE BITMAP INDEX "HR"."I_T04310_E$REORG" ON "HR"."T04310_E$REORG" ("TYPE") local
2 TABLESPACE "USERS" PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING;
Index created.
SQL> BEGIN
2 DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('"HR"', '"T04310_E"', '"T04310_E$REORG"',
3 DBMS_REDEFINITION.CONS_INDEX, '"HR"', '"I_T04310_E"', '"I_T04310_E$REORG"');
4* END;
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REDEFINITION.SYNC_INTERIM_TABLE('"HR"', '"T04310_E"', '"T04310_E$REORG"');
3* END;
PL/SQL procedure successfully completed.
SQL> CREATE OR REPLACE TRIGGER "HR"."TRG_T04310_E$REORG" AFTER
2 UPDATE OF "LINE" ON "HR"."T04310_E$REORG" REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW begin
3 if :old.line <= :new.line
4 then
5 raise_application_error('-20001','new values must big than old');
6 end if;
7* end;
Trigger created.
SQL> BEGIN DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT('"HR"', '"T04310_E"', '"T04310_E$REORG"',
2 DBMS_REDEFINITION.CONS_TRIGGER, '"HR"', '"TRG_T04310_E"', '"TRG_T04310_E$REORG"');
3* END;
PL/SQL procedure successfully completed.
SQL> BEGIN
2 DBMS_REDEFINITION.FINISH_REDEF_TABLE('"HR"', '"T04310_E"', '"T04310_E$REORG"');
3* END;
PL/SQL procedure successfully completed
验证结果
SQL> select table_name,partitioning_type,partition_count from dba_part_tables pt
2* where pt.owner='HR' and pt.table_name='T04310_E'
TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT
---------- --------------------- ---------------
T04310_E RANGE 2
5.截断分区表的单个分区
SQL> select count(*) from hr.t04310_a;
COUNT(*)
----------
100000
SQL> alter table hr.t04310_a truncate partition t04310a_p1;
Table truncated.
SQL> select count(*) from hr.t04310_a;
COUNT(*)
----------
90001
二：索引组织表
索引组织表同普通堆表的对比：访问更快，不重复存储，更少的存储空间，使用第二索引，适用于小表
–Have faster key-based access to table data
–Do not duplicate the storage of primary key values
–Require less storage
–Use secondary indexes and log