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

 
 Home
 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
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

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

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

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:

CREATE TABLE SAMPLE
(
ID NUMBER(9),
Name VARCHAR(50),
Create_Date_Time DATE,
Mod_Date_Time DATE,
User_ID VARCHAR(15)
)
Here is the code snippet:

SET SERVEROUTPUT ON

DECLARE
v_used_bytes NUMBER(10);
v_Allocated_Bytes NUMBER(10);
v_type sys.create_table_cost_columns;
BEGIN
v_Type := sys.create_table_cost_columns
(
sys.create_table_cost_colinfo(?NUMBER?,9),
sys.create_table_cost_colinfo(?VARCHAR2′,50),
sys.create_table_cost_colinfo(?VARCHAR2′,15),
sys.create_table_cost_colinfo(?DATE?,NULL),
sys.create_table_cost_colinfo(?DATE?,NULL)
);

DBMS_SPACE.CREATE_TABLE_COST(?USERS?,v_Type,10000,7,v_used_Bytes,v_Allocated_Bytes);

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

END;/

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.