Segment shrink and FBIs [message #588730] |
Thu, 27 June 2013 14:41  |
John Watson
Messages: 8966 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL>
SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;
Table created.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 shrink space;
Table altered.
SQL> create index i1 on t1(c2);
Index created.
SQL> alter table t1 shrink space;
alter table t1 shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object
SQL> Can anyone think of a technical limitation here? Why it can't be done? (This is idle curiosity, not a problem.)
|
|
|
|
Re: Segment shrink and FBIs [message #588769 is a reply to message #588734] |
Fri, 28 June 2013 04:14   |
John Watson
Messages: 8966 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
You could well be right! Though the fact that this limitation has persisted since release 10.1 makes me wonder if there is something else. I had assumed that a segment shrink was implemented with matched pairs of delete/insert and a commit from time to time, with triggers disabled. So no problem with index maintenance, no matter what type of index. But perhaps there is more to it than that.
|
|
|
|
|
|
|
|