Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    2

    Unanswered: Max record size of an Oracle table

    Do anyone have a script that calculates the maximum record size of an oracle table? A script that can sum the bytes of each column data type for a given table.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    I don't think there is an automatic script. You can use the vsize function to retrieve the size for each column.

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    Code:
    select sum(data_length) 
    from all_tab_cols 
    where table_name = <table name>
    I don't know if data_length is accurate to-the-byte for all datatypes, but it's a start. --=cf

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    DATA_LENGTH reports the max. length that a column can have (e.g. vor VARCHAR columns), it does not show the actual size of the data.

    @mister_b
    Do you want to see the size for each row in the table, or is a a single value per table enough? (A statement to retrieve the size per table is quite easy)

  5. #5
    Join Date
    Aug 2008
    Posts
    2

    Thumbs up Code:

    I wrote an anonymous block of code to determine the maximum size of a record for any table. Here is the code:

    -- ################################################## ########
    -- Script name: get_max_record_size.sql
    -- Author: Larry Bailey
    -- Date Written: 08/12/2008
    -- Revised:
    -- Remarks: This code will extract each column from
    -- all_tab_columns view and sum the maximum
    -- bytes per column to obtain the maximum
    -- size of a given row in a table.
    --
    -- ################################################## ########

    set serveroutput on linesize 300

    DECLARE

    v_max_size number := 0;
    v_owner varchar2(30);
    v_table_name varchar2(30);
    v_data_type varchar2(30);
    v_data_length number := 0;
    v_data_precision number := 0;

    CURSOR cur_logsearch is
    select owner, table_name, data_type, data_length, data_precision
    from all_tab_columns
    where owner='AVMLOG' and
    table_name='LOGSEARCH';

    BEGIN

    --
    -- Process Table LOGSEARCH
    --

    for i in cur_logsearch
    loop
    if i.data_type = 'NUMBER'
    then
    v_max_size := (v_max_size + i.data_precision);
    else
    v_max_size := (v_max_size + i.data_length);
    end if;
    end loop;

    dbms_output.put_line(chr(10));
    dbms_output.put_line('Table = LOGSEARCH, '||'Max Record Size = '||v_max_size ||' bytes');

    END;
    /

Posting Permissions

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