[오라클] TableSpace 사용량 조회

조회 쿼리

 set linesize 400;
 select substr(a.tablespace_name,  1, 30) as tablespace_name
	    , round(sum(a.total1) /1024/1024, 1) "Total(MB)"
	    , round(sum(a.total1) /1024/1024/1024, 1) "Total(GB)"
	    , round(sum(a.total1) /1024/1024,1) -round (sum(a.sum1) /1024/1024, 1) "Used(MB)"
	    , round(sum(a.sum1) /1024/1024, 1) "Free(MB)"
	    , 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
	         group by tablespace_name
	         union
	         select tablespace_name
	              , sum(bytes) total1
	              , 0
	              , 0
	              , 0  
	           from dba_data_files
	           group by tablespace_name) a
  group by a.tablespace_name
  order by tablespace_name;

결과

데이터 파일 사이즈 비교

$ du -sh * | sort -k 2
201M    274752.tdf
32G     TS_PART_TBL_PB_SYNC.tdf
201M    TS_TBL_PB_SYNC.tdf
9.2G    TS_TBL_PB_SYNC_LOB.tdf
41M     TS_TEST_1.dtf
41M     TS_TEST_2.dtf
41M     TS_TEST_3.dtf
41M     TS_TEST_4.dtf
11M     TS_TEST_5.dtf
141M    TS_TEST_lob.dtf
4.0K    hc_ORCL19C.dat
4.0K    hc_ORCLCDB.dat
4.0K    hc_orcl19c.dat
1.1G    iconn.tdf
4.0K    init.ora
4.0K    initORCL19C.ora
4.0K    lkORCL19C
4.0K    lkORCLCDB
4.0K    orapwORCLCDB
4.0K    orapworcl19c
4.0K    spfileORCLCDB.ora
4.0K    spfileorcl19c.ora
4.0K    test_pfile.ora
201M    ts1.tdf
201M    ts2.tdf
201M    ts274259_1.tdf
201M    ts274259_2.tdf
201M    ts274259_3.tdf
201M    ts274259_4.tdf
201M    ts3.tdf
201M    ts4.tdf