SELECT
b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
FROM
v$session a,
v$sort_usage b
WHERE
a.saddr = b.session_addr
ORDER BY
b.tablespace, b.segfile#, b.segblk#, b.blocks

The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:

select sum(bytes)/1024/1024 "Meg"  
from dba_data_files; 

To get the size of all TEMP files: 


select nvl(sum(bytes),0)/1024/1024 "Meg" 
from dba_temp_files;  

To get the size of the on-line redo-logs:

select sum(bytes)/1024/1024 "Meg" 
from sys.v_$log; 

Putting it all together into a single query:

select a.data_size+b.temp_size+c.redo_size "total_size"  
from ( select sum(bytes) data_size           
       from dba_data_files ) a,       
     ( select nvl(sum(bytes),0) temp_size           
       from dba_temp_files ) b,       
     ( select sum(bytes) redo_size           
       from sys.v_$log ) c; 

Another query ("Free space" reports data files free space):

col "Database Size" format a20   
col "Free space" format a20   
select round(sum(used.bytes) / 1024 / 1024 ) || ' MB' "Database Size"   
,      round(free.p / 1024 / 1024) || ' MB' "Free space"   
from (select bytes from v$datafile         
      union all         
      select bytes from v$tempfile         
      union all         
      select bytes from v$log) used   
,     (select sum(bytes) as p 
      from dba_free_space) free   
group by free.p   
/ 

How do I find the used space within the database size?

Select from the DBA_SEGMENTS or DBA_EXTENTS views to find the used space of a database.

SELECT SUM(bytes)/1024/1024 "Meg" 
FROM dba_segments; 

r a table?Where can one find the high water mark for a table?

There is no single system table which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements:

SELECT BLOCKS  
FROM   DBA_SEGMENTS  
WHERE  OWNER=UPPER(owner) 
AND SEGMENT_NAME = UPPER(table);    


ANALYZE TABLE owner.table ESTIMATE STATISTICS; 
   
SELECT EMPTY_BLOCKS  
FROM   DBA_TABLES  
WHERE  OWNER=UPPER(owner) AND TABLE_NAME = UPPER(table); 

Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE.

Instead use the V$TEMP_SPACE_HEADER view

SELECT tablespace_name
, SUM(bytes_used)
, SUM(bytes_free)  
FROM   V$temp_space_header  
GROUP  BY tablespace_name; 

To report true free space within the used portion of the TEMPFILE:

SELECT   A.tablespace_name tablespace, D.mb_total
         ,SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used
         ,D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free  
FROM     v$sort_segment A,           
         (           
         SELECT   B.name
                  , C.block_size
                  , SUM (C.bytes) / 1024 / 1024 mb_total           
         FROM     v$tablespace B, v$tempfile C           
WHERE    B.ts#= C.ts#           
GROUP BY B.name, C.block_size           
         ) D  
WHERE    A.tablespace_name = D.name  
GROUP by A.tablespace_name, D.mb_total; 

select s.username, u."USER"
, u.tablespace
, u.contents
, u.extents
, u.blocks  
from   sys.v_$session s, sys.v_$sort_usage u  
where  s.saddr = u.session_addr  
/ 
   
select s.osuser, s.process
, s.username
, s.serial#
,         sum(u.blocks)*vp.value/1024 sort_size  
from   sys.v_$session s
, sys.v_$sort_usage u
, sys.v_$parameter vp  
where  s.saddr = u.session_addr    
  and  vp.name = 'db_block_size'   
  and  s.osuser like '&1'  
group  by s.osuser
       , s.process
       , s.username
       , s.serial#
       , vp.value  / 

Execute the following query to determine who is using a particular UNDO or Rollback Segment:

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,    
     NVL(s.username, 'None') orauser,    
     s.program,    
     r.name undoseg,    
     t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"    
FROM sys.v_$rollname    r,    
     sys.v_$session     s,    
     sys.v_$transaction t,    
     sys.v_$parameter   x   
WHERE s.taddr = t.addr   
    AND r.usn   = t.xidusn(+)   
   AND x.name  = 'db_block_size' 

Execute the following query to determine who is using a TEMP Segment:

SELECT b.tablespace,    
     ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE",    
     a.sid||','||a.serial# SID_SERIAL,    
     a.username,    
    a.program    
   FROM sys.v_$session a,    
        sys.v_$sort_usage b,    
        sys.v_$parameter p    
   WHERE p.name  = 'db_block_size'   
   AND a.saddr = b.session_addr   
   ORDER BY b.tablespace, b.blocks; 

How does one get the view definition of fixed views/tables?

SELECT * 
FROM v$fixed_view_definition 
WHERE view_name='V$SESSION'; 

How full is the current redo log file?