Many times when we have to calculate an index size, or should calculate the space we need because we could having to extend the tablespace, of course, you can always run the create index command and if it fails to add more datafiles and reexecute. In this post we will remember that Oracle has a tool to help with this issue, using the DBMS_SPACE package.
We just pass the create command “CREATE INDEX ON TAB1_INX TAB1 (COL1)”
Run from sqlplus
SET SERVEROUTPUT ON SIZE UNLIMITED
VARIABLE A_Bytes NUMBER
VARIABLE E_Bytes NUMBER
exec dbms_space.create_index_cost( ‘CREATE INDEX TAB1_INX ON TAB1 (COL1)’, –
:A_Bytes, :E_Bytes );
Very important (From documentation)
- The table on which the index is created must already exist.
- The computation of the index size depends on statistics gathered on the segment.
- It is imperative that the table must have been analyzed recently.
- In the absence of correct statistics, the results may be inaccurate, although the procedure will not raise any errors.
HTH – Antonio NAVARRO