OraFAQ Forum: Performance Tuning » How to release the un-used INDEX space, which were created by DELETE

Home » RDBMS Server » Performance Tuning » How to release the un-used INDEX space, which were created by DELETE (Oracle9i Release 9.2.0.4.0 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to release the un-used INDEX space, which were created by DELETE [message #391237]

Wed, 11 March 2009 09:28

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Hi,

Database is shcema size is 80GB.
Threat to performance and identified, Purging implementation.
Can one suggest as due numourous DML operation and DELETE records unreleased
space by its associated INDEX remain in the tablespace, so in Oracle9i (Enterprise Edition Release 9.2.0.4.0 - 64bit Production)
i) Is online rebuild of index is possible, if there is no down-time in the production?
ii) if yes than how to proceed with that in order to release the space occupied by the
unused INDEX space.

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391240 is a reply to message #391237]

Wed, 11 March 2009 09:33

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Rebuilding indexes online have their own pitfalls, you will actually end with "ballooned" tablespace.
If really want it, rebuild offline and do not do that as regular job.
For performance issues, just recollect the stats on tables and indexes.

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391245 is a reply to message #391237]

Wed, 11 March 2009 09:37

alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com

Senior Member

Hi,

No down time. Just do
alter index <index-name> rebuild online;
Regards,
Alex

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391259 is a reply to message #391245]

Wed, 11 March 2009 10:01

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Praying you will not get an ORA-01555 or ORA-01410 error in other sessions.

Regards
Michel

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391752 is a reply to message #391245]

Fri, 13 March 2009 05:47

tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia

Member

Hi,

REBUILD INDEX ONLINE in a Huge Database will might give an ORA-01555 error, i.e. SNAPSHOT_TOO OLD ERROR.
Is there any way in Oracle 9.2.0.4.0 to over from this error while implementing the REBUILD INDEX ONLINE ?

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391774 is a reply to message #391752]

Fri, 13 March 2009 07:20

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

No unless you remove concurrent transactions (and in this case, why using online?).

Regards
Michel

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391905 is a reply to message #391752]

Sat, 14 March 2009 09:18

alexzeng
Messages: 133
Registered: August 2005
Location: alexzeng.wordpress.com

Senior Member

hi,

I think you can rebuild online at night or when the db is less busy. Also you can enlarge undo tablespace size to avoid error.

Regards,
Alex

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391915 is a reply to message #391905]

Sat, 14 March 2009 10:04

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Enlarging undo tablespace does not prevent from ORA-01410 error.

Regards
Michel

Report message to a moderator

Re: How to release the un-used INDEX space, which were created by DELETE [message #391944 is a reply to message #391905]

Sat, 14 March 2009 19:36

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

With online rebuild, the before image and after image of index would be maintained upto somepoint.

You need space to accommodate both.
Rules are simple. If one can afford offline, do it.
Else, live with consequences by doing it online.

[Updated on: Sat, 14 March 2009 19:56]

Report message to a moderator

Previous Topic:	TNS-12518: TNS:listener could not hand off client connection
Next Topic:	Query perfomance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 07:56:01 CST 2024