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

Oracle SGA and growth stat

 

 

 

SGA Report
SGA and growth stat




sga.sql

===========

  set serverout on

  declare

  dbname        varchar2(15);     -- Database Name
  tsgasize      number;           -- Total SGA Size
  bcsize        number;           -- Buffer Cache Size
  spsize        number;           -- Shared Pool Size
  jpsize        number;           -- Java Pool Size
  lpsize        number;           -- Large Pool Size
  fsize         number;           -- Fixed SGA Size
  rbsize        number;           -- Redo Buffers
  used          number;           -- Used SGA Memory
  free          number;           -- Free SGA Memory
  granule_size  number;           -- Granule Size
  tvsize        number;           -- Total Variable Size

  cursor c1 is
  select name, value from sys.v$parameter where name in ('java_pool_size', 'large_pool_size');

  cursor c2 is
  select name, value from sys.v$sga;

  begin
  select name into dbname from sys.v$database;
  select x.ksppstvl/(1024*1024) into granule_size
  from sys.x$ksppsv x, sys.x$ksppi y
  where x.indx=y.indx
  and y.ksppinm='_ksmg_granule_size';

  for cur1 in c1 loop
  case cur1.name
  when 'java_pool_size' then jpsize  := cur1.value;
  when 'large_pool_size' then lpsize := cur1.value;
  end case;
  end loop;

  for cur2 in c2 loop
  case cur2.name
  when 'Fixed Size' then fsize        := cur2.value;
  when 'Variable Size' then tvsize    := cur2.value;
  when 'Database Buffers' then bcsize :=cur2.value;
  when 'Redo Buffers' then rbsize     :=cur2.value;
  end case;
  end loop;

  -- Getting Shared Pool Size. Can not use shared_pool_size parameter value due to bug 1673506

  select cursiz_kghdsnew*granule_size into spsize from sys.x$ksmsp_dsnew;
  tsgasize := (fsize+tvsize+bcsize+rbsize);
  free     := (tvsize - ((spsize*1024*1024)+lpsize+jpsize));
  used     := tsgasize - free ;
  dbms_output.put_line('kk ');
  dbms_output.new_line;
  dbms_output.put_line('.....................SGA Configuration for.......................||dbname);
  dbms_output.put_line('------------------------------------------------------------------------');
  dbms_output.put_line('kkk ');
  dbms_output.new_line;
  dbms_output.put_line('Current SGA Size               : '||round(used/(1024*1024),2)||' MB');
  dbms_output.put_line('Maximum SGA Size               : '||round(tsgasize/(1024*1024),2)||' MB');
  dbms_output.put_line('Memory Available for SGA Growth: '||round(free/(1024*1024),2)||' MB');
  dbms_output.put_line('Buffer Cache Size              : '|| round(bcsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Shared Pool Size               : '|| spsize ||' MB');
  dbms_output.put_line('Large Pool Size                : '|| round(lpsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Java Pool Size                 : '|| round(jpsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Fixed SGA                      : '||round(fsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Redo Buffers                   : '|| round(rbsize/(1024*1024),2) ||' MB');
  dbms_output.put_line('Granule Size                   : '||granule_size||' MB');
  end
  /
  set serverout off
  set doc on


====================



@sga
.....................SGA Configuration for.......................ORCL
------------------------------------------------------------------------
Current SGA Size               : 540.4 MB
Maximum SGA Size               : 800.4 MB
Memory Available for SGA Growth: 260 MB
Buffer Cache Size              : 196 MB
Shared Pool Size               : 340 MB
Large Pool Size                : 0 MB
Java Pool Size                 : 0 MB
Fixed SGA                      : 2.12 MB
Redo Buffers                   : 2.29 MB
Granule Size                   : 4 MB

How to calculate week in oracle

Oracle week calculate 

calculate week

 

SQL> select  abs(to_number(to_char(to_date('21-oct-2011','dd-mon-yyyy'), 'WW')) - to_number(to_char(to_date('06-dec-2011','dd-mon-yyyy'),'WW')) ) "No of weeks" from dual ;

No of weeks
-----------
          7

 

 

 

SQL> select abs(to_number(to_char(to_date('21-oct-2011','dd-mon-yyyy'), 'WW')) - to_number(to_char(to_date(sysdate,'dd-mon-yyyy'),'WW')) ) "No of weeks"
from dual ;

 

No of weeks
-----------
          7