Home » RDBMS Server » Server Administration » Reorg the tables and Indexes (10g,Solaries)
icon5.gif  Reorg the tables and Indexes [message #399265] Tue, 21 April 2009 06:23 Go to next message
madhavamanohar
Messages: 58
Registered: February 2009
Member
Hello Experts...!


How to find which table or Index needs to be reorg..??

IS there any Query!!!

Thanks In advance.
Re: Reorg the tables and Indexes [message #399268 is a reply to message #399265] Tue, 21 April 2009 06:35 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
As a general rule, they don't.

Re: Reorg the tables and Indexes [message #399275 is a reply to message #399268] Tue, 21 April 2009 06:54 Go to previous messageGo to next message
madhavamanohar
Messages: 58
Registered: February 2009
Member
>>As a general rule

Means there is no way...

Can u please clear me...or Suggest any procedure!
Re: Reorg the tables and Indexes [message #399281 is a reply to message #399275] Tue, 21 April 2009 07:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Means you don't need to reorg.

Please read OraFAQ Forum Guide and don't use IM/SMS speak.

Regards
Michel
Re: Reorg the tables and Indexes [message #399283 is a reply to message #399265] Tue, 21 April 2009 07:25 Go to previous messageGo to next message
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
Senior Member
SELECT object_name
       ,object_type
       ,'No / Probably Not' "Needs Reorg"
FROM all_objects
WHERE object_type IN ('TABLE', 'INDEX')

[Updated on: Tue, 21 April 2009 07:25]

Report message to a moderator

Re: Reorg the tables and Indexes [message #399309 is a reply to message #399283] Tue, 21 April 2009 09:39 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Laughing
Re: Reorg the tables and Indexes [message #543975 is a reply to message #399309] Sat, 18 February 2012 10:23 Go to previous messageGo to next message
Jack14
Messages: 497
Registered: December 2011
Location: INDIA
Senior Member
Hi,

Normally we reorganize tables and indexes when there is a huge amount of deletion
of records in tables to free up the corresponding tablespace.

This activity will free up the tablespace .
Re: Reorg the tables and Indexes [message #543981 is a reply to message #543975] Sat, 18 February 2012 13:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>This activity will free up the tablespace .

no it does not since the space is already free.

[Updated on: Sat, 18 February 2012 13:38]

Report message to a moderator

Re: Reorg the tables and Indexes [message #543989 is a reply to message #543981] Sun, 19 February 2012 00:41 Go to previous messageGo to next message
Database admin
Messages: 365
Registered: September 2006
Location: india
Senior Member

We can gain some space in tablespace related table by reorganizing them.
Re: Reorg the tables and Indexes [message #543997 is a reply to message #543989] Sun, 19 February 2012 01:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sometimes you gain, sometimes you lose, some tims it is even.

Regards
Michel
Re: Reorg the tables and Indexes [message #544003 is a reply to message #543997] Sun, 19 February 2012 02:35 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
One technique that should guarantee a large space saving is to alter the table to enable compression before the reorg. Sort the the table as you reorganize, and the benefits will be even greater. You must test the impact on subsequent DML against the compressed blocks.
Basic compression is included in Enterprise Edition.
Previous Topic: Reorganize table and index
Next Topic: Storage IOPS and transport speed
Goto Forum:
  


Current Time: Thu Mar 28 12:09:42 CDT 2024