Tablespace datafiles

SET LINESIZE 147
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN tablespace      FORMAT a29                HEADING 'Tablespace Name'
COLUMN filename        FORMAT a64                HEADING 'Filename'
COLUMN filesize        FORMAT 9,999,999,999,999  HEADING 'File Size'
COLUMN autoextensible  FORMAT a4                 HEADING 'Auto'
COLUMN increment_by    FORMAT 999,999,999        HEADING 'Next'
COLUMN maxbytes        FORMAT 99,999,999,999     HEADING 'Max'

BREAK ON report
COMPUTE SUM OF filesize  ON report

SELECT /*+ ordered */
    d.tablespace_name                     tablespace
  , d.file_name                           filename
  , d.bytes                               filesize
  , d.autoextensible                      autoextensible
  , d.increment_by * e.value              increment_by
  , d.maxbytes                            maxbytes
FROM
    sys.dba_data_files d
  , v$datafile v
  , (SELECT value
     FROM v$parameter 
     WHERE name = 'db_block_size') e
WHERE
  (d.file_name = v.name)
and d.tablespace_name = upper('&TABLESPACE_NAME')
/

Tablespace usage

SET LINESIZE 141
SET PAGESIZE 9999
SET VERIFY   OFF

COLUMN status      FORMAT a9                 HEADING 'Status'
COLUMN name        FORMAT a22                HEADING 'Tablespace Name'
COLUMN type        FORMAT a12                HEADING 'TS Type'
COLUMN extent_mgt  FORMAT a10                HEADING 'Ext. Mgt.'
COLUMN segment_mgt FORMAT a9                 HEADING 'Seg. Mgt.'
COLUMN ts_size     FORMAT 9,999,999,999,999  HEADING 'Tablespace Size'
COLUMN used        FORMAT 9,999,999,999,999  HEADING 'Used (in bytes)'
COLUMN free        FORMAT 9,999,999,999,999  HEADING 'Free (in bytes)'
COLUMN pct_used    FORMAT 999                HEADING 'Pct. Used'

BREAK ON report
COMPUTE SUM OF ts_size  ON report
COMPUTE SUM OF used     ON report
COMPUTE SUM OF free     ON report
COMPUTE AVG OF pct_used ON report

SELECT
    d.status                                            status
  , d.tablespace_name                                   name
  , d.contents                                          type
  , d.extent_management                                 extent_mgt
  , d.segment_space_management                          segment_mgt
  , NVL(a.bytes, 0)                                     ts_size
  , NVL(a.bytes - NVL(f.bytes, 0), 0)                   used
  , NVL(f.bytes, 0)                                     free
  , NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0) pct_used
FROM 
    sys.dba_tablespaces d
  , ( select tablespace_name, sum(bytes) bytes
      from dba_data_files
      group by tablespace_name
    ) a
  , ( select tablespace_name, sum(bytes) bytes
      from dba_free_space
      group by tablespace_name
    ) f
WHERE
      d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND NOT (
    d.extent_management like 'LOCAL'
    AND
    d.contents like 'TEMPORARY'
  )
and a.tablespace_name = upper('&TABLESPACE_NAME')
/

Add new datafile to tablespace

alter tablespace TABLESPACE_NAME add datafile 'NEW_DATAFILE_NAME' size 1G autoextend on next 128M maxsize 16G;