10 Replies Latest reply: May 7, 2013 2:02 AM by KR10822864 RSS

    tablespace alert

    sam995972
      hi all,

      oracle 9.2.0.5
      linux

      Actually my database is olap's one, am having 100+ tablespaces each tbs is having more than 50GB + of space occupied, am planning to get the alert when tablespace which is having less than 1GB free space. Can anyone please tell me how to write a sql script and shell.

      thanks,
      Sam.
        • 1. Re: tablespace alert
          sb92075
          sam995972 wrote:
          hi all,

          oracle 9.2.0.5
          linux

          Actually my database is olap's one, am having 100+ tablespaces each tbs is having more than 50GB + of space occupied, am planning to get the alert when tablespace which is having less than 1GB free space. Can anyone please tell me how to write a sql script and shell.

          thanks,
          Sam.
          do any of the datafiles have AUTOEXTEND enabled?
          • 2. Re: tablespace alert
            994218
            Hi,

            SELECT TABLESPACE_NAME,SUM(BYTES)/(1024*1024*1024) "FREE SPACE IN GB" FROM DBA_FREE_SPACE where SUM(BYTES)/(1024*1024*1024) < 1 GROUP BY TABLESPACE_NAME;

            This will give you output, when a tablespace will have free space less than 1G.

            Thanks,
            http://www.dbas-oracle.com/
            • 3. Re: tablespace alert
              JohnWatson
              991215 wrote:
              Hi,

              SELECT TABLESPACE_NAME,SUM(BYTES)/(1024*1024*1024) "FREE SPACE IN GB" FROM DBA_FREE_SPACE where SUM(BYTES)/(1024*1024*1024) < 1 GROUP BY TABLESPACE_NAME;

              This will give you output, when a tablespace will have free space less than 1G.

              Thanks,
              http://www.dbas-oracle.com/
              This is not valid SQL, and even when you correct it by using a HAVING clause instead of a WHERE clause, you should realize that the query will not report on tablespaces that are full. You need to outer join to dba_tablespaces.
              • 4. Re: tablespace alert
                Justin_Mungal
                I don't have a 9i instance available for testing... but something like this should do the trick.
                SELECT a.tablespace_name,
                 sum(nvl(b.free_bytes/1024/1024/1024,0)) free_gb
                FROM dba_data_files a,
                 (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b
                WHERE a.file_id=b.file_id(+)
                GROUP BY a.tablespace_name
                HAVING sum(nvl(b.free_bytes/1024/1024/1024,0))  < 1
                /
                Edited by: Justin Mungal on May 6, 2013 5:05 PM - This won't account for data files that have autoextend enabled
                • 5. Re: tablespace alert
                  %bala%
                  http://www.oracle-base.com/dba/script.php?category=monitoring&file=ts_full.sql
                  • 6. Re: tablespace alert
                    Aman....
                    991215 wrote:
                    Hi,

                    SELECT TABLESPACE_NAME,SUM(BYTES)/(1024*1024*1024) "FREE SPACE IN GB" FROM DBA_FREE_SPACE where SUM(BYTES)/(1024*1024*1024) < 1 GROUP BY TABLESPACE_NAME;

                    This will give you output, when a tablespace will have free space less than 1G.

                    Thanks,
                    Did you actually run this query? Just to echo the same what Justin already mentioned,
                    SQL> SELECT TABLESPACE_NAME,SUM(BYTES)/(1024*1024*1024) "FREE SPACE IN GB" FROM DBA_FREE_SPACE where SUM(BYTES)/(1024*1024*1024) < 1 GROUP BY TABLESPACE_NAME;
                    SELECT TABLESPACE_NAME,SUM(BYTES)/(1024*1024*1024) "FREE SPACE IN GB" FROM DBA_FREE_SPACE where SUM(BYTES)/(1024*1024*1024) < 1 GROUP BY TABLESPACE_NAME
                                                                                                                    *
                    ERROR at line 1:
                    ORA-00934: group function is not allowed here
                    
                    
                    SQL> 
                    Aman.....
                    • 7. Re: tablespace alert
                      sam995972
                      sb92075 wrote:
                      sam995972 wrote:
                      hi all,

                      oracle 9.2.0.5
                      linux

                      Actually my database is olap's one, am having 100+ tablespaces each tbs is having more than 50GB + of space occupied, am planning to get the alert when tablespace which is having less than 1GB free space. Can anyone please tell me how to write a sql script and shell.

                      thanks,
                      Sam.
                      thanks Sb for your reply.
                      do any of the datafiles have AUTOEXTEND enabled?
                      No.
                      • 8. Re: tablespace alert
                        sam995972
                        thanks Justin.suggested query is working fine.
                        can you please suggest sample shell script (to put that qery into shell script).i will test it in my machine.
                        • 9. Re: tablespace alert
                          Justin_Mungal
                          Check the following:
                          http://www.oracle-base.com/articles/misc/oracle-shell-scripting.php
                          http://www.oracle.com/technetwork/articles/linux/saternos-scripting-088882.html

                          Here is a simple hack job I threw together:
                          [oracle@oracle1 ~]$ cat exec_sql.sh
                          #!/bin/bash
                          
                          sqlplus -s "/ as sysdba" <<EOF
                          SELECT a.tablespace_name,
                           sum(nvl(b.free_bytes/1024/1024/1024,0)) free_gb
                          FROM dba_data_files a,
                           (SELECT file_id, SUM(bytes) free_bytes FROM dba_free_space b GROUP BY file_id) b
                          WHERE a.file_id=b.file_id(+)
                          GROUP BY a.tablespace_name
                          HAVING sum(nvl(b.free_bytes/1024/1024/1024,0))  < 1;
                          exit
                          EOF
                          I gave it 770 permissions, then executed it. Output goes to standard out.
                          [oracle@oracle1 ~]$ ./exec_sql.sh
                          
                          TABLESPACE_NAME                   FREE_GB
                          ------------------------------ ----------
                          SYSAUX                          .06640625
                          UNDOTBS1                       .473388672
                          USERS                          .023864746
                          SYSTEM                         .055053711
                          JUSTIN                         .009765625
                          And here I email the output:
                          [oracle@oracle1 ~]$ ./exec_sql.sh |mail -s "tablespaces with less than 1GB free space" fake@email.address
                          [oracle@oracle1 ~]$
                          Hopefully this gets you started. I'm not writing any more scripts for you.
                          • 10. Re: tablespace alert
                            KR10822864
                            can you please suggest sample shell script (to put that qery into shell script).i will test it in my machine.
                            please check below link.

                            https://communities.bmc.com/docs/DOC-9942
                            http://honglus.blogspot.in/2013/02/shell-script-to-check-oracle-tablespace.html