[펌]테이블스페이스 관리

-- 테이블스페이스 확인
select   substr(a.tablespace_name,1,30) tablespace,
         round(sum(a.total1)/1024/1024,1) "TotalMB",
         round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1) "UsedMB",
         round(sum(a.sum1)/1024/1024,1) "FreeMB",
         round((round(sum(a.total1)/1024/1024,1)-round(sum(a.sum1)/1024/1024,1))/round(sum(a.total1)/1024/1024,1)*100,2) "Used%"
from
         (select   tablespace_name,0 total1,sum(bytes) sum1,max(bytes) MAXB,count(bytes) cnt
          from     dba_free_space
          where tablespace_name = upper('sfa_isss_ts')
          group by tablespace_name
          union
          select   tablespace_name,sum(bytes) total1,0,0,0
          from     dba_data_files
          where tablespace_name = upper('테이블스페이스명')
          group by tablespace_name) a
group by a.tablespace_name
order by tablespace;

-- 인덱스 확인
select de.tablespace_name,de.segment_name,sum(de.bytes)/1024/1024 as tsize
from dba_extents de, dba_data_files df
where de.file_id = df.file_id
and df.tablespace_name = '테이블스페이스명'
--and de.segment_name like upper('%테이블명%')
group by de.tablespace_name,de.segment_name
order by tsize desc

-- 테이블스페이스의 단편화 현상을 분석하는 쿼리
SELECT tablespace_name, total_extents, percent_extents_coalesced
FROM dba_free_space_coalesced
WHERE percent_extents_coalesced <> 100;

 

-- 테이블 용량 확인하기
select segment_name as 테이블명, to_char(round(bytes/1024/1024,2))||'M' as 용량M,round(bytes/1024/1024/1024, 3) as 용량G
from dba_segments
where segment_name = upper(테이블명')

select segment_name as 테이블명, to_char(round(bytes/1024/1024,2))||'M' as 용량M,round(bytes/1024/1024/1024, 3) as 용량G
from dba_segments
where segment_name in ('테이블명')
 

-- 테이블스페이스 공간합병하기 : percent_ex 컬럼의 값이 100%가 될 때까지 연속적으로 "alter tablespace ~ coalesce" 문장
ALTER TABLESPACE [테이블스페이스명] COALESCE;

-- 테이블스페이스 공간확인 1 
select a.tablespace_name,
    a.total_MB "전체 MB",  
    a.total_GB "전체 GB",
    a.total_MB - b.free_MB "사용 MB",
    a.total_GB - b.free_GB "사용 GB",
    b.free_MB "여유 MB",
    b.free_GB "여유 GB"
from  
(
select tablespace_name,
round(sum(bytes/1024/1024),1) total_MB,
round(sum(bytes/1024/1024/1024),1) total_GB
from DBA_DATA_FILES
where tablespace_name in ('테이블스페이스명')
group by tablespace_name
) a,
(
select tablespace_name,  
       round(sum(bytes/1024/1024),1) free_MB,
    round(sum(bytes/1024/1024/1024),1) free_GB
from dba_free_space
where tablespace_name in ('테이블스페이스명')
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name


-- 테이블스페이스 공간확인 2 
select substrb(d.tablespace_name,1,20)  tablespace,
       round(s_bytes/1024/1024)        total_mb,
       round(nvl(u_bytes,0)/1024/1024)   used_mb,
       round(nvl(s_bytes,0)/1024/1024-nvl(u_bytes,0)/1024/1024) free_mb,
       round(nvl(s_bytes,0)/1024/1024/1024-nvl(u_bytes,0)/1024/1024/1024) free_gb,
       round((nvl(u_bytes,0)/s_bytes)*100, 1) pctused, t.status
from (select nvl(round(sum(bytes)),0) s_bytes, tablespace_name
      from dba_data_files
      group by tablespace_name) d,
     (select nvl(round(sum(bytes)),0) u_bytes, tablespace_name
      from dba_extents
      group by tablespace_name) e,
     dba_tablespaces t
where d.tablespace_name = e.tablespace_name(+)
  and d.tablespace_name = t.tablespace_name
--  and t.tablespace_name in (테이블스페이스명')
;
[출처]http://cafe.naver.com/okjsp