9 Replies Latest reply: Dec 4, 2012 9:48 AM by Me_101 RSS

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

    Me_101
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        JustinCave
                        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
                          thanks so much Justin, you show me my error :)