Field size


    Oracle DB 12.2.0

    RHEL 7.4


    Hello Team,


    I have a few queries regarding datatypes used for calculating field size



    As per doc above,


    1. NUMBER(10) - does this mean the field stores NUMBER of size 10 bytes?

    2. BINARY_DOUBLE - what size by default is binary_double? Is it 8 bytes?

    3. TIMESTAMP(6) - does this mean TIMESTAMP of size 6 bytes? As per doc, it says frational seconds precision.  Or does this  field takes by default 11 bytes irrespective of the fractional seconds precision - TIMESTAMP(6), TIMESTAMP(10) ...?


    Can anyone pls validate code below?

    set serveroutput on


    ub NUMBER;

    ab NUMBER;

    cl sys.create_table_cost_columns;


    cl := sys.create_table_cost_columns(

    sys.create_table_cost_colinfo('VARCHAR2',11 ) ,

    sys.create_table_cost_colinfo('VARCHAR2',2 ) ,

    sys.create_table_cost_colinfo('VARCHAR2',20 ) ,

    sys.create_table_cost_colinfo('NUMBER',20) ,

    sys.create_table_cost_colinfo('NUMBER',20) ,

    sys.create_table_cost_colinfo('TIMESTAMP', 11) ,

    sys.create_table_cost_colinfo('VARCHAR2',30 ),

    sys.create_table_cost_colinfo('VARCHAR2',20 ) ,

    sys.create_table_cost_colinfo('VARCHAR2',20 ) ,

    sys.create_table_cost_colinfo('VARCHAR2',10 ) ,

    sys.create_table_cost_colinfo('BINARY_DOUBLE' ,8),

    sys.create_table_cost_colinfo('VARCHAR2',255) ,

    sys.create_table_cost_colinfo('NUMBER',10) ,


    sys.create_table_cost_colinfo('VARCHAR2',100 ) ,

    sys.create_table_cost_colinfo('VARCHAR2',100 )




    DBMS_SPACE.CREATE_TABLE_COST('TESTTBS',cl,16709155 ,10,ub,ab);

    DBMS_OUTPUT.PUT_LINE('Used s = ' || TO_CHAR(ub));

    DBMS_OUTPUT.PUT_LINE('Allocated s = ' || TO_CHAR(ab));







      • 1. Re: Field size

        I am not familiar with the procedure(s) in your PL/SQL code.

        With that said:


        NUMBER(10) is not ten bytes. Rather, it can hold any whole number (positive or negative) up to ten digits in base 10. How much space each value will take is variable; Oracle stores numbers in a quasi-"base 100" format. For example, the number 1000000 (one million) is stored in 2 bytes. 9999999999 (ten digits) is stored in 6 bytes, and -9999999999 in 7 bytes.


        BINARY_FLOAT is always 32 bits (4 bytes) and BINARY_DOUBLE is always 64 bits (8 bytes).


        With that said, Oracle also stores meta-data - a "length byte"; add 1 to the byte counts above.


        Dates and timestamps are also stored in a proprietary internal format; TIMESTAMP(6) means that the fractional second part is at most six decimal places. The number of bytes it occupies depends on the value: it's at most 11 bytes, but it may be as few as 7 bytes (when the fractional second part is .000000).

        1 位用户发现它有用
        • 2. Re: Field size
          Gaz in Oz

          Further to TIMESTAMP, the maximum precision definition allowed is 9 so declaring a column as TIMESTAMP(10) will fail.

          SQL> create table ts (ts10 timestamp(10));
          create table ts (ts10 timestamp(10))
          ERROR at line 1:
          ORA-30088: datetime/interval precision is out of range

          ...and the calls you are making to the zining procedures etc, need to be prefixed with DBMS_SPACE...

          Take a look here for a usage example:

          • 3. Re: Field size

            Hello Joe,


            you point to a doc explaining the various data types but not the way they are stored.  If you search more you will find interesting information (for example: it is very interesting to know how Oracle stores a "DATE" on seven bytes, it is also very interesting to know how Oracle stores a NUMBER, …). More than knowing how one column is stored it is important to know that Oracle stores only "what has to be stored": if you have a table defined with 60 columns ( col1, col2, …, col60 ) and that for a given row you have values for col1, col2, col3 and col 15 only (all other columns being NULL), note that at the end of the col15 value Oracle has a "end of record" marker so there is no need to store col16, col17, …, col60 even in a short format.


            Then you have to get informed about the way rows are stored in Oracle blocks... (search the documentation "Database Concepts", "12 Logical Storage Structures", "Data Block Format")… (and I don't mention the possibility of compression)


            Don't forget that a table has (usually) indexes, they have to be taken into account (and store their entries in a different way).


            All this is really good for the "general knowledge" but I doubt you need to compute precisely the size of a table based on this; your "naïve" approach is far from reality, refining it enough to be realistic is a really complex task and you will have more success by creating a sample table, populating it with some representative data (especially: average length of varchar2 columns) and from this derive an idea of your target...


            What is your goal?

            Note that usually people are quite bad at predicting the number of rows that a table will contain in 2 years... Illustration with two cases that I have seen:

            - a team asked for a database, requesting 2TB of size... 3 years later they use hardly a few GB, no idea how they had "found" the 2TB

            - another team told me that a DB would keep a stable size after a few months of initial growth. More than a year later the DB continues to grow by about 20GB per week, especially 1 table and its indexes... I had a talk with the application guys who told me "indeed, we changed our mind, we had decided to purge the old data and keep only a few months but now we prefer to keep all the history"

            With such guys I would have been fool to spend time computing the size of 1 row of 1 table to try to derive I don't know what ;-)


            Best regards,


            Bruno Vroman.

            • 4. Re: Field size

              You mean the procedure is not good? I tried it on another table and the estimation was near to dba segments.


              • 5. Re: Field size



                I don't know sys.create_table_cost_columns / sys.create_table_cost_colinfo (after a quick look: I tend to think that you have to provide the average size, not the "defined size" for example if a column "address" is defined as VARCHAR2(255 CHAR), its actually used size is probably closer to 50 than to 255...)


                I say that ideas like "NUMBER(10) occupies 10 bytes or TIMESTAMP(6) occupies 6 bytes is far from reality, and that you cannot tell the size of a NUMBER or VARCHAR2 "in general" as the size depends of the data actually stored (it takes less space to store 123 than 456.78901234567, or to store 'Joe' than 'Bruno'...) So you have to perform this work (determining reasonable average size of each column) before trying to size a table.


                So my suggestion if your need is to estimate the size of a table supposed to contain for example 100 million rows is to create such a table (and its indexes) with representative data and for example 200K rows, and to extrapolate, with the grain of salt given by my real-life examples that you might end up with completely wrong figures even if the size of 1 row is estimated carefully if for example the table ends up with 10 million rows only, or 10 billion...


                Anyway, beside this it is good to get interested in the details of Oracle hence my suggestion to have a look at the documentation about for example the structure of an Oracle block, the way a NUMBER or a DATE are stored, and so on.


                Best regards,



                1 位用户发现它有用
                • 6. Re: Field size

                  I executed command below after loading data into the table. Why is the result null? Should I gather statistics?


                  select column_name, data_type, avg_col_len from dba_tab_cols where table_name ='ARCHICOM_2020' order by column_id;






                  • 7. Re: Field size

                    Robeen wrote:


                    I executed command below after loading data into the table. Why is the result null? Should I gather statistics?




                    So, you already thought of what you may have missed. Why don't you go ahead and try for yourself? Gather table statistics, then run your query again. What happens when you try?


                    The idea is to ask questions here only when you can't answer them yourself. Here there's no reason why you can't.

                    • 8. Re: Field size

                      Now it is good