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 )
How to release the un-used INDEX space, which were created by DELETE [message #391237] Wed, 11 March 2009 09:28 Go to next message
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.
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 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
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 Go to previous messageGo to next message
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

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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Praying you will not get an ORA-01555 or ORA-01410 error in other sessions.

Regards
Michel
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 Go to previous messageGo to next message
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 ?
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No unless you remove concurrent transactions (and in this case, why using online?).

Regards
Michel
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enlarging undo tablespace does not prevent from ORA-01410 error.

Regards
Michel
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 Go to previous message
Mahesh Rajendran
Messages: 10672
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:
  


Current Time: Thu Dec 08 22:14:40 CST 2016

Total time taken to generate the page: 0.17979 seconds