[SQL] Tablespace 및 Datafile 조회

현재 Tablespace 조회

select tablespace_name, status, contents, extent_management, segment_space_management from dba_tablespaces;

현재 Tablespace size 조회

SELECT 	ddf.tablespace_name "Tablespace Name",
				ddf.bytes/1024/1024 "Bytes(MB)",
				(ddf.bytes - dfs.bytes)/1024/1024 "Used(MB)",
				ROUND(((ddf.bytes - dfs.bytes) / ddf.bytes) * 100, 2) "Percent(%)",
				dfs.bytes/1024/1024 "Free(MB)",
				ROUND((1 - ((ddf.bytes - dfs.bytes) / ddf.bytes)) * 100, 2) "Free(%)"
				FROM (SELECT tablespace_name, SUM(bytes) bytes
				FROM   dba_data_files
				GROUP BY tablespace_name) ddf,
				(SELECT tablespace_name, SUM(bytes) bytes
				FROM   dba_free_space
				GROUP BY tablespace_name) dfs
				WHERE ddf.tablespace_name = dfs.tablespace_name
				ORDER BY 4 DESC;

현재 datafile 조회

col TABLESPACE_NAME for 30
col file_name for a50
select tablespace_name,file_name,bytes/1024/1024, autoextensible from dba_data_files order by 1;