2

I'm trying to estimate the size of a tablespace using these steps: Space Estimations for Schema Objects

and I was little confused about some of the steps. In step 3 for calculating the rowsize I first need to calculate the column size and then the sum of all column sizes in the table, I was wondering how I get the column size value without having actually created the table yet do I have to create sample data and then use vsize()? or am I missunderstanding what the column size is..

Also these steps in the link are for estimating the size of a table (?) so if I wanted to estimate the size of the tablespace would I just take the sum of all the estimated table sizes?

1

Are you using Oracle 7? If not, please make sure to always use current versions of documentation. Oracle is already on 12C, and while some concepts still the same, a huge part of features has changed.

You actually won't get column size. This is why it is only an "estimate". You need to have an idea of what you are going to need, and then you can use the package dbms_space with the create_table_cost procedure to get an idea of space required.

For example:

I need to create a table with 3 columns (val1, val2, val3), and I know each one is going to take up 4bytes (3 of literals, like abc, and the leading byte that varchar2 takes). I also estimate that this table will have approximately 1000 rows. Now just think about cartesian graph:

row_count
^
|
|
|
|-----------> avg_row_len

and use the following to estimate the size:

 set serveroutputon
    DECLARE
      estimated_used_size   NUMBER;
      allocated  NUMBER;
    BEGIN
        DBMS_SPACE.create_table_cost (
        tablespace_name => 'USERS',
        avg_row_size    => 12,
        row_count       => 1000,
        pct_free        => 10,
        used_bytes      => estimated_used_size,
        alloc_bytes     => allocated);

      DBMS_OUTPUT.put_line ('the table (TS=USERS): will use =' || estimated_used_size ||' bytes  to be allocated =' ||  allocated);
end;
/

Regarding your last question, to estimate the size of an existing table, you can check the view dba_segments (also to query index and other objects size).

Example:

SQL> SELECT bytes/1024/1024 "Size in MB" FROM dba_segments WHERE segment_name = 'TABLE_NAME_HERE' AND segment_type = 'TABLE';
  • My last question was regarding tablespace size estimation and whether it's size is the sum of the estimated table sizes. If I have a column of varchar(10), would I be able to use 10 as the column size? – joe Aug 27 '15 at 4:44
  • No. Tablespaces will take up additional storage for internal management. Take a loook at segment, extent and blocks concepts, and test yourself creating a tablespace of 200kb for example, and querying against dba_free_space...: docs.oracle.com/cd/B19306_01/server.102/b14220/logical.htm. If you estimate 10 without taking into consideration leading and null indicator in case of varchar, the final estimate will be incorrect. – GuilhermeMesquitaX Aug 27 '15 at 12:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.