This discussion is archived
9 Replies Latest reply: Dec 4, 2012 7:48 AM by Me_101 RSS

how to know the specific size of a field in oracle table

Me_101 Newbie
Currently Being Moderated
Hello,

oracle 11.2.0.3 on redhat
i have three tables with same structure, table0, table50, table100:

create table table0 (id number, shipment RAW(2000), options RAW(2000));

i inserted 10000 rows in each but with some differences:
table0 has 10000 id, but shipment and options has null values
table50 has 10000 id, but shipment and options has only 5000 rows, the other 5000 are null values
table100 has 10000 rows of id, shipment and options. No null values.

I want to know the specific size of each column, also the size of each row, to know the exactly size of the field.

for example:

row 1, column options of table100 has "asdfasdfagasdgasbabsdgoasdpgiahnwe1129u412094u12"
row 2, column options of table100 has "a".
both are raw datatype, but i think its space will has different size (bytes), how to know it? any query?

I found this query:
select owner,tablespace_name,segment_name,sum((bytes/1024/1024)) Bytes
from sys.dba_extents
where owner in 'MAA' and segment_type='TABLE' and segment_name ='TABLE00'
group by tablespace_name,owner,segment_name
order by owner,tablespace_name,segment_name, bytes; 
but i think it isn't my solution.
  • 1. Re: how to know the specific size
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    the query that you are using for row size and its post here :

    http://www.baanboard.com/baanboard/showthread.php?t=13985

    but for the column size you could use dba_tab_columns view
  • 2. Re: how to know the specific size
    Me_101 Newbie
    Currently Being Moderated
    yes Mr. mustafa, this web is where i found that query.

    with dba_tab_columns i have the value of raw(2000) but in table00 values are null, they must be 0 bytes:
    SQL> SELECT DATA_TYPE, COLUMN_NAME, DATA_LENGTH, DATA_DEFAULT, LOW_VALUE, HIGH_VALUE from dba_tab_columns where owner='VLIB_02' AND TABLE_NAME='RANDOM_JP_00' AND COLUMN_NAME='C_BET_INFO';
    
    {DATA_TYPE
    --------------------------
    COLUMN_NAME                    DATA_LENGTH DATA_DEFAULT
    ------------------------------ ----------- --------------------------------------------------------------------------------
    LOW_VALUE                                                        HIGH_VALUE
    ---------------------------------------------------------------- ----------------------------------------------------------------
    RAW
    OPTIONS                            2000}
    Edited by: Me_101 on 04-dic-2012 5:14
  • 3. Re: how to know the specific size
    Osama_Mustafa Oracle ACE
    Currently Being Moderated
    select column_name,data_length,char_length,avg_char_len,char_col_decl_length from
    dba_tab_columns where table_name = '<TABLE-NAME>'
  • 4. Re: how to know the specific size
    Me_101 Newbie
    Currently Being Moderated
    Thanks, but still it isn't the solution :S

    TABLE00
    SQL> select column_name,data_length,char_length,avg_col_len, char_col_decl_length from
    dba_tab_columns where table_name = 'TABLE00' AND OWNER='MAA' AND COLUMN_NAME='OPTIONS';
    
    COLUMN_NAME                    DATA_LENGTH CHAR_LENGTH AVG_COL_LEN CHAR_COL_DECL_LENGTH
    ------------------------------ ----------- ----------- ----------- --------------------
    OPTIONS                            2000           0
    TABLE100
    SQL> select column_name,data_length,char_length,avg_col_len, char_col_decl_length from
    dba_tab_columns where table_name = 'TABLE100' AND OWNER='MAA' AND COLUMN_NAME='OPTIONS';
     
    COLUMN_NAME                    DATA_LENGTH CHAR_LENGTH AVG_COL_LEN CHAR_COL_DECL_LENGTH
    ------------------------------ ----------- ----------- ----------- --------------------
    OPTIONS                            2000           0
    same result
  • 5. Re: how to know the specific size of a field in oracle table
    sb92075 Guru
    Currently Being Moderated
    http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions233.htm#SQLRF06162
  • 6. Re: how to know the specific size of a field in oracle table
    Me_101 Newbie
    Currently Being Moderated
    thx mr. sb92075

    i tried with vsize too but same result:

    with table50
    SELECT VSIZE(OPTIONS) FROM TABLE00;
    
    VSIZE(OPTIONS)
    -----------------
                 1000
                 1000
                 1000
                 1000
                   *
                   *
                   *
                   *
    with table100
    SELECT VSIZE(OPTIONS) FROM TABLE100;
    VSIZE(C_ELECCION)
    -----------------
                 2000
                 2000
                 2000
                 2000
                      *
                      *
    it's the same size for 'a' like 'abcasdfjañldngaosnbaposnbpaosuqroiwejrklasndiaoshbaposdjgajs'?? It's hard for me to believe.
    data is saved in blocks, a extents is a contiguos number of blocks and a group of extensions are segments. If you insert some text in a table field, Oracle create a extension and insert there the data but depend the bytes you insert Oracle will create one or more extents. How to know this specific space in that field?
  • 7. Re: how to know the specific size of a field in oracle table
    Me_101 Newbie
    Currently Being Moderated
    I can check the space of a tablespace, space of a table, space of a row, and i see at google that you can check the space in a row. but can't check the space in a field?
  • 8. Re: how to know the specific size of a field in oracle table
    Justin Cave Oracle ACE
    Currently Being Moderated
    If VSIZE is returning a constant value, that would imply that whatever is inserting the data into your table is always inserting a constant number of bytes.

    If I create a table with a RAW(1000) and insert data with different binary sizes, VSIZE gives different results
    SQL> create table foo(
      2    col1 raw(1000)
      3  );
    
    Table created.
    
    SQL> insert into foo values( utl_raw.cast_from_number(12) );
    
    1 row created.
    
    SQL> insert into foo values( utl_i18n.string_to_raw('This could be a much longer string') );
    
    1 row created.
    
    SQL> select vsize(col1) from foo;
    
    VSIZE(COL1)
    -----------
              2
             34
    If you are saying that VSIZE always returns 1000, that implies that whatever is inserting the data is always inserting 1000 bytes of data.

    Justin
  • 9. Re: how to know the specific size of a field in oracle table
    Me_101 Newbie
    Currently Being Moderated
    thanks so much Justin, you show me my error :)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points