Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Using dbms_space.create_table_cost

Oracle Database Tips by Donald BurlesonMay 26, 2015

For basic table and index sizing estimates see these notes on Tablespace and Datafile Sizing, and note that some Oracle professionals offer Oracle database tablespace sizing spreadsheets

Oracle provides the dbms_space built-in package to facilitate computing and estimating the space used by database objects (table).  Let's take a close look at how we can use the dbms_space package to estimate the required storage for tables and indexes.

Using the create_table_cost procedure

The dbms_space.create_table_cost procedure is used for estimating the final size of a table or index (with dbms_space.create_index_cost) by accepting input sizing parameters and computing the estimated used bytes and allocated bytes for a table or index.  The Oracle docs note the sizing input columns for create_table_cost:

avg_row_size The anticipated average row size in the table
colinfos The description of the columns
row_count The anticipated number of rows in the table
pct_free The percentage of free space in each block for future expansion of existing rows due to updates
used_bytes The space used by user data
alloc_bytes The size of the object taking into account the tablespace extent characteristics

Here is an example invocation of dbms_space.create_table_cost:

Name VARCHAR(50),
Create_Date_Time DATE,
Mod_Date_Time DATE,
Here is the code snippet:


v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
v_type sys.create_table_cost_columns;
v_Type := sys.create_table_cost_columns


DBMS_OUTPUT.PUT_LINE(?Used Bytes: - || TO_CHAR(v_used_Bytes));
DBMS_OUTPUT.PUT_LINE(?Allocated Bytes: - || TO_CHAR(v_Allocated_Bytes));


Using create_index_cost procedure to estimate index storage

For dbms_space.create_index_cost, the procedure accepts the DDL for a "create index" statement and outputs the storage needed to create the index.  The Oracle documentation notes these input parameters for dbms_space.create_index_cost:

ddl The create index DDL statement
used_bytes The number of bytes representing the actual index data
alloc_bytes Size of the index when created in the tablespace
plan_table Which plan table to use, default NULL

Note:  Using dbms_space.create_index_cost requires that the table already exist and that the table have current metadata statistics (as computed with dbms_stats).

See my related notes on estimating Oracle table and index sizes:




Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.