SELECT a.tablespace_name,100 - ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Usage",
ROUND (a.bytes_alloc / 1024 / 1024) "Size MB",
ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Used MB",
ROUND (NVL (b.bytes_free, 0) / 1024 / 1024) "Free MB",
--ROUND ( (NVL (b.bytes_free, 0) / a.bytes_alloc) * 100) "%Free",
ROUND (maxbytes / 1048576) "Max MB",
round(maxbytes/1048576-(ROUND (a.bytes_alloc / 1024 / 1024)- ROUND (NVL (b.bytes_free, 0) / 1024 / 1024)),0) "Free_MB_Max",
ROUND (ROUND ( (a.bytes_alloc - NVL (b.bytes_free, 0)) / 1024 / 1024)/ ROUND (maxbytes / 1048576) * 100) "%Used of Max"
FROM (SELECT f.tablespace_name, SUM (f.bytes) bytes_alloc, SUM (DECODE (f.autoextensible, 'YES', f.maxbytes, 'NO', f.bytes)) maxbytes
FROM dba_data_files f
GROUP BY tablespace_name) a,
(SELECT f.tablespace_name, SUM (f.bytes) bytes_free FROM dba_free_space f GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+) --and (a.tablespace_name in ('TEMP1','DATA201511','DATA201512','DATA2016','INDX'))
order by "%Used of Max" desc;
--Dung lượng DB
SELECT ROUND(SUM(BYTES)/1024/1024/1024,2) FROM DBA_DATA_FILES;
--Dung lượng dữ liệu READ ONLY
select sum(bytes)/1024/1024/1024 "GB" from dba_data_files where tablespace_name in
(select tablespace_name from dba_tablespaces where
status='READ ONLY');
--Dung lượng schema
select owner,round(sum(bytes/1024/1024/1024),3) from dba_segments
group by owner order by owner;
--Dung lượng của từng data files
SELECT FILE_NAME, BLOCKS, round(bytes/1024/1024/1024,2), TABLESPACE_NAME
FROM DBA_DATA_FILES
WHERE file_name like '%data2018%' or file_name like '%data2018%'
order by tablespace_name;
--Check trạng thái của datafile, tablespace
--+Online/Offline
select file_name,tablespace_name,online_status from dba_data_files where tablespace_name='DATA2018';
--+Read only| RW
select tablespace_name, status from dba_tablespaces where status='READ ONLY';
select distinct(ts#) from v$datafile where ts# in (select ts# from v$tablespace) and enabled!='READ WRITE';
