最近有个客户的表空间使用率使用50%左右就出现了ora-1653，我们知道ora-165N是空间无法扩展，这么多的free空间还无法扩展，其中有可能是存在碎片，也就是数据文件中不连续的”洞”free space， 在申请一个比较大的extents时，无法匹配连续空间而失败， 你是否想过查看数据文件上的段分布？或表空间的碎片情况？或move 哪个对象可以让datafile resize更小？ 关于datafile HWM这问题十年前小记过一篇《Script: 查看datafile 的HWM,估算resize 最小size》，这里发现两个不错的脚本，拿出来分享一下，其实从dba_extents和dba_free_space聚合的结果就可以实现上面的方法， 很多年前使用toad 工具时还记的那个列数据文件分布的功能确实直观。
脚本1
-- Author : DR Timothy S Hall
-- Call Syntax : @ts_extent_map (tablespace-name) [all | file_id]
SET SERVEROUTPUT ON SIZE 1000000
SET FEEDBACK OFF
SET TRIMOUT ON
SET VERIFY OFF
DECLARE
l_tablespace_name VARCHAR2(30) := UPPER('&1');
l_file_id VARCHAR2(30) := UPPER('&2');
CURSOR c_extents IS
SELECT owner,
segment_name,
file_id,
block_id AS start_block,
block_id + blocks - 1 AS end_block
FROM dba_extents
WHERE tablespace_name = l_tablespace_name
AND file_id = DECODE(l_file_id, 'ALL', file_id, TO_NUMBER(l_file_id))
ORDER BY file_id, block_id;
l_block_size NUMBER := 0;
l_last_file_id NUMBER := 0;
l_last_block_id NUMBER := 0;
l_gaps_only BOOLEAN := TRUE;
l_total_blocks NUMBER := 0;
BEGIN
SELECT block_size
INTO l_block_size
FROM dba_tablespaces
WHERE tablespace_name = l_tablespace_name;
DBMS_OUTPUT.PUT_LINE('Tablespace Block Size (bytes): ' || l_block_size);
FOR cur_rec IN c_extents LOOP
IF cur_rec.file_id != l_last_file_id THEN
l_last_file_id := cur_rec.file_id;
l_last_block_id := cur_rec.start_block - 1;
END IF;
IF cur_rec.start_block > l_last_block_id + 1 THEN
DBMS_OUTPUT.PUT_LINE('*** GAP *** (' || l_last_block_id || ' -> ' || cur_rec.start_block || ')' ||
' FileID=' || cur_rec.file_id ||
' Blocks=' || (cur_rec.start_block-l_last_block_id-1) ||
' Size(MB)=' || ROUND(((cur_rec.start_block-l_last_block_id-1) * l_block_size)/1024/1024,2)
);
l_total_blocks := l_total_blocks + cur_rec.start_block - l_last_block_id-1;
END IF;
l_last_block_id := cur_rec.end_block;
IF NOT l_gaps_only THEN
DBMS_OUTPUT.PUT_LINE(RPAD(cur_rec.owner || '.' || cur_rec.segment_name, 40, ' ') ||
' (' || cur_rec.start_block || ' -> ' || cur_rec.end_block || ')');
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total Gap Blocks: ' || l_total_blocks);
DBMS_OUTPUT.PUT_LINE('Total Gap Space (MB): ' || ROUND((l_total_blocks * l_block_size)/1024/1024,2));
END;
/
PROMPT
SET FEEDBACK ON
--- example
SQL> @tbs_map users 12
Tablespace Block Size (bytes): 8192
*** GAP *** (32007 -> 32048) FileID=12 Blocks=40 Size(MB)=.31
*** GAP *** (32895 -> 32984) FileID=12 Blocks=88 Size(MB)=.69
*** GAP *** (33151 -> 33216) FileID=12 Blocks=64 Size(MB)=.5
*** GAP *** (33535 -> 33640) FileID=12 Blocks=104 Size(MB)=.81
*** GAP *** (33663 -> 33760) FileID=12 Blocks=96 Size(MB)=.75
*** GAP *** (34047 -> 34072) FileID=12 Blocks=24 Size(MB)=.19
*** GAP *** (39295 -> 39760) FileID=12 Blocks=464 Size(MB)=3.63
..
..
*** GAP *** (266319 -> 266368) FileID=12 Blocks=48 Size(MB)=.38
*** GAP *** (271103 -> 271568) FileID=12 Blocks=464 Size(MB)=3.63
*** GAP *** (271615 -> 271744) FileID=12 Blocks=128 Size(MB)=1
*** GAP *** (272895 -> 273024) FileID=12 Blocks=128 Size(MB)=1
*** GAP *** (273151 -> 277504) FileID=12 Blocks=4352 Size(MB)=34
*** GAP *** (277719 -> 277760) FileID=12 Blocks=40 Size(MB)=.31
Total Gap Blocks: 110680
Total Gap Space (MB): 864.69
脚本2
-- author: Marc Billette
--
-- Create the global temporary tables.
--
create global temporary table seg_list
(file_id number,
block_id number,
owner varchar2(30),
segment_name varchar2(30),
segment_type varchar2(30),
blocks number,
constraint seg_list_pk primary key
(file_id, block_id, owner, segment_name, segment_type))
;
create global temporary table aggregated_extent_map
(file_id number,
root_block_id number,
owner varchar2(30),
segment_name varchar2(30),
segment_type varchar2(30),
total_blocks number)
;
--
-- Load the base extent data
-- from dba_extents and dba_free_space
--
insert into seg_list
select file_id,block_id,owner,
segment_name,segment_type,blocks
from dba_extents
-- this is optional, you can load all your tablespaces
-- where tablespace_name = 'MY_TS'
union all
select file_id,block_id,
'free space',
'free space',
'free space',
blocks
from dba_free_space
-- this is optional, you can load all your tablespaces
-- where tablespace_name = 'MY_TS';
--
-- Generate the aggregate extent map using a hierarchical query.
-- Be patient, this will take a short while depending on the number
-- of extents to process and your system's speed. It took 5:02.69
-- minutes on a dev server to process 18033 extents and
-- generated 11848 aggregated extents.
--
insert into aggregated_extent_map
select file_id, root, owner, segment_name, segment_type,
sum(blocks)
from
(
select owner, segment_name, segment_type, file_id,
blocks, block_id,
substr(sys_connect_by_path(block_id,'/'),2,
decode(instr(sys_connect_by_path(block_id,'/'),'/',2)
-2,-2,length(sys_connect_by_path(block_id,'/')),
instr(sys_connect_by_path(block_id,'/'),'/',2)-2))
root
from seg_list a
start with (file_id, block_id) in
(select file_id, block_id
from seg_list
where (file_id,block_id) in
(select file_id, min(block_id)
from seg_list group by file_id)
union all
select b.file_id, b.block_id
from seg_list a, seg_list b
where b.block_id = a.block_id + a.blocks
and a.file_id = b.file_id
and (a.owner <> b.owner or
a.segment_name <> b.segment_name)
)
connect by owner = prior owner
and segment_name = prior segment_name
and file_id = prior file_id
and block_id = prior a.block_id + prior a.blocks
) c
group by owner, segment_name, segment_type, file_id, root
;
-- >>> run all your queries here...
-- Don't forget to re-populate the temporary tables if you
-- sign out or rollback.
break on file_id skip 1
set linesize 140 pagesize 10000
col file_id for 9999
col top_n noprint
col segment_type for a12
col size_mb for 999999.99
select * from (
select a.file_id,
rank() over (partition by a.file_id
order by root_block_id desc) top_n,
segment_name,
segment_type,
root_block_id,
total_blocks*(b.bytes/b.blocks)/1048576 size_mb
from aggregated_extent_map a, dba_data_files b
where a.file_id = b.file_id
-- use this if you loaded more than one TS in the seg_list
-- and tablespace_name = 'MY_TS'
-- use this to list a single datafile
-- and a.file_id = 12
) where top_n <=10
;
-- example
FILE_ID SEGMENT_NAME SEGMENT_TYPE ROOT_BLOCK_ID SIZE_MB
------- ------------------------------ ------------ ------------- ----------
12 free space free space 278656 49.25
TEMP_TBH_BOOK2 TABLE 277760 7.00
free space free space 277720 .31
TEMP_TBH_BOOK2 TABLE 277592 1.00
TBH_BOOK TABLE 277504 .69
free space free space 273152 34.00
T_CI TABLE 273024 1.00
free space free space 272896 1.00
T_CI TABLE 272768 1.00
IDX_T_CI_ID INDEX 272640 1.00
...