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: 5224
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: 61219
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 messageGo to next message
John Watson
Messages: 5224
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.
Re: Segment shrink and FBIs [message #636038 is a reply to message #588769] Wed, 15 April 2015 06:08 Go to previous messageGo to next message
surmaa013
Messages: 12
Registered: April 2015
Location: delhi
Junior Member
Hi all ,

As In my production DB oracle segment recommendation asking for shrink the Space of table and Indexes .
can I directly implement it to Production DB ?
Re: Segment shrink and FBIs [message #636040 is a reply to message #636038] Wed, 15 April 2015 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 61219
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do what you want even poke a pencil into your eye.

[Updated on: Wed, 15 April 2015 07:01]

Report message to a moderator

Re: Segment shrink and FBIs [message #636041 is a reply to message #636040] Wed, 15 April 2015 07:02 Go to previous messageGo to next message
surmaa013
Messages: 12
Registered: April 2015
Location: delhi
Junior Member
Doesn't that hurt ? Shocked
Re: Segment shrink and FBIs [message #636042 is a reply to message #636041] Wed, 15 April 2015 07:22 Go to previous messageGo to next message
gazzag
Messages: 552
Registered: November 2010
Location: Bristol, UK
Senior Member
You'll have to try it to find out Very Happy
Re: Segment shrink and FBIs [message #636043 is a reply to message #636041] Wed, 15 April 2015 09:48 Go to previous message
Lalit Kumar B
Messages: 2917
Registered: May 2013
Location: World Wide on the Web
Senior Member
surmaa013 wrote on Wed, 15 April 2015 17:32
Doesn't that hurt ? Shocked


It would be even more painful when someone gets fired. Reason is simple, implementing directly on production, without(any proof of) testing in lower(test) environments.

Quote:
can I directly implement it to Production DB ?


Oh come one man! This is IT industry, you must have all the fun in test environment Smile
Previous Topic: time difference between 2 oracle instance
Next Topic: Database Patch (20127071) - version 11.2.0.4.12 Contradicting Instructions
Goto Forum:
  


Current Time: Thu May 28 18:04:45 CDT 2015

Total time taken to generate the page: 0.10070 seconds