Square DBA – Oracle DBA Talk

A subquery in the FROM clause of a SELECT statement is also called an inline view. When a query contains an inline view, the inline is merged into the query. Powerful constructs can be built using inline views as the next example shows: check the tablespace available, free and used space.

Example

set linesize 100
column file_name format a40 heading "File"
column tablespace_name format a10 heading "Tablespace"
column allocated format 999,999,999 heading "Allocated"
column free format 999,999,999 heading "Free"
column used format 999,999,999 heading "Used"

select a.file_name file_name,
       a.tablespace_name tablespace_name,
       a.bytes allocated,
       nvl(b.free,0) free,
       a.bytes-nvl(b.free,0) used
  from dba_data_files a, (select file_id, sum(bytes) free
                          from dba_free_space
                          group by file_id) b
 where a.file_id = b.file_id (+);