dbms_space.free_space[15]

seems it does not work for partitioned table  september 17, 2003
reviewer:  dusan  from czech rep.

problems appeared when i tried to use this utility for partitioned table.

  1  select table_owner, partition_name
  2  from all_tab_partitions
  3  where table_owner='valasekd'
  4* and table_name='dv_all_objects_part'
sys@dv9ir2.us.oracle.com> /

table_owner                    partition_name
------------------------------ ----------------
valasekd                       p030915
valasekd                       p030916
valasekd                       pmax


  1  select ts.segment_space_management
  2    from dba_segments seg, dba_tablespaces ts
  3   where seg.segment_name      = 'dv_all_objects_part'
  4     and ('pmax' is null or
  5         seg.partition_name = 'pmax')
  6     and seg.owner = 'valasekd'
  7*    and seg.tablespace_name = ts.tablespace_name
sys@dv9ir2.us.oracle.com> /

segmen
------
auto

sys@dv9ir2.us.oracle.com> ed
zapsáno soubor afiedt.buf

  1* exec show_space('dv_all_objects_part', 'valasekd','table','pmax');
sys@dv9ir2.us.oracle.com> exec show_space('dv_all_objects_part', 
'valasekd','table','pmax');
begin show_space('dv_all_objects_part', 'valasekd','table','pmax'); end;

*
error na øádku 1:
ora-03204: zadání typu segmentu by mìlo vyznaèovat dìlení na partition
ora-06512: na "sys.dbms_space", line 97
ora-06512: na "sys.show_space", line 64
ora-06512: na line 1 

different extent sizes  october 02, 2003
reviewer:  kailash  from east brunswick, nj

thanks for a wonderful response. however, in response to your first review, i am 
not clear as to why the dba_extents shows different extent sizes (ie. the number 
of blocks in the extent are 5,5,5,10,7 a total of 32 extents). in this example, 
we did not specify any pctincrease. so my understanding is that all extents must 
be of equal size and 1 of the extents must have less space compared to other 
extents because of the 1 insert. however, the query shows a different result. i 
would appreciate if you could explain me how dba_extents calculated the above 
mentioned extent sizes. 

did you read the followup i posted with a link to the docs?

when you ask for 40k in a dictionary managed tablespace and we find a free 
extent that is within 5 blocks of your request (eg: 40k = 5 * 8k blocks -- 
anything that is free and has 5, 6, 7, 8, 9, 10 blocks will be used).  this is 
to avoid the situation whereby you have a free extent of say 7 blocks -- you 
asked for 5, so we take 5 leaving "2 totally useless blocks" hanging out there.


if the free extent was 11 blocks -- we would have given you your 5 and left 6 
out there for the next guy.

otherwise you would end up with lots of 1/2/3 block free extents that couldn't 
be used for anything. 

more explanation please  june 01, 2004
reviewer:  parag jayanat patankar  from india

hi tom,

thanks for explaining this thing in great way. i have done analysis on my table 
and found following

11:21:50 test@prod>  exec show_space('v9spd140');
free blocks.............................4051
total blocks............................7418
total bytes.............................30384128
unused blocks...........................0
unused bytes............................0
last used ext fileid....................5
last used ext blockid...................89500
last used block.........................3

i am confused with above data as it is not matching with your formula as 
explained in this thread  :-

ops$tkyte@8i> exec show_space( 't' )
free blocks.............................19
total blocks............................32
total bytes.............................262144
unused blocks...........................12
unused bytes............................98304
last used ext fileid....................1
last used ext blockid...................64681
last used block.........................5

pl/sql procedure successfully completed.

the above shows that the delete simply put all of our blocks on the free list.  
we have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 
blocks.

in my data

total blocks are      : 7418 = 

blocks on free list   : 4051 + 
unused blocks         :    0 + 
system block          :    1

then where are other blocks gone ? total blocks should be matched as per your 
formula 

kindly explain as i have missed out some imp part.

thanks & regards
parag jayant patankar 

the other blocks have data on them and just are not on the freelist (not 
available for inserts)

 

it's amazing what you find when you actually read the documentation .. ;)  july 13, 2004
reviewer:  greg  from toronto, ontario

funny that ... i was having trouble with some privilege rights on this 
dbms_space package and pulling my hair out .. i finally come here and do a 
search .... 

do i find my problem conveniently answered by tom?
no.

what do i get instead: 
"have you read about the dbms_* packages i use (they are documented).  it is 
pretty straightforward stuff."

... right, so a slap in the face, and the answer sitting right under my nose in 
the docs ... hehe

*sigh* ... thanks, tom!! 

.. i needed that!!
:)