ORACLE--TRUNCATE大表操作

How To Efficiently Drop A Table With Many Extents [ID 68836.1]
		修改时间 25-MAR-2010     类型 BULLETIN     状态 PUBLISHED

               How to efficiently drop a table with many extents

PURPOSE
~~~~~~~

    This note describes why a user process can consume large amounts of CPU 
    after dropping a table consisting of many extents, and a potential
    workaround to stop the problem occurring. Essentially the CPU is being
    used to manipulate the extents i.e. moving used extents (uet$) to free
    extents (fet$). In certain circumstances it may be possible to regulate
    this CPU activity.


SCOPE & APPLICATION
~~~~~~~~~~~~~~~~~~~
This article is intended to assist DBAs who may need to drop a table
consisting of many extents.

RELATED DOCUMENTS
~~~~~~~~~~~~~~~~~
Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing

Permanent object cleanup
~~~~~~~~~~~~~~~~~~~~~~~~

   If a permanent object (table) is made up of many extents, and the object is 
   to be dropped, the user process dropping the object will consume large 
   amounts of CPU - this is an inescapable fact. However, with some forethought
   it is possible to mitigate the effects of CPU usage (and hence the knock-on
   effect on other users of system resources) thus:

   1. Identify, but do NOT drop the table
   2. Truncate the table, specifying the REUSE STORAGE clause. This will be 
      quick as extents are not deallocated; the highwater mark is simply 
      adjusted to the segment header block.
   3. Deallocate unused extents from the table, SPECIFYING THE KEEP CLAUSE.
      This is the crux - you can control how many extents are to be deallocated
      by specifying how much (in terms of Kb or Mb) of the table is NOT
      to be deallocated.

   Example:
   o. Table BIGTAB is 2Gb in size and consists of 262144 8Kb extents
   o. There is little CPU power available, and (from past experience) it is
      known that dropping an object of this number of extents can take days
   o. The system is quiet at night times (no other users or batch jobs)
   
   In the above example the table could be dropped in 'phases' over the period
   of a few nights as follows:
   1. Truncate the table, specifying the REUSE STORAGE clause:
      SQL> TRUNCATE TABLE BIGTAB REUSE STORAGE;
   2. If it takes 3 days (72 hours) to drop the table, spread this out over
      6 nights i.e. drop 1/3 Gb per night. This can be achieved in 6 (nightly)
      steps as follows:
      Night 1: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1707M; (2Gb*5/6)
      Night 2: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1365M; (2Gb*4/6)
      Night 3: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 1024M; (2Gb*3/6)
      Night 4: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 683M; (2Gb*2/6)
      Night 5: 
        SQL> ALTER TABLE BIGTAB DEALLOCATE UNUSED KEEP 341M; (2Gb*1/6)
      Night 6: 
        SQL> DROP TABLE BIGTAB;

   The same method can be applied if LOB segments or indexes are involved.

        SQL> ALTER TABLE  MODIFY LOB ()
             DEALLOCATE UNUSED KEEP M;
 
        SQL> ALTER INDEX  DEALLOCATE UNUSED KEEP M;
 

Caveats
~~~~~~~

   o. If you have inadvertently tried to drop the table, this method will
      not work. This is because the drop will first convert the segment to
      a temporary segment, and only then start cleaning up the now temporary
      segment's extents. Thus, if the drop is interrupted, the temporary
      segment will now be cleaned up by SMON.
   
   o. This method will only work for table, lob and index segment types.

   o. This method will not work for segments bigger than 4gb in size due to
      unpublished bug:
      1190939 -- ORA-3277 WHEN ISSUING AN ALTER TABLE DEALLOCATE UNUSED > 4G (fixed in 10g and higher)

-------------------------------------------------------------------------------
  实际测试:

表名:TCL_COMP_ACC  blocks:16358144 extents:3251 bytes/1024/1024:127798MB
索引: PK_COMP_ACC  blocks:19720800 extents:4443 bytes/1024/1024:154068.75MB
 
只想truncate表，重建索引,故开始未DROP INDEX
直接进行truncate操作:
TRUNCATE TABLE KS38.TCL_COMP_ACC  reuse storage; 耗时: 547秒
ALTER TABLE KS38.TCL_DONE_STAT  DEALLOCATE UNUSED KEEP 1250000M;
ALTER TABLE KS38.TCL_DONE_STAT  DEALLOCATE UNUSED KEEP 1200000M;
ALTER TABLE KS38.TCL_DONE_STAT  DEALLOCATE UNUSED KEEP 1100000M;
ALTER TABLE KS38.TCL_DONE_STAT  DEALLOCATE UNUSED KEEP 1000000M;
..............
 多次执行，直到
ALTER TABLE KS38.TCL_DONE_STAT  DEALLOCATE UNUSED KEEP 1000M;
 
做好后，继续:
ALTER INDEX KS38.PK_COMP_ACC  REBUILD 
 
执行完毕后，结果:
表名:TCL_COMP_ACC  blocks: 128024   extents:126    bytes/1024/1024:1000.1875MB
索引: PK_COMP_ACC   blocks:16384 extents:25 bytes/1024/1024:128MB
 
整个过程耗时10分钟左右