SQL scripts for checking database tablespce

When a database tablespace is full, EBS will not work correctly. For example, I got below warnings from adpatch:

AutoPatch warning:
Error while Inserting into temp table (AD_PATCH_HIST_TEMP)
AutoPatch warning:
ORA-01653: unable to extend table APPLSYS.AD_PATCH_HIST_TEMP by 16 in tablespace APPS_TS_INTERFACE

Unable to Create Patch Objects
Error calling adphistStoreCurPatchHistInDB2

That came from last stage of applying a patch, and seems it is ignorable. But, it is good idea to check tablespace availability before applying apps patch.

Here are scripts check if free space (in 11g and 12c) is available:

SQL> SELECT ddf.tablespace_name,
       SUM( distinct ddf.ddfbytes )/1048576 avail_MB,
       SUM( NVL( ds.bytes , 0 ) / 1048576 ) used_MB
FROM
   ( SELECT tablespace_name, SUM( bytes ) ddfbytes
     FROM dba_data_files
     GROUP BY tablespace_name ) ddf,
   dba_segments ds
WHERE ddf.tablespace_name = ds.tablespace_name (+)
GROUP BY ddf.tablespace_name
;

SQL> select * from dba_data_files;

SQL> select * from dba_temp_files;

SQL> select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB,
(total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name, i.host_name
from v$sort_segment ss, v$instance i
where ss.tablespace_name in
(select tablespace_name from dba_tablespaces where contents='TEMPORARY')
 ;

Besides full tablespace, disk space for database archive logs could be full. In that case, the EBS login page may give error:

Unable to generate forwarding URL. Exception: oracle.apps.fnd.common.AppsException: oracle.apps.fnd.common.PoolException: Exception creating new Poolable object.

And Sqlplus login may fail with
ERROR:
ORA-00257: archiver error. Connect internal only, until freed.

When there is a tablespace or database problem, the EBS login page may hang and then give error in IE:

Internal Server Error
The server encountered an internal error or misconfiguration and was unable to complete your request.
Please contact the server administrator, appl_mgr@xxxx.com and inform them of the time the error occurred, and anything you might have done that may have caused the error.
More information about this error may be available in the server error log.