Query to Check available Table-Space size details and Alter the table-space in Oracle database

-- To Check available tablespace in database



SELECT df.tablespace_name "Tablespace",
       totalusedspace "Used MB",
       (df.totalspace - tu.totalusedspace) "Free MB",
       df.totalspace "Total MB",
       ROUND (100 * ( (df.totalspace - tu.totalusedspace) / df.totalspace))
          "Pct. Free"
  FROM (  SELECT tablespace_name, ROUND (SUM (bytes) / 1048576) TotalSpace
            FROM dba_data_files
        GROUP BY tablespace_name) df,
       (  SELECT ROUND (SUM (bytes) / (1024 * 1024)) totalusedspace,
                 tablespace_name
            FROM dba_segments
        GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name;


--To alter Tablespace


SELECT FILE_NAME
  FROM dba_data_files
 WHERE TABLESPACE_NAME = :P_TABLESPACE_NAME;

 -- copy past the File_name against datafile

ALTER DATABASE
DATAFILE 'enter_file_path/with_file_name'

RESIZE 800M;