Oracle Database Storage Usage queries

Create a view using the system SYS or DBA authority account as follows:

1. It is mainly obtained from table dba_data_files,dba_segments two tables in the database.
2. The default database holds a byte unit, and the conversion relationship is as follows:
1024bytes = 1kb
1024KB = 1M
1024M = 1G

CREATE OR REPLACE VIEW V_get_diskspace as
SELECT A.tablespace_name,
A.use "Used (MB)",
(B.total-a. Use) ' Free (MB) ',
B.total "Total (MB)",
Round (b.total-a. Use)/b.total,5) *100| | ' % ' "Per_free"
From
(
Select Tablespace_name,sum (bytes)/(1024*1024) as use from dba_segments
/*where Tablespace_name not in (' USERS ', ' sysaux ', ' UNDOTBS1 ', ' SYSTEM ', ' UNDOTBS2 ') */
GROUP by Tablespace_name
A
(
With Tablespace_total as
(
SELECT tablespace_name,sum (MaxBytes)/(1024*1024) total from Dba_data_files T
WHERE/*t.tablespace_name not in (' USERS ', ' sysaux ', ' UNDOTBS1 ', ' SYSTEM ', ' UNDOTBS2 ')
and*/t.autoextensible= ' YES ' GROUP by Tablespace_name
UNION All
SELECT tablespace_name,sum (bytes) Total from Dba_data_files T
WHERE/*t.tablespace_name not in (' USERS ', ' sysaux ', ' UNDOTBS1 ', ' SYSTEM ', ' UNDOTBS2 ')
and*/t.autoextensible= ' NO ' GROUP by Tablespace_name
)
SELECT tablespace_name,sum (total) total from Tablespace_total GROUP by Tablespace_name
) B
WHERE A.tablespace_name=b.tablespace_name;

Oracle Database Storage Usage queries