首页 » ORACLE [C]系列, ORACLE 9i-23c » Scripts: 查找Oracle数据文件碎片和extents分布

Scripts: 查找Oracle数据文件碎片和extents分布

最近有个客户的表空间使用率使用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

...
打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：