Datafiles

<- System

Add

ALTER TABLESPACE <TableSpaceName>
ADD DATAFILE 'DatafilePathAndName'
SIZE <nnnnM|nnnnG>;

SQLPLUS> ALTER TABLESPACE users ADD DATAFILE '/u01/oradata/DB1/users02.dbf' SIZE 25M;
A BFTS can only have 1 datafile.

Display

 COL tablespace_name FORMAT a25
 COL file_name       FORMAT a60

-- Database Size

 SELECT  round((a.data_size + b.temp_size + c.redo_size)/power(1000,3)) "TOTAL 
 (gb)"
 FROM ( SELECT sum(bytes) data_size FROM dba_data_files ) a,
      ( SELECT sum(bytes) temp_size FROM dba_temp_files ) b,
      ( SELECT sum(bytes) redo_size FROM sys.v_$log     ) c;

-- File_name, Tablespace_name and Size

 SELECT file_name, tablespace_name, bytes
 FROM dba_data_files
 ORDER BY file_name, tablespace_name, bytes;

-- Datafiles that Cannot Be AutoExtended

 SELECT tablespace_name, file_name, AutoExtensible, MaxBytes, Increment_By
 FROM dba_data_files
 WHERE AutoExtensible != 'YES'
 ORDER BY 1,2;

-- Temp Tablespace Files

 -- Files
 set pages 999
 set lines 400
 col TABLESPACE_NAME format a12 HEAD 'TS'
 col FILE_NAME       format a50
 SELECT d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024 SIZE_MB,
        d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB, 
        d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
 FROM dba_temp_files d, v$tempfile v
 WHERE d.FILE_ID = v.FILE#
 ORDER by d.TABLESPACE_NAME, d.FILE_NAME;

 -- Size Used
 SELECT round(sum(bytes)/power(1000,3)) "Temp_Tablespace (gb)" 
 FROM dba_temp_files;

-- Redo Log Files

 SELECT round(sum(bytes)/power(1000,2)) "Redo_Logs (mb)" 
 FROM sys.v_$log;

Drop

ALTER TABLESPACE <TablespaceName> DROP DATAFILE 'datafile_path_and_name'; 

 ALTER TABLESPACE users DROP DATAFILE '/u01/oradata/DB1/users02.dbf';

Move

1. ALTER TABLESPACE <TablespaceName> OFFLINE NORMAL;
2. Copy source_datafile(s) to destination_datafile(s) location.
3. ALTER DATABASE RENAME FILE <'Source_Datafile(s)_Location'>
   TO <'Destination_Datafile(s)_Location'>;
4. ALTER TABLESPACE <TablespaceName> ONLINE;

 ALTER TABLESPACE users OFFLINE NORMAL;
 OS> cp /u02/oradata/DB1/users.dbf /u06/oradata/DB1/users.dbf
 ALTER DATABASE RENAME FILE '/u02/oradata/DB1/users.dbf'
       TO '/u06/oradata/DB1/users.dbf';
 ALTER TABLESPACE users ONLINE;

Delete the original\source datafile at OS level after appropriate confirmation that all is working OK for your environment.

Resize

-- Standard

ALTER DATABASE DATAFILE 'datafile_path_and_name' RESIZE <nnnnM|nnnnG>; 

 ALTER DATABASE DATAFILE '/u01/DB1/users.dbf' RESIZE 1024M;

-- Temp Tablespace Files

ALTER DATABASE TEMPFILE 'tempfile_path_and_name' RESIZE <nnnnM|nnnnG>; 

 ALTER DATABASE TEMPFILE '/u01/DB1/temp01.dbf' RESIZE 1g;

<- System