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
    i have to create a table and load the data daily. The table should have tablespaces usage,datafile growth,owner,object_name,object_type,daily timestamp.
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Code (SQL):
    CREATE tablespace tools
    datafile '&1' SIZE 100m
    segment SPACE management auto;

    CREATE TABLE sys.db_growth
    (run_dt            DATE,
    tablespace_name    varchar2(35),
    ts_id            NUMBER,
    curr_size        NUMBER)
    tablespace tools;

    CREATE TABLE sys.db_growth_by_file
    (run_dt            DATE,
    tablespace_name    varchar2(35),
    file_id        NUMBER,
    curr_size        NUMBER,
    max_size        NUMBER)
    tablespace tools;

    INSERT INTO db_growth
    (tablespace_name, run_dt, ts_id, curr_size)
    SELECT tablespace_name, sysdate, MIN(file_id), SUM(bytes)
    FROM dba_data_files
    GROUP BY tablespace_name, sysdate;

    INSERT INTO db_growth_by_file
    (tablespace_name, run_dt, file_id, curr_size, max_size)
    SELECT
    tablespace_name, sysdate, file_id, bytes, nvl(maxbytes,0)
    FROM dba_data_files
    ORDER BY tablespace_name, file_id;

    commit;

     
     
  3. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon,
    Thanks for the SQL code, I think you didnt mention schema and shema objects information. As you mentioned only tablespace and datafile growth. As we need to club all to together in one table if possible or else we can create two tables. So, could you please help me in concatenate the schema, schema objects and timestamp in anyone of the tables.
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I got you started; write the rest of it and if you have issues return and post your code and the errors and/or problems you see.
     
  5. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Sure Zargon and Thank you for your help
     
  6. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Zargon, i have created one more table and inserted data successfully. but when am trying to pull the data from 3 tables using inner join, am not getting the data. below are the columns i needed from these tables
    Needed columns are tablespace_name,run_dt,ts_id, file_id, current size , owner, segment_name,segment_type, curr_size

    this is the 3rd table i have created.
    CREATE TABLE db_growth_by_objects
    (run_dt DATE,
    owner varchar2(50),
    segment_name varchar2(50),
    segment_type varchar2(30),
    tablespace_name varchar2(30),
    curr_size NUMBER)
    tablespace USERS;

    I'm using below query to pull the data.

    select * from db_growth
    INNER JOIN db_growth_by_file
    ON db_growth.tablespace_name = db_growth_by_file.tablespace_name
    INNER JOIN db_growth_by_objects
    ON db_growth_by_file.run_dt = db_growth_by_objects.run_dt;
     
  7. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    So what are you getting? No one here is psychic to be able to know what data exists in your tables without you providing that information in your post. If you are getting an error running the query, something is wrong with your SQL. If you are getting no rows returned, then either something is wrong with your SQL or something is wrong with your data (or at least different from what you think it is)
     
  8. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    HI Matthew,
    Im just getting the column names but not getting the data. Previously, i have done with the data insertion in 3 tables and able to pull the data individually for each table. but, unable to pull the data using 3 tables using above query.Basically, here am trying to pull the data by using the above mentioned query. As am not sure, whether the query is correct or not. To be Frank, am not the sql expert So, i request you to guide me in getting the result.

    Thanks,
    Sunil
     
  9. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    If you can select from each of the three tables individually and return rows, but get no rows when you join them together, then there is not a value match in the columns used for the JOIN. Try the joins in isolation, for example:

    Code (Text):

    select * from db_growth
    INNER JOIN db_growth_by_file
    ON db_growth.tablespace_name = db_growth_by_file.tablespace_name;
    If the above returns no rows, then the values in the TABLESPACE_NAME columns of the two tables have no matches.

    Code (Text):
    SELECT * FROM db_growth_by_file
    INNER JOIN db_growth_by_objects
    ON db_growth_by_file.run_dt = db_growth_by_objects.run_dt;
    If this second query returns no rows, then there are no matching values in the RUN_DT columns in the two tables.

    My expectation based on the information you have provided is that one of the two issues (or both) is the cause of no rows being returned by your query.
     
  10. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Matthew, first query worked for me and returned the output. but the second query didnt return the output as its showing only the columns. but my question is run_dt is the date that define when the query is fired. As i ran the query for individual tables before and i fired the above query. but the run_dt should be same for both tables i.e db_growth_by_file and db_growth_by_objects.
     
  11. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Matthew,
    for the second query, when i change the run_dt with tablespace_name, it worked for me. Below is the query for which i got the output.
    SELECT * FROM db_growth_by_file
    INNER JOIN db_growth_by_objects
    ON db_growth_by_file.tablespace_name = db_growth_by_objects.tablespace_name;

    Thanks,
    Sunil
     
  12. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Whether or not the values are intended to match -- they don't, or else the JOIN between the two tables on that column would return rows. Most likely you have values in the date fields that are the same DAY, but not the same TIME. Date fields in Oracle store both day and time values, but when queried only display the DAY by default. Most likely your values are seconds (or minutes) apart in the same day. This is still enough that they will not be considered equal in a join. If this is the case, the following query will return rows:

    Code (Text):
    SELECT * FROM db_growth_by_file
    INNER JOIN db_growth_by_objects
    ON TRUNC(db_growth_by_file.run_dt) = TRUNC(db_growth_by_objects.run_dt);
     
  13. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Thanks for the information Matthew. Now, i can see the output for this query.
     
  14. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Matthew,
    I have one more question, can we automate this to run daily instead of insert manually.

    Thanks,
    Sunil
     
  15. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    Of course. Create a PL/SQL procedure that performs the INSERTs then create a DBMS_SCHEDULER job to run that procedure once per day.
     
  16. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    ok Thanks Matthew for the info.
     
  17. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Another option is to create a shell script to execute the SQL commands and run that from cron on a regular schedule. DBMS_SCHEDULER is flexible but it's also prone to scheduling the next run to start X hours after the previous run finishes, causing the job to 'drift' away from the desired time schedule. You can set the interval in DBMS_SCHEDULER to work from the truncated sysdate and add time to that, so that the start time is always X hours from midnight.

    The choice is yours.
     
  18. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    When I use DBMS_SCHEDULER, I do not use intervals, but rather the FREQ, BYDAY, and BYHOUR settings which prevents such drift. It's at least as flexible as cron and somewhat less cryptic.

    Code (Text):
    BEGIN
        DBMS_SCHEDULER.CREATE_JOB (
                job_name => 'LOG_TS_SPACE',
                job_type => 'PLSQL_BLOCK',
                job_action => 'BEGIN ts_daily_log; END;',
                number_of_arguments => 0,
                start_date => TO_TIMESTAMP_TZ('2015-08-11 03:00:00.000000000 -04:00','YYYY-MM-DD HH24:MI:SS.FF TZR'),
                repeat_interval => 'FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=3;',
                end_date => NULL,
                enabled => TRUE,
                comments => 'Logs Tablespace space usage each weekday at 3am.');
    END;
     
  19. sunil

    sunil Active Member

    Messages:
    51
    Likes Received:
    1
    Trophy Points:
    140
    Location:
    MICHIGAN
    Hi Matthew,
    Thanks for the schedular job. I'm trying to test the script before automate it. but not getting correct output. below is the script. Could you please take a look into it once.

    #!/bin/sh

    echo "ORA TABLEASPACE:"
    ORACLE_SID=gtcdwt1
    ORACLE_BASE=/u01/app/oracle
    ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1
    SUBJECT="Tablespace Space Utilization for $ORACLE_SID Database for `date +%d-%b-%Y`"
    EMAIL_ADDRESS1="xxxxxxxx.com"
    sqlplus -s "/as sysdba"<<EOF
    set head off
    set feed off;
    set newpage none;

    set pagesize 9999;
    set linesize 132;

    SELECT /* + RULE */ df.tablespace_name "Tablespace",
    df.bytes / (1024 * 1024) "Size (MB)",
    SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
    Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
    Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
    FROM dba_free_space fs,
    (SELECT tablespace_name,SUM(bytes) bytes
    FROM dba_data_files
    GROUP BY tablespace_name) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,df.bytes
    UNION ALL
    SELECT /* + RULE */ df.tablespace_name tspace,
    fs.bytes / (1024 * 1024),
    SUM(df.bytes_free) / (1024 * 1024),
    Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
    Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
    FROM dba_temp_files fs,
    (SELECT tablespace_name,bytes_free,bytes_used
    FROM v$temp_space_header
    GROUP BY tablespace_name,bytes_free,bytes_used) df
    WHERE fs.tablespace_name (+) = df.tablespace_name
    GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used

    EOF
    exit

    When i executed this. I just got the output like this.

    [oracle@gtcbi-db-tst ~]$ ./test2.sh
    ORA TABLEASPACE:
     
  20. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    There is only one statement in that script to generate output -- namely echo "ORA TABLEASPACE:"
    Your script is generating the output ORA TABLEASPACE:
    What exactly were you expecting to see?

    Also -- the rule-based optimized was desupported in 11g, so a RULE hint makes no sense. Mind you, it's simply being ignored, so it's causing no harm... but it's not doing any good either.