Oracle9i provides the dbms_redefinition package to implement online redefinition of database tables. In practical application, we can use this package for: (1) Heap table and
Oracle 9i provides the dbms_redefinition package to implement online redefinition of database tables. In practical application, we can use this package for: (1) Heap table and
Oracle 9i provides the dbms_redefinition package to implement online redefinition of database tables. In practical applications, we can use this package:
(1) convert heap tables and partitions. (2) recreate the table to reduce HWM. 10 Gb shrink, 9i if you use move tablespace and rebuild index, the table will be locked during move. If you want to reduce HWM online, you can only use this.
(3) change the table structure online. For example, change column_a and column_ B to column_ B and column_a
1. Create a test table
Create table rebuild_table as select * from dba_objects;
Insert into rebuild_table select * from rebuild_table;
Insert into rebuild_table select * from rebuild_table;
Insert into rebuild_table select * from rebuild_table;
Commit;
Delete from rebuild_table;
Insert into rebuild_table select * from dba_objects;
Insert into rebuild_table select * from rebuild_table;
Insert into rebuild_table select * from rebuild_table;
Commit;
Update rebuild_table set object_id = rownum;
Alter table rebuild_table add CONSTRAINT P_YY primary key (OBJECT_ID );
Select * from rebuild_table;
2. Create a partition table
Create table REBUILD_PA_TABLE
(
STATIS_DATE NUMBER (8 ),
OWNER VARCHAR2 (30 ),
OBJECT_NAME VARCHAR2 (128 ),
SUBOBJECT_NAME VARCHAR2 (30 ),
OBJECT_ID NUMBER not null,
DATA_OBJECT_ID NUMBER,
OBJECT_TYPE VARCHAR2 (18 ),
Created date,
LAST_DDL_TIME DATE,
TIMESTAMP VARCHAR2 (19 ),
STATUS VARCHAR2 (7 ),
TEMPORARY VARCHAR2 (1 ),
GENERATED VARCHAR2 (1 ),
SECONDARY VARCHAR2 (1)
)
Partition by list (STATIS_DATE)
(
Partition p20111031 values (20111031)
);
Alter table REBUILD_PA_TABLE add partition p20111101 values (20111101 );
;
Alter table REBUILD_PA_TABLE
Add constraint Pa_YYY primary key (OBJECT_ID)
;
-- Enable redefinition
Begin
Dbms_redefinition.CAN_REDEF_TABLE ('boc _ rdm', 'rebuild _ table ');
End;
-- Start redefinition
Begin
Dbms_redefinition.START_REDEF_TABLE ('boc _ rdm', 'rebuild _ table', 'rebuild _ PA_TABLE ');
End;
-- Synchronize data
Begin
Dbms_redefinition.sync_interim_table ('boc _ rdm', 'rebuild _ table', 'rebuild _ PA_TABLE ');
End;
-- Rename a table and convert a non-partitioned table to a partitioned table
Begin
Dbms_redefinition.finish_redef_table ('boc _ rdm', 'rebuild _ table', 'rebuild _ PA_TABLE ');
End;
Select * from REBUILD_TABLE partition (p20111031)