1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Can someone help me how to create a table for tablespace and datafile growth

Discussion in 'SQL PL/SQL' started by sunil, Aug 7, 2015.

  1. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Actually, am trying to generate the tablespace usage report through this script and later will automate this script using dbms_schedular jobs.
     
  2. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    And the script is working... for a given definition of 'working'. The script runs the echo statement, then sets some environment variables, then runs SQL*Plus, executes a query in SQL*Plus, then exits SQL*Plus.

    The output of the SQL query goes only to SQL*Plus, so it's not going to show as an output from the script. Even if it did output the query results to the OS console -- the output wouldn't be getting saved anywhere, so running this script automatically each day would be fairly pointless. Since you have (according to this thread) created tables to store the results of these daily tablespace checks, why is the script not inserting into those tables?

    Beyond that -- since you are implementing this as an OS shell script, there is no reason to use DBMS_SCHEDULER to automate it rather than cron. It makes sense to automate OS shell scripts with the OS scheduler tool (cron). By contrast, if this job were implemented as a PL/SQL subprogram (i.e. from within Oracle), it would follow that scheduling it was best done through the Oracle scheduling tool (DBMS_SCHEDULER). Mixing and matching OS-based code vs Oracle-based code and OS-scheduling vs Oracle-scheduling is possible, but adds unnecessary complexity.
     
  3. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Actually, i would like to inform you that, i had tried with some .sh and .sql scripts together which has bunch of sql and sh scripts. So, Firstly i had created table i.e tablespace info

    cat table.sql
    create table tbs_space_info(INFO_DATE date, TS_NAME VARCHAR2(20), FULL_SIZE NUMBER(15,8), FREE_SPACE NUMBER(15,8),FREE_PERCENTAGE NUMBER(15,8))
    /

    Then when i tried to execute tbs_space_info_scr.sh script. it throwing an below error.

    declare
    *
    ERROR at line 1:
    ORA-12899: value too large for column "SYS"."TBS_SPACE_INFO"."TS_NAME" (actual:
    21, maximum: 20)
    ORA-06512: at line 9

    Please find the tbs_space_info_scr.sh script below.
    cat tbs_space_info_scr.sh
    #!/bin/bash

    . /home/oracle/.bash_profile

    HOME=/home/oracle/TBS_SPACE # Make sure that you have copied the TBS_SPACE Directory in your home directory.

    LOGNAME=`date +%d-%b-%Y`.log
    LOGFILE="$HOME"/logs/"$LOGNAME"
    TEMPFILE="$HOME"/temp.log

    SUBJECT="Tablespace Space Utilization for $ORACLE_SID Database for `date +%d-%b-%Y`"
    EMAIL_ADDRESS1="xxxxxxxxxxxx.com"
    EMAIL_ADDRESS2="xxxxxxxxxxxx.com"
    ATTACHED_FILE="$LOGFILE"

    typeset -i TOTAL
    typeset -i COUNT

    TOTAL=`sh "$HOME"/total_tbs_count.sh`
    COUNT=`sh "$HOME"/full_tbs_count.sh`

    echo -e "\n" >> $LOGFILE
    echo "There are a total "$TOTAL" tablespaces in the Database." >> $LOGFILE
    if [ $COUNT -eq 0 ]
    then
    echo -e "\n" >> $LOGFILE
    echo "Currently there are no Tablespaces running out of Space." >> $LOGFILE
    else
    echo -e "\n" >> $LOGFILE
    echo "The following Tablespaces are running out of Space." >> $LOGFILE
    echo "--------------------------------------------------" >> $LOGFILE
    sh "$HOME"/full_tbs_list.sh >> $LOGFILE
    echo -e "\n" >> $LOGFILE
    echo "--------------------------------------------------" >> $LOGFILE
    fi

    sh "$HOME"/tbs_space_info_sql.sh

    echo -e "\n" >> $LOGFILE
    echo "Following Table lists the Space utilization for all the Tablespaces in the Database." >> $LOGFILE
    echo -e "\n" >> $LOGFILE
    sh "$HOME"/tbs_space_info_extract.sh >/dev/null # Extracts information from the table 'tbs_space_info' for the day.
    cat "$TEMPFILE" >> $LOGFILE
    # echo -e "\n" >> $LOGFILE
    echo '* Please note that the above table shows space information in MB(Megabytes).' >> $LOGFILE
    echo -e "\n" >> $LOGFILE
    echo "################################################################################" >> $LOGFILE
    echo -e "\n" >> $LOGFILE

    cat "$ATTACHED_FILE" | mailx -s "$SUBJECT" "$EMAIL_ADDRESS1"
    cat "$ATTACHED_FILE" | mailx -s "$SUBJECT" "$EMAIL_ADDRESS2"

    rm -rf ${HOME}/oradiag_oraspprd # This folder may get created while you run the script.
    rm -rf "$HOME"/oradiag_oraspprd
    rm -rf "$TEMPFILE" # Remove the temp.log

    This script is calling other files too.
    -rwxr-xr-x 1 oracle oinstall 167 Aug 10 21:44 tbs_space_info_sql.sh
    -rwxr-xr-x 1 oracle oinstall 326 Aug 10 21:45 tbs_space_info_extract.sh
    -rwxr-xr-x 1 oracle oinstall 283 Aug 10 21:45 full_tbs_list.sh
    -rwxr-xr-x 1 oracle oinstall 259 Aug 10 21:46 full_tbs_count.sh
    drwxr-xr-x 2 oracle oinstall 4096 Aug 10 21:51 support_scripts
    drwxr-xr-x 2 oracle oinstall 4096 Aug 10 22:13 logs
    -rwxr-xr-x 1 oracle oinstall 1972 Aug 10 22:22 tbs_space_info_scr.sh
    -rwxr-xr-x 1 oracle oinstall 148 Aug 11 09:32 table.sql
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,599
    Likes Received:
    363
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have no query terminator (;, /) so it's never executing:

    SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
    2 df.bytes / (1024 * 1024) "Size (MB)",
    3 SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    4 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    5 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    6 FROM dba_free_space fs,
    7 (SELECT tablespace_name,SUM(bytes) bytes
    8 FROM dba_data_files
    9 GROUP BY tablespace_name) df
    10 WHERE fs.tablespace_name (+) = df.tablespace_name
    11 GROUP BY df.tablespace_name,df.bytes
    12 UNION ALL
    13 SELECT /* + RULE */ df.tablespace_name tspace,
    14 fs.bytes / (1024 * 1024),
    15 SUM(df.bytes_free) / (1024 * 1024),
    16 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
    17 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
    18 FROM dba_temp_files fs,
    19 (SELECT tablespace_name,bytes_free,bytes_used
    20 FROM v$temp_space_header
    21 GROUP BY tablespace_name,bytes_free,bytes_used) df
    22 WHERE fs.tablespace_name (+) = df.tablespace_name
    23 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
    24
    SQL>

    Add either a ; at the end of the last line or a / after the last line so it will generate output:

    SQL> SELECT /* + RULE */ df.tablespace_name "Tablespace",
    2 df.bytes / (1024 * 1024) "Size (MB)",
    3 SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    4 Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    5 Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    6 FROM dba_free_space fs,
    7 (SELECT tablespace_name,SUM(bytes) bytes
    8 FROM dba_data_files
    9 GROUP BY tablespace_name) df
    10 WHERE fs.tablespace_name (+) = df.tablespace_name
    11 GROUP BY df.tablespace_name,df.bytes
    12 UNION ALL
    13 SELECT /* + RULE */ df.tablespace_name tspace,
    14 fs.bytes / (1024 * 1024),
    15 SUM(df.bytes_free) / (1024 * 1024),
    16 Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
    17 Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
    18 FROM dba_temp_files fs,
    19 (SELECT tablespace_name,bytes_free,bytes_used
    20 FROM v$temp_space_header
    21 GROUP BY tablespace_name,bytes_free,bytes_used) df
    22 WHERE fs.tablespace_name (+) = df.tablespace_name
    23 GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
    24 /

    Tablespace Size (MB) Free (MB) % Free % Used
    ------------------------------ ---------- ---------- ---------- ----------
    UNDOTBS1 22290 22268 100 0
    SYSTEM 700 185.875 27 73
    USERS 500 495 99 1
    INDX 50 49 98 2
    SYSAUX 760 36.8125 5 95
    TOOLS 157.0625 7.75 5 95
    TEMP 17855 0 0 100

    7 rows selected.

    SQL>
     
  5. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Thanks David. It's working fine now. Once again i really appreciated your help.

    Thanks,
    Sunil
     
  6. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Matthew,
    Thanks for the correction. Now am getting the exact output. Once again thanks alot for your help.

    Thanks,
    Sunil
     
  7. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Mathew,
    Could you please help me in DBMS_MAIL setup after the successfull job run. Here, we need the email notification when job ran successfully.
    I have created one procedure and call script and scheduled job via scheduler in Sql developer. So, when am trying to insert the email id, its throwing an error.

    Below is the calling script that i have scheduled
    declare
    v_error varchar2(2000);

    Begin
    procedure _xxxx_xxxx(v_error);
    dbms_output.put_line('procedure output is: '||v_error);
    End;

    Thanks,
    Sunil Gowda
     
  8. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Mathew,
    Could you please help me in DBMS_MAIL setup after the successfull job run. Here, we need the email notification when job ran successfully.
    I have created one procedure and call script and scheduled job via scheduler in Sql developer. So, when am trying to insert the email id, its throwing an error.

    Below is the calling script that i have scheduled
    declare
    v_error varchar2(2000);

    Begin
    procedure _xxxx_xxxx(v_error);
    dbms_output.put_line('procedure output is: '||v_error);
    End;

    Thanks,
    Sunil
     
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    With one Google search I could find half a dozen articles with step-by-step examples for setting up DBMS_MAIL. So can you.