I can't replicate your findings....but please read on past the demo
SQL> create table t
2 partition by list ( x )
3 (
4 partition p1 values (1)
5 )
6 tablespace largets
7 as select 1 x, d.*
8 from dba_objects d,
9 ( select 1 from dual connect by level <= 400 );
Table created.
SQL>
SQL> create index ix1 on t ( owner ) local tablespace largets ;
Index created.
SQL> create index ix2 on t ( object_name ) local tablespace largets ;
Index created.
SQL> create index ix3 on t ( object_id ) local tablespace largets ;
Index created.
SQL> create index ix4 on t ( created ) local tablespace largets ;
Index created.
SQL> create index ix5 on t ( last_ddl_time ) local tablespace largets ;
Index created.
SQL> create index ix6 on t ( data_object_id ) local tablespace largets ;
Index created.
SQL> alter index ix1 modify partition p1 unusable;
Index altered.
SQL> alter index ix2 modify partition p1 unusable;
Index altered.
SQL> alter index ix3 modify partition p1 unusable;
Index altered.
SQL> alter index ix4 modify partition p1 unusable;
Index altered.
SQL> alter index ix5 modify partition p1 unusable;
Index altered.
SQL> alter index ix6 modify partition p1 unusable;
Index altered.
SQL>
SQL> create or replace
2 procedure ppp is
3 begin
4 execute immediate 'alter table t modify partition p1 rebuild unusable local indexes' ;
5 end;
6 /
Procedure created.
SQL>
SQL> set timing on
SQL> exec ppp
PL/SQL procedure successfully completed.
Elapsed: 00:02:47.63
SQL> set timing off
SQL>
SQL> alter index ix1 modify partition p1 unusable;
Index altered.
SQL> alter index ix2 modify partition p1 unusable;
Index altered.
SQL> alter index ix3 modify partition p1 unusable;
Index altered.
SQL> alter index ix4 modify partition p1 unusable;
Index altered.
SQL> alter index ix5 modify partition p1 unusable;
Index altered.
SQL> alter index ix6 modify partition p1 unusable;
Index altered.
SQL>
SQL> set timing on
SQL> alter table t modify partition p1 rebuild unusable local indexes;
Table altered.
Elapsed: 00:02:46.72
SQL>
SQL>
Having said that, I got bitten by similar issue to this a few years back, but it was with explicit rebuild calls for each index. The cause in that case was that temporary space needed is released at the end of the database call.
So if you rebuilds 10 indexes in SQL Plus, we are doing this:
- rebuild #1 (consume big temp segment)
- database call is complete and we are returning control to the client
- release the big temp segment
- rebuild #2 (consume big temp segment)
- database call is complete and we are returning control to the client
- release the big temp segment
etc etc
If you do this in a PLSQL loop, we are doing this:
- rebuild #1 (consume big temp segment)
- database call is NOT complete
- rebuild #2 (consume ANOTHER big temp segment)
- database call is NOT complete
- rebuild #3 (consume ANOTHER big temp segment)
etc etc
In my case, the elapsed time was burnt doing lots and lots of extensions to our tempfile.
So perhaps log a call with Support and see if perhaps there is a related issue for your particular patch level/platform etc.