hoe to find free extents

[venkattr]

Hi guys,
How to find used extents,free extents in a table.can anyone please help me ASAP.

[pando]

rephrase your question, it does not make sense

although I suspect we have some concepts problems here

[venkattr]

hi pando,
I want to find the no of available extents in a table.for ex. i allocate 121 extents .out of this how much extents has been used and how much is free.help me.

[pando]

look dba_segments for number of extents

you said you allocate 121 extents, did you use minextents 121?

if that is the case you have to use dbms_space package to find freespace

[venkattr]

Hi,
Most of the time I am getting error like this for table Max .extents reached for table.That's y i plan to find used extents and free extents for a table.reply IMMEIATELY

[gandolf989]

select t1.owner,t1.tablespace_name,t1.table_name,t1.initial_extent/1024 init_extK,
t1.next_extent/1024 Next_ExtK,
t1.max_extents,count(*) from dba_tables t1, dba_extents t2
where t1.owner=t2.owner and t1.table_name=t2.segment_name
group by t1.owner,t1.tablespace_name,t1.table_name,t1.initial_extent,
t1.next_extent,t1.max_extents
order by t1.owner,t1.table_name
/
select t1.owner,t1.tablespace_name,t1.index_name,t1.initial_extent/1024 init_extK,
t1.next_extent/1024 Next_ExtK,
t1.max_extents,count(*) from dba_indexes t1, dba_extents t2
where t1.owner=t2.owner and t1.index_name=t2.segment_name
group by t1.owner,t1.tablespace_name,t1.index_name,t1.initial_extent,t1.next_extent,
t1.max_extents
order by t1.owner,t1.index_name
/
select t1.tablespace_name, t1.segment_name, t1.initial_extent/1024 init_extK,
t1.next_extent/1024 Next_ExtK, t1.max_extents, t1.status, Count(*)
from dba_rollback_segs t1,
dba_extents t2
where t1.segment_name = t2.segment_name
group by t1.tablespace_name, t1.segment_name,t1.initial_extent,t1.next_extent,
t1.max_extents,t1.status
/

Look for places where count(*) = t1.max_extents.

[pando]

hm why would someone count extents from dba_extents when you have an extents column in dba_segments...

[gandolf989]

I didn't write the script. Perhaps there is a more efficient script that Venkattr could use. But the selects do work.