Home arrow Oracle arrow Page 5 - Developing and Implementing Applications, continued

Sizing the Objects - Oracle

This article, the second of three parts, focuses on the design and creation of applications that use the database. It is excerpted from chapter five of the book Oracle Database 10g DBA Handbook, written by Kevin Loney and Bob Bryla (McGraw-Hill/Osborne, 2005; ISBN: 0072231459).

  1. Developing and Implementing Applications, continued
  2. Switching Consumer Groups
  3. Editing Stored Outlines
  4. The Golden Rule for Space Calculations
  5. Sizing the Objects
  6. Estimating Space Requirements for Indexes
  7. Sizing Tables That Contain Large Objects (LOBs)
  8. Using Object Views
By: McGraw-Hill/Osborne
Rating: starstarstarstarstar / 5
February 16, 2006

print this article



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.


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:





The tablespace in which the object will be created.


The average length of a row in the table.


The description of the columns.


The anticipated number of rows in the table.


The pctfree setting for the table.


The space used by the table’s data. This value includes the overhead due to the pctfree setting and other block features.


The space allocated to the table’s data, based on the tablespace characteristics. This value takes the tablespace extent size settings into account.

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:

calc_used_bytes NUMBER;
   calc_alloc_bytes NUMBER;
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);

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.


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

>>> More Oracle Articles          >>> More By McGraw-Hill/Osborne

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Java Security Woes Continue
- Oracle's New IaaS Cloud Option: There's a Ca...
- Oracle Acquires Eloqua to Boost Cloud Presen...
- Choosing Innovation: Oracle Survey Insights
- Oracle Fixes Privilege Escalation Bug
- Oracle`s Communications Service Availability...
- Oracle Releases Exalytics, Taleo Plans
- Oracle Releases Communications Network Integ...
- Oracle Releases Communications Data Model 11...
- Oracle Releases PeopleSoft PeopleTools 8.52
- Oracle Integrates Cloudera Apache Distro, My...
- Oracle Releases MySQL 5.5.18
- Oracle Announces NoSQL Database Availability
- Sorting Database Columns With the SELECT Sta...
- Retrieving Table Data with the LIKE Operator

Developer Shed Affiliates


Dev Shed Tutorial Topics: