테이블 스페이스 조회

테이블 스페이스별 목록

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
          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; 

 

테이블 스페이스 파일별 목록

SELECT TABLESPACE_NAME, FILE_NAME, BYTES/1024 AS MBytes, RESULT/1024 AS USE_MBytes FROM
  (
  SELECT E.TABLESPACE_NAME,E.FILE_NAME,E.BYTES, (E.BYTES-SUM(F.BYTES)) RESULT
  FROM DBA_DATA_FILES E, DBA_FREE_SPACE F
  WHERE E.FILE_ID = F.FILE_ID
  GROUP BY E.TABLESPACE_NAME, E.FILE_NAME, E.BYTES
  ) A;

 

테이블별 목록

SELECT
	owner,
	table_name,
	TRUNC(SUM(bytes)/ 1024 / 1024 / 1024) GB
FROM
	(
	SELECT
		segment_name table_name,
		owner,
		bytes
	FROM
		dba_segments
	WHERE
		segment_type IN ('TABLE',
		'TABLE PARTITION')
UNION ALL
	SELECT
		i.table_name,
		i.owner,
		s.bytes
	FROM
		dba_indexes i,
		dba_segments s
	WHERE
		s.segment_name = i.index_name
		AND s.owner = i.owner
		AND s.segment_type IN ('INDEX',
		'INDEX PARTITION')
UNION ALL
	SELECT
		l.table_name,
		l.owner,
		s.bytes
	FROM
		dba_lobs l,
		dba_segments s
	WHERE
		s.segment_name = l.segment_name
		AND s.owner = l.owner
		AND s.segment_type IN ('LOBSEGMENT',
		'LOB PARTITION')
UNION ALL
	SELECT
		l.table_name,
		l.owner,
		s.bytes
	FROM
		dba_lobs l,
		dba_segments s
	WHERE
		s.segment_name = l.index_name
		AND s.owner = l.owner
		AND s.segment_type = 'LOBINDEX')
	WHERE owner in UPPER('IREXNET') 
	GROUP BY table_name, owner HAVING SUM(bytes)/1024/1024 > 10 
	/* Ignore really small tables */ 
	ORDER BY SUM(bytes) desc