How to check datafile usage in Oracle Database?

Below is the script script which will list datafile wise Allocated size, Used Size and Free Size
Size details are displayed in MB (Mega Bytes)

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

How to change the location of datafiles in Oracle database?

Below are steps to move the datafiles without shutting down the database.

1. Make the tablespaces offline.
ALTER TABLESPACE tablespace_name offline;

2.copy the files to the new location at the OS level

3.Modify the location of the datafiles
ALTER TABLESPACE tablespace_name RENAME DATAFILE 'datafile_name' to 'datafile_name';

4.Make the tablesapces online
ALTER TABLESPACE tablespace_name online;

Script for Datafile usage in Oracle Database

This script will list datafile wise Allocated size, Used Size and Free Size

For running this query you must have SELECT privileges to V$DATAFILE and DBA_FREE_SPACE views or run the sript as sysdba

The Size details are displayed in MB (Mega Bytes)
-----------------------------------------------------------------------

SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 "Allocated Size(MB)",
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) "Used Size (MB)",
NVL (SUM (dfs.bytes) / 1024 / 1024, 0) "Free Size(MB)"
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;