Pages

Saturday, February 15, 2020

Query to find Tablespace Space Utilization


Query to find Tablespace Space Utilization in Bytes


SET lines 132 pages 66 feedback off
COLUMN tablespace_name        format a15             heading 'Tablespace|(TBS)|Name'
COLUMN autoextensible         format a6              heading 'Can|Auto|Extend'
COLUMN files_in_tablespace    format 999             heading 'Files|In|TBS'
COLUMN total_tablespace_space format 99,999,999,999 heading 'Total|Current|TBS|Space'
COLUMN total_used_space       format 99,999,999,999 heading 'Total|Current|Used|Space'
COLUMN total_tablespace_free_space format 99,999,999,999 heading 'Total|Current|Free|Space'
COLUMN total_used_pct              format 999.99      heading 'Total|Current|Used|PCT'
COLUMN total_free_pct              format 999.99      heading 'Total|Current|Free|PCT'
COLUMN max_size_of_tablespace      format 99,999,999,999 heading 'TBS|Max|Size'
COLUMN total_auto_used_pct         format 999.99      heading 'Total|Max|Used|PCT'
COLUMN total_auto_free_pct         format 999.99      heading 'Total|Max|Free|PCT'

TTITLE left _date center Tablespace Space Utilization Status Report skip 2

WITH tbs_auto AS
     (SELECT DISTINCT tablespace_name, autoextensible
                 FROM dba_data_files
                WHERE autoextensible = 'YES'),
     files AS
     (SELECT   tablespace_name, COUNT (*) tbs_files,
               SUM (BYTES) total_tbs_bytes
          FROM dba_data_files
      GROUP BY tablespace_name),
     fragments AS
     (SELECT   tablespace_name, COUNT (*) tbs_fragments,
               SUM (BYTES) total_tbs_free_bytes,
               MAX (BYTES) max_free_chunk_bytes
          FROM dba_free_space
      GROUP BY tablespace_name),
     AUTOEXTEND AS
     (SELECT   tablespace_name, SUM (size_to_grow) total_growth_tbs
          FROM (SELECT   tablespace_name, SUM (maxbytes) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'YES'
                GROUP BY tablespace_name
                UNION
                SELECT   tablespace_name, SUM (BYTES) size_to_grow
                    FROM dba_data_files
                   WHERE autoextensible = 'NO'
                GROUP BY tablespace_name)
      GROUP BY tablespace_name)
SELECT a.tablespace_name,
       CASE tbs_auto.autoextensible
          WHEN 'YES'
             THEN 'YES'
          ELSE 'NO'
       END AS autoextensible,
       files.tbs_files files_in_tablespace,
       files.total_tbs_bytes total_tablespace_space,
       (files.total_tbs_bytes - fragments.total_tbs_free_bytes
       ) total_used_space,
       fragments.total_tbs_free_bytes total_tablespace_free_space,
       (  (  (files.total_tbs_bytes - fragments.total_tbs_free_bytes)
           / files.total_tbs_bytes
          )
        * 100
       ) total_used_pct,
       ((fragments.total_tbs_free_bytes / files.total_tbs_bytes) * 100
       ) total_free_pct,
       AUTOEXTEND.total_growth_tbs max_size_of_tablespace,
       (  (  (  AUTOEXTEND.total_growth_tbs
              - (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes
                )
             )
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_used_pct,
       (  (  (AUTOEXTEND.total_growth_tbs - fragments.total_tbs_free_bytes)
           / AUTOEXTEND.total_growth_tbs
          )
        * 100
       ) total_auto_free_pct
  FROM dba_tablespaces a, files, fragments, AUTOEXTEND, tbs_auto
 WHERE a.tablespace_name = files.tablespace_name
   AND a.tablespace_name = fragments.tablespace_name
   AND a.tablespace_name = AUTOEXTEND.tablespace_name
   AND a.tablespace_name = tbs_auto.tablespace_name(+);

Query to find Tablespace Space Utilization in MBs



clear breaks
clear computes
col database_name noprint new_value db_name
col TODAY noprint NEW_VALUE _DATE
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY HH24:MI:SS') TODAY from DUAL;
set termout on
TTITLE OFF
select name database_name from v$database;

TTITLE left _DATE CENTER "Tablespace used by " db_name " database" Skip 1 -
CENTER "-----------------------------------" skip 2
col tablespace_name format a15 heading 'Tbsc|Name'
col initial_extent_size format 99,99,999 heading 'Initial|Extent|in (KB)'
col next_extent_size format 99,99,999 heading 'Next|Extent|in (KB)'
col min_extents format 99 heading 'Min|Extent'
col max_extents heading 'Max|Extent'
col status format a8 heading 'Status'
col contents format a9 heading 'Type'
col avail format 99,99,990.90 heading 'Total Size|(in Mb)'
col free format 99,99,990.90 heading 'Free |(in Mb)'
col used format 99,99,990.90 heading 'Used |(in Mb)'
col extent_management format a10 heading 'TYPE |(Extent |Mgmnt )'

SELECT
        dts.tablespace_name,
        initial_extent/1024 initial_extent_size,
        next_extent/1024 next_extent_size,
        NVL(ddf.bytes / 1024 / 1024, 0) avail,
        NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
        NVL(dfs.bytes / 1024 / 1024, 0) free,
        TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %",
        dts.contents,
        dts.extent_management,
        dts.status
FROM
        sys.dba_tablespaces dts,
        (select tablespace_name, sum(bytes) bytes
                from dba_data_files group by tablespace_name) ddf,
                        (select tablespace_name, sum(bytes) bytes
                                from dba_free_space group by tablespace_name) dfs
                                WHERE
                                        dts.tablespace_name = ddf.tablespace_name(+)
                                AND dts.tablespace_name = dfs.tablespace_name(+)
                                AND NOT (dts.extent_management like 'LOCAL'
                                AND dts.contents like 'TEMPORARY')
                                UNION ALL
                                SELECT dts.tablespace_name,
                                        initial_extent/1024 initial_extent_size,
                                        next_extent/1024 next_extent_size,
                                        NVL(dtf.bytes / 1024 / 1024, 0) avail,
                                        NVL(t.bytes, 0)/1024/1024 used,
                                        NVL(dtf.bytes - NVL(t.bytes, 0), 0)/1024/1024 free,
                                        TO_CHAR(NVL(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %",
                                        dts.contents,
                                        dts.extent_management,
                                        dts.status
                                FROM
                                        sys.dba_tablespaces dts,
                                        (select tablespace_name, sum(bytes) bytes
                                        from dba_temp_files group by tablespace_name) dtf,
                                                (select tablespace_name, sum(bytes_used) bytes
                                                from v$temp_space_header group by tablespace_name) t
        WHERE
        dts.tablespace_name = dtf.tablespace_name(+)
        AND dts.tablespace_name = t.tablespace_name(+)
        AND dts.extent_management like 'LOCAL'
        AND dts.contents like 'TEMPORARY';

set feedback on
set verify on
clear breaks
clear computes
clear columns
set pagesize 100
set echo on
set echo off
set linesize 999
set termout off

Query to find Tablespace Space Utilization in GBs


set echo off
set feedback off verify off pagesize 100 linesize 120
clear breaks
clear computes
col database_name noprint new_value db_name
col TODAY noprint NEW_VALUE _DATE
set termout off
select to_char(SYSDATE,'fmMonth DD, YYYY HH24:MI:SS') TODAY from DUAL;
set termout on
TTITLE OFF
select name database_name from v$database;

TTITLE left _DATE CENTER "Tablespace used by " db_name " database" Skip 1 -
CENTER "-----------------------------------" skip 2
col tablespace_name format a15 heading 'Tbsc|Name'
col initial_extent_size format 99,99,999 heading 'Initial|Extent|in (KB)'
col next_extent_size format 99,99,999 heading 'Next|Extent|in (KB)'
col min_extents format 99 heading 'Min|Extent'
col max_extents heading 'Max|Extent'
col status format a8 heading 'Status'
col contents format a9 heading 'Type'
col avail format 99,99,990.90 heading 'Total Size|(in Gb)'
col free format 99,99,990.90 heading 'Free |(in Gb)'
col used format 99,99,990.90 heading 'Used |(in Gb)'
col extent_management format a10 heading 'TYPE |(Extent |Mgmnt )'

SELECT
        dts.tablespace_name,
        initial_extent/1024 initial_extent_size,
        next_extent/1024 next_extent_size,
        NVL(ddf.bytes / 1024 / 1024 /1024, 0) avail,
        NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024/1024 used,
        NVL(dfs.bytes / 1024 / 1024/1024, 0) free,
        TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00') "Used %",
        dts.contents,
        dts.extent_management,
        dts.status
FROM
        sys.dba_tablespaces dts,
        (select tablespace_name, sum(bytes) bytes
                from dba_data_files group by tablespace_name) ddf,
                        (select tablespace_name, sum(bytes) bytes
                                from dba_free_space group by tablespace_name) dfs
                                WHERE
                                        dts.tablespace_name = ddf.tablespace_name(+)
                                AND dts.tablespace_name = dfs.tablespace_name(+)
                                AND NOT (dts.extent_management like 'LOCAL'
                                AND dts.contents like 'TEMPORARY')
                                UNION ALL
                                SELECT dts.tablespace_name,
                                        initial_extent/1024 initial_extent_size,
                                        next_extent/1024 next_extent_size,
                                        NVL(dtf.bytes / 1024 / 1024 /1024, 0) avail,
                                        NVL(t.bytes, 0)/1024/1024 /1024 used,
                                        NVL(dtf.bytes - NVL(t.bytes, 0), 0)/1024/1024/1024 free,
                                        TO_CHAR(NVL(t.bytes / dtf.bytes * 100, 0), '990.00') "Used %",
                                        dts.contents,
                                        dts.extent_management,
                                        dts.status
                                FROM
                                        sys.dba_tablespaces dts,
                                        (select tablespace_name, sum(bytes) bytes
                                        from dba_temp_files group by tablespace_name) dtf,
                                                (select tablespace_name, sum(bytes_used) bytes
                                                from v$temp_space_header group by tablespace_name) t
        WHERE
        dts.tablespace_name = dtf.tablespace_name(+)
        AND dts.tablespace_name = t.tablespace_name(+)
        AND dts.extent_management like 'LOCAL'
        AND dts.contents like 'TEMPORARY';

set feedback on
set verify on
clear breaks
clear computes
clear columns
set pagesize 100
set echo on
set echo off
set linesize 999
set termout off
Posted by at
Labels:

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)