OraFAQ Forum: Server Administration » dba_free

Home » RDBMS Server » Server Administration » dba_free_space

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

dba_free_space [message #199057]

Thu, 19 October 2006 22:18

*Jess*
Messages: 48
Registered: December 2005
Location: Penang, Malaysia

Member

Hi guys,

I would like to check whether any of the tablespace in my database is fragmented.

Is this the correct SQL statement to query?
SQL> select sum(bytes) from dba_free_space
where tablespace_name='USERS';

Any suggestions? Thanks.

Report message to a moderator

Re: dba_free_space [message #199059 is a reply to message #199057]

Thu, 19 October 2006 23:06

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> select sum(bytes) from dba_free_space where tablespace_name='USERS';
How does above answer below?
What value of answer above change from "yes" to "no" or vice versa?
>I would like to check whether any of the tablespace in my database is fragmented.
Why do you think "fragmentation" is causing any problems?

Report message to a moderator

Re: dba_free_space [message #199071 is a reply to message #199057]

Fri, 20 October 2006 00:26

*Jess*
Messages: 48
Registered: December 2005
Location: Penang, Malaysia

Member

we are currently facing space constraint, if a tablespace or table is fragmented, we would wan to do a re-org

Report message to a moderator

Re: dba_free_space [message #199160 is a reply to message #199071]

Fri, 20 October 2006 12:39

andrew again
Messages: 2577
Registered: March 2000

Senior Member

To check table and index over allocation you can use a script like
http://www.orafaq.com/forum/mv/msg/42883/113376/45693/#msg_113376
Don't be too concerned about large number of extents
http://www.oracle.com/technology/deploy/availability/pdf/defrag.pdf
http://www.dizwell.com/prod/node/66

Simple report on free space in a tablespace

select min(bytes)/1024/1024 min_meg, 
       max(bytes)/1024/1024 max_meg, 
	   sum(bytes)/1024/1024 tot_meg, 
	   count(*) holes 
from dba_free_space
where tablespace_name = 'USERS';

MIN_MEG	MAX_MEG	TOT_MEG	        HOLES
0.0625	15	468.1875	248

Report message to a moderator

Previous Topic:	Oracle Client 8i -> 9i
Next Topic:	Trouble in starding oracleservicedawn

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 06:50:30 CDT 2024