orcale_tablespace.sql
· 576 B · MySQL
Raw
SET linesize 9999
SELECT T1.TABLESPACE_NAME,
T1.BYTES / 1024 / 1024 AS "bytes_used (Mb)",
T2.BYTES / 1024 / 1024 AS "bytes_free (Mb)",
T2.largest /1024 /1024 AS "largest (Mb)",
round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) percent_used
FROM
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME
)
T1,
(
SELECT TABLESPACE_NAME,
SUM(BYTES) BYTES ,
MAX(BYTES) largest
FROM dba_free_space
GROUP BY TABLESPACE_NAME
)
T2
WHERE T1.TABLESPACE_NAME=T2.TABLESPACE_NAME
--and T2.TABLESPACE_NAME like 'WKFL_%'
ORDER BY ((T1.BYTES-T2.BYTES)/T1.BYTES) DESC ;
| 1 | SET linesize 9999 |
| 2 | SELECT T1.TABLESPACE_NAME, |
| 3 | T1.BYTES / 1024 / 1024 AS "bytes_used (Mb)", |
| 4 | T2.BYTES / 1024 / 1024 AS "bytes_free (Mb)", |
| 5 | T2.largest /1024 /1024 AS "largest (Mb)", |
| 6 | round(((T1.BYTES-T2.BYTES)/T1.BYTES)*100,2) percent_used |
| 7 | FROM |
| 8 | ( |
| 9 | SELECT TABLESPACE_NAME, |
| 10 | SUM(BYTES) BYTES |
| 11 | FROM dba_data_files |
| 12 | GROUP BY TABLESPACE_NAME |
| 13 | ) |
| 14 | T1, |
| 15 | ( |
| 16 | SELECT TABLESPACE_NAME, |
| 17 | SUM(BYTES) BYTES , |
| 18 | MAX(BYTES) largest |
| 19 | FROM dba_free_space |
| 20 | GROUP BY TABLESPACE_NAME |
| 21 | ) |
| 22 | T2 |
| 23 | WHERE T1.TABLESPACE_NAME=T2.TABLESPACE_NAME |
| 24 | --and T2.TABLESPACE_NAME like 'WKFL_%' |
| 25 | ORDER BY ((T1.BYTES-T2.BYTES)/T1.BYTES) DESC ; |