Results 1 to 14 of 14

Thread: row size

  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Unanswered: row size

    how to find (not calculate) the rowsize of each table in the schema

    --Jaggu

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    You can analyze tables and get the average row size from user_tables
    AVG_ROW_LEN

    HTH
    Gregg

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    I am working for capacity planning for this I need row_size of all the table, I do not want to caluclate it as it will take time for 300 tables.
    how to find actual row size from AVG_ROW_LEN

    --Jaggu

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    In Oracle the row size can vary so to do capacity planning I assume (with the proviso I have reasonable data) row_size = AVG_ROW_LEN from user_tables

    So to calculate say the total amount of space taken purely by the data (not the extent size or any other stuff used by Oracle such as block headers etc) in your schema

    select table_name, (avg_row_len * num_rows)/(1024*1024) "size (MB)" from user_tables

    Alan

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    Alan
    I have checked with the query you sent to me after analyze,
    for ex.
    I have a table (f1 number,f2 varchar2(4000),f3 timestamp), and currently 6 rows are there
    the output shows 0.12890625 (KB), is it right,
    2. The size based on the actual length of the data in the row or as per the datawidth declared

    Alan, can you pls. quickly respond.

    --Jaggu

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    AVG_ROW_LEN is based on the average row size for the ACTUAL data you have in your table. It is NOT calculated from the table definition.

    So you table size is accurate as dont forget that varchar2 only uses up as much space as is required to hold the actual data.

    Alan

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    Alan,
    I appreciate you quick replay, I need a your suggestion, for capacity plannning we need to work out for the actual size of the table rather the actual size of the data in it, bcause we do not know what will be the data size in the future recds, I am right.

    --Jaggu

  8. #8
    Join Date
    Aug 2003
    Posts
    123
    Alan,
    Can I write a script to find the row_size of all the tables in the schema using the data dictionary tables. I appreciate if you could give me any inputs on this. As this task is urgent i need a short cut .


    ---Jaggu.

  9. #9
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    just select avg_row_len from user_tables. For all schemas use dba_tables.

    To do capacity planning you need to be able to estimate the number of rows in your large tables. If you can estimate this then multiply by avg_row_len to get the size of your data. However when sizing a database you nned to take into account pctfree, size of block headers, block size etc.

    Alan

  10. #10
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Oh forgot to mention paste the list of tables and their row_size into excel. Add a column with your estimate for number of rows and get excel to add it all up. Dont forget to sort your tables by their tablespace so you can get a total for each tablespace.

    Alan

  11. #11
    Join Date
    Aug 2003
    Posts
    123
    Alan,
    I am realy greatful to you for your kindness.
    btw, how the pctfree, size of block headers, block size etc. matters in CP, can you please how I should use this to arrive my CP.

    --Jaggu

  12. #12
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Here's a little formula from sizing tables... There is overhead involved in the number of columns a table has, the number of columns over 250 bytes, etc ... Also consider, when creating a table to put all the
    "NOT NULL" columns at the top of the table and together... If you have
    a "NOT NULL" column after 10 "NULL" columns, the 10 NULL columns
    MUST maintain a byte for placement purposes... That byte is NOT maintained if the "NOT NULL" columns are at the top and all "NULL"
    columns are at the bottom ...


    set verify off
    undefine pctfree
    undefine avg_row_length
    undefine number_of_columns
    undefine number_of_cols_over_250_bytes
    undefine number_of_rows

    select &&number_of_rows/
    (((100-&&pctfree)*(Value-90))
    /(100*(&&avg_row_length + 3 + &&number_of_columns
    + &&number_of_cols_over_250_bytes))) Blocks_Required
    from V$PARAMETER
    where Name = 'db_block_size';



    HTH
    Gregg

  13. #13
    Join Date
    Jan 2013
    Posts
    4
    @Alan P

    can i calculate no. of characters in a row (sum of total data in all the columns) using AVG_ROW_LEN from user_tables

  14. #14
    Join Date
    Jan 2013
    Posts
    4
    By setting LINESIZE parameter we are setting total no. of coloumns or total no. of caharcters that can accomdate in a line(sum of all columns).

    I am new to this .. so have this doubt ..

    Please help ..

Posting Permissions

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