Oracle view the use of each physical file in the Tablespace ____oracle

--View the use of table space files


SELECT
MAX (b.file_id) ID,
B.tablespace_name table Space,
B.file_name Physical file name,
b.bytes/1024/1024 size M,
(B.bytes-sum (NVL (a.bytes, 0)))/1024/1024 has used M,
SUBSTR (B.bytes-sum (NVL (a.bytes, 0))/(b.bytes) * 100, 1, 5) utilization
From Dba_free_space A, Dba_data_files B
WHERE a.file_id = b.file_id
GROUP by B.tablespace_name, B.file_name, b.bytes

Order BY B.tablespace_name;

--Automatically generate a statement to reduce the size of the table space

Select ' ALTER DATABASE datafile ' ' | | A.file_name | | "' Resize ' | |
Round (A.filesize-(a.filesize-c.hwmsize-100) * 0.8) | | ' M; ',
A.filesize | | Total size of ' M ' as ' data file ',
C.hwmsize | | Practical size of ' M ' as data file
From (select file_id, file_name, round (bytes/1024/1024) as FileSize
From Dba_data_files) A,
(select file_id, round (max (block_id) * 8/1024) as Hwmsize
From Dba_extents
Group by FILE_ID) c
where a.file_id = c.file_id
and a.filesize-c.hwmsize > 100;

--performing a reduced table space file

ALTER DATABASE datafile ' C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS02. DBF ' RESIZE 300M

--View table space files

Select File#,name from V$datafile