OraFAQ Forum: Performance Tuning » Shrinking Index tablespace

Home » RDBMS Server » Performance Tuning » Shrinking Index tablespace

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

Shrinking Index tablespace [message #153052]

Wed, 28 December 2005 13:40

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

All
If I move some indexes from index tablespace to another tablespace. How do I claim the free size of previous index tablespace. How do I shrink the previous tablespace.

Thanks

Report message to a moderator

Re: Shrinking Index tablespace [message #153055 is a reply to message #153052]

Wed, 28 December 2005 15:02

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

What Oracle version you are running?
if 10g and using ASSM - search for:
ALTER INDEX <INDEX_NAME> SHRINK SPACE;

If 9/8 then search for:
ALTER DATABASE DATAFILE '/full path/datafile.dbf' RESIZE size XX[K|M];
(The last will help only if the last extent has never been used)
I think I got it right, but research for the correct syntax.

Hope this help.mj

Report message to a moderator

Re: Shrinking Index tablespace [message #153155 is a reply to message #153055]

Thu, 29 December 2005 09:43

toshidas2000
Messages: 120
Registered: November 2005

Senior Member

It is oracle 8i. The datafiles was full before but the indexes has been moved to different tablespace. This leaves lot of scattered space in the tablespace.

Report message to a moderator

Re: Shrinking Index tablespace [message #153356 is a reply to message #153052]

Sat, 31 December 2005 23:34

waseem_aijaz
Messages: 16
Registered: December 2005
Location: Kingdom of Saudi Arabia

Junior Member

Hai,

Just check whether you can coalesce a tablespace.

Syntax:

SQL> alter tablespace HRINDEX coalesce;

Report message to a moderator

Previous Topic:	Turn Logging on, on Index
Next Topic:	Inlist operator performance

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:15:57 CDT 2024