|
 |
| |
|
|
데이타 화일의 extent map
|
|
park1q
|
2018-01-26 18:45:39, 조회 : 7,298, 추천 : 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 |
|
|
|
|
|
|
|
Technote
