Search This Blog

Total Pageviews

Tuesday, 6 December 2011

Oracle Tablespace space report

Oracle Space report
Tablespace report


 




SET LINES 150
SET PAGES 50
SET FEEDBACK OFF
SET HEADING OFF
SET AUTOPRINT OFF
--SET NOTIFY OFF
SET VERIFY OFF
PROMPT ..............................................................

SELECT    '** TableSpace Usage Snap For '
       || NAME
       || ' Instance Taken On '
       || RTRIM (TO_CHAR (SYSDATE, 'Day'))
       || ' '
       || TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
       || ' ** '
FROM   v$database;
COMPUTE SUM LABEL 'Total' OF Tot_Spc_Alloc ON report

BREAK ON REPORT
COLUMN TSname        Format A25               Heading 'Tablespace Name'
COLUMN TSid          Format           999     Heading 'Tablespace#'
COLUMN Tot_Spc_Alloc Format 9,999,999,990.00  Heading 'Total Space|Allocated'
COLUMN Tot_Spc_Free  Format   999,999,990.00  Heading 'Total Free|Space'
COLUMN Tot_Spc_Used  Format   999,999,990.00  Heading 'Total Space|Used'
COLUMN Pct_Used      Format           990.000 Heading 'Percent|Used'
COLUMN Pct_Free      Format           990.000 Heading 'Percent|Free'
COLUMN FileName      Format A65               Heading 'File Name'
COLUMN fileid        Format 99999             Heading 'File|ID'
SET HEADING ON
SELECT tsid, tsname, pct_used, pct_free, tot_spc_alloc, tot_spc_used, tot_spc_free
FROM   (SELECT   c.ts# tsid,
                 a.tsname,
                 (1 - (b.tot_spc_free / a.tot_spc_alloc)) * 100 pct_used,
                 (b.tot_spc_free / a.tot_spc_alloc) * 100 pct_free,
                 a.tot_spc_alloc / 1024 / 1024 tot_spc_alloc,
                 (a.tot_spc_alloc - b.tot_spc_free) / 1024 / 1024 tot_spc_used,
                 b.tot_spc_free / 1024 / 1024 tot_spc_free
        FROM     (SELECT   tablespace_name tsname, SUM (BYTES) tot_spc_alloc
                  FROM     DBA_DATA_FILES
                  GROUP BY tablespace_name) a,
                 (SELECT   tablespace_name tsname, SUM (BYTES) tot_spc_free, MAX (BYTES) max_b2
                  FROM     DBA_FREE_SPACE
                  GROUP BY tablespace_name) b,
                 v$tablespace c
        WHERE    a.tsname = b.tsname AND a.tsname = c.NAME
        ORDER BY c.ts#);

SET HEADING ON

SELECT DISTINCT a.file_id fileid,
                SUBSTR (a.file_name, 1, 60) filename,
                (1 - (b.tot_spc_free / a.BYTES)) * 100 pct_used,
                (b.tot_spc_free / a.BYTES) * 100 pct_free,
                a.BYTES / 1024 / 1024 tot_spc_alloc,
                tot_spc_free / 1024 / 1024 tot_spc_free
FROM   DBA_DATA_FILES a,
(SELECT   file_id, tablespace_name tsname,
SUM (BYTES) tot_spc_free, MAX (BYTES) max_b2
            FROM     DBA_FREE_SPACE
           GROUP BY file_id, tablespace_name) b
WHERE     a.file_id = b.file_id
AND a.tablespace_name in (SELECT NAME  FROM  v$tablespace )
order by 1;



SQL> @tablespace_info
..............................................................

** TableSpace Usage Snap For ORCL Instance Taken On Tuesday 06-DEC-2011 12:25:37 **

                                       Percent  Percent       Total Space     Total Space      Total Free
Tablespace# Tablespace Name               Used     Free         Allocated            Used           Space
----------- ------------------------- -------- -------- ----------------- --------------- ---------------
          0 SYSTEM                      96.168    3.832            840.00          807.81           32.19
          1 SYSAUX                      91.325    8.675            830.00          758.00           72.00
          4 USERS                       92.622    7.378            641.25          593.94           47.31
          6 EXAMPLE                     78.750   21.250            100.00           78.75           21.25
          7 ANUJTEST                    80.000   20.000             10.00            8.00            2.00
          8 TSAPEXF                      2.000   98.000             50.00            1.00           49.00
          9 TSAPEXU                     88.636   11.364            110.00           97.50           12.50
         10 TEST                         5.000   95.000             20.00            1.00           19.00
         11 RMAN                        13.875   86.125             50.00            6.94           43.06
         12 DROP1                       50.000   50.000              2.00            1.00            1.00
         13 PERFSTAT                    11.500   88.500          1,000.00          115.00          885.00
         14 UNDOTBR                      3.775   96.225            500.00           18.88          481.13
                                                        -----------------
Total                                                            4,153.25

  File                                                                    Percent  Percent       Total Space      Total Free
    ID File Name                                                             Used     Free         Allocated           Space
------ ----------------------------------------------------------------- -------- -------- ----------------- ---------------
     1 /opt/app/oracle/oradata/orcl/system01.dbf                           96.168    3.832            840.00           32.19
     2 /opt/app/oracle/oradata/orcl/sysaux01.dbf                           91.325    8.675            830.00           72.00
     4 /opt/app/oracle/oradata/orcl/users01.dbf                            92.622    7.378            641.25           47.31
     5 /opt/app/oracle/oradata/orcl/example01.dbf                          78.750   21.250            100.00           21.25
     6 /opt/app/oracle/oradata/orcl/anujtest.dbf                           80.000   20.000             10.00            2.00
     7 /opt/app/oracle/oradata/orcl/tsapexf01.dbf                           2.000   98.000             50.00           49.00
     8 /opt/app/oracle/oradata/orcl/tsapexu01.dbf                          88.636   11.364            110.00           12.50
     9 /opt/app/oracle/oradata/orcl/test.dbf                                5.000   95.000             20.00           19.00
    10 /opt/app/oracle/oradata/orcl/rman.dbf                               13.875   86.125             50.00           43.06
    11 /opt/app/oracle/oradata/orcl/drop.dbf                               50.000   50.000              2.00            1.00
    12 /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf                      11.500   88.500          1,000.00          885.00
    13 /opt/app/oracle/oradata/orcl/undotbR.dbf                             3.775   96.225            500.00          481.13
                                                                                           -----------------
Total                                                                                               4,153.25




No comments:

Oracle DBA

anuj blog Archive