/*
Module        emagent_SQL_oracle_database
Action        db_tablespaces
Parsing Schema        DBSNMP

Execution Statistics

Total    Per Execution    Per Row
Executions    1    1    n/a
Elapsed Time (sec)    405.43    405.43    n/a
CPU Time (sec)    30.37    30.37    n/a
Buffer Gets    529,079    529,079.00    n/a
Disk Reads    126,691    126,691.00    n/a
Direct Writes    0    0.00    n/a
Rows    0    0.00    n/a
*/
SELECT dtp.tablespace_name "TABLESPACE_NAME", dtp.status "STATUS", dtp.contents "CONTENTS", dtp.extent_management "EXTENT MANAGEMENT",
dtp.allocation_type "ALLOCATION TYPE", dtp.logging "LOGGING", NVL(ts.bytes, 0) "SIZE (M)", dtp.initial_extent "INITIAL EXT SIZE (K)",
dtp.next_extent "Next Extent", dtp.pct_increase "INCREMENT PCT", dtp.max_extents "MAX EXTENTS",
NVL(ts.bytes - NVL(f.bytes, 0), 0) "USED BYTES", dtp.min_extents, dtp.min_extlen,
dtp.segment_space_management "SEGMENT SPACE MANAGEMENT", dtp.block_size, dtp.bigfile
  FROM sys.dba_tablespaces dtp,
(      SELECT   tablespace_name, SUM(bytes) bytes
           FROM dba_data_files
       GROUP BY tablespace_name) ts,
(      SELECT   tablespace_name, SUM(bytes) bytes
           FROM dba_free_space
       GROUP BY tablespace_name) f
 WHERE     dtp.tablespace_name = ts.tablespace_name(+)
AND dtp.tablespace_name = f.tablespace_name(+)
AND NOT (dtp.extent_management LIKE 'LOCAL' AND dtp.contents LIKE 'TEMPORARY')
UNION ALL
SELECT dtp.tablespace_name "TABLESPACE_NAME", dtp.status "STATUS", dtp.contents "CONTENTS", dtp.extent_management "EXTENT MANAGEMENT",
dtp.allocation_type "ALLOCATION TYPE", dtp.logging "LOGGING", NVL(a.bytes, 0) "SIZE (M)", dtp.initial_extent "INITIAL EXT SIZE (K)",
dtp.next_extent "Next Extent", dtp.pct_increase "INCREMENT PCT", dtp.max_extents "MAX EXTENTS", NVL(t.bytes, 0) "USED BYTES",
dtp.min_extents, dtp.min_extlen, dtp.segment_space_management "SEGMENT SPACE MANAGEMENT", dtp.block_size, dtp.bigfile
  FROM sys.dba_tablespaces dtp,
(      SELECT   tablespace_name, SUM(bytes) bytes
           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     dtp.tablespace_name = a.tablespace_name(+)
AND dtp.tablespace_name = t.tablespace_name(+)
AND dtp.extent_management LIKE 'LOCAL'
AND dtp.contents LIKE 'TEMPORARY';

/*
SQL Details: 0vnw4qxp6vg21
Module        Admin Connection
Action        /database/databaseObjectsSearch
Execution Statistics

Total    Per Execution    Per Row
Executions    1    1    <0.01
Elapsed Time (sec)    463.23    463.23    2.39
CPU Time (sec)    34.54    34.54    0.18
Buffer Gets    611,553    611,553.00    3,152.34
Disk Reads    144,636    144,636.00    745.55
Direct Writes    3    3.00    0.02
Rows    194    194.00    1
*/

WITH df AS
      (SELECT   tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
           FROM dba_data_files
       GROUP BY tablespace_name),
     tf AS
      (SELECT   tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible, 'NO', 0, 1)), 0, 'NO', 'YES') autoext
           FROM dba_temp_files
       GROUP BY tablespace_name),
     sp AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics)
SELECT d.tablespace_name, TO_CHAR(s.used_percent, '99999990.0'), NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), a.autoext,
       NVL(a.bytes / 1024 / 1024, 0), NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024, NVL(f.bytes, 0) / 1024 / 1024, d.status, a.cnt,
       d.contents, d.extent_management, d.segment_space_management
  FROM dba_tablespaces d,
       df a,
       sp s,
       (SELECT   tablespace_name, SUM(bytes) bytes
            FROM dba_free_space
        GROUP BY tablespace_name) f
 WHERE     d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND d.tablespace_name = s.tablespace_name(+)
       AND NOT d.contents = 'UNDO'
       AND NOT (d.extent_management = 'LOCAL' AND d.contents = 'TEMPORARY')
       AND d.tablespace_name LIKE :1
UNION ALL
SELECT d.tablespace_name, TO_CHAR(s.used_percent, '99999990.0'), NVL((t.ub * d.block_size) / a.bytes * 100, 0), a.autoext,
       NVL(a.bytes / 1024 / 1024, 0), NVL(t.ub * d.block_size, 0) / 1024 / 1024,
       (NVL(a.bytes, 0) / 1024 / 1024 - NVL((t.ub * d.block_size), 0) / 1024 / 1024), d.status, a.cnt, d.contents, d.extent_management,
       d.segment_space_management
  FROM dba_tablespaces d,
       tf a,
       sp s,
       (SELECT   ss.tablespace_name, SUM(ss.used_blocks) ub
            FROM gv$sort_segment ss
        GROUP BY ss.tablespace_name) t
 WHERE     d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = t.tablespace_name(+)
       AND d.tablespace_name = s.tablespace_name(+)
       AND d.extent_management = 'LOCAL'
       AND d.contents = 'TEMPORARY'
       AND d.tablespace_name LIKE :2
UNION ALL
SELECT d.tablespace_name, TO_CHAR(s.used_percent, '99999990.0'), NVL(u.bytes / a.bytes * 100, 0), a.autoext, NVL(a.bytes / 1024 / 1024, 0),
       NVL(u.bytes, 0) / 1024 / 1024, NVL(a.bytes - NVL(u.bytes, 0), 0) / 1024 / 1024, d.status, a.cnt, d.contents, d.extent_management,
       d.segment_space_management
  FROM dba_tablespaces d,
       df a,
       sp s,
       (SELECT   tablespace_name, SUM(bytes) bytes
            FROM dba_undo_extents
           WHERE status IN ('ACTIVE', 'UNEXPIRED')
        GROUP BY tablespace_name) u
 WHERE     d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = u.tablespace_name(+)
       AND d.tablespace_name = s.tablespace_name(+)
       AND d.contents = 'UNDO'
       AND d.tablespace_name LIKE :3
ORDER BY 1;