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  2018-01-26 18:45:39, Á¶È¸ : 7,269, Ãßõ : 1294

    • ÇϳªÀÇ µ¥ÀÌŸ È­ÀÏÀ» 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-2024 Zeroboard

     

     
     
    [Today:2 / Total:171597]    Design by p@rk1q