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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29500582/viewspace-1402915/