SQLs for DBA

Tablespace Usage:

SELECT d.tablespace_name,
  ROUND (NVL (a.bytes / 1024 / 1024, 0), 2) size_in_mb,
  ROUND (DECODE (d.contents, 'UNDO', NVL (u.bytes, 0) / 1024 / 1024, NVL (a.bytes - NVL (f.bytes, 0), 0) / 1024 / 1024), 2) usage_in_mb,
  ROUND (DECODE (d.contents, 'UNDO', NVL (u.bytes / a.bytes * 100, 0), NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0)), 2) usage_perc,
  a.autoext,
  ROUND (DECODE (d.contents, 'UNDO', NVL (a.bytes - NVL (u.bytes, 0), 0) / 1024 / 1024, NVL (f.bytes, 0) / 1024 / 1024), 2) free_in_mb,
  d.status, a.num_of_file, d.contents, d.extent_management, d.segment_space_management
FROM sys.dba_tablespaces d,
  (
    SELECT tablespace_name,
      SUM (bytes) bytes, COUNT (file_id) num_of_file,
      DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)),0, 'NO', 'YES') autoext
    FROM dba_data_files
    GROUP BY tablespace_name
  ) a,
  (SELECT tablespace_name, SUM (bytes) bytes FROM dba_free_space GROUP BY tablespace_name) f,
  (
    SELECT tablespace_name, SUM (bytes) bytes
    FROM ( 
      SELECT tablespace_name, SUM (bytes) bytes, status FROM dba_undo_extents WHERE status = 'ACTIVE'
      GROUP BY tablespace_name, status
     
      UNION ALL
      SELECT tablespace_name, SUM (bytes) bytes, status FROM dba_undo_extents WHERE status = 'UNEXPIRED'
      GROUP BY tablespace_name, status
    )
    GROUP BY tablespace_name
  ) u
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = f.tablespace_name(+)
  AND d.tablespace_name = u.tablespace_name(+)
  AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')

UNION ALL
SELECT d.tablespace_name,
  NVL (a.bytes / 1024 / 1024, 0) size_in_MB,
  ROUND (NVL (t.bytes, 0) / 1024 / 1024, 2) usage_in_MB,
  ROUND (NVL (t.bytes / a.bytes * 100, 0), 2) usage_perc,
  a.autoext,
  ROUND ((NVL (a.bytes, 0) / 1024 / 1024 - NVL (t.bytes, 0) / 1024 / 1024), 2) free_in_MB,
  d.status, a.COUNT, d.contents, d.extent_management, d.segment_space_management
FROM sys.dba_tablespaces d,
  (
    SELECT tablespace_name, SUM (bytes) bytes, COUNT (file_id) COUNT,
      DECODE (SUM (DECODE (autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
    FROM dba_temp_files
    GROUP BY tablespace_name
  ) a,
  (
    SELECT ss.tablespace_name, SUM ( (ss.used_blocks * ts.blocksize)) bytes
    FROM gv$sort_segment ss, sys.ts$ ts
    WHERE ss.tablespace_name = ts.name
    GROUP BY ss.tablespace_name
  ) t
WHERE d.tablespace_name = a.tablespace_name(+)
  AND d.tablespace_name = t.tablespace_name(+)
  AND d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY'
ORDER BY 1 desc