SQL script sharing for viewing table space usage in Oracle, oraclesql

SQL script sharing for viewing table space usage in Oracle, oraclesql

Copy codeThe Code is as follows:
/* Formatted on 14:51:13 (QP5 v5.185.11230.41888 )*/
Select d. TABLESPACE_NAME,
SPACE | 'M' "SUM_SPACE (M )",
BLOCKS "SUM_BLOCKS ",
SPACE-NVL (FREE_SPACE, 0) | 'M' "USED_SPACE (M )",
ROUND (1-NVL (FREE_SPACE, 0)/SPACE) * 100, 2) | '%'
"USED_RATE (% )",
FREE_SPACE | 'M' "FREE_SPACE (M )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
Group by TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) FREE_SPACE
FROM DBA_FREE_SPACE
Group by TABLESPACE_NAME) F
Where d. TABLESPACE_NAME = F. TABLESPACE_NAME (+)
Union all -- If temporary tablespace exists
Select d. TABLESPACE_NAME,
SPACE | 'M' "SUM_SPACE (M )",
BLOCKS SUM_BLOCKS,
USED_SPACE | 'M' "USED_SPACE (M )",
ROUND (NVL (USED_SPACE, 0)/SPACE * 100, 2) | '%' "USED_RATE (% )",
NVL (FREE_SPACE, 0) | 'M' "FREE_SPACE (M )"
FROM (SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES)/(1024*1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
Group by TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED)/(1024*1024), 2) USED_SPACE,
ROUND (SUM (BYTES_FREE)/(1024*1024), 2) FREE_SPACE
From v $ TEMP_SPACE_HEADER
Group by TABLESPACE_NAME) F
Where d. TABLESPACE_NAME = F. TABLESPACE_NAME (+)
Order by 1;

The effect is as follows:

How can I check the utilization of each tablespace in the oracle database?

Select. tablespace_name,. bytes/1024/1024 "Sum MB", (. bytes-b.bytes)/1024/1024 "used MB", B. bytes/1024/1024 "free MB", round (. bytes-b.bytes)/. bytes) * 100,2) "percent_used"
From
(Select tablespace_name, sum (bytes) bytes from dba_data_files group by tablespace_name),
(Select tablespace_name, sum (bytes) bytes, max (bytes) largest from dba_free_space group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by (a. bytes-b.bytes)/a. bytes) desc

How to use SQL statements to query the table space usage of an oracle Database

Select upper (F. TABLESPACE_NAME) "tablespace name ",

D. TOT_GROOTTE_MB "tablespace size (M )",

D. TOT_GROOTTE_MB-F. TOTAL_BYTES "used space (M )",

TO_CHAR (ROUND (D. TOT_GROOTTE_MB-F. TOTAL_BYTES)/D. TOT_GROOTTE_MB * 990), '2014. 99') | '%' "usage ratio ",

F. TOTAL_BYTES "Idle space (M )",

F. MAX_BYTES "maximum block (M )"

FROM (SELECT TABLESPACE_NAME,

ROUND (SUM (BYTES)/(1024*1024), 2) TOTAL_BYTES,

ROUND (MAX (BYTES)/(1024*1024), 2) MAX_BYTES

From sys. DBA_FREE_SPACE

Group by TABLESPACE_NAME) F,

(Select dd. TABLESPACE_NAME,

ROUND (SUM (DD. BYTES)/(1024*1024), 2) TOT_GROOTTE_MB

From sys. DBA_DATA_FILES DD

Group by dd. TABLESPACE_NAME) D

Where d. TABLESPACE_NAME = F. TABLESPACE_NAME

Order by 1