Oracle - How do I get the actual size of a specific ROW?

Question

Is this possible? Or at least I'm looking to have a list of the size of all rows in a table.

score 26 · Accepted Answer · edited May 23 '17 at 12:09

26

select vsize(col1) + vsize(col2) + vsize(col3) + 
long_raw_length_function(long_col) + DBMS_LOB.GETLENGTH(blob_col) 
from table 
where id_col = id_val;

for the long_raw_length_function, see this Get the LENGTH of a LONG RAW

edited May 23 '17 at 12:09

Community

1
1

answered May 03 '11 at 15:02

bpgergo

15,669
5
44
68

is `herp/derp` replacing `foo/bar` for arbitrary examples in code? – xdumaine May 03 '11 at 15:10
1

`herp/derp` is significantly more derogatory, so I think not. – Adam Musch May 03 '11 at 15:11
Thank you but what if one of the columns is a Long Raw? – danboh May 03 '11 at 15:11
1

replace vsize with [UTL_RAW.LENGTH(derpy_long_col);](http://download.oracle.com/docs/html/A96197_01/ap_urpkg.htm) – bpgergo May 03 '11 at 15:16
SQL Error: ORA-00997: illegal use of LONG datatype – danboh May 03 '11 at 15:31
2

My bad. Please see this http://stackoverflow.com/questions/5497238/get-the-length-of-a-long-raw/5497260#5497260 – bpgergo May 03 '11 at 15:52
LONG and LONG RAW have been deprecated for more than fifteen years now, precisely because they are a pain to work with. – APC May 03 '11 at 18:24
Note that `VSIZE` returns size of a specific value, not of the column on disk. It returns different sizes for different values of the same column. – Olexa Feb 04 '15 at 09:40

score 9 · Answer 2 · answered May 03 '11 at 15:59

9

if you're interested in the average row length, you could analyze the table (with the DBMS_STATS package), then query ALL_TABLES.avg_row_len.

answered May 03 '11 at 15:59

Vincent Malgrat

66,725
9
119
171

It may return lower value than sum of column lengths in bytes. Does it reflect actual disk storage space taken by an average row? – Olexa Feb 04 '15 at 09:55
2

@olexa: It seems the `avg_row_len` column takes into account the [actual space occupied by data](https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1041629603947). In most cases, it should be larger than the average of the sum of the size of the individual columns since there is small overhead at the column level (for instance: NULL values take space when not at the end of a row). Of course if you're using compression it could be lower. – Vincent Malgrat Feb 04 '15 at 10:45

score 3 · Answer 3 · edited Jul 17 '13 at 09:43

Here below is the query I have modified to get the table row length when you don't have any data. This can help you with Capacity planning for Environment setup:

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_TABLE
  IS
    SELECT DISTINCT table_name
    FROM all_tab_columns
    WHERE owner='TMS_OWNER'
    AND table_name NOT LIKE 'VIEW%'
    ORDER BY table_name;
BEGIN
  FOR Tab IN CUR_TABLE
  LOOP
    v_table_name := Tab.table_name;
    v_max_size   := 0;
    FOR i        IN
    (SELECT owner,
      table_name,
      data_type,
      data_length,
      data_precision
    FROM all_tab_columns
    WHERE owner    ='TMS_OWNER'
    AND table_name = v_table_name
    )
    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 ='||v_table_name||', Max Record Size = '||v_max_size||' bytes');
  END LOOP;
END;
/

It doesn't answer the question of the topic, because the physical size of a specific row is requested. This script will only show the theoretical logical max size based on dictionary, but not the resulting max physical size of a specific row. — D. Lohrsträter, Jul 21 '22 at 14:33

Oracle - How do I get the actual size of a specific ROW?

3 Answers3

Linked

Related