| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: rebuild indexes with autoextend on databasefiles?
Anders Blomkvist wrote:
>
> What happens if one would try to rebuild all indexes in an index tablespace
> with no free space and with autoextend enabled for the databasefiles? Would
> the databasefiles be extended to twice the original size (to hold twice the
> data during rebuild) and then hold that size? Should one add a new temporary
> datafile to get enough space for rebuild and then delete it afterwards? If
> so, is it necessary to disable autoextend on the other databasefiles? Is
> there another way?
>
> Rgrds,
> Anders
An example shows that the file will grow:
SQL> create tablespace blah datafile 'E:\ORASW\DB1\DUMMY.DBF' size 10m 2 autoextend on next 2m;
Tablespace created.
SQL> create user blah identified by blah default tablespace blah temporary tablespace temp;
User created.
SQL> grant dba to blah;
Grant succeeded.
SQL> conn blah/blah
SQL> create table test1 nologging as select mod(rownum,123123) x from sys.source$;
Table created.
SQL> create index text1_ix on test1 ( x ) nologging;
Index created.
SQL> select name, bytes from v$datafile where name like '%DUMMY%';
NAME
BYTES
----------
E:\ORASW\DB1\DUMMY.DBF
31457280
SQL> alter index text1_ix rebuild nologging;
Index altered.
SQL> select name,bytes from v$datafile where name like '%DUMMY%';;
NAME
BYTES
----------
E:\ORASW\DB1\DUMMY.DBF
50331648
7 rows selected.
SQL> spool off
However, presumably you would be rebuilding the indexes one at a time, which means you only need free space for the largest.
hth
connor
-- ============================== Connor McDonald http://www.oracledba.co.uk "Some days you're the pigeon, some days you're the statue..."Received on Wed Aug 22 2001 - 13:00:47 CDT
 |
 |