[doc]How To Efficiently Drop A Table With Many Extents
http://space.itpub.net/10834762/viewspace-507991
今天drop 了一張3.7T的表,這張表為分割槽本身的資料只有23G,兩個lob欄位和lob index加起來佔到3.7T,下面處理過程:
1.因為表特別大,開始嘗試用以下方式來做:
drop index ...;
truncate table ... reuse storage;
alter table ... deallocate unused keep 2000000m;
alter table ... deallocate unused keep 1500000m;
alter table ... deallocate unused keep 1000000m;
alter table ... deallocate unused keep 500000m;
alter table ... deallocate unused keep 0;
drop table ...;
結果做完truncate table ... reuse storage;之後做alter table ... deallocate
unused keep 2000000m;時一直報
alter table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK deallocate unused
keep 10000M
*
ERROR at line 1:
ORA-03230: segment only contains 77 blocks of unused space above high
water
mark
問了一下oracle,推薦我用下以下方法來做:
1.先truncate所有的subpartition:
truncate table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK subpartition
**;......
2.truncate所有的partition
truncate table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK partition
**;......
3.drop table EAI_CT_BUILD.EA_CROSS_SYS_DATA_TBL_BAK ;
大概在truncate subpartition時花了25分鐘,之後的就快了,drop table時只發了1分29秒.
附:
How To Efficiently Drop A Table With Many Extents | ||||
: | Note:68836.1 | Type: | BULLETIN | |
Last Revision Date: | 12-JUN-2008 | Status: | 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
~~~~~~~~~~~~~~~~~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 TABLEMODIFY LOB ( )
DEALLOCATE UNUSED KEEPM;
SQL> ALTER INDEXDEALLOCATE 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:
11909394G
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/23590362/viewspace-662130/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- How To Efficiently Drop A Table With Many Extents
- How to drop Oracle RAC database manually?OracleDatabase
- drop table和truncate table的區別
- Oracle ASM How many allocation units per fileOracleASM
- How to Prepare Storage for ASM (Doc ID 452924.1)ASM
- flashback drop/query/table/database/archiveDatabaseHive
- oracle 誤刪表 drop tableOracle
- HDU 1213 How Many Tables(並查集)並查集
- Script to Report Extents and Contiguous Free Space (Doc ID 162994.1)
- drop apply INSTANTIATION for one tableAPP
- audit drop table為什麼不行
- Drop table cascade constraintsAI
- HDU 2157 How many ways?? (矩陣快速冪)矩陣
- 動態規劃 hdu 1978 How many ways動態規劃
- How to monitor data transaction on one table
- How to partition a non-partitioned table
- How to Duplicate a Database in NOARCHIVELOG mode (Doc ID 275480.1)DatabaseHive
- Oracle10g 回收站及徹底刪除table : drop table xx purge 以及drop flashOracle
- 深入解析 oracle drop table內部原理Oracle
- DROP TABLE ** CASCADE CONSTRAINTS PURGEAI
- How To Size the Database Smart Flash Cache (Doc ID 1317950.1)Database
- One more way regarding germany niubian how to remain difficult forREMAI
- HDU 3038 How Many Answers Are Wrong (帶權並查集)並查集
- MySQL資料災難挽救之drop tableMySql
- Drop Table Fails With ORA-600 [15264]AI
- Get detailed table(many other objects) structure with dbms_metadataAIObjectStruct
- How To List All The Named Events Set For A Database (Doc ID 436036.1)Database
- How to free space from an ASM diskgroup? (Doc ID 1553744.1)ASM
- Leetcode 1365. How Many Numbers Are Smaller Than the Current Number (cpp)LeetCode
- MySQL 5.6 drop database時,table metadata lock等待MySqlDatabase
- Query against DBA_EXTENTS slow after upgrade to 11.2.0.3 (Doc ID 1453425.1)AI
- How To Drop, Create And Recreate DB Control In A 10g DatabaseDatabase
- How to Collect Diagnostics for Database Hanging Issues (Doc ID 452358.1)Database
- How to Monitor and tune Open and Cached Cursors (Doc ID 1430255.1)
- codeforces 9D How many trees? (組合二叉樹)二叉樹
- Truncate table 詳解及與delete,drop 的區別delete
- Truncate table詳解及與delete,drop的區別delete
- 資料庫審計(create/alter/drop table、user、tablespace)資料庫