Home » RDBMS Server » Server Administration » finding fragmented tables. (oracle 10g,(10.2.0), linux AS4)
finding fragmented tables. [message #336044] Thu, 24 July 2008 10:29 Go to next message
suresh.wst
Messages: 48
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
Re: finding fragmented tables. [message #336045 is a reply to message #336044] Thu, 24 July 2008 10:30 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
What type of tablespace is in the database?
Re: finding fragmented tables. [message #336049 is a reply to message #336044] Thu, 24 July 2008 10:32 Go to previous messageGo to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
It is tablespace which contains all users data (non-system).
Re: finding fragmented tables. [message #336051 is a reply to message #336049] Thu, 24 July 2008 10:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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
Re: finding fragmented tables. [message #336052 is a reply to message #336044] Thu, 24 July 2008 10:40 Go to previous messageGo to next message
BlackSwan
Messages: 25040
Registered: January 2009
Location: SoCal
Senior Member
SQL> select tablespace_name, allocation_type from dba_tablespaces;
post results from SQL above following Posting Guidelines
Re: finding fragmented tables. [message #336053 is a reply to message #336044] Thu, 24 July 2008 10:40 Go to previous messageGo to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
using which view I can see the list of tables which are fragmented.
Re: finding fragmented tables. [message #336061 is a reply to message #336053] Thu, 24 July 2008 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
select table_name from dba_fragmented_tables;

Regards
Michel
Re: finding fragmented tables. [message #336069 is a reply to message #336053] Thu, 24 July 2008 12:39 Go to previous messageGo to next message
joy_division
Messages: 4640
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.
Re: finding fragmented tables. [message #336161 is a reply to message #336061] Fri, 25 July 2008 01:26 Go to previous messageGo to next message
suresh.wst
Messages: 48
Registered: June 2008
Location: Hyderabad
Member
I couldn't find such data dictionary view like DBA_FRAGMENTED_TABLES.

can you check this out.
Re: finding fragmented tables. [message #336186 is a reply to message #336161] Fri, 25 July 2008 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
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
Re: finding fragmented tables. [message #336196 is a reply to message #336186] Fri, 25 July 2008 02:52 Go to previous messageGo to next message
suresh.wst
Messages: 48
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
Re: finding fragmented tables. [message #336202 is a reply to message #336196] Fri, 25 July 2008 03:14 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is no fragmented tables in LMT.

Regards
Michel
Re: finding fragmented tables. [message #336207 is a reply to message #336202] Fri, 25 July 2008 03:41 Go to previous messageGo to next message
suresh.wst
Messages: 48
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?
Re: finding fragmented tables. [message #336208 is a reply to message #336207] Fri, 25 July 2008 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Define fragmented.

Regards
Michel
Re: finding fragmented tables. [message #336210 is a reply to message #336208] Fri, 25 July 2008 04:01 Go to previous messageGo to next message
suresh.wst
Messages: 48
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.
Re: finding fragmented tables. [message #336212 is a reply to message #336210] Fri, 25 July 2008 04:05 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And then? they will be filled some time later.

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


Current Time: Mon Dec 05 23:59:54 CST 2016

Total time taken to generate the page: 0.06902 seconds