[펌] 유용한 몇가지 오라클 쿼리

※ 현재 extent 수가 max extent 의 90% 이상인것 조회하기..

select Owner,Tablespace_Name,Segment_Name,Segment_Type,
       round(Bytes/1024/1024, 2) as "cur_Size(MB)",
       Extents, max_extents,
       initial_extent/1024 as "initial_extent(KB)",
       next_extent/1024 as "next_extent(KB)"
 from DBA_SEGMENTS
 Where owner <> 'SYS'
   and extents > (max_extents * 0.9)     /* 현재 extents 90% 이상인것 */
 Order By Owner, Tablespace_Name,Segment_Type,Segment_Name;

[출처] http://cafe.naver.com/sanovice/197

---

※ 특정 테이블의 사용량 등 속성값 확인
select * from USER_segments where segment_name='SMACCESSLOG4'


※ 테이블스페이스 현황 확인
SELECT  d.tablespace_name , d.bytes "Total(M)" , f.bytes "Free(M)" ,e.bytes "Max Extent(M)" , Round(f.bytes/d.bytes*100,0) "Free %"
FROM    ( SELECT tablespace_name , sum(bytes)/1024/1024 as bytes
          FROM   dba_data_files
          GROUP BY tablespace_name ) d ,
        ( SELECT tablespace_name , sum(bytes)/1024/1024 as bytes
          FROM   dba_free_space
          GROUP BY tablespace_name ) f ,
        ( SELECT tablespace_name , max(bytes)/1024/1024 as bytes
          FROM   dba_free_space
          GROUP BY tablespace_name ) e
WHERE   d.tablespace_name = f.tablespace_name (+)
AND     f.tablespace_name = e.tablespace_name
;

어디서 퍼왔는지 기억이 안나요 ㅡㅡ;