Home » RDBMS Server » Server Administration » Segment shrink and FBIs (DB12.1.0.1)
Segment shrink and FBIs [message #588730] Thu, 27 June 2013 14:41 Go to next message
John Watson
Messages: 4693
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 #588734 is a reply to message #588730] Thu, 27 June 2013 15:07 Go to previous messageGo to next message
Michel Cadot
Messages: 59427
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first thing I think is that the developer did not think about this case and in the end did not have time to modify his code and so throw an error instead. Smile

Regards
Michel
Re: Segment shrink and FBIs [message #588769 is a reply to message #588734] Fri, 28 June 2013 04:14 Go to previous message
John Watson
Messages: 4693
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.
Previous Topic: Unable to see/insert Chinees characters in Oracle db
Next Topic: how to connect oracle client to a remote oracle server DB
Goto Forum:
  


Current Time: Sat Oct 25 20:01:43 CDT 2014

Total time taken to generate the page: 0.07541 seconds