How to Re-Organize a Table Online (Doc ID 177407.1)
Checked for relevance on 06-May-2009
PURPOSE
Prior to Oracle9i, any table re-organizations and redefinitions forced the
table to be offlined for the duration of the operation.
From Oracle9i forward, table re-organizations and redefinitions can be performed
online.
This note explains how to reorganize a table online.
NOTE: This note does not cover the use of row ids. As of 9.2.0.5,
additional parameters were added to the DBMS_REDEFINITION
packages which allow redefinition to use a row id instead
of a primary key.
SCOPE & APPLICATION
Oracle8, Oracle8i, and Oracle9i.
How to Re-Organize a Table Online:
==================================
What can be Redefined ONLINE on a Table?
----------------------------------------
=> A non-partitioned table can be converted into a partitioned table, and
vice versa
=> The organization of a table can be changed from a heap based to IOTs (Index
Organized Tables), and vice versa
=> Non-primary key columns can be dropped
=> New columns can be added to a table
=> Existing columns can be renamed
=> Parallel support can be added or removed
=> Storage parameters can be modified
Renaming columns is possible without using Online Redefinition in RDBMS version
9.2 with the new RENAME COLUMN clause of the ALTER TABLE statement.
Restrictions
------------
The table to be re-organized:
* Must have a primary key (restriction should have been lifted in 9.2.0.5. It is possible that there is still a problem with this.)
* Cannot have User-defined data types
* Cannot have FILE or LONG columns
* Cannot be clustered
* Cannot be in the SYS or SYSTEM schema
* Cannot have materialized view logs and/or
materialized views defined on them
* Cannot be an horizontal subsetting of data
* Must be re-organized within the same schema
* Looses its snapshot logs
* Can get new columns as part of the re-organization, but the
new columns must be declared NULL until the re-organization
is complete
Process
-------
------ 5' Table INT_EMP becomes EMP -------
| and vice-versa |
1 2
Source Table 3 Create interim Table
EMP ---> Start redefinition ---> INT_EMP
3' data Source to Target ^ ^
^ / |
| / |
| / 4
| / Create constraints
/ Create indexes
3'' / Create triggers
DML on source table /
updates/deletes/inserts 5 /Finish redefinition
stored into MLOG$_EMP ------
Step 1: Verify that the source table is able to undergo an ONLINE
redefinition using the dbms_redefinition.can_redef_table
procedure.
Executing the following should let you know if the table is eligible for online redefinition:
BEGIN Dbms_Redefinition.Can_Redef_Table('USER','TABLE NAME'); END;
If the table is not eligible it will fail with with errors.
Step 2: Create an empty interim table reflecting the final
structure
Step 3: Start the redefinition of the source table using the
dbms_redefinition.start_redef_table procedure, defining:
-> the source table to be reorganized
-> the interim table
-> the columns mapping for each column undergoing a change
The start_redef_table procedure automatically:
-> inserts all rows from the source table into the
interim table
-> creates a snapshot table MLOG$_EMP and a snapshot log
to store DML changes temporarily until the final step
of the redefinition
Step 4: Create the constraints, indexes and triggers on the interim
table as they are defined on the source table.
Any referential constraint involving the interim table
should be created disabled.
Step 5: Finish the redefinition of the source table using the
dbms_redefinition.finish_redef_table procedure.
The finish_redef_table procedure automatically:
-> applies all DML changes stored in the snapshot table
into the interim table
-> exchanges the names of the 2 tables:
the interim table becomes the source table and vice versa
Be aware that the names of the constraints, indexes, and
triggers do not have the names they had on the source table.
Step 6: Drop the interim table
Step 7: From RDBMS version 9.2, the constraints and indexes can be
renamed with the ALTER TABLE ... RENAME CONSTRAINT ...
statement.
In RDBMS version 8.x, and 9.0.x, if you want the restructured
table to have the initial names for the constraints and indexes,
you have to rebuild them, and for the triggers, you have
to recreate them.
Other Useful Procedures
-----------------------
a. Intermediate synchronization
----------------------------
It is recommended to periodically synchronize the interim table with the source
one when a large amount of DML is executed on the table while the
re-organization is taking place by calling the
dbms_redefinition.sync_interim_table() procedure.
Calling this procedure reduces the time taken by
dbms_redefinition.finish_redef_table() to complete the re-organization process.
The small amount of time that the original table is locked during
dbms_redefinition.finish_redef_table() is independent of whether
dbms_redefinition.sync_interim_table() has been called.
b. Abort and cleanup after errors
------------------------------
The user can call dbms_redefinition.abort_redef_table() in the event that an
error is raised during the re-organization process or the user wants to abort
the re-organization process. This procedure allows to user to specify if the
interim table should be dropped as part of the cleanup operations.
Example
-------
The example below re-organizes the EMP table (assumes use of primary key):
--> whose current structure is:
SQL> desc emp
Name Null? Type
------------------------------------- -------- ---------------
EMPNO NOT NULL NUMBER
NAME VARCHAR2(20)
DEPTNO NUMBER
--> with the following constraints:
EMP_PK : a primary key with a UNIQUE index EMP_PK
EMP_FK : a foreign key referencing the DEPT_PK (PK of the DEPT table)
--> with the following indexes:
EMP_PK on the primary key column EMPNO
I_EMP_DEPTNO non-unique index on column DEPTNO
--> with the following trigger:
T_EMP that increments a counter in the AUDIT_EMP table by 1 each time an
INSERT/UPDATE/DELETE occurs on a row in the EMP table
--> The table contains 100 000 rows before the re-organization:
SQL> select count(*) from emp;
COUNT(*)
----------
100000
--> The counter in the audit_emp table corresponds to the number
of rows in the emp table:
SQL> select * from audit_emp;
C
----------
100000
----------------------------------------------------------------
1. Determine if the table is a candidate for online re-organization
----------------------------------------------------------------
SQL> connect sys/x as sysdba
Connected.
SQL> grant execute on dbms_redefinition to test;
Grant succeeded.
SQL> connect test/test
Connected.
SQL> exec dbms_redefinition.can_redef_table('TEST', 'EMP');
BEGIN dbms_redefinition.can_redef_table('TEST', 'EMP'); END;
*
ERROR at line 1:
ORA-12089: cannot online redefine table "TEST"."EMP" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1
A primary key is mandatory since materialized views and logs are
created during the start of redefinition.
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
SQL> execute DBMS_REDEFINITION.CAN_REDEF_TABLE('TEST','EMP');
PL/SQL procedure successfully completed.
------------------------------------------------------------
2. Create an empty interim table reflecting the final structure
------------------------------------------------------------
For example, the EMP table undergoes the following transformations:
* non partitioned --> partitioned
* column NAME --> LAST_NAME
* a new colum SAL is added
SQL> create table int_emp(empno number, last_name varchar2(20),
2 deptno number, sal number)
3 partition by list (deptno)
4 (partition p10 values (10),
5 partition p20 values (20),
6 partition p30 values (30),
7 partition p40 values (40));
SQL> select object_name,object_type,status,object_id,data_object_id
2 from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS OBJECT_ID DATA_OBJECT_ID
------------ ------------------ ------- ---------- --------------
AUDIT_EMP TABLE VALID 5806 5806
DEPT TABLE VALID 5793 5793
DEPT_PK INDEX VALID 5794 5794
EMP TABLE VALID 5803 5803
EMP_PK INDEX VALID 5808 5808
I_EMP_DEPTNO INDEX VALID 5805 5805
T_EMP TRIGGER VALID 5807
INT_EMP TABLE PARTITION VALID 5831 5831
INT_EMP TABLE PARTITION VALID 5832 5832
INT_EMP TABLE PARTITION VALID 5833 5833
INT_EMP TABLE PARTITION VALID 5834 5834
INT_EMP TABLE VALID 5830
---------------------------------
3. Start the re-organization process
---------------------------------
SQL> execute SYS.DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'name last_name');
BEGIN SYS.DBMS_REDEFINITION.START_REDEF_TABLE('TEST', ;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
The user performing the re-organization requires the following
privileges:
* CREATE ANY TABLE
* ALTER ANY TABLE
* DROP ANY TABLE
* LOCK ANY TABLE
* SELECT ANY TABLE
* CREATE ANY INDEX
* CREATE ANY TRIGGER
SQL> execute DBMS_REDEFINITION.START_REDEF_TABLE('TEST', -
> 'EMP', -
> 'INT_EMP', -
> 'empno empno, name last_name, deptno deptno');
PL/SQL procedure successfully completed.
SQL> select sql_text from v$sqlarea where sql_text like '%INT_EMP%';
SQL_TEXT
--------------------------------------------------------------------------------
truncate table "TEST"."INT_EMP" purge snapshot log
DELETE FROM "TEST"."INT_EMP" SNAP$ WHERE "EMPNO" = :1
INSERT INTO "TEST"."INT_EMP" ("EMPNO","LAST_NAME","DEPTNO") VALUES (:1,:2,:3)
UPDATE "TEST"."INT_EMP"
SET "EMPNO" = :1,"LAST_NAME" = :2,"DEPTNO" = :3 WHERE "EMPNO" = :1
INSERT INTO "TEST"."INT_EMP"("EMPNO","LAST_NAME","DEPTNO")
SELECT "EMP"."EMPNO","EMP"."NAME","EMP"."DEPTNO"
FROM "TEST"."EMP" "EMP"
INSERT /*+ APPEND */ INTO "TEST"."INT_EMP"
("EMPNO","LAST_NAME","DEPTNO")
SELECT "EMP"."EMPNO","EMP"."NAME","EMP"."DEPTNO"
FROM "TEST"."EMP" "EMP"
BEGIN DBMS_REDEFINITION.START_REDEF_TABLE('TEST','EMP','INT_EMP',
'empno empno, name last_name, deptno deptno'); END;
SQL> select object_name, object_type, status, object_id, data_object_id
2 from user_objects order by 4;
OBJECT_NAME OBJECT_TYPE STATUS OBJECT_ID DATA_OBJECT_ID
------------ ------------------ ------- ---------- --------------
DEPT TABLE VALID 5793 5793
DEPT_PK INDEX VALID 5794 5794
EMP TABLE VALID 5803 5803
I_EMP_DEPTNO INDEX VALID 5805 5805
AUDIT_EMP TABLE VALID 5806 5806
T_EMP TRIGGER VALID 5807
EMP_PK INDEX VALID 5808 5808
INT_EMP TABLE VALID 5830
INT_EMP TABLE PARTITION VALID 5831 5857
INT_EMP TABLE PARTITION VALID 5832 5858
INT_EMP TABLE PARTITION VALID 5833 5859
INT_EMP TABLE PARTITION VALID 5834 5860
MLOG$_EMP TABLE VALID 5855 5855
RUPD$_EMP TABLE VALID 5856
14 rows selected.
=> 2 tables are created:
--> a permanent table MLOG$_EMP which is a snapshot log on EMP
to store all DML performed on EMP table once the
START_REDEF_TABLE is launched
--> a temporary table RUPD$_EMP (of SESSION duration)
SQL> select count(*) from int_emp;
COUNT(*)
----------
100000
SQL> select count(*) from mlog$_emp;
COUNT(*)
----------
0
SQL> select count(*) from rupd$_emp;
COUNT(*)
----------
0
SQL> select count(*) from emp;
COUNT(*)
----------
100000
SQL> select count(*) from audit_emp;
COUNT(*)
----------
100000
SQL> select master,log_table from user_mview_logs;
MASTER LOG_TABLE
------------------------------ ------------------------------
EMP MLOG$_EMP
SQL> select mview_name,container_name, build_mode
2 from user_mviews;
MVIEW_NAME CONTAINER_NAME BUILD_MOD
--------------------- ------------------------ ---------
INT_EMP INT_EMP PREBUILT
SQL> select query from user_mviews;
QUERY
-------------------------------------------------------------------
select empno empno, name last_name, deptno deptno from "TEST"."EMP"
---------------------------------------
4. Create constraints on the interim table
---------------------------------------
SQL> alter table int_emp
2 add constraint int_emp_pk primary key(empno);
Table altered.
SQL> alter table int_emp add constraint
2 int_emp_fk foreign key(deptno) references dept(deptno);
Table altered.
SQL> alter table int_emp MODIFY CONSTRAINT int_emp_fk
2 DISABLE KEEP INDEX;
SQL> select constraint_name,constraint_type, status
2 from user_constraints;
CONSTRAINT_NAME C STATUS
------------------------------ - --------
DEPT_PK P ENABLED
EMP_PK P ENABLED
EMP_FK R ENABLED
INT_EMP_PK P ENABLED
INT_EMP_FK R DISABLED
---------------------------------------
Create triggers on the interim table
---------------------------------------
We deliberately create a trigger which increments the C column
by 2 instead of 1 to show that after the re-organization completion
the appropriate triggge is used.
SQL> create or replace trigger t_int_emp
2 before insert or update or delete on int_emp
3 for each row
4 declare
5 PRAGMA AUTONOMOUS_TRANSACTION;
6 begin
7 update audit_emp set c=c+2;
8 commit;
9 end;
10 /
Trigger created.
SQL> select trigger_name, status from user_triggers;
TRIGGER_NAME STATUS
------------------------------ --------
T_EMP ENABLED
T_INT_EMP ENABLED
-----------------------------------------
5. Simulate DML activity on the source table
-----------------------------------------
SQL> delete emp where empno=1;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
99999
SQL> select count(*) from int_emp;
COUNT(*)
----------
100000
SQL> select count(*) from mlog$_emp;
COUNT(*)
----------
1
SQL> select * from audit_emp;
C
----------
100001
------------------------------------------------------
6. You can resync the Interim table with the Source table
------------------------------------------------------
SQL> execute DBMS_REDEFINITION.SYNC_INTERIM_TABLE('TEST', -
> 'EMP', 'INT_EMP');
PL/SQL procedure successfully completed.
SQL> select count(*) from emp;
COUNT(*)
----------
99999
SQL> select count(*) from int_emp;
COUNT(*)
----------
99999
SQL> select count(*) from mlog$_emp;
COUNT(*)
----------
0
SQL> select * from audit_emp;
C
----------
100001
-------------------------------------
7. Grant privileges on the interim table
-------------------------------------
SQL> select * from USER_TAB_PRIVS_MADE;
GRANTEE TABLE_NAME GRANTOR PRIVILEGE GRA HIE
---------- ----------- --------- ---------- --- ---
SCOTT EMP TEST SELECT NO NO
SCOTT EMP TEST UPDATE NO NO
SQL> grant select, update on INT_EMP to SCOTT;
Grant succeeded.
----------------------------------
8. Finish the re-organization process
----------------------------------
SQL> execute DBMS_REDEFINITION.FINISH_REDEF_TABLE('TEST',-
> 'EMP', 'INT_EMP');
PL/SQL procedure successfully completed.
SQL> delete from emp where empno=2;
1 row deleted.
SQL> commit;
Commit complete.
SQL> select * from audit_emp;
C
----------
100003
The new trigger is used: 100001 is incremented by 2.
SQL> desc emp
Name Null? Type
------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
LAST_NAME VARCHAR2(20)
DEPTNO NUMBER
SAL NUMBER
----------------------------------
9. Clean up
----------------------------------
SQL> drop table int_emp;
Table dropped.
-- If you receive an ORA-02449 error while trying to drop the interim table, then
issue:
SQL> drop table int_emp cascade constraints;
Table dropped.
----------------------------------
10. Rename the constraints
----------------------------------
In RDBMS version 9.2, you can rename the constraints:
SQL> alter table emp rename constraint INT_EMP_PK to EMP_PK ;
Table altered.
SQL> alter table emp rename constraint INT_EMP_FK to EMP_FK;
Table altered.
SQL> select constraint_name, index_name, status from user_constraints;
CONSTRAINT_NAME INDEX_NAME STATUS
------------------------ --------------------------- --------
EMP_PK INT_EMP_PK ENABLED
EMP_FK ENABLED
Be aware that the index associated to the primary key keeps the former name.
Before RDBMS version 9.2, if you want to rename the constraints, drop and
recreate them:
SQL> alter table emp drop constraint int_emp_pk;
Table altered.
SQL> alter table emp add constraint emp_pk primary key (empno);
Table altered.
SQL> alter table emp drop constraint int_emp_fk;
Table altered.
SQL> alter table emp add constraint emp_fk
2 foreign key (deptno) references dept (deptno);
Table altered.
Note
----
Once the START_REDEF_TABLE procedure is started, you can interrumpt the process
using the following procedure:
SQL> execute dbms_redefinition.abort_redef_table('TEST','EMP','INT_EMP');
PL/SQL procedure successfully completed.
====================================================================
Maintenance operations causing index partitions to become INDEX UNUSABLE (IU):
1. IMPORT PARTITION or conventional path SQL*Loader.
2. Direct-path SQL*Loader leaves affected local index partitions and global indexes in an IU state if it does not complete successfully.
3. Partition maintenance operations like ALTER TABLE MOVE PARTITION.
4. Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION.
5. Partition maintenance operations like ALTER TABLE SPLIT PARTITION.
6. Index maintenance operations like ALTER INDEX SPLIT PARTITION.
7. Adding a Partition to a Hash-Partitioned Table
Adding a Partition to a Hash-Partitioned Table
When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.
The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.
==============================================================
FAQ: Row Movement Common Questions and Problems (Doc ID 1518567.1)
Information in this document applies to any platform.
SEGMENT SHRINK and Details. (Doc ID 242090.1)
1. IMPORT PARTITION or conventional path SQL*Loader.
2. Direct-path SQL*Loader leaves affected local index partitions and global indexes in an IU state if it does not complete successfully.
3. Partition maintenance operations like ALTER TABLE MOVE PARTITION.
4. Partition maintenance operations like ALTER TABLE TRUNCATE PARTITION.
5. Partition maintenance operations like ALTER TABLE SPLIT PARTITION.
6. Index maintenance operations like ALTER INDEX SPLIT PARTITION.
7. Adding a Partition to a Hash-Partitioned Table
Adding a Partition to a Hash-Partitioned Table
When you add a partition to a hash-partitioned table, the database populates the new partition with rows rehashed from an existing partition (selected by the database) as determined by the hash function. Consequently, if the table contains data, then it may take some time to add a hash partition.
The following statements show two ways of adding a hash partition to table scubagear. Choosing the first statement adds a new hash partition whose partition name is system generated, and which is placed in the default tablespace. The second statement also adds a new hash partition, but that partition is explicitly named p_named and is created in tablespace gear5.
==============================================================
FAQ: Row Movement Common Questions and Problems (Doc ID 1518567.1)
APPLIES TO:
Oracle Database - Enterprise Edition - Version 8.1.6.0 and laterInformation in this document applies to any platform.
PURPOSE
This FAQ documents answers some of the common questions and problems in Row Movements.
QUESTIONS AND ANSWERS
1. What is Row Movement?
The row movement lets you specify whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data.
For more information, have a look at: row_movement_clause
For more information, have a look at: row_movement_clause
2. How to enable row movement?
Use the option below with ALTER TABLE statement
{ ENABLE | DISABLE } ROW MOVEMENT
{ ENABLE | DISABLE } ROW MOVEMENT
Default value is disable. For complete syntax, go to "ALTER TABLE" in Oracle Database SQL Language Reference (11.2)
3. Concepts / how it works internally?
When enable row movement was not set a rowid was assigned when you inserted a row and that never changed. After row movement is enabled a simple update could change your rowid.
When you "Enable Row Movement", you are just giving "Permission" to move a row.
You do not need to delete and insert, you just update the row, and that causes delete and insert, and in turn changes the ROWID.
So, delete and insert is the implicit operation which happens in the background.
Row movement is enabled at the table level, and is disabled by default.
When you "Enable Row Movement", you are just giving "Permission" to move a row.
You do not need to delete and insert, you just update the row, and that causes delete and insert, and in turn changes the ROWID.
So, delete and insert is the implicit operation which happens in the background.
Row movement is enabled at the table level, and is disabled by default.
4. Is it only for partitioned tables?
No, Row Movement is associated with partitioned as well as non-partitioned tables.
It is allowed for non-partitioned tables starting 9.2. It comes in to affect for non-partitioned tables when operations like table compression is performed.
It is allowed for non-partitioned tables starting 9.2. It comes in to affect for non-partitioned tables when operations like table compression is performed.
5. Does it invalidate index?
No, it does not invalidate indexes
6. Does it create row chaining?
Row Movement will not cause Row Chaining. If the row doesn’t fit into a single data block, it must be chained. Row Chaining basically is the distribution of a single table row across multiple data blocks.
Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation.
Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation.
7. Does it have impact on performance?
Yes, it can cause performance problem as it can cause Oracle to move rows to discontinuous data blocks.
There could be some performance impact as it will necessarily consume processing resources on your machine while running.
The reason being it will:
- read the table
- delete/insert the rows at the bottom of the table to move them up
- generate redo
- generate undo
There could be some performance impact as it will necessarily consume processing resources on your machine while running.
The reason being it will:
- read the table
- delete/insert the rows at the bottom of the table to move them up
- generate redo
- generate undo
8. What are restrictions associated with "Enable Row Movement"?
You cannot specify this clause for a nonpartitioned index-organized table.
Tables need to be in an ASSM (Automatic Segment Space Management) tablespace for this to work.
Tables need to be in an ASSM (Automatic Segment Space Management) tablespace for this to work.
9. Related Errors
ORA-14402: updating partition key column would cause a partition change
You get this error during UPDATE of a row if row movement is not enabled on the partitioned table, and the row with the new partitioned key value would need to be placed into a a different partition compared where the row before update is. See Document 236191.1 for more details.
You get this error during UPDATE of a row if row movement is not enabled on the partitioned table, and the row with the new partitioned key value would need to be placed into a a different partition compared where the row before update is. See Document 236191.1 for more details.
ORA-10636: ROW MOVEMENT is not enabled
Document 1132163.1 What is the Meaning of SHRINK SPACE CHECK?
ORA-08189: cannot flashback the table because row movement is not enabled
Document 270060.1 Use Flashback Table Feature and Resolve errors
Document 287698.1 OERR: ORA-8189 cannot flashback the table because row movement is not enabled
ORA-29887: cannot support row movement if domain index defined on table
Look at the documentation e.g. for 11.2
ORA-14661: row movement must be enabled
Look at the documentation e.g. for 11.2
ORA-14662: row movement cannot be enabled
Look at the documentation e.g. for 11.2
Document 1132163.1 What is the Meaning of SHRINK SPACE CHECK?
ORA-08189: cannot flashback the table because row movement is not enabled
Document 270060.1 Use Flashback Table Feature and Resolve errors
Document 287698.1 OERR: ORA-8189 cannot flashback the table because row movement is not enabled
ORA-29887: cannot support row movement if domain index defined on table
Look at the documentation e.g. for 11.2
ORA-14661: row movement must be enabled
Look at the documentation e.g. for 11.2
ORA-14662: row movement cannot be enabled
Look at the documentation e.g. for 11.2
===========================================================
SEGMENT SHRINK and Details. (Doc ID 242090.1)
PURPOSE
========
In Oracle10g and onwards, we have the option to shrink a segment, which will help DBAs to manage the space in better way.
This feature also help for better performance for query.
SCOPE & APPLICATION
===================
Information on new Oracle10g feature.
Oracle 10g Segment shrink
=========================
Mandatory
=========
Init.ora parameter 'Compatible' must be >=10.0
Shrink operations can be performed only on segments in locally managed
tablespaces with automatic segment space management (ASSM).
How it works
============
1. Enable row movement for the table.
SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;
2. Shrink table but don't want to shrink HWM (High Water Mark).
SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;
3. Shrink table and HWM too.
SQL> ALTER TABLE scott.emp SHRINK SPACE;
4. Shrink table and all dependent index too.
SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;
5. Shrink table under MView.
SQL> ALTER TABLE SHRINK SPACE;
6. Shrink Index only.
SQL> ALTER INDEX SHRINK SPACE;
Restrictions on the shrink_clause, 10gR1
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any
object with a LONG column.
2. Segment shrink is not supported for LOB segments even if CASCADE is
specified.
3. Segment shrink is not supported for tables with function-based indexes.
4. This clause does not shrink mapping tables or overflow segments of
index-organized tables, even if you specify CASCADE.
5. You cannot shrink a table that is the master table of an ON COMMIT
materialized view. Rowid materialized views must be rebuilt after the
shrink operation.
6. Table with a domain index is not supported.
Restrictions on the shrink_clause, 10gR2
========================================
1. You cannot specify this clause for a cluster, a clustered table, or any
object with a LONG column.
2. Segment shrink is not supported for tables with function-based indexes or
bitmap join indexes.
3. This clause does not shrink mapping tables of index-organized tables,
even if you specify CASCADE.
4. You cannot specify this clause for a compressed table.
5. You cannot shrink a table that is the master table of an ON COMMIT
materialized view. Rowid materialized views must be rebuilt after the
shrink operation.
6. Table with a domain index is not supported.
Restrictions on the shrink_clause, 11gR1
========================================
1. You cannot combine this clause with any other clauses in the same ALTER TABLE
statement.
2. You cannot specify this clause for a cluster, a clustered table, or any
object with a LONG column.
3. Segment shrink is not supported for tables with function-based indexes or
bitmap join indexes.
4. This clause does not shrink mapping tables of index-organized tables, even if
you specify CASCADE.
5. You cannot specify this clause for a compressed table.
6. You cannot shrink a table that is the master table of an ON COMMIT
materialized view. Rowid materialized views must be rebuilt after the shrink
operation.
Query/DML Concurrency
=======================
The online phase of segment shrink is done with DML-compatible locks. Hence DMLs
can coexist during this phase. During the space-release/HWM adjustment phase,
incompatible locks will be acquired on the table, hence, DMLs will block on
shrink.
There are no user visible errors that shrink will cause on DMLs.
Queries cache the segment HWM. Oracle guarantees that the HWM always moves forward,
hence CR (consistent read) is not required on segment header and extent map blocks. The only operations
that cause the segment HWM to move backward are drop and truncate.
We allow queries to coexist with drop/truncate DDLs since queries do not acquire locks.
If after the drop/truncate, the space gets reused in some other segment, then the
queries get "8103 - object does not exist" external error message.
During segment shrink, the segment’s incarnation number is changed in the bitmap
blocks and segment header when the segment HWM is adjusted. Subsequent data block
changes happen at this newer incarnation number.
Queries that span this phase can die with an external error "10632 - invalid rowid"
if
1) They read the updated bitmap blocks (that have new inc#). Note that this failure
happens if the space is not reused
2) The space got reused for some other object or the same object.
Limitations on Online Segment Shrink, 10gR2:
============================================
Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:
- IOT mapping tables
- Tables with rowid based materialized views
- Tables with function-based indexes
Dependency Maintenance and Restrictions on Shrink
=================================================
The only dependency that will be taken care of during segment shrink is the index.
The indexes will not be in an unusable state after shrink.
The compaction phase of segment shrink will be done as insert/delete pairs. The
DML triggers will not be fired during data movement phase. Since the data does
not change, it is not required to fire the triggers.
ROWID based triggers should be disabled before issuing a shrink since it will not
fire during shrink.
Segment shrink cannot be done on objects with on-commit materialized views.
Materialized views that are based on primary key need not be refreshed or rebuilt
after shrink.
However, it is the DBA’s responsibility to refresh/rebuild the materialized views
that are rowid based.
Availability
============
Segment shrink is done online, thereby it increases the availability of the object.
While conventional DML operations can coexist with segment shrink, parallel DMLs
cannot.
During segment shrink, data will be moved as part of the compaction phase. During
compaction locks will be held on individual rows and/or blocks containing the data.
This will cause the concurrent DMLs like updates and deletes to serialize on the
locks. The compaction will be done in units of smaller transactions, so the availability
of the object will not be impacted significantly.
However during certain phases of segment shrink (when the HWM is adjusted), the segment
will have to be locked in exclusive mode.
This phase is for a very short duration and should impact the availability of the
object less significantly.
Security
========
The privileges required to execute segment shrink on an object will be the same
as that for ALTER object.
Detail Example
===============
SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 1
FS3 Blocks = 1
FS4 Blocks = 3
Full Blocks = 0
PL/SQL procedure successfully completed.
SQL> alter table t_shrink shrink space compact;
alter table t_shrink shrink space compact
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
SQL> ALTER TABLE t_shrink ENABLE ROW MOVEMENT;
Table altered.
SQL> ALTER TABLE t_shrink SHRINK SPACE COMPACT;
Table altered.
SQL> set serveroutput on
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 2
Full Blocks = 1
PL/SQL procedure successfully completed.
SQL> ALTER TABLE t_shrink SHRINK SPACE;
Table altered.
SQL> declare
2 v_unformatted_blocks number;
3 v_unformatted_bytes number;
4 v_fs1_blocks number;
5 v_fs1_bytes number;
6 v_fs2_blocks number;
7 v_fs2_bytes number;
8 v_fs3_blocks number;
9 v_fs3_bytes number;
10 v_fs4_blocks number;
11 v_fs4_bytes number;
12 v_full_blocks number;
13 v_full_bytes number;
14 begin
15 dbms_space.space_usage ('SYSTEM', 'T_SHRINK', 'TABLE', v_unformatted_blocks,
16 v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
17 v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
18 dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
19 dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
20 dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
21 dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
22 dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
23 dbms_output.put_line('Full Blocks = '||v_full_blocks);
24 end;
25 /
Unformatted Blocks = 0
FS1 Blocks = 0
FS2 Blocks = 0
FS3 Blocks = 0
FS4 Blocks = 1
Full Blocks = 1
PL/SQL procedure successfully completed.
SQL>
========
To monitor the progress of a long running SHRINK operation you may execute the pl/sql block in this note. You should see a change in the number of blocks.
For specific details on the output of the pl/sql blocks in this Note please reference the following documentation:
For 9i:
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_space2.htm#1002701
For 10gR2:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#sthref6026
For 11gR1:
http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28419/d_space.htm#sthref7301
NOTE:
=====
Shrinking space of a large segment can take a lot of time, e.g. tens of hours, and can generate lots of redo.
Therefore, it is not advised to interrupt a shrink statement to prevent a possibly massive rollback.
Best approach would be:
1. Initially, only use:
SQL> alter table shrink space compact;
This also takes long but should have little impact because of only short dml locks
2. Only when convenient, issue
SQL> alter table shrink space;
Before running in production, it is advised to test for possible impact in your test environment.
Comments
Post a Comment
Oracle DBA Information