[Solved] How do you see Oracle tablespace usage and increase tablespace size?

To see your tablespace usage login as system user and run this SQL:

SELECT b.tablespace_name,
  tbs_size SizeMb,
  a.free_space FreeMb
FROM
  (SELECT tablespace_name,
    ROUND(SUM(bytes)/1024/1024 ,2) AS free_space
  FROM dba_free_space
  GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name,
    SUM(bytes)/1024/1024 AS tbs_size
  FROM dba_data_files
  GROUP BY tablespace_name
  ) b
WHERE a.tablespace_name(+)=b.tablespace_name;

Here we see i am out of space on "SYSTEM"

To increasing the tablespace can be done through the "Oracle SQL Developer" GUI. Select "View->DBA"

In the "DBA" panel that opens add a connection for the "SYSTEM" or administrative oracle user. Then select "Storage->Tablespaces".

Right click on the tablespace which has run out of space. Check unlimited and click "OK".

Another option for increasing tablespace size:

In sqlplus, run the following as SYSTEM:

SET LINESIZE 200;
select 'alter database datafile '''||    file_name||    ''' '||    ' autoextend on maxsize unlimited;' from    dba_data_files;

This will spit out lines like the following. Copy and paste those lines to execute them.

alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\USERS.DBF'  autoextend on maxsize unlimited;
alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSAUX.DBF'  autoextend on maxsize unlimited;
alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\UNDOTBS1.DBF'  autoextend on maxsize unlimited;
alter database datafile 'C:\ORACLEXE\APP\ORACLE\ORADATA\XE\SYSTEM.DBF'  autoextend on maxsize unlimited;