10 Replies Latest reply: Jan 1, 2014 6:05 PM by petra-K RSS

    DB Freespace Monitoring

      Hi all,

       

      11.2.0.1

       

       

      Can you lend me your monitoring script the will also map  O.S. freespace against TS freesace?  Thanks.

       

      Sample :

       

      PROD-DB

      TABLESPACE

      TOTAL (Mb)

      USED (Mb)

      % USED

      CRITICAL

      FILESYSTEM

      FREESPC (Mb)

      AUTOEXTEND

       

      INDX

      260

      10

      4

       

      /u01

      1,000

      NO

       

      XDB

      200

      10

      5

       

      /u01

      1,000

      YES

       

      UNDOTBS1

      393

      42

      10

       

      /u01

      1,000

      YES

       

      TOOLS

      100

      10

      10

       

      /u01

      1,000

      YES

       

      DRSYS

      200

      20

      13

       

      /u01

      1,000

      YES

       

      ODM

      200

      70

      35

       

      /u02

      5,000

      YES

       

      SYSAUX

      1,080

      923

      85

       

      /u02

      5,000

      YES

       

      EXAMPLE

      151

      143

      92

      *

      /u02

      5,000

      NO

       

      USERS

      830

      790

      95

      *

      /u02

      5,000

      NO

       

      SYSTEM

      849

      842

      99

      *

      /u02

      5,000

      YES

       

      TEMP

      300

      300

      100

      *

      /u02

      5,000

      YES

        • 1. Re: DB Freespace Monitoring
          Sai-oadba

          There are different flavors of scripts available from Oracle Guru site,

          http://asktom.oracle.com/pls/asktom/ASKTOM.download_file?p_file=6551402703363001567

          • 2. Re: DB Freespace Monitoring
            EdStevens

            f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

             

            Hi all,

             

            11.2.0.1

             

             

            Can you lend me your monitoring script the will also map  O.S. freespace against TS freesace?  Thanks.

             

            Sample :

             

            PROD-DB

            TABLESPACE

            TOTAL (Mb)

            USED (Mb)

            % USED

            CRITICAL

            FILESYSTEM

            FREESPC (Mb)

            AUTOEXTEND

             

            INDX

            260

            10

            4

             

            /u01

            1,000

            NO

             

            XDB

            200

            10

            5

             

            /u01

            1,000

            YES

             

            UNDOTBS1

            393

            42

            10

             

            /u01

            1,000

            YES

             

            TOOLS

            100

            10

            10

             

            /u01

            1,000

            YES

             

            DRSYS

            200

            20

            13

             

            /u01

            1,000

            YES

             

            ODM

            200

            70

            35

             

            /u02

            5,000

            YES

             

            SYSAUX

            1,080

            923

            85

             

            /u02

            5,000

            YES

             

            EXAMPLE

            151

            143

            92

            *

            /u02

            5,000

            NO

             

            USERS

            830

            790

            95

            *

            /u02

            5,000

            NO

             

            SYSTEM

            849

            842

            99

            *

            /u02

            5,000

            YES

             

            TEMP

            300

            300

            100

            *

            /u02

            5,000

            YES

             

            "Lend" you a script?  Does that mean you'll give it back when you are done with it?   ;-)

             

            Seriously, no one here is going to do your work for you.  Now, if you will

            1) show what you've done so far (thus, as a byproduct, proving you've put out some effort yourself)

            2) explain what the script is doing that is different from what you expected

            then many people will be willing to help out.

             

             

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

            BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is no better than posting as "Anonymous".

             

            All you ed0f625b-6857-4956-9b66-da280b7cf3a2's look alike . . .

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

            • 3. Re: DB Freespace Monitoring
              jgarry

              Would you think the operating system might matter?  On mine, this works:

               

              $ df -b|grep oradata|awk '{print $5 " "$6}'

              106659885 Kbytes


              Then you have to figure out how to put that into your sql.  There's a faq for that.  YMMV.

              • 4. Re: DB Freespace Monitoring

                Thanks Ed and all

                 

                BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is no better than posting as "Anonymous".

                 

                All you ed0f625b-6857-4956-9b66-da280b7cf3a2's look alike . . .

                 

                 

                Actually I tried it at first sign-up, to edit this portion:

                 

                Select to hide informationOTN Community Display Name

                This public display name identifies you when you contribute to communities such as forums, groups, and wikis.
                Visit OTN Forums

                As you can see,  its not editable  and "greyed out"

                 

                Maybe you can call the attention of the forum admin?

                 

                 

                Thanks,

                pK

                • 5. Re: DB Freespace Monitoring

                  Hi Ed,

                   

                  Seriously, no one here is going to do your work for you.  Now, if you will

                  1) show what you've done so far (thus, as a byproduct, proving you've put out some effort yourself)

                  2) explain what the script is doing that is different from what you expected

                  then many people will be willing to help out.

                   

                   

                  Well I have this:

                   

                  A.

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

                  set pages 0

                  set lines 120

                  set feedback off

                  set term off

                  set echo off

                  set verify off

                  col total for 999999999999999

                  col used for 999999999999999

                  col free for 999999999999999

                  spool freespc.lst

                  select  sysdate,tbs.tablespace_name,

                              tot.bytes total,

                              tot.bytes-sum(nvl(fre.bytes,0)) used,

                              sum(nvl(fre.bytes,0)) free,

                              round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) pct,

                              decode(

                                  greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),

                                  90, '', '*'

                              ) pct_warn

                  from    dba_free_space fre,

                             (select tablespace_name, sum(bytes) bytes

                             from    dba_data_files

                             group by tablespace_name) tot,

                             dba_tablespaces tbs

                  where   tot.tablespace_name    = tbs.tablespace_name

                  and     fre.tablespace_name(+) = tbs.tablespace_name

                  group by tbs.tablespace_name, tot.bytes, tot.bytes

                  order by 6, 2  ;

                  spool off

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


                  And I want to match them with


                  select * from dba_data_files union select * from dba_temp_files, and grep "awk" the freespace of the mounted filesystem

                  $ df -b|grep oradata|awk '{print $5 " "$6}'

                  106659885 Kbytes


                  Maybe you can summarize or put then together, my brain is not so good at mixing things up, coz i'm just simple minded. I just wanted simple stress free life.



                  Thanks,

                  • 6. Re: DB Freespace Monitoring
                    EdStevens

                    f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                     

                    Thanks Ed and all

                     

                    BTW, it would be really helpful if you would go to your profile and give yourself a recognizable name.  It doesn't have to be your real name, just something that looks like a real name.  Who says my name is really Ed Stevens?  But at least when people see that on a message they have a recognizable identity.  Unlike the system generated name of 'ed0f625b-6857-4956-9b66-da280b7cf3a2', which is no better than posting as "Anonymous".

                     

                    All you ed0f625b-6857-4956-9b66-da280b7cf3a2's look alike . . .

                     

                     

                    Actually I tried it at first sign-up, to edit this portion:

                     

                    https://myprofile.oracle.com/EndUser/faces/profile/sso/updateUser.jspx?tid=complete&showRegions=primaryAddress|newsletters|communities|developerInterests|displayName&nextURL=https://community.oracle.comOTN Community Display Name

                    This public display name identifies you when you contribute to communities such as forums, groups, and wikis.
                    Visit OTN Forums

                    As you can see,  its not editable  and "greyed out"

                     

                    Maybe you can call the attention of the forum admin?

                     

                     

                    Thanks,

                    pK

                    Ok, so there was an issue when you first signed up.  How about if you go back to edit your profile now?

                    For more help with this See https://community.oracle.com/community/developer/english/technology_network_community/community_feedback_and_suggestions…

                    • 7. Re: DB Freespace Monitoring
                      EdStevens

                      f55237a7-2c38-4db3-a7a3-1d77256f0730 wrote:

                       

                      Hi Ed,

                       

                      Seriously, no one here is going to do your work for you.  Now, if you will

                      1) show what you've done so far (thus, as a byproduct, proving you've put out some effort yourself)

                      2) explain what the script is doing that is different from what you expected

                      then many people will be willing to help out.

                       

                       

                      Well I have this:

                       

                      A.

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

                      set pages 0

                      set lines 120

                      set feedback off

                      set term off

                      set echo off

                      set verify off

                      col total for 999999999999999

                      col used for 999999999999999

                      col free for 999999999999999

                      spool freespc.lst

                      select  sysdate,tbs.tablespace_name,

                                  tot.bytes total,

                                  tot.bytes-sum(nvl(fre.bytes,0)) used,

                                  sum(nvl(fre.bytes,0)) free,

                                  round((1-sum(nvl(fre.bytes,0))/tot.bytes)*100,2) pct,

                                  decode(

                                      greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, 90),

                                      90, '', '*'

                                  ) pct_warn

                      from    dba_free_space fre,

                                 (select tablespace_name, sum(bytes) bytes

                                 from    dba_data_files

                                 group by tablespace_name) tot,

                                 dba_tablespaces tbs

                      where   tot.tablespace_name    = tbs.tablespace_name

                      and     fre.tablespace_name(+) = tbs.tablespace_name

                      group by tbs.tablespace_name, tot.bytes, tot.bytes

                      order by 6, 2  ;

                      spool off

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


                      And I want to match them with


                      select * from dba_data_files union select * from dba_temp_files, and grep "awk" the freespace of the mounted filesystem

                      $ df -b|grep oradata|awk '{print $5 " "$6}'

                      106659885 Kbytes


                      Maybe you can summarize or put then together, my brain is not so good at mixing things up, coz i'm just simple minded. I just wanted simple stress free life.



                      Thanks,

                      Well, you are going to need to make the results of your OS command (which runs outside of the database) available to a sql statement that runs inside the database.  How do you suppose you might do that? (hint: the first thing that comes to my mind is an external table.  There may be other methods someone else will chime in on).

                      • 8. Re: DB Freespace Monitoring

                        Ok, maybe thatwouldhelp 

                        • 9. Re: DB Freespace Monitoring
                          jgarry

                          Here's an older way: SQL*Plus FAQ - Oracle FAQ

                          (the general idea being you run a script to load the variables before running the sqlplus script).

                           

                          More generally, you can use shell variables in hereis scripts.

                           

                          $ sqlplus /nolog <<EOF

                          > connect XXXX/XXXXX

                          > select '$ORACLE_HOME' from dual;

                          > EOF

                           

                          SQL*Plus: Release 10.2.0.4.0 - Production on Tue Dec 31 10:21:07 2013

                           

                          Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

                           

                          @> Connected.

                          XXXX@TTST>

                          '/ORACLE/APP/ORACLE/PRODUCT/10.2.0/DB_

                          --------------------------------------

                          /oracle/app/oracle/product/10.2.0/db_1

                           

                          XXXX@TTST> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

                          With the Partitioning, OLAP, Data Mining and Real Application Testing options

                           

                          Getting the sqlplus options and quoting correct are left as an exercise for the student.

                          • 10. Re: DB Freespace Monitoring

                            Thanks Jg