Oracle Object Size Growth Analysis

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

You plan to create a table in a tablespace and populate it with data. So, you want to estimate its initial size. This can be achieved using Segment Advisor in the EM or its package DBMS_SPACE.

Estimating Object Size using EM

You can use the Segment Advisor to determine your future segment resource usage.

Follow these steps:

1. From the Database Control home page, click the Administration tab.

2. Under the Storage section, click the Tables link.

3. Click the Create button to create a new table.

4. You�ll now be on the Create Table page. Under the Columns section, specify your column data types. Then click the Estimate Table Size button.

5. On the Estimate Table Size page, specify the estimated number of rows in the new table, under Projected Row Count. Then click the Estimated Table Size button. This will show you the estimated table size.

Estimating Object Size using DBMS_SPACE

For example, if your table has 30,000 rows, its average row size is 3 and the PCTFREE parameter is 20. You can issue the following code:

set serveroutput on
DECLARE
V_USED NUMBER;
V_ALLOC NUMBER;
BEGIN
DBMS_SPACE.CREATE_TABLE_COST (
TABLESPACE_NAME => 'USERS',
AVG_ROW_SIZE => 30,
ROW_COUNT => 30000,
PCT_FREE => 5,
USED_BYTES => V_USED,
ALLOC_BYTES => V_ALLOC);
DBMS_OUTPUT.PUT_LINE('USED: '|| V_USED/1024 ||
' KB');
DBMS_OUTPUT.PUT_LINE('ALLOCATED: '||
V_ALLOC/1024 || ' KB');
END;

The USED_BYTES represent the actual bytes used by the data. The ALLOC_BYTES represent the size of the table when it is created in the tablespace. This takes into account, the size of the extents in the tablespace and tablespace extent management properties.

If you want to make the estimation based on the column definitions (not average row size and PCTFREE):

set serveroutput on
DECLARE
UB NUMBER;
AB NUMBER;
CL SYS.CREATE_TABLE_COST_COLUMNS;
BEGIN
CL := SYS.CREATE_TABLE_COST_COLUMNS(
SYS.CREATE_TABLE_COST_COLINFO('NUMBER',10),
SYS.CREATE_TABLE_COST_COLINFO('VARCHAR2',30),
SYS.CREATE_TABLE_COST_COLINFO('VARCHAR2',30),
SYS.CREATE_TABLE_COST_COLINFO('DATE',NULL));
DBMS_SPACE.CREATE_TABLE_COST('USERS',CL,100000
,0,UB,AB);
DBMS_OUTPUT.PUT_LINE('USED: '|| UB/1024 || '
KB');
DBMS_OUTPUT.PUT_LINE('ALLOCATED: '|| AB/1024
|| ' KB');
END;