Home » RDBMS Server » Server Administration » dba_free_space
dba_free_space [message #199057] Thu, 19 October 2006 22:18 Go to next message
*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.
Re: dba_free_space [message #199059 is a reply to message #199057] Thu, 19 October 2006 23:06 Go to previous messageGo to next message
BlackSwan
Messages: 25050
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?
Re: dba_free_space [message #199071 is a reply to message #199057] Fri, 20 October 2006 00:26 Go to previous messageGo to next message
*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
Re: dba_free_space [message #199160 is a reply to message #199071] Fri, 20 October 2006 12:39 Go to previous message
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

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


Current Time: Sat Dec 10 10:53:05 CST 2016

Total time taken to generate the page: 0.20328 seconds