Table and Index Spaceing.....
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Table and Index Spaceing.....

  1. #1
    Join Date
    Dec 2000
    Posts
    12
    Hi All,

    I have to get table and index spaceing reports everyday in the production environment. I wrote the script, displayed the following columns, tablename, tablespacename, blocks used, free blocks, avg.lenth. from the dba_tables/user_tables...

    I calculated the Initial in MB, based on the num_rows and avg_row_len columns.

    like, (num_rows * avg_row_len / 2^20)

    Samething, I want to evaluate, next, pctincrease and so...

    Please, update me asap, If anyone have the formula of the above...

    Thanks

    -Tami

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I am not clear with what you wanted, I guess this z what you wanted... try running this and see the results are what you wanted...This z at tablespace level.


    rem -------------------------------------------------------------
    rem Tablespace Usage
    rem -------------------------------------------------------------

    set pagesize 66
    set line 132

    clear breaks
    clear computes

    column "Total Bytes" format 9,999,999,999
    column "SQL Blocks" format 999,999,999
    column "VMS Blocks" format 999,999,999
    column "Bytes Free" format 9,999,999,999
    column "Bytes Used" format 9,999,999,999
    column "% Free" format 9999.999
    column "% Used" format 9999.999
    break on report
    compute sum of "Total Bytes" on report
    compute sum of "SQL Blocks" on report
    compute sum of "VMS Blocks" on report
    compute sum of "Bytes Free" on report
    compute sum of "Bytes Used" on report
    compute avg of "% Free" on report
    compute avg of "% Used" on report

    select substr(fs.FILE_ID,1,3) "ID#",
    fs.tablespace_name,
    df.bytes "Total Bytes",
    df.blocks "SQL Blocks",
    df.bytes/512 "VMS Blocks",
    sum(fs.bytes) "Bytes Free",
    (100*((sum(fs.bytes))/df.bytes)) "% Free",
    df.bytes-sum(fs.bytes) "Bytes Used",
    (100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
    from sys.dba_data_files df, sys.dba_free_space fs
    where df.file_id(+) = fs.file_id
    group by fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
    order by fs.tablespace_name;


    Try getting info (@ table level)you wanted from DBA_TABLES on the similar lines... I have one at work, if you want it... I can send it tomorrow.


    [Edited by sreddy on 12-28-2000 at 09:19 PM]

  3. #3
    Join Date
    Dec 2000
    Posts
    12

    Thumbs up

    I took the following columns, table_name, tablespace_name, num_rows, avg_row_len from the user_tables, after I executed the analyze table command...

    Followed, I'm calculating the Initial, next, pctincrease values based on the above, num_rows and avg_row_len colums.

    Already, I calculated the Initial values, based on the following method,
    Initial value (in bytes)= (num_rows * avg_row_len) / (2 ^ 20)

    Samething, I want to calculate the other column values, next and pctincrease...

    ....



    [QUOTE][i]Originally posted by sreddy [/i]
    [B]I am not clear with what you wanted, I guess this z what you wanted... try running this and see the results are what you wanted...


    rem -------------------------------------------------------------
    rem Tablespace Usage
    rem -------------------------------------------------------------

    set pagesize 66
    set line 132

    clear breaks
    clear computes

    column "Total Bytes" format 9,999,999,999
    column "SQL Blocks" format 999,999,999
    column "VMS Blocks" format 999,999,999
    column "Bytes Free" format 9,999,999,999
    column "Bytes Used" format 9,999,999,999
    column "% Free" format 9999.999
    column "% Used" format 9999.999
    break on report
    compute sum of "Total Bytes" on report
    compute sum of "SQL Blocks" on report
    compute sum of "VMS Blocks" on report
    compute sum of "Bytes Free" on report
    compute sum of "Bytes Used" on report
    compute avg of "% Free" on report
    compute avg of "% Used" on report

    select substr(fs.FILE_ID,1,3) "ID#",
    fs.tablespace_name,
    df.bytes "Total Bytes",
    df.blocks "SQL Blocks",
    df.bytes/512 "VMS Blocks",
    sum(fs.bytes) "Bytes Free",
    (100*((sum(fs.bytes))/df.bytes)) "% Free",
    df.bytes-sum(fs.bytes) "Bytes Used",
    (100*((df.bytes-sum(fs.bytes))/df.bytes)) "% Used"
    from sys.dba_data_files df, sys.dba_free_space fs
    where df.file_id(+) = fs.file_id
    group by fs.FILE_ID, fs.tablespace_name, df.bytes, df.blocks
    order by fs.tablespace_name;

    [/B][/QUOTE]

  4. #4
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    I got you, but don't have as such formula on top of my head.
    I never even think about any formula in calculating my next extent size from the fragmentation point of view. I will make sure that I will end up having my Initial and Next extents sizes are same always and thats how I try to avoid fragmentation.

    Same case with percentage increase and I will keep it at Zero.
    This is what big guys/Oracle Support advise... you know !

    I don't understand one thing here. Are you trying to caluculate this stuff for documentation and reporting purpose or creating the Objects with redefinded storage clauses?

    Sorry ! Im not of much help to you...



  5. #5
    Join Date
    Dec 2000
    Posts
    21
    Sreddy,

    Please put the script on dba_table and dba_index here
    when you get a chance. Thanks!

    Linda

  6. #6
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Linda, what do you mean by dba_table and dba_index... you mean reporting script/ something else... clarify me.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width