Oracle 表碎片
Oracle 表碎片
資料庫在日常使用過程中，不斷的insert，delete，update操作，導致表和索引出現碎片是在所難免的事情，
碎片多了，sql的執行效率自然就差了，道理很簡單，高水位線（HWL）下的許多資料塊都是無資料的，
但全表掃描的時候要掃描到高水位線的資料塊，也就是說oracle要做許多的無用功！因此oracle提供了shrink space碎片整理功能。
對於索引，可以採取rebuild online的方式進行碎片整理，一般來說，經常進行DML操作的物件DBA要定期進行維護，同時注意要及時更新統計資訊！
一：準備測試資料，使用SCOTT使用者，建立T1表，插入約30W的資料，並根據object_id建立普通索引，表佔儲存空間34M左右，索引佔6M左右的儲存空間
SQL> conn /as sysdba
已連線。
SQL> select default_tablespace from dba_users where username='SCOTT';
DEFAULT_TABLESPACE
------------------------------------------------------------
USERS
SQL> conn SCOTT/tiger
已連線。
SQL> create table t1 (object_id number(10), object_name varchar(25));
Table created.
SQL> insert into t1 values ('1', 'user');
1 row created.
SQL> insert into t1 values ('2', 'test');
1 row created.
SQL> insert into t1 select * from t1;
SQL> commit;
SQL> create index idx_t1_id on t1(object_id);
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> select count(1) from t1;
COUNT(1)
----------
299248
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
34.0625
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
SUM(BYTES)/1024/1024
--------------------
6
二：估算表在高水位線下還有多少空間可用，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少!
DBMS_STATS包無法獲取EMPTY_BLOCKS統計資訊，所以需要用analyze命令再收集一次統計資訊
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4302 0 299248
SQL> analyze table t1 compute statistics;
表已分析。
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4302 50 299248
SQL> col table_name for a20
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 5.07086182
三: 檢視執行計劃，全表掃描大概需要消耗CPU 1175
SQL> explain plan for select * from t1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 299K| 28M| 1175 (1)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T1 | 299K| 28M| 1175 (1)| 00:00:15 |
--------------------------------------------------------------------------
四：刪除大部分資料，收集統計資訊，全表掃描依然需要消耗CPU 1168
SQL> delete from t1 where object_id>100;
已刪除298852行。
SQL> commit;
提交完成。
SQL> select count(*) from t1;
COUNT(*)
----------
396
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4302 50 396
SQL> explain plan for select * from t1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 29700 | 1168 (1)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 1168 (1)| 00:00:15 |
--------------------------------------------------------------------------
五：估算表在高水位線下還有多少空間是無資料的，但在全表掃描時又需要做無用功的資料
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 33.5791626
六：對錶進行碎片整理，重新收集統計資訊
SQL> alter table t1 enable row movement;
表已更改。
SQL> alter table t1 shrink space cascade;
表已更改。
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
.125
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
SUM(BYTES)/1024/1024
--------------------
.0625
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 33.5791626
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
這個時候，只剩下0.1M的無用功了，執行計劃中，全表掃描也只需要消耗CPU 3
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 .010738373
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 29700 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
總共只有5個塊，空塊卻有50個，明顯empty_blocks資訊過期
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
5 50 396
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
5 3 396
檢視Oracle碎片比較多的表
select t.table_name,
avg_row_len * num_rows / (1 - pct_free / 100) / 8192 actual_block,
alloc_block,
1 - ((avg_row_len * num_rows / (1 - pct_free / 100) / 8192) / alloc_block) percen,
'alter table '||t.table_name||'enable row movement'||';' enable_row,
'alter table '||t.table_name||'shrink space'||';' ddl_shrink
from (select segment_name, sum(blocks) alloc_block
from user_extents
having sum(blocks) > 8
group by segment_name) a,
user_tables t
where a.segment_name = t.table_name
and num_rows is not null
and num_rows > 0
order by 4 desc;
SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) "WASTE_PER(%)",
ROUND(BYTES/1024/1024, 2) "Total(M)", NUM_ROWS,
BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME
FROM
(SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,SYS.DBA_TABLES B,SYS.TS$ C
WHERE A.OWNER =B.OWNER
and SEGMENT_NAME = TABLE_NAME
and SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME
UNION ALL
SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,SYS.DBA_TAB_PARTITIONS B,SYS.TS$ C,SYS.DBA_TABLES D
WHERE A.OWNER = B.TABLE_OWNER
and SEGMENT_NAME = B.TABLE_NAME
and SEGMENT_TYPE = 'TABLE PARTITION'
AND B.TABLESPACE_NAME = C.NAME
AND D.OWNER = B.TABLE_OWNER
AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME),
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES) MAX_FREE_SPACE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND BLOCKS > 128
AND OWNER in (user_name1,user_name2)
ORDER BY 1 ASC, 4 DESC;
資料庫在日常使用過程中，不斷的insert，delete，update操作，導致表和索引出現碎片是在所難免的事情，
碎片多了，sql的執行效率自然就差了，道理很簡單，高水位線（HWL）下的許多資料塊都是無資料的，
但全表掃描的時候要掃描到高水位線的資料塊，也就是說oracle要做許多的無用功！因此oracle提供了shrink space碎片整理功能。
對於索引，可以採取rebuild online的方式進行碎片整理，一般來說，經常進行DML操作的物件DBA要定期進行維護，同時注意要及時更新統計資訊！
一：準備測試資料，使用SCOTT使用者，建立T1表，插入約30W的資料，並根據object_id建立普通索引，表佔儲存空間34M左右，索引佔6M左右的儲存空間
SQL> conn /as sysdba
已連線。
SQL> select default_tablespace from dba_users where username='SCOTT';
DEFAULT_TABLESPACE
------------------------------------------------------------
USERS
SQL> conn SCOTT/tiger
已連線。
SQL> create table t1 (object_id number(10), object_name varchar(25));
Table created.
SQL> insert into t1 values ('1', 'user');
1 row created.
SQL> insert into t1 values ('2', 'test');
1 row created.
SQL> insert into t1 select * from t1;
SQL> commit;
SQL> create index idx_t1_id on t1(object_id);
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> select count(1) from t1;
COUNT(1)
----------
299248
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
34.0625
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
SUM(BYTES)/1024/1024
--------------------
6
二：估算表在高水位線下還有多少空間可用，這個值應當越低越好，表使用率越接近高水位線，全表掃描所做的無用功也就越少!
DBMS_STATS包無法獲取EMPTY_BLOCKS統計資訊，所以需要用analyze命令再收集一次統計資訊
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4302 0 299248
SQL> analyze table t1 compute statistics;
表已分析。
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4302 50 299248
SQL> col table_name for a20
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 5.07086182
三: 檢視執行計劃，全表掃描大概需要消耗CPU 1175
SQL> explain plan for select * from t1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 299K| 28M| 1175 (1)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T1 | 299K| 28M| 1175 (1)| 00:00:15 |
--------------------------------------------------------------------------
四：刪除大部分資料，收集統計資訊，全表掃描依然需要消耗CPU 1168
SQL> delete from t1 where object_id>100;
已刪除298852行。
SQL> commit;
提交完成。
SQL> select count(*) from t1;
COUNT(*)
----------
396
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
SQL> analyze table t1 compute statistics;
表已分析。
SQL> SELECT blocks, empty_blocks, num_rows FROM user_tables WHERE table_name ='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
4302 50 396
SQL> explain plan for select * from t1;
已解釋。
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 29700 | 1168 (1)| 00:00:15 |
| 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 1168 (1)| 00:00:15 |
--------------------------------------------------------------------------
五：估算表在高水位線下還有多少空間是無資料的，但在全表掃描時又需要做無用功的資料
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 33.5791626
六：對錶進行碎片整理，重新收集統計資訊
SQL> alter table t1 enable row movement;
表已更改。
SQL> alter table t1 shrink space cascade;
表已更改。
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1';
SUM(BYTES)/1024/1024
--------------------
.125
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_ID';
SUM(BYTES)/1024/1024
--------------------
.0625
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 33.5791626
SQL> exec dbms_stats.gather_table_stats('SCOTT','T1',CASCADE=>TRUE);
PL/SQL 過程已成功完成。
這個時候，只剩下0.1M的無用功了，執行計劃中，全表掃描也只需要消耗CPU 3
SQL>
SELECT TABLE_NAME,
(BLOCKS * 8192 / 1024 / 1024) -
(NUM_ROWS * AVG_ROW_LEN / 1024 / 1024) "Data lower than HWM in MB"
FROM USER_TABLES
WHERE table_name = 'T1';
TABLE_NAME Data lower than HWM in MB
-------------------- -------------------------
T1 .010738373
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 396 | 29700 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T1 | 396 | 29700 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
總共只有5個塊，空塊卻有50個，明顯empty_blocks資訊過期
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
5 50 396
SQL> analyze table t1 compute statistics;
表已分析。
SQL> select blocks,empty_blocks,num_rows from user_tables where table_name='T1';
BLOCKS EMPTY_BLOCKS NUM_ROWS
---------- ------------ ----------
5 3 396
檢視Oracle碎片比較多的表
select t.table_name,
avg_row_len * num_rows / (1 - pct_free / 100) / 8192 actual_block,
alloc_block,
1 - ((avg_row_len * num_rows / (1 - pct_free / 100) / 8192) / alloc_block) percen,
'alter table '||t.table_name||'enable row movement'||';' enable_row,
'alter table '||t.table_name||'shrink space'||';' ddl_shrink
from (select segment_name, sum(blocks) alloc_block
from user_extents
having sum(blocks) > 8
group by segment_name) a,
user_tables t
where a.segment_name = t.table_name
and num_rows is not null
and num_rows > 0
order by 4 desc;
SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE TYPE,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) "WASTE_PER(%)",
ROUND(BYTES/1024/1024, 2) "Total(M)", NUM_ROWS,
BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,CHAIN_PER, EXTENTS, MAX_EXTENTS, ALLO_EXTENT_PER,
DECODE(GREATEST(MAX_FREE_SPACE - NEXT_EXTENT, 0), 0,'N','Y') CAN_EXTEND_SPACE,
NEXT_EXTENT, MAX_FREE_SPACE,
O_TABLESPACE_NAME TABLESPACE_NAME
FROM
(SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, A.BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER,A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT NEXT_EXTENT, B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,SYS.DBA_TABLES B,SYS.TS$ C
WHERE A.OWNER =B.OWNER
and SEGMENT_NAME = TABLE_NAME
and SEGMENT_TYPE = 'TABLE'
AND B.TABLESPACE_NAME = C.NAME
UNION ALL
SELECT A.OWNER OWNER, SEGMENT_NAME || '.' || B.PARTITION_NAME, SEGMENT_TYPE, BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0),0, 1,
ROUND((B.AVG_ROW_LEN * B.NUM_ROWS * (1 + (B.PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT,0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
ROUND(100 * (A.EXTENTS/A.MAX_EXTENTS), 2) ALLO_EXTENT_PER, A.EXTENTS EXTENTS,
A.MAX_EXTENTS MAX_EXTENTS, B.NEXT_EXTENT,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,SYS.DBA_TAB_PARTITIONS B,SYS.TS$ C,SYS.DBA_TABLES D
WHERE A.OWNER = B.TABLE_OWNER
and SEGMENT_NAME = B.TABLE_NAME
and SEGMENT_TYPE = 'TABLE PARTITION'
AND B.TABLESPACE_NAME = C.NAME
AND D.OWNER = B.TABLE_OWNER
AND D.TABLE_NAME = B.TABLE_NAME
AND A.PARTITION_NAME = B.PARTITION_NAME),
(SELECT TABLESPACE_NAME F_TABLESPACE_NAME,MAX(BYTES) MAX_FREE_SPACE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME)
WHERE F_TABLESPACE_NAME = O_TABLESPACE_NAME AND
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND BLOCKS > 128
AND OWNER in (user_name1,user_name2)
ORDER BY 1 ASC, 4 DESC;
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29500582/viewspace-1402915/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle表碎片整理Oracle
- Oracle資料表碎片整理Oracle
- Oracle資料庫表碎片整理Oracle資料庫
- ORACLE表空間的碎片整理Oracle
- Oracle 資料庫整理表碎片Oracle資料庫
- oracle表碎片以及整理（高水位線）Oracle
- Oracle 整理表碎片、釋放表的空間Oracle
- oracle 表碎片太多的處理辦法Oracle
- Oracle表碎片起因及解決辦法Oracle
- MySQL表碎片整理MySql
- oracle碎片清理Oracle
- oracle 碎片整理Oracle
- 回收mysql表的碎片MySql
- ORACLE碎片問題Oracle
- oracle碎片整理方法Oracle
- MySQL的表碎片處理MySql
- 表空間碎片檢測
- ORACLE碎片整理一(轉載)Oracle
- ORACLE碎片整理二(轉載)Oracle
- Oracle 資料庫碎片整理Oracle資料庫
- Oracle資料庫碎片整理Oracle資料庫
- mysql表碎片的查詢自己回收MySql
- MySQL的 data_free，表碎片整理MySql
- Tablespace Fragmentation - 表空間碎片問題Fragment
- 表碎片的相關知識（ZT）
- 表碎片起因及解決辦法
- Oracle 資料庫碎片整理(zt)Oracle資料庫
- Oracle資料庫碎片整理（轉）Oracle資料庫
- 表碎片起因及解決辦法(zt)
- Sqlserver的表沒有高水位但是有碎片的概念，sqlserver表的碎片的檢視和整理方法SQLServer
- 資料庫優化之表碎片處理資料庫優化
- 表中出現碎片，造成查詢、DML操作變慢
- 如何處理表空間級別，表級別，索引級別的碎片索引
- SQL server資料庫表碎片比例查詢語句SQLServer資料庫
- jQuery碎片jQuery
- 字串碎片字串
- MySQL關於表碎片整理OPTIMIZE TABLE操作的官方建議MySql
- Oracle優化經典文章------磁碟I/O和碎片篇Oracle優化