Sizing the Objects - Oracle

To effectively manage your space, all you need to do is select a set of space values that meet the criteria described in the preceding sections. Once the space allocations are finalized, separate them by tablespace. Here’s an example:

create tablespace DATA_1M
datafile '/u01/oracle/VLDB/data_1m.dbf'
size 100M
extent management local uniform size 1M;
create tablespace DATA_MEDIUM
datafile '/u01/oracle/VLDB/data_4m.dbf'
size 400M
extent management local uniform size 4M;
create tablespace DATA_LARGE
datafile '/u01/oracle/VLDB/data_16m.dbf' size 16000M
extent management local uniform size 16M;

In this example, three separate DATA tablespaces are created, with extent sizes of 1MB, 4MB, and 16MB. If you need to create a table 3MB in size, you can either create it with three 1MB extents in DATA_1M or with one 4MB extent in DATA_4M. A table that will grow to 10MB can be placed in DATA_4M.

As your tables grow in size, your default storage clauses will grow in a consistent fashion, following the space rules and your standards for extent sizes. DATA_64M would be next, followed by DATA_256M and DATA_1G. Use the same extent sizes across your databases to ease space management of your entire database environment.

As the extent sizes grow, the distribution of extent sizes across tablespaces will usually result in a separation of table types—small static tables will be isolated in the tablespaces with small extent sizes. Large transaction-processing tables (or their partitions) will be segregated to the large extent size tables, simplifying later management and tuning activities.

In the following sections you will see guidelines for estimations of the space usage for your objects. Because the target sizes (1MB, 4MB, 16MB, and so on) are not close together, the following estimations do not include highly detailed calculations.

NOTE

You can set the database block size at the tablespace level. You must set the block size during the tablespace creation, and you must have already created a buffer cache for the block size. Be sure the extent sizes you choose account for the largest block size in use in the database. Limiting the usage of nonstandard block sizes in the database will simplify cross-database maintenance and your sizing procedures.

Estimating Space Requirements for Tables

As of Oracle Database 10g, you can use the CREATE_TABLE_COST procedure of the DBMS_ SPACE package to estimate the space required by a table. The procedure determines the space required for a table based on attributes such as the tablespace storage parameters, the tablespace block size, the number of rows, and the average row length. The procedure is valid for both dictionary-managed and locally managed tablespaces.

There are two versions of the CREATE_TABLE_COST procedure (it is overloaded so you can use the same procedure both ways). The first version has four input variables: tablespace_name, avg_row_size, row_count, and pct_free. Its output variables are used_bytes and alloc_bytes. The second version’s input variables are tablespace_name, colinfos, row_count, and pct_free; its output variables are used_bytes and alloc_bytes. Descriptions of the variables are provided in the following table:

For example, if you have an existing tablespace named USERS, you can estimate the space required for a new table in that tablespace. In the following example, the CREATE_TABLE_COST procedure is executed with values passed for the average row size, the row count, and the pctfree setting. The used_bytes and alloc_bytes variables are defined and are displayed via the DBMS_ OUTPUT.PUT_LINE procedure:

declare
   calc_used_bytes NUMBER;
   calc_alloc_bytes NUMBER;
begin
   DBMS_SPACE.CREATE_TABLE_COST (
       tablespace_name => 'USERS',
       avg_row_size => 100,
       row_count => 5000,
       pct_free => 10,
       used_bytes => calc_used_bytes,
       alloc_bytes => calc_alloc_bytes
   );
   DBMS_OUTPUT.PUT_LINE('Used bytes: '||calc_used_bytes);
   DBMS_OUTPUT.PUT_LINE('Allocated bytes: '||calc_alloc_bytes);
end;
/

The output of this PL/SQL block will display the used and allocated bytes calculated for these variable settings. You can easily calculate the expected space usage for multiple combinations of space settings prior to creating the table.

NOTE

You must use the set serveroutput on  command to enable the script’s output to be displayed within a SQL*Plus session.