OraFAQ Forum: Server Administration » finding fragmented tables.

Home » RDBMS Server » Server Administration » finding fragmented tables. (oracle 10g,(10.2.0), linux AS4)

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

finding fragmented tables. [message #336044]

Thu, 24 July 2008 10:29

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

Hi,

How to find out the tables which are fragmented. Is there any view for that. please help me out.

Thanks & Regards
suresh

Report message to a moderator

Re: finding fragmented tables. [message #336045 is a reply to message #336044]

Thu, 24 July 2008 10:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

What type of tablespace is in the database?

Report message to a moderator

Re: finding fragmented tables. [message #336049 is a reply to message #336044]

Thu, 24 July 2008 10:32

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

It is tablespace which contains all users data (non-system).

Report message to a moderator

Re: finding fragmented tables. [message #336051 is a reply to message #336049]

Thu, 24 July 2008 10:36

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select extent_management from dba_tablespace where tablespace_name = 'your ts name'

This is this type of tablespace that asked Ana.

Regards
Michel

Report message to a moderator

Re: finding fragmented tables. [message #336052 is a reply to message #336044]

Thu, 24 July 2008 10:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SQL> select tablespace_name, allocation_type from dba_tablespaces;
post results from SQL above following Posting Guidelines

Report message to a moderator

Re: finding fragmented tables. [message #336053 is a reply to message #336044]

Thu, 24 July 2008 10:40

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

using which view I can see the list of tables which are fragmented.

Report message to a moderator

Re: finding fragmented tables. [message #336061 is a reply to message #336053]

Thu, 24 July 2008 11:43

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

select table_name from dba_fragmented_tables;

Regards
Michel

Report message to a moderator

Re: finding fragmented tables. [message #336069 is a reply to message #336053]

Thu, 24 July 2008 12:39

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

suresh.wst wrote on Thu, 24 July 2008 11:40

using which view I can see the list of tables which are fragmented.

You are using 10g, therefore you do not have fragmented tables.

Report message to a moderator

Re: finding fragmented tables. [message #336161 is a reply to message #336061]

Fri, 25 July 2008 01:26

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

I couldn't find such data dictionary view like DBA_FRAGMENTED_TABLES.

can you check this out.

Report message to a moderator

Re: finding fragmented tables. [message #336186 is a reply to message #336161]

Fri, 25 July 2008 02:23

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, but there is dba_tablespaces, can you check this and execute the query we asked?

Regards
Michel

Report message to a moderator

Re: finding fragmented tables. [message #336196 is a reply to message #336186]

Fri, 25 July 2008 02:52

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

yes, I executed following query & posting result below.

SYS>select tablespace_name,extent_management,allocation_type from dba_tablespaces where tablespace_name in('USERDATA','TS_2K');

TS_NAME EXTENT_MAN ALLOC_TYPE
---------- ---------- ----------
TS_2K LOCAL SYSTEM
USERDATA LOCAL SYSTEM

Regards
suresh

Report message to a moderator

Re: finding fragmented tables. [message #336202 is a reply to message #336196]

Fri, 25 July 2008 03:14

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

There is no fragmented tables in LMT.

Regards
Michel

Report message to a moderator

Re: finding fragmented tables. [message #336207 is a reply to message #336202]

Fri, 25 July 2008 03:41

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

If the table is present in Dictionary Managed tablespace, How can we find out whether table is fragmented or not?

Report message to a moderator

Re: finding fragmented tables. [message #336208 is a reply to message #336207]

Fri, 25 July 2008 03:47

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Define fragmented.

Regards
Michel

Report message to a moderator

Re: finding fragmented tables. [message #336210 is a reply to message #336208]

Fri, 25 July 2008 04:01

suresh.wst
Messages: 53
Registered: June 2008
Location: Hyderabad

Member

In a segment(table), if some rows are deleted, some blocks may become empty. this is what I mean to say fragmented.

Report message to a moderator

Re: finding fragmented tables. [message #336212 is a reply to message #336210]

Fri, 25 July 2008 04:05

Michel Cadot
Messages: 68675
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

And then? they will be filled some time later.

Regards
Michel

Report message to a moderator

Previous Topic:	10gR2 on AIX - Table size
Next Topic:	What extent size would your recommend here (uniform tablespace)?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 08 23:34:56 CDT 2024