在创建一个索引之前，可以通过DBMS_SPACE.CREATE_INDEX_COST存储过程来估算所需的空间,但仅仅是估算。

This procedure determines the cost of creating an index on an existing table. The input is the DDL statement that will be used to create the index. The procedure will output the storage required to create the index.

DBMS_SPACE.CREATE_INDEX_COST (
   ddl             IN    VARCHAR2,
   used_bytes      OUT   NUMBER,
   alloc_bytes     OUT   NUMBER,
   plan_table      IN    VARCHAR2 DEFAULT NULL);

Parameter	Description
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

SQL> exec dbms_stats.gather_table_stats('HR','T1');
PL/SQL procedure successfully completed.
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='T1' and owner='HR';
SUM(BYTES)/1024/1024
--------------------
72
SQL> select a.tablespace_name,total,free,total-free used from
2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
3 group by tablespace_name) a,
4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
5 group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name and a.tablespace_name='USERS';
TABLESPACE_NAME TOTAL FREE USED
------------------------------ ---------- ---------- ----------
USERS 483.75 380.0625 103.6875

SQL> declare
2 v1 number;
3 v2 number;
4 begin
5 DBMS_SPACE.CREATE_INDEX_COST('create index hr.idx_t1_jobid on hr.t1(job_id)',v1,v2);
6 dbms_output.put_line('create the index need actually use: '||v1/1024/1024||' MB ,and allocalte: '||v2/1024/1024||' MB in the tablespace!');
7 end;
8 /
create the index need actually use: 5.9375 MB ,and allocalte: 18 MB in the
tablespace!
PL/SQL procedure successfully completed.

SQL> create index hr.idx_t1_jobid on hr.t1(job_id);
Index created.
SQL> select sum(bytes)/1024/1024 from dba_segments where segment_name='IDX_T1_JOBID';
SUM(BYTES)/1024/1024
--------------------
17
SQL> select a.tablespace_name,total,free,total-free used from
2 ( select tablespace_name,sum(bytes)/1024/1024 total from dba_data_files
3 group by tablespace_name) a,
4 ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space
5 group by tablespace_name) b
6 where a.tablespace_name=b.tablespace_name and a.tablespace_name='USERS';
TABLESPACE_NAME TOTAL FREE USED
------------------------------ ---------- ---------- ----------
USERS 483.75 363.0625 120.6875

oracle 估算一个索引所需要的空间