OraFAQ Forum: Server Administration » how can i know tablespace is fragmented.

Home » RDBMS Server » Server Administration » how can i know tablespace is fragmented.

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

how can i know tablespace is fragmented. [message #62467]

Fri, 23 July 2004 02:36

BhavinShah
Messages: 105
Registered: February 2004

Senior Member

Hi.. friends,

HOw can i know tablespace is fragmented.. after using.. command alter tablespace <name> coalesce.. how can i see result..

and is any view in database by which i can know fragmentation..

bye..
thax for future reply

Report message to a moderator

Re: how can i know tablespace is fragmented. [message #62470 is a reply to message #62467]

Fri, 23 July 2004 04:21

Sanjay
Messages: 236
Registered: July 2000

Senior Member

Bhavin,

First of all, 'coalesce' is different from 'fragment'. 2 different animals.

alter table ... coalesces 2 empty adjacent extents. DOES NOT defragment the tablespace.

To defrag a table, export and import all objects in the tablespace.

You can see the defragmentation in OEM. There are a couple of scripts to see how your tablespace is doing, but I do not have them readily here. if your tablespace is dictionary managed, it is a good idea to coalesce them on a regular basis.
--
Sanjay

Report message to a moderator

Re: how can i know tablespace is fragmented. [message #62476 is a reply to message #62470]

Fri, 23 July 2004 06:31

shoblock
Messages: 325
Registered: April 2004

Senior Member

After you do the coalesce
select tablespace_name, file_id, count(*)
from dba_free_space
group by tablespace_name, file_id

This will tell you how fragmented each data file is.

Report message to a moderator

Is coalesce is not for fragmentation? [message #62481 is a reply to message #62476]

Fri, 23 July 2004 07:35

BhavinShah
Messages: 105
Registered: February 2004

Senior Member

hi sanjay..
u have given me to new idea abt fragmentation..

just can u give me explanation or some query by which i can see fragmentation.. i have seen in oem .. but by oem we can't see fragmentation..

bye..

Report message to a moderator

Previous Topic:	Oracle shared pool
Next Topic:	I want my IMP to overwrite the existing tables data

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 04:05:57 CST 2025