www.1q.co.kr(park1q.com)

ID :  Password : Auto  

   회원:0명 / 손님:0명
 

 

Technote

자료 및 관리팁

  • DBA Notes
  • Q & A

    북마크
  • Asktom
       (Oracle 의 대표 Forum)
  • Technical Bulltin(KR)
       (기술지원게시판)
  • Dbazine

  •  

     


     데이타 화일의 extent map
    park1q  2009-12-17 18:25:21, 조회 : 6,151, 추천 : 1115

    • 하나의 데이타 화일을 100개의 bucket 으로 보았을때 포함되어 각 bucket 에 포함되어 있는 segment 를 출력하는 SQL
    • 대용량 파티션 drop 또는 object 의 Drop 으로 인해 비어 있는 free block 을 알고 싶을때 사용하고, 해당 오브젝트를 move tablespace 또는 rebuild 로 reorg 할때 사용한다.

     
    select a1.file_id,
           a1.bucket_no,
           a1.start_block_id,
           a1.end_block_id,
           a1.hwm_size_mega,
           b.segment_type,
           b.segment_name
      from (select file_id,
                   no bucket_no,
                   round(blocks * (no - 1) * 0.01 + 1) start_block_id,
                   round(blocks * no  * 0.01) end_block_id,
                   (blocks / 100) * 8192 / 1024 / 1024 * no  hwm_size_mega
              from dba_data_files,
                   t_no) a1,
           (select distinct b.file_id,
                   b.bucket_no,
                   b.start_block_id ,
                   b.end_block_id ,
                   b.hwm_size_mega,
                   a.segment_type,
                   a.owner||'.'||a.segment_name||decode(partition_name,null,null,'(PART:'||partition_name||')') segment_name
              from dba_extents a,
                   (select file_id,
                           no bucket_no,
                           round(blocks * (no - 1) * 0.01 + 1) start_block_id,
                           round(blocks * no  * 0.01) end_block_id,
                           (blocks / 100) * 8192 / 1024 / 1024 * no  hwm_size_mega
                      from dba_data_files,
                           t_no ) b
             where a.file_id  = b.file_id
               and exists (select 1
                             from dba_data_files s1,
                                  t_no s2
                            where s2.no = b.bucket_no  and s1.file_id = a.file_id
                              and (a.block_id between round(s1.blocks * (s2.no - 1) * 0.01 + 1) and round(s1.blocks * s2.no  * 0.01)
                               or a.block_id+a.blocks between round(s1.blocks * (s2.no - 1) * 0.01 + 1) and round(s1.blocks * s2.no  * 0.01)    
                               or round(s1.blocks * (s2.no - 1) * 0.01 + 1) between a.block_id and a.block_id+a.blocks
                               or round(s1.blocks * s2.no  * 0.01) between a.block_id and a.block_id+a.blocks))
           ) b
     where a1.bucket_no =  b.bucket_no(+)
       and a1.file_id =  b.file_id(+)
       and a1.file_id = 1
     order by file_id , bucket_no


      추천하기 프린트   목록보기

    Copyright 1999-2018 Zeroboard

     

     
     
    [Today:5 / Total:155151]    Design by p@rk1q