Managing datafile's size

If you need to change the actual size or maximum size of a datafile, you can check tablespace and datafile information with these handy sentences:

SQL> column expandible format A10
SQL> column Tot_Size_MB format 99,999,999 
SQL> column Tot_Free_MB format 99,999,999
SQL> column maxbytes_MB format 99,999,999
SQL> column PCT_USED format 999.9
SQL> column Chunks_Free format 99,999,999   

SQL> select a.tablespace_name,
sum(a.sum_alloc)/1048576 Tot_Size_MB,
sum(a.sum_free)/1048576 Tot_Free_MB,
100 - sum(a.sum_free)*100/sum(a.sum_alloc) PCT_USED,
sum(a.chunks) Chunks_Free,
max(expandible) expandible, 
sum(maxbytes)/1048576 maxbytes_MB
from
(select tablespace_name,0 sum_alloc,sum(bytes) sum_free,
max(bytes) largest,count(*) chunks, ' ' expandible, 0 maxbytes
from dba_free_space a
where tablespace_name != 'UNDO'
group by tablespace_name
union all
select tablespace_name,sum(bytes) sum_alloc, 0, 0, 0,
max(autoextensible), sum(case when maxbytes = 0 then bytes else maxbytes end) maxbytes
from dba_data_files
where tablespace_name != 'UNDO'
group by tablespace_name) a
group by a.tablespace_name
order by PCT_USED desc;

TABLESPACE_NAME  TOT_SIZE_MB TOT_FREE_MB PCT_USED CHUNKS_FREE EXPANDIBLE MAXBYTES_MB
---------------- ----------- ----------- -------- ----------- ---------- -----------
MYDATA                 3,584           4     99.9           4 YES              4,096
MYINDEXES                312           6     98.1           8 YES              1,024
PERFSTAT                 192          48     74.9           1 YES                384
SYSAUX                   300          97     67.7          12 NO                 300
SYSTEM                   312         102     67.4           9 NO                 312
USERS                    128         126      1.7         915 YES              1,024

6 rows selected.

SQL> column FILE_NAME format a45
SQL> select TABLESPACE_NAME, FILE_NAME, AUTOEXTENSIBLE, MAXBYTES
from dba_data_files order by TABLESPACE_NAME, FILE_NAME;

TABLESPACE_NAME                FILE_NAME                    AUT   MAXBYTES
------------------------------ ---------------------------- --- ----------
MYDATA                         /mydb/mydata01.dbf           YES 4294967296
MYINDEXES                      /mydb/myindexes01.dbf        YES 1073741824
PERFSTAT                       /mydb/perfstat.dbf           YES  402653184
SYSAUX                         /mydb/sysaux01.dbf           NO           0
SYSTEM                         /mydb/system01.dbf           NO           0
UNDO                           /mydb/undo01.dbf             NO           0
USERS                          /mydb/users01.dbf            YES 1073741824

7 rows selected.

SQL> alter database datafile '/mydb/mydata01.dbf' AUTOEXTEND ON MAXSIZE 4608M;

Database altered.

SQL> alter database datafile '/mydb/myindexes01.dbf' resize 2G;

Database altered.