SQL statement to get tablespace_free values (Oracle)

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

What SQL statement is used by the oracle probe to get tablespace_free values?

 

 

 

Environment

Unified Infrastructure Management (UIM) oracle probe

Resolution

The following query is used to get tablespace_free checkpoint values for the oracle probe. Run it in the SQLPLUS query engine, and it must be run as the "sysdba" user (command: "sqlplus.exe / as sysdba").  ->

SELECT d.tablespace_name as name, NVL (ddf.BYTES - NVL(u.BYTES, 0), 0) as freeSp, DECODE (d.CONTENTS, 'UNDO', NVL (TRUNC ((ddf.BYTES - NVL (u.BYTES, 0))/(ddf.bytes)*100,3),0), NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100,3), 0)) as avPct, NVL (dfs.antall, 0) as chunks, ddf.autoextend_flag as autoext, NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize, NVL (TRUNC ( (ddf.BYTES - NVL (dfs.BYTES, 0))/(ddf.maxbytes)*100,3),0) as maxPct FROM dba_tablespaces d, (SELECT tablespace_name, SUM (BYTES) BYTES, MAX (BYTES) maxbytes, COUNT (1) antall FROM dba_free_space GROUP BY tablespace_name) dfs, (SELECT tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag FROM dba_data_files GROUP BY tablespace_name) ddf, (SELECT tablespace_name, SUM (BYTES) BYTES FROM dba_undo_extents WHERE status <> ('EXPIRED') GROUP BY tablespace_name) u WHERE d.tablespace_name = ddf.tablespace_name(+) AND d.tablespace_name = dfs.tablespace_name(+) AND d.tablespace_name = u.tablespace_name(+) AND NOT (d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY') UNION ALL SELECT LTRIM (d.tablespace_name) as name, NVL (TRUNC (ddf.BYTES), 0) - NVL (TRUNC (dfs.BYTES), 0) as freeSp, 100 - NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0) as avPct, DECODE (NVL (TRUNC (dfs.BYTES / ddf.BYTES * 100), 0),0, 1,100, 0) as chunks, ddf.autoextend_flag as autoext, NVL (TRUNC ((ddf.maxbytes)), 0) as maxSize, NVL (TRUNC ( (ddf.BYTES - NVL (dfs.BYTES, 0)) / (ddf.maxbytes)* 100,3),0) as maxPct FROM dba_tablespaces d, (SELECT tablespace_name, SUM (BYTES) BYTES, SUM (GREATEST (maxbytes, BYTES)) maxbytes, COUNT (1) antall, MAX(autoextensible) autoextend_flag FROM dba_temp_files GROUP BY tablespace_name) ddf, (SELECT ss.tablespace_name, SUM ((ss.used_blocks * ts.BLOCKSIZE)) BYTES, MAX ((ss.used_blocks * ts.BLOCKSIZE)) maxbytes, COUNT (1) antall FROM gv$sort_segment ss, SYS.ts$ ts WHERE ss.tablespace_name = ts.NAME GROUP BY ss.tablespace_name) dfs WHERE d.tablespace_name = ddf.tablespace_name(+) AND d.tablespace_name = dfs.tablespace_name(+) AND d.extent_management LIKE 'LOCAL' AND d.CONTENTS LIKE 'TEMPORARY' ORDER BY 1;