Oracle DB Administration Part 3

To check TEMP file which has been used for execution processes:

SELECT tablespace_name, file_name, sum(bytes)/(1024*1024) Temp_Size_MB 
FROM dba_temp_files 
WHERE tablespace_name = 'TEMP' 
GROUP BY tablespace_name, file_name;

Result:

To check TEMP file during processing of some Client’s request:

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;

Result:

SELECT inst_id, tablespace_name, total_blocks, used_blocks, free_blocks 
FROM gv$sort_segment;

To check a free disk space within all of schemas of the current DB Server:

 SELECT 
        df.tablespace_name "Tablespace",
        totalusedspace "Used MB",
        (df.totalspace - tu.totalusedspace) "Free MB",
        df.totalspace "Total MB",
        round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace)) "Pct. Free"
 FROM
         (SELECT tablespace_name, round(sum(bytes) / 1048576) TotalSpace
          FROM dba_data_files
          GROUP BY tablespace_name) df,
         (SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
          FROM dba_segments
          GROUP BY tablespace_name) tu
 WHERE df.tablespace_name = tu.tablespace_name ; 

Result:

Look at each of datafile separately:

SELECT Substr(df.tablespace_name,1,20) "Tablespace Name",
             Substr(df.file_name,1,40) "File Name",
             Round(df.bytes/1024/1024,2) "Size (M)",
             Round(e.used_bytes/1024/1024,2) "Used (M)",
             Round(f.free_bytes/1024/1024,2) "Free (M)",
             Rpad(' '|| Rpad ('X',Round(e.used_bytes*10/df.bytes,0), 'X'),11,'-') "% Used"
 FROM DBA_DATA_FILES DF,
         (SELECT file_id,
                     Sum(Decode(bytes,NULL,0,bytes)) used_bytes
         FROM dba_extents
         GROUP by file_id) E,
         (SELECT Max(bytes) free_bytes,
                     file_id
         FROM dba_free_space
         GROUP BY file_id) f
 WHERE e.file_id (+) = df.file_id AND df.file_id = f.file_id (+)
 ORDER BY df.tablespace_name, df.file_name;

Result:

If there’re some huge LOB filled up the latest *.dbf file:

SELECT owner, table_name, column_name
   FROM dba_lobs
  WHERE segment_name = 'SYS_LOB0000076290C00004$$'

Result: