26 April 2011

How to Check tablespace size and freespace

SELECT d.status,
         db.name dbname,
         d.tablespace_name tsname,
         d.extent_management,
         d.allocation_type,
         TO_CHAR (NVL (d.min_extlen / 1024, 0),
                  '99G999G990D90',
                  'NLS_NUMERIC_CHARACTERS = '',.'' ')
            "SIZE In MB",
         d.contents "Type",
         CASE
            WHEN (d.contents = 'TEMP')
            THEN
               TO_CHAR (NVL (a.bytes / 1024 / 1024, 0),
                        '99G999G990D90',
                        'NLS_NUMERIC_CHARACTERS = '',.'' ')
            ELSE
               TO_CHAR (NVL (t.bytes / 1024 / 1024, 0),
                        '99G999G990D90',
                        'NLS_NUMERIC_CHARACTERS = '',.'' ')
         END
            AS "Size In MB",
         TO_CHAR (NVL ( (a.bytes - NVL (f.bytes, 0)) / 1024 / 1024, 0),
                  '99G999G990D90',
                  'NLS_NUMERIC_CHARACTERS = '',.'' ')
            "Used MB",
         TO_CHAR (NVL ( (a.bytes - NVL (f.bytes, 0)) / a.bytes * 100, 0),
                  '990D90',
                  'NLS_NUMERIC_CHARACTERS = '',.'' ')
            "Used Size"
    FROM sys.dba_tablespaces d,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_data_files
          GROUP BY tablespace_name) a,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_temp_files
          GROUP BY tablespace_name) t,
         (  SELECT tablespace_name, SUM (bytes) bytes
              FROM dba_free_space
          GROUP BY tablespace_name) f,
         v$database db
   WHERE     d.tablespace_name = a.tablespace_name(+)
         AND d.tablespace_name = f.tablespace_name(+)
         AND d.tablespace_name = t.tablespace_name(+)
ORDER BY 10 DESC;

Query :-2
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 ;

Query :-3
select b.tablespace_name, tbs_size Total_Size, a.free_space Free
from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
       from dba_free_space
       group by tablespace_name) a,
      (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
       from dba_data_files
       group by tablespace_name) b
where a.tablespace_name(+)=b.tablespace_name order by Total_SIZE desc;

No comments: