Something must be wrong -- give me a full example like this (that shows it works)


ops$tkyte@ORA817DEV.US.ORACLE.COM> create user a identified by a;
User created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> grant create session, create procedure, create table to a;
Grant succeeded.

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter user a quota unlimited on users;
User altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> connect a/a
Connected.

a@ORA817DEV.US.ORACLE.COM> @showspace
Procedure created.

a@ORA817DEV.US.ORACLE.COM> create table t ( x int ) tablespace users;
Table created.

a@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> set serveroutput on
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' );
Free Blocks.............................0
Total Blocks............................64
Total Bytes.............................524288
Unused Blocks...........................63
Unused Bytes............................516096
Last Used Ext FileId....................7
Last Used Ext BlockId...................4809
Last Used Block.........................1

PL/SQL procedure successfully completed.

<b>so, do the whole create user/install the procedure/run the test and see if it reproduces.  If not, either you were not logged in as TOM, TOM did not own T, etc... (is T a view or synonym in your case??)</b> 

the 5 extents were expected. the 32 blocks just happened.

Ok, time for an update of this utility!  I had this sitting around already -- it does two things 

1) adds partition support
2) makes it so this runs in SQL for anything...  gives a result set instead of printing. You can easily make it dbms_output.put_line if you want...


First we start with the types:

create or replace type show_space_type
as object
( owner                 varchar2(30),
  segment_name          varchar2(30),
  partition_name        varchar2(30),
  segment_type          varchar2(30),
  free_blocks           number,
  total_blocks          number,
  unused_blocks         number,
  last_used_ext_fileid  number,
  last_used_ext_blockid number,
  last_used_block       number
)
/
create or replace type show_space_table_type
as table of show_space_type
/


And then the function:

create or replace
function show_space_for
( p_segname   in varchar2,
  p_owner     in varchar2 default user,
  p_type      in varchar2 default 'TABLE',
  p_partition in varchar2 default NULL )
return show_space_table_type
authid CURRENT_USER
as
    pragma autonomous_transaction;
    type rc is ref cursor;
    l_cursor rc;

    l_free_blks                 number;
    l_total_blocks              number;
    l_total_bytes               number;
    l_unused_blocks             number;
    l_unused_bytes              number;
    l_LastUsedExtFileId         number;
    l_LastUsedExtBlockId        number;
    l_last_used_block           number;
    l_sql                       long;
    l_conj                      varchar2(7) default ' where ';
    l_data                      show_space_table_type := show_space_table_type();
    l_owner varchar2(30);
    l_segment_name varchar2(30);
    l_segment_type varchar2(30);
    l_partition_name varchar2(30);

    procedure add_predicate( p_name in varchar2, p_value in varchar2 )
    as
    begin
        if ( instr( p_value, '%' ) > 0 )
        then
            l_sql := l_sql || l_conj || p_name || 
                            ' like ''' || upper(p_value) || '''';
            l_conj := ' and ';
        elsif ( p_value is not null )
        then
            l_sql := l_sql || l_conj || p_name || 
                            ' = ''' || upper(p_value) || '''';
            l_conj := ' and ';
        end if;
    end;
begin
    l_sql := 'select owner, segment_name, segment_type, partition_name
                from dba_segments ';

    add_predicate( 'segment_name', p_segname );
    add_predicate( 'owner', p_owner );
    add_predicate( 'segment_type', p_type );
    add_predicate( 'partition', p_partition );

    execute immediate 'alter session set cursor_sharing=force';
    open l_cursor for l_sql;
    execute immediate 'alter session set cursor_sharing=exact';

    loop
        fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name;
        exit when l_cursor%notfound;
        begin
        dbms_space.free_blocks
        ( segment_owner     => l_owner,
          segment_name      => l_segment_name,
          segment_type      => l_segment_type,
          partition_name    => l_partition_name,
          freelist_group_id => 0,
          free_blks         => l_free_blks );

        dbms_space.unused_space
        ( segment_owner     => l_owner,
          segment_name      => l_segment_name,
          segment_type      => l_segment_type,
          partition_name    => l_partition_name,
          total_blocks      => l_total_blocks,
          total_bytes       => l_total_bytes,
          unused_blocks     => l_unused_blocks,
          unused_bytes      => l_unused_bytes,
          LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
          LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
          LAST_USED_BLOCK => l_LAST_USED_BLOCK );

        l_data.extend;
        l_data(l_data.count) := 
               show_space_type( l_owner, l_segment_name, l_partition_name,
                  l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks,
                  l_lastUsedExtFileId, l_LastUsedExtBlockId, l_last_used_block );
        exception
            when others then null;
        end;
    end loop;
    close l_cursor;

    return l_data;
end;
/




Then we can:

ops$tkyte@ORA817DEV.US.ORACLE.COM> select SEGMENT_NAME, PARTITION_NAME SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
  2    from table( cast( show_space_for( 'HASHED',user,'%' ) as show_space_table_type ) )
  3  /

SEGMENT_NA SEGMENT_TYPE      FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS
---------- ----------------- ----------- ------------ -------------
HASHED     PART_2                      1           64            62
HASHED     PART_3                      1           64            62
HASHED     PART_4                      1           64            62
HASHED     PART_1                      1           64            62

ops$tkyte@ORA817DEV.US.ORACLE.COM> 

And in 9i, we'd change the function to be pipelined:

ops$tkyte@ORA9I.WORLD> create or replace
  2  function show_space_for
  3  ( p_segname   in varchar2,
  4    p_owner     in varchar2 default user,
  5    p_type      in varchar2 default 'TABLE',
  6    p_partition in varchar2 default NULL )
  7  return show_space_table_type
  8  authid CURRENT_USER
  9  <b>PIPELINED</b>
 10  as
 11      pragma autonomous_transaction;
 12      type rc is ref cursor;
 13      l_cursor rc;
 14  
 15      l_free_blks                 number;
 16      l_total_blocks              number;
 17      l_total_bytes               number;
 18      l_unused_blocks             number;
 19      l_unused_bytes              number;
 20      l_LastUsedExtFileId         number;
 21      l_LastUsedExtBlockId        number;
 22      l_last_used_block           number;
 23      l_sql                       long;
 24      l_conj                       varchar2(7) default ' where ';
 25      l_owner varchar2(30);
 26      l_segment_name varchar2(30);
 27      l_segment_type varchar2(30);
 28      l_partition_name varchar2(30);
 29  
 30      procedure add_predicate( p_name in varchar2, p_value in varchar2 )
 31      as
 32      begin
 33          if ( instr( p_value, '%' ) > 0 )
 34          then
 35              l_sql := l_sql || l_conj || p_name || ' like ''' || upper(p_value) || '''';
 36              l_conj := ' and ';
 37          elsif ( p_value is not null )
 38          then
 39              l_sql := l_sql || l_conj || p_name || ' = ''' || upper(p_value) || '''';
 40              l_conj := ' and ';
 41          end if;
 42      end;
 43  begin
 44      l_sql := 'select owner, segment_name, segment_type, partition_name
 45                  from dba_segments ';
 46  
 47      add_predicate( 'segment_name', p_segname );
 48      add_predicate( 'owner', p_owner );
 49      add_predicate( 'segment_type', p_type );
 50      add_predicate( 'partition', p_partition );
 51  
 52      execute immediate 'alter session set cursor_sharing=force';
 53      open l_cursor for l_sql;
 54      execute immediate 'alter session set cursor_sharing=exact';
 55  
 56      loop
 57          fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name;
 58                  dbms_output.put_line( l_segment_name || ',' || l_segment_type );
 59          exit when l_cursor%notfound;
 60          begin
 61          dbms_space.free_blocks
 62          ( segment_owner     => l_owner,
 63              segment_name      => l_segment_name,
 64              segment_type      => l_segment_type,
 65              partition_name    => l_partition_name,
 66              freelist_group_id => 0,
 67              free_blks         => l_free_blks );
 68  
 69          dbms_space.unused_space
 70          ( segment_owner     => l_owner,
 71              segment_name      => l_segment_name,
 72              segment_type      => l_segment_type,
 73              partition_name    => l_partition_name,
 74              total_blocks      => l_total_blocks,
 75              total_bytes       => l_total_bytes,
 76              unused_blocks     => l_unused_blocks,
 77              unused_bytes      => l_unused_bytes,
 78              LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 79              LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 80              LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 81  <b>
 82          pipe row ( show_space_type( l_owner, l_segment_name, l_partition_name,
 83                     l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks,
 84                      l_lastUsedExtFileId, l_LastUsedExtBlockId, l_last_used_block ) );</b>
 85          exception
 86              when others then null;
 87          end;
 88      end loop;
 89      close l_cursor;
 90  <b>
 91      return;</b>
 92  end;
 93  /

Function created.

ops$tkyte@ORA9I.WORLD> set arraysize 1

ops$tkyte@ORA9I.WORLD> select SEGMENT_NAME, SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
  2    from table( show_space_for( '%',user,'%' ) )
  3  /

SEGMENT_NAME    SEGMENT_TYPE      FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS
--------------- ----------------- ----------- ------------ -------------
KEEP_SCN        TABLE                       1           64            62
EMPLOYEES       TABLE                       0           64            63
STINKY          TABLE                       0           64            63
OBJECT_TABLE    TABLE                       1           64            62
RUN_STATS       TABLE                       2           64            53
EMP             TABLE                       0           64            62
PROJ            TABLE                       0           64            62
X               TABLE                       1           64            62
WORDS           TABLE                       0           64            63
DOCS            TABLE                       0           64            63
KEYWORDS        TABLE                       0           64            63
DEPT            TABLE                       2           64            61
C               TABLE                       1           64            62
DSINVLINES      TABLE                       1           64            62
NUM_STR         TABLE                       1           64            23
T               TABLE                       4           64            28
T1              TABLE                       0           64            63
T2              TABLE                       0           64            63
BOM             TABLE                       1           64            62
PARTS           TABLE                       1           64            62
SYS_C001371     INDEX                       0           64            62
SYS_C001372     INDEX                       0           64            62
SYS_C001574     INDEX                       0           64            62
SYS_C001694     INDEX                       0           64            62
SYS_C001695     INDEX                       0           64            62
BOM_PK          INDEX                       0           64            62
PARTS_PK        INDEX                       0           64            62

27 rows selected. 

I never said the first block would be used by the OS. In any case, you are asking for 8192k (8 MEG) of space, not 8k.

what is the CREATE TABLESPACE command you used (and why are we losing sleep over 1 block)

Yes, the first block of a file in a DICTIONARY managed tablespace is used by the system (us, Oracle)

Just like the first 64k of a LOCALLY managed tablespaces.

Additionally, on my system we allocated 32k for the datafiles -- not 24k.  The following shows what you ask for -- what you get and how much is usable by you (i would highly recomment LMT's btw -- avoid DMT's):




ops$tkyte@ORA817DEV.US.ORACLE.COM> Create tablespace TS1 datafile '/tmp/ts1.dbf' size 24k reuse;

Tablespace created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> host ls -l /tmp/ts1.dbf
-rw-r-----   1 ora817      <b>32768</b> Dec 26 13:33 /tmp/ts1.dbf

ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
                                                                                              MaxPoss    Max
Tablespace Name   KBytes       Used      Free   Used Largest   Kbytes   Used
---------------- ------- ---------- --------- ------ ------- -------- ------
.....
TS1                   24          8        16   33.3      16        0     .0
                 ------- ---------- ---------
sum            3,818,848  1,605,144 2,213,704

13 rows selected.

<b>see, 24k in size -- 8 is used, 16 free...</b>

ops$tkyte@ORA817DEV.US.ORACLE.COM> alter tablespace ts1 add datafile '/tmp/ts2.dbf' size 24k reuse;

Tablespace altered.

ops$tkyte@ORA817DEV.US.ORACLE.COM> host ls -l /tmp/ts2.dbf
-rw-r-----   1 ora817      <b>32768</b> Dec 26 13:33 /tmp/ts2.dbf

ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
MaxPoss    Max
Tablespace Name   KBytes      Used      Free   Used  Largest   Kbytes   Used
---------------- ------- --------- --------- ------ -------- -------- ------
TS1                   48        16        32   33.3       16        0     .0
                 ------- --------- ---------
sum            3,818,872 1,605,152 2,213,720

13 rows selected.

<b>now, 48k (24*2), 16k used (1 block / file )

ops$tkyte@ORA817DEV.US.ORACLE.COM>  

It is because I created the table in a dictionary managed tablespace and we allocate things to within +- 5 blocks (to avoid fragmenting the tablespace freespace totally)

Read
</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c02block.htm#2846 <code>

it covers the algorithm.

at least one, it might get more later. depends on block size and number of extents.

I just assumed one freelist. if you have more, this routine is not "sophisticated enough"



there is a dbms_space.space_usage routine for ASSM

go for it. I adjust it as I need.

it only calls free blocks IF the segment space management is not AUTO and the object exists.

just add a "and rownum = 1" to the query. Lose the distinct, it is not relevant.

having no clue what t1 is, i have no comment.

sorry, maybe if you show us the entire thing -- are you using the script that does auto segment space management "show space", etc...

have you read about the dbms_* packages I use (they are documented).  It is pretty straightforward stuff.  

works for me in 9i


ops$tkyte@ORA920> l
  1  create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner   in varchar2 default user,
  4    p_type    in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  authid current_user
  7  as
  8      l_free_blks                 number;
  9
 10      l_total_blocks              number;
 11      l_total_bytes               number;
 12      l_unused_blocks             number;
 13      l_unused_bytes              number;
 14      l_LastUsedExtFileId         number;
 15      l_LastUsedExtBlockId        number;
 16      l_LAST_USED_BLOCK           number;
 17      procedure p( p_label in varchar2, p_num in number )
 18      is
 19      begin
 20          dbms_output.put_line( rpad(p_label,40,'.') ||
 21                                p_num );
 22      end;
 23  begin
 24      for x in ( select tablespace_name
 25                   from dba_tablespaces
 26                  where tablespace_name = ( select tablespace_name
 27                                              from dba_segments
 28                                             where segment_type = p_type
 29                                               and segment_name = p_segname
 30                                                                                      and owner = p_owner
 31                                               and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
 32               )
 33      loop
 34      dbms_space.free_blocks
 35      ( segment_owner     => p_owner,
 36        segment_name      => p_segname,
 37        segment_type      => p_type,
 38       partition_name    => p_partition,
 39        freelist_group_id => 0,
 40        free_blks         => l_free_blks );
 41      dbms_output.put_line( 'Old fashioned' );
 42      end loop;
 43
 44      dbms_space.unused_space
 45      ( segment_owner     => p_owner,
 46        segment_name      => p_segname,
 47        segment_type      => p_type,
 48       partition_name    => p_partition,
 49        total_blocks      => l_total_blocks,
 50        total_bytes       => l_total_bytes,
 51        unused_blocks     => l_unused_blocks,
 52        unused_bytes      => l_unused_bytes,
 53        LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 54        LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 55        LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 56
 57      p( 'Free Blocks', l_free_blks );
 58      p( 'Total Blocks', l_total_blocks );
 59      p( 'Total Bytes', l_total_bytes );
 60      p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
 61      p( 'Unused Blocks', l_unused_blocks );
 62      p( 'Unused Bytes', l_unused_bytes );
 63      p( 'Last Used Ext FileId', l_LastUsedExtFileId );
 64      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
 65      p( 'Last Used Block', l_LAST_USED_BLOCK );
 66* end;
ops$tkyte@ORA920> drop table t;

Table dropped.

ops$tkyte@ORA920> create table t ( x int );

Table created.

ops$tkyte@ORA920> exec show_space('T')
Free Blocks.............................
Total Blocks............................8
Total Bytes.............................65536
Total MBytes............................0
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................9
Last Used Ext BlockId...................48
Last Used Block.........................3

PL/SQL procedure successfully completed.

 

1) both of those tables are so small, If I had a "batch" that took 9 minutes to process 20meg of data, I'd be concerned about that in itself. That is probably 8 minutes and 30 seconds longer then it should be.

Use TKPROF and SQL_TRACE to find your low hanging fruit.

2) you have a very very small table, I think you are looking in the wrong place here. You have most likely a bad algorithm. You need to diagnose where the problem is, then fix it. Not "fix something" and see if it was the right thing.

there could be -- but in this case, the numbers are far too small. I thought it was clear that I do not think that the small number of blocks you have on freelists here would have any material affect on anything....

No, you cannot attribute anything to anything here. There is insufficient data to say anything reasonable.

You don't say which way the performance change was, what type of operations you do on this table, nothing. Again, totally insufficient data to say anything about anything (except hypothesize, which I don't like doing too much). Now, if you had some metrics (tkprofs!!! statspacks!!!) - hard numerical, incontravertible data points that would provide information as to the number of consistent gets, query plans, inputs, etc over time -- then, well, then we'd be cooking with gas...

(but, the performance of queries is unaffected by the number of blocks on the freelist -- I could set pctfree/pctused so that every block is on the freelist. I could set the pctfree/pctused so that NO block is on the freelist. I could have BOTH of these tables -- one with every block on and other with every block off -- organized in such a way that block for block they are in effect "the same". They would perform identically. It is nothing to do with how many blocks are on the freelist (although you may be able to use that as an indicator if you understand how pctfree/pctused are set and how they are used). It has everything to do with how many blocks Oracle must process in order to answer your queries.



You need a TKPROF. You need a TKPROF of this "batch" on dev, of this "batch" on prod. You need to compare them. That'll get you 90% of your way along in your root cause analysis.

create or replace procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )
-- this procedure uses authid current user so it can query DBA_*
-- views using privileges from a ROLE and so it can be installed
-- once per database, instead of once per user that wanted to use it
authid current_user
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
l_segment_space_mgmt varchar2(255);
l_unformatted_blocks number;
l_unformatted_bytes number;
l_fs1_blocks number; l_fs1_bytes number;
l_fs2_blocks number; l_fs2_bytes number;
l_fs3_blocks number; l_fs3_bytes number;
l_fs4_blocks number; l_fs4_bytes number;
l_full_blocks number; l_full_bytes number;

-- inline procedure to print out numbers nicely formatted
-- with a simple label
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
to_char(p_num,'999,999,999,999') );
end;
begin
-- this query is executed dynamically in order to allow this procedure
-- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
-- via a role as is customary.
-- NOTE: at runtime, the invoker MUST have access to these two
-- views!
-- this query determines if the object is a ASSM object or not
begin
execute immediate
'select ts.segment_space_management
from dba_segments seg, dba_tablespaces ts
where seg.segment_name = :p_segname
and (:p_partition is null or
seg.partition_name = :p_partition)
and seg.owner = :p_owner
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner;
exception
when too_many_rows then
dbms_output.put_line
( 'This must be a partitioned table, use p_partition => ');
return;
end;


-- if the object is in an ASSM tablespace, we must use this API
-- call to get space information, else we use the FREE_BLOCKS
-- API for the user managed segments
if l_segment_space_mgmt = 'AUTO'
then
dbms_space.space_usage
( p_owner, p_segname, p_type, l_unformatted_blocks,
l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);

p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );
else
dbms_space.free_blocks(
segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks);

p( 'Free Blocks', l_free_blks );
end if;

-- and then the unused space API call to get the rest of the
-- information
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );

p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/


try that one, you must be in 9i using ASSM with the older version from 8i -- assm does not have freelists

thanks -- i fixed that up

instead of me querying the dba_views, you can pass in that information (what type of tablespace is it in)

use the "8i" like version, just parameterize it to call the proper API

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.

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

correct, the HWM would not even "exist" really.

do you have a dba you can work with?


you control the size of temp, that is entirely up to you. if you don't like temp being so large, drop it and recreate it.


search this site for maxshrink, it'll tell you if you can shrink your users01.dbf file.

it is normal for a database to consume space. you have indexes, you have free space in there. 3gig is tiny.

1) because here I have a simple report, rather then blow up part way thru on an object that cannot be "free space reported" on i skip it.

2)

l_sql := 'select owner, segment_name, segment_type, partition_name
from dba_segments ';

add_predicate( 'segment_name', p_segname );
add_predicate( 'owner', p_owner );
add_predicate( 'segment_type', p_type );
add_predicate( 'partition', p_partition );

execute immediate 'alter session set cursor_sharing=force';
open l_cursor for l_sql;
execute immediate 'alter session set cursor_sharing=exact';

I used dynamic sql -- so the CREATOR of the procedure need not have DBA_* access, just the INVOKER of the procedure. and they can use the privs from a role since roles can be in place during the execution of an invokers rights routine.

3) because this was written to be a DBA_ utility? DBA_ views have no filters on them.

4) more efficient, search for pipelined for many examples

5) it isn't, don't know what I was thinking leaving it in.

dba and programmer both is going to be a problem.  this is straight forward dba stuff really.  

to downsize your temp, create new:

ops$tkyte@ORA9IR2> create temporary tablespace temp tempfile size 100m;
Tablespace created.

and create new
 
ops$tkyte@ORA9IR2> drop tablespace temp2;
Tablespace dropped.
 

the details on maxshrink are as follows:

search this site for maxshrink, read the first couple of pages you find.  


perhaps you need "more disk".   

High Water Mark -- the biggest the table ever was, HWM's are pushed "up" if you fill up a table with 1,000 blocks of data and then delete all of the data -- there will still be 1,000 blocks of "data" there that would be full scanned.

you'll need to refer to your sql reference, I cannot (will not) be your dba.

you are using 817, you didn't have Oracle Managed Files way back then. You need to name your tempfile. The sql reference (available on otn.oracle.com) will give you the syntax.

create temporary tablespace temp

TEMPFILE '.......' .....

^^^^^^^^ tempfile, not datafile. documentation is very useful for this stuff

</code> http://docs.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem5e.htm#2063472 <code>

(hint, hit the shiftlock key...)

ops$tkyte@ORA9IR2> create user a identified by a default tablespace manual quota unlimited on manual;
 
User created.
 
ops$tkyte@ORA9IR2> create user b identified by b;
 
User created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> grant create session, create cluster to a;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> grant create session to b;
 
Grant succeeded.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> @connect a/a
ops$tkyte@ORA9IR2> set termout off
a@ORA9IR2> set termout on
a@ORA9IR2> create cluster test_cluster (x number) size 1024;
 
Cluster created.
 
a@ORA9IR2>
a@ORA9IR2> @connect b/b
a@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> var x number;
b@ORA9IR2> exec dbms_space.free_blocks ('A','TEST_CLUSTER','CLUSTER',0,:x)
BEGIN dbms_space.free_blocks ('A','TEST_CLUSTER','CLUSTER',0,:x); END;
 
*
ERROR at line 1:
ORA-00943: cluster does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at line 1
 
 
b@ORA9IR2>
b@ORA9IR2> @connect /
b@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> set termout on<b>
ops$tkyte@ORA9IR2> grant analyze any to b;</b>
 
Grant succeeded.
 
ops$tkyte@ORA9IR2> @connect b/b
ops$tkyte@ORA9IR2> set termout off
b@ORA9IR2> set termout on
b@ORA9IR2> exec dbms_space.free_blocks ('A','TEST_CLUSTER','CLUSTER',0,:x)
 
PL/SQL procedure successfully completed.
 
 

this is a function of your O7_DICTIONARY_ACCESSIBILITY setting and is there for security - to wall off SYS from everything else.

You'll either need to run this as SYSDBA to analyze SYS (preferred) or set O7_DICTIONARY_ACCESSIBILITY=true (not recommended).

p( 'Unformatted Blocks ', l_unformatted_blocks );
p( 'FS1 Blocks (0-25) ', l_fs1_blocks );
p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
p( 'FS4 Blocks (75-100)', l_fs4_blocks );
p( 'Full Blocks ', l_full_blocks );


FS1 means 0-25% free space within a block
FS2 means 25-50% free space within a block
FS3 means 50-75% free space within a block
FS4 means 75-100% free space within a block

unformatted blocks are blocks that are available for immediate use (below the true high water mark) but haven't yet had any data. when the table says "i'm full", we pull a bunch of blocks down into the table from above the HWM and they would all be unformatted until you use them.

there are the blocks used by Oracle to manage the blocks you are using.

</code> http://docs.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_space.htm#1001674 <code>

see "requirements"

the problems are not with privs on dbms_space, but on the object itself

Please don't use SYS, SYSTEM or any other Oracle supplied account -- use your own accounts for everything.

1) I like autoallocate -- it is the "no brainer" option.

If you know exactly how big your object is going to be, you can use uniform and size it out perfectly.

Or, if you want to monitor growth PRECISELY and know about how big the object will be initially and how fast it'll grow, you can use uniform (eg: object will start at 500mb, we anticipate it adding 50meg/month. You might use 50meg uniform extents, it'll add about an extent per month and you'll be able to predict when it will next extend)

2) not exactly, it is the system saying as you get bigger, our extents will get bigger.

3) no, not really -- they pretty much fit inside of eachother -- there are very few sizes.

that should only happen if we call space_usage on an object we should not have -- but I've code in there to only call it when it is "ok"

can you give me all of the details to reproduce? -- the create tablespace command, the create <segment> command and the call to show_space you are using?

1) because only blocks that have space FREE on them are in free blocks. some of the blocks are FULL and not "free" anymore.

you are not counting blocks that are full

3) 3 blocks on the freelist means there are 3 blocks that may or may not have any data on them (in general -- if you fill up 3 blocks in a table and then delete the rows on them -- they will be on the freelist but they will effectively be "empty") and are able to accept more rows.


They are used blocks that have space for more inserts.

are you using DMT's or LMT's

any clues as to versions and such is always very much appreciated as well.

program provide sure does work

person running program:

A@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "OPM.SHOW_SPACE", line 44
ORA-06512: at line 1

====

needs access to the DBA_ views that are queried therein.



To answer this
<quote>
What I really want is a way to estimate how much disk space
the database will require for a customer's usage patterns,
to develop a formula/spreadsheet to give to our sales people.
The data is in two users' tables, so I want to know how much
space is being used by all these tables.
</quote>

I would use nothing more than dba_segments


select owner, segment_name, segment_type, round(bytes/1024/1024,1) meg
from dba_segments
where owner in ( ...... );


<quote>
I don't care about the relatively constant amount of space
that Oracle requires for overhead. (Or is this not constant?)
</quote>

you need to see how much space the data consumes -- that is dba_segments.

did you check out the docs?

fileid/blockid -> extent
last used block is the last block in this extent that contains data.

but you do know that the "free blocks" i report are blocks on the freelist -- they are not empty -- they could be, or they could be 89% full.

Not sure what the goal here is but if it is sizing for a install or growth, you just want dba_segments.

but why isn't dba_segments sufficient for monitoring growth? it is what *counts* at the end of the day.


that is the space they *actually need* to run your software. That 10% of it is on freelist is not relevant, that they needa segment 100meg in size is.

<quote>
My guess is that Oracle's seeing how
fast the data is coming in and preallocating enough storage for the next hour or
ten which is way too much error for me.
</quote>

nope, not at all. we allocate storage in response to a request for "we need more space", we do not predictively allocate storage.

And we do so in very predicable increments if you are using locally managed tablespaces with uniform sizes. We allocate a new extent of exactly what you tell us to do (so, sounds like someone used a 1000k uniform size, or you are using old fashioned, not recommended dictionary managed tablespaces and the pctincrease plus next extent size grew and grew and grew -- in a predicable fashion though)



do you have access to my book "Expert one on one Oracle".

Please don't be insulted by these next questions, I've no idea what your level of "DBA'nish" is:

o do you know what freelists are?
o how extent managment in oracle works?
o what freelist groups are?
o do you specify storage options on your segments?
o have you read the concepts guide which explains many of these structures

these were not/are not meant to be insulting -- I really have no idea what level you are at.


Typically -- people monitor their growth (Oracle Enterprise Manager does this for example) over time and you extropolate from there. This is something you do over *weeks* and *months* -- not in an afternoon.

And you'll end up with guidelines as each of your customers will have a different use pattern -- slightly different but different enough to make a difference.

The concepts manual is free and immediate -- otn.oracle.com, if you spent a day with that, you will not be sorry.


MSSM (manual segment space management) uses freelists and unless you changed it, freelists will be "1".


lobs are managed a little different. there is pctversion, they don't use rbs to version changes, they are managed radically differntly inside the segment

BUT

the segment extends using the same exact rules as anything else. Every segment is made of extents. Extents are allocated as a set of logically contigous blocks. If a segment needs more space, but cannot find the space within itself -- it requests a next extent (which using UNIFORM extents, you control the size of totally).

num_freelist_blocks would be double counting. a block on the freelist is already "a block"

blocks is the number of blocks available to you (we took one for ourselves). blocks+our_overhead = blocks in dba_segments.


blocks-empty_blocks would be the number of blocks you have data on

blocks-empty_blocks is greater than or equal to num_freelist_blocks

you need to rebuild indexes.

and bear in mind that in 10g, you can "shrink" tables instead of moving them (assuming they are in ASSM - automatic segment space management - tablespaces)

original example had this?

ops$tkyte@ORA9I.WORLD> select SEGMENT_NAME, SEGMENT_TYPE, 
                              FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
  2    from table( show_space_for( '%',user,'%' ) )
  3  / 

ops$tkyte@ORA817DEV.US.ORACLE.COM> select SEGMENT_NAME, PARTITION_NAME 
SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
  2    from table( cast( show_space_for( 'HASHED',user,'%' ) as 
show_space_table_type ) )
  3  /

was there as well :) 

put some dbms_outputs in there and see whats going on -- what query isn't returning stuff that "should" and we can work from there.

exec null;

after you run it.

ah, subpartitions in ASSM, I remember something vaguely about that - yes.


and now, and NOW I am asking myself, what was I thinking with "when others". I see I said a while ago

1) because here I have a simple report, rather then blow up part way thru on an
object that cannot be "free space reported" on i skip it.

but now you know why I'm so against them, I'd definitely change my mind on that in hind sight.

did you use authid current_user?

ctl-f for authid

the reason I pointed to authid current_user was in response to someone asking

Hey, i installed as user1 and ran as user2 and .........


authid current_user lets you install as user1 and run as user2

well, first it is very confusing because you have many segments named T apparently:

SQL> select extent_id,bytes/1024,blocks from dba_extents where segment_name='T';

 EXTENT_ID BYTES/1024     BLOCKS
---------- ---------- ----------
         0         64          8
         0         64          8
         0         64          8
         0         64          8
                      ----------
sum                           32


extent_id = 0, first extent - you have 4 "T"'s in your database.  Each one has 64k of space.



Here:


SQL> select extent_id,bytes/1024,blocks from dba_extents where segment_name='T';

 EXTENT_ID BYTES/1024     BLOCKS
---------- ---------- ----------
         0         64          8
         0         64          8
         1         64          8
         2         64          8
         3         64          8
         0         64          8
         0         64          8
                      ----------
sum                           56


You have 4 again, 3 of them have 64k, one of them has 256k.



Now, you are using system - which is using "AUTOALLOCATE", meaning - the size of the extents <b>is controlled and managed by Oracle</b>.

When using storage clauses - Oracle uses the information upon create to figure out "how much space WOULD have been allocated if this were a dictionary managed tablespace".  It will then allocate <b>AT LEAST</b> that much space, but using the algorithms for LMT's.

So:

storage (initial 40K next 40K )

that said to allocate "AT LEAST" 40k of space, and it did - with one 64k extent which is the smallest I've see system allocated extents use.  


storage (initial 40K next 40K minextents 5)

says to allocate 200k of space (40k * 5).  WHICH IT DID, using 4 of the smallest extents it is known to use.


storage (initial 40K minextents 5)


is a bit more vague, I don't know what the default NEXT and PCTINCREASES where - so it is hard to say HOW MUCH space would have been allocated had this been a DMT.. 

can you see bug #2948717 in metalink?

the fix was - dba_segments could be painfully slow. so they changed the way it worked. but only for "new" segments. you would need to run that "fix" for those (or recreate them, or use the _old view)

not really - not relevant really either.

doesn't "make sense", you either want to know how much space an object occupies or not.

what is the goal here.

</code> http://asktom.oracle.com/Misc/free.html <code>

I just use my script free.sql, you are "free" to use it and modify it as well.


I would NOT be looking at unused space inside of each segment - that would be

a) really long
b) not very "useful"

well, what about tables that

a) had 1000000000 rows
b) now have 1 row

tell them "about 80%" as a guess.

Unless you really want to go through every stinking table....


I would AT MOST have all tables freshly analyzed and look at the blocks used. You cannot even just look at freelist blocks - for freelist blocks are not entirely empty.

80/90% sounds good.

sum(avg_row_len * num_rows)

that would woefully UNDERESTIMATE BIG TIME the actual size needed.


Just calculate SUM(BLOCKS).

you can analyze and look at the average space on blocks.

no it does not.

</code> http://docs.oracle.com/docs/cd/B10501_01/server.920/a96536/ch2152.htm#1303953 <code>

blocks - number of USED blocks
empty blocks - number of never used blocks.

user_segments dba_segments, either of those

ops$tkyte@ORA9IR2> CREATE TABLE t
  2  (
  3    dt  date,
  4    x   int,
  5    y   varchar2(25)
  6  )
  7  PARTITION BY RANGE (dt)
  8  subpartition by hash(x) subpartitions 4
  9  (
 10    PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
 11    PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
 12    PARTITION junk VALUES LESS THAN (MAXVALUE)
 13  )
 14  /

Table created.

ops$tkyte@ORA9IR2> column segment_name format a10
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select segment_name, partition_name, segment_type, bytes
  2    from user_segments;

SEGMENT_NA PARTITION_NAME                 SEGMENT_TYPE            BYTES
---------- ------------------------------ ------------------ ----------
T          SYS_SUBP13                     TABLE SUBPARTITION      65536
T          SYS_SUBP14                     TABLE SUBPARTITION      65536
T          SYS_SUBP15                     TABLE SUBPARTITION      65536
T          SYS_SUBP16                     TABLE SUBPARTITION      65536
T          SYS_SUBP17                     TABLE SUBPARTITION      65536
T          SYS_SUBP18                     TABLE SUBPARTITION      65536
T          SYS_SUBP19                     TABLE SUBPARTITION      65536
T          SYS_SUBP20                     TABLE SUBPARTITION      65536
T          SYS_SUBP21                     TABLE SUBPARTITION      65536
T          SYS_SUBP22                     TABLE SUBPARTITION      65536
T          SYS_SUBP23                     TABLE SUBPARTITION      65536
T          SYS_SUBP24                     TABLE SUBPARTITION      65536

12 rows selected.


just query user segments and see what you see - for table subpartitions - i don't have your create table.

worked for me. 

assm or manual segement space managed?

number of extents?

database version down to 4 digits?

platform?

number of freelists? freelist groups?

let me guess - is this table almost empty? are MOST of the blocks on the freelist?

well, just gather statistics and you would be able to measure that easily. num rows and avg row length, add a bit for overhead, multiply and see what you see.

the free blocks are not free blocks - they are blocks on the freelist!


since you did a massive purge
you will likely be gather statistics
that should be all you need....


(10g has a job that will notify you of segments that would possibly benefit from a "compaction" which can be done online with alter table T shrink too)

that is why I said to add in some overhead.

We were talking about sizing a table. I said "load it up, multiply out the blocks actually allocated"

They said "nah, I'll load it up, analyze and take the avg row len * number of rows"

That won't work, you'd need that magic overhead. AND - big AND here - if you have the precise number of blocks it takes for a freshly loaded table - why the heck would you bother with row lengths and numbers of rows - you now KNOW the number of blocks it takes (best case)


1) so would blocks from user_tables
2)

a) and the space on the blocks on the freelists is what precisely?? Say pctused is 40. You have 100 blocks (8k blocks) on the freelist. Tell me, how much free space do you have?

b) pctfree tells you what would have been left during the initial insertion into the block. avg space (another analyze thingy) can tell you the AVERAGE space, but nothing is really going to tell you block by block.



I'll go back to "since you purged, you will likely be analyzing. use this information to guestimate what would happen if you rebuild (and then measure that after you are done)"

you must have bitmaps, else you would not get that.

you are saying you are getting an error raised from code that maintains bitmaps but have no bitmaps? Please contact support to work through this one.

could be - that's why "please contact support" rolled off the tongue so easily...

ops$tkyte@ORA9IR2> exec show_space( 'T' )
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               5
Unused Bytes............................          40,960
Last Used Ext FileId....................               9
Last Used Ext BlockId...................               8
Last Used Block.........................               3

PL/SQL procedure successfully completed.


the fsN blocks show "free space blocks, blocks with free space" 

because with the default 10% free and block overhead, there is less then 8k of space for rows on each block.

if you do a delete
AND the space free on the block becomes such that PCTUSED kicks in and tells us to put the block on the freelist - yes.

<quote src=Expert Oracle Database Architecture>

PCTFREE and PCTUSED

In general, the PCTFREE parameter tells Oracle how much space should be reserved on a block for future updates. By default, this is 10 percent. If there is a higher percentage of free space than the value specified in PCTFREE, then the block is considered to be “free.” PCTUSED tells Oracle the percentage of free space that needs to be present on a block that is not currently “free” in order for it to become free again. The default value is 40 percent.

As noted earlier, when used with a table (but not an IOT, as we’ll see), PCTFREE tells Oracle how much space should be reserved on a block for future updates. This means if we use an 8KB block size, as soon as the addition of a new row onto a block causes the free space on the block to drop below about 800 bytes, Oracle will use another block from the FREELIST instead of the existing block. This 10 percent of the data space on the block is set aside for updates to the rows on that block.

Note PCTFREE and PCTUSED are implemented differently for different table types. Some table types employ both, whereas others only use PCTFREE, and even then only when the object is created. IOTs use PCTFREE upon creation to set aside space in the table for future updates, but do not use PCTFREE to decide when to stop inserting rows into a given block, for example.

The exact effect of these two parameters varies depending on whether you are using ASSM or MSSM tablespaces. When you are using MSSM, these parameter settings control when the block will be put on and taken off the FREELIST. If you are using the default values for PCTFREE (10) and PCTUSED (40), then a block will remain on the FREELIST until it is 90 percent full (10 percent free space). Once it hits 90 percent, it will be taken off the FREELIST and remain off the FREELIST until the free space on the block exceeds 60 percent of the block.
When you are using ASSM, PCTFREE still limits if a new row may be inserted into a block, but it does not control whether a block is on a FREELIST or not, as ASSM does not use FREELISTs at all. In ASSM, PCTUSED is simply ignored.
There are three settings for PCTFREE: too high, too low, and just about right. If you set PCTFREE for blocks too high, you will waste space. If you set PCTFREE to 50 percent and you never update the data, you have just wasted 50 percent of every block. On another table, however, 50 percent may be very reasonable. If the rows start out small and tend to double in size, setting PCTFREE too small will cause row migration as you update the rows.


Row Migration

So, that poses the question, what exactly is row migration? Row migration is when a row is forced to leave the block it was created on, because it grew too large to fit on that block with the rest of the rows. I’ll illustrate a row migration in this section. We start with a block that looks like Figure 10-3.

Insert 5300f1003scrap.gif CRX
Figure 10-3. Data block before update

Approximately one-seventh of the block is free space. However, we would like to more than double the amount of space used by row 4 via an UPDATE (it currently consumes one-seventh of the block). In this case, even if Oracle coalesced the space on the block as shown in Figure 10-4, there is still insufficient room double the size of row 4, because the size of the free space is less than the current size of row 4.

Insert 5300f1004scrap.gif CRX
Figure 10-4. Data block as it would appear after coalescing free space

If the row fit into the coalesced space, then this would have happened. This time, however, Oracle will not perform this coalescing and the block will remain as it is. Since row 4 would have to span more than one block if it stayed on this block, Oracle will move, or migrate, the row. However, Oracle cannot just move the row; it must leave behind a “forwarding address.” There may be indexes that physically point to this address for row 4. A simple update will not modify the indexes as well. (Note that there is a special case with partitioned tables that a rowid, the address of a row, will change. We will look at this case in Chapter 13.) Therefore, when Oracle migrates the row, it will leave behind a pointer to where the row really is. After the update, the blocks might look as shown in Figure 10-5.

Insert 5300f1005scrap.gif CRX
Figure 10-5. Migrated Row Depiction.

So, a migrated row is a row that had to move from the block it was inserted into, onto some other block. Why is this an issue? Your application will never know; the SQL you use is no different. It only matters for performance reasons. If you go to read this row via an index, the index will point to the original block. That block will point to the new block. Instead of doing the two or so I/Os to read the index plus one I/O to read the table, you’ll need to do yet one more I/O to get to the actual row data. In isolation, this is no big deal—you won’t even notice it. However, when you have a sizable percentage of your rows in this state, with lots of users accessing them, you’ll begin to notice this side effect. Access to this data will start to slow down (additional I/Os and the associated latching that goes with the I/O add to the access time), your buffer cache efficiency goes down (you need to buffer two blocks instead of just the one you would if the rows were not migrated), and your table grows in size and complexity. For these reasons, you do not want migrated rows.

It is interesting to note what Oracle will do if the row that was migrated from the block on the left to the block on the right, in Figure 10-5, has to migrate again at some future point in time. This would be due to other rows being added to the block it was migrated to and then updating this row to make it even larger. Oracle will actually migrate the row back to the original block and, if there is sufficient space, leave it there (the row might become “unmigrated”). If there isn’t sufficient space, Oracle will migrate the row to another block altogether and change the forwarding address on the original block. As such, row migrations will always involve one level of indirection.

So, now we are back to PCTFREE and what it is used for: it is the setting that will help you to minimize row chaining when set properly.

Setting PCTFREE and PCTUSED Values

Setting PCTFREE and PCTUSED is an important—and greatly overlooked—topic. In summary, PCTUSED and PCTFREE are both crucial when using MSSM; with ASSM, only PCTFREE is. On one hand, you need to use them to avoid too many rows from migrating. On the other hand, you use them to avoid wasting too much space. You need to look at your objects and describe how they will be used, and then you can come up with a logical plan for setting these values. Rules of thumb may very well fail you on these settings; they really need to be set based on usage. You might consider the following (keeping in mind that “high” and “low” are relative terms, and that when using ASSM only PCTFREE applies):

* High PCTFREE, low PCTUSED: This setting is for when you insert lots of data that will be updated and the updates will increase the size of the rows frequently. This setting reserves a lot of space on the block after inserts (high PCTFREE) and makes it so that the block must almost be empty before getting back onto the FREELIST (low PCTUSED).

* Low PCTFREE, high PCTUSED: This setting is for if you tend to only ever INSERT or DELETE from the table, or if you do UPDATE, the UPDATE tends to shrink the row in size.
</quote>

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:406873025890 <code>

create tablespace my_tablespace extent management local;

and then just


create table t ( x int ) tablespace my_tablespace;


I would not necessarily compute inital and next at all. System allocated extents work very nicely.

You always have the option.

I would not accept anything else.

So, I would not have the problem of trying to figure it out.

I consider that a DBA book - as well as all of the books I've written, Expert Oracle Database Architecture being the most current.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2588723819082#205698400346825799

the space is released..... back to the table. a delete does that, and that alone.

show_space did exactly what you want "precise space usage of this table", it did exactly that!!!!!


You would have to reorganize this table to "reclaim space" - alter table t shrink space compact + alter table t shrink space in 10gR2, dbms_redefinition or other methods before that.

1) dbms_space shows you the number of blocks that are now empty, 75% full, 50% full and so on. dba_segments won't change, the blocks belong to the table - after the delete they belong to the table. They are just "less full" after the delete.

2) correct, that is precisely the way it works.

Your delete - it could have deleted every other row in general, it doesn't make sense to give back a random block here and there, once assigned to a segment, a block belongs to that segment until you reorganize that segment.

3) you can use the shrink (new in 10g) command to reclaim the space, to return the space to the "free" status, to be used in other segments.

4) dbms_redefinition, alter table T move, export+import, create table as select/drop old table

or best yet: use partitioning or some other technique to make purging be efficient - so you do not have to use delete, you can use truncate or drop (of a partition). that is, DESIGN for this.

could be that you have a locally managed tablespace with uniform extent sizes, only full extents would be released. and 64 doesn't fit that description in your case.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#sthref7172


dbms_space:

Security Model

This package runs with SYS privileges. The execution privilege is granted to PUBLIC. Subprograms in this package run under the caller security. The user must have ANALYZE privilege on the object.

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#sthref7239

details fs1 and so on.


when you look at unused blocks - they are blocks that HAVE NEVER BEEN USED. they are above the "high water mark".

Once a block is USED, it'll remain under the high water mark - even if you make it "empty"

A block that is used, will stay used until you "reorganize" (NOT that I am recommending a reorganization - far far far from it)


the fs1... blocks were not displayed because the segment you were looking at was not in a ASSM tablespace

DO NOT RUN STUFF AS SYS, stop it right now.

do not do that, just stop.


Then, read:

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_space.htm#sthref7175

user running procedure must be able to analyze the object - via a direct grant of course, not a role

http://asktom.oracle.com/tkyte/Misc/RolesAndProcedures.html

so, create YOUR OWN ACCOUNTS to use, and give them appropriate privileges.

no, it would not (using a CONVENTIONAL PATH INSERT)

it was used. high water marks for segments only advance.


in general, if you insert 1,000 rows - they will be intermingled with other peoples work as well - we are not a single user system.

ops$tkyte%ORA10GR2> create table t as select * from all_objects where 1=0;

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               5
Unused Bytes............................          40,960
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             713
Last Used Block.........................               3

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> insert into t select * from all_objects;

50298 rows created.

ops$tkyte%ORA10GR2> rollback;

Rollback complete.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................              32
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................             716
Full Blocks        .....................               0
Total Blocks............................             768
Total Bytes.............................       6,291,456
Total MBytes............................               6
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           1,673
Last Used Block.........................             128

PL/SQL procedure successfully completed.

<b>768 blocks allocated, 716 under the "high water" mark - they are fs4 - 75-100% EMPTY (they are 100% empty) but they are allocated and under the high water mark</b>


ops$tkyte%ORA10GR2> truncate table t;

Table truncated.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               5
Unused Bytes............................          40,960
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             713
Last Used Block.........................               3

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> insert /*+ APPEND */ into t select * from all_objects;

50298 rows created.

ops$tkyte%ORA10GR2> rollback;

Rollback complete.

ops$tkyte%ORA10GR2> exec show_space( 'T' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               0
Total Blocks............................             768
Total Bytes.............................       6,291,456
Total MBytes............................               6
Unused Blocks...........................             765
Unused Bytes............................       6,266,880
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             713
Last Used Block.........................               3

PL/SQL procedure successfully completed.
<b>768 blocks allocated - but they are all pretty much under the high water mark, they are "unused" - due to the DIRECT PATH operation (in which case our rows cannot be intermingled with others work since we are writing above the high water mark with a direct path load</b>

ops$tkyte%ORA10GR2>

search site for show_space


I used ASSM in the above example myself.

unused blocks are exactly the blocks above the high water mark. continue reading that paragraph:

... Unused Blocks are
exactly the block above the high water mark. ...

after you reorg, you still have extents and extents are not always 100% full - so they may contain unused blocks above the high water mark

1022, looks like a temporary file, subtract from that number your max datafiles setting and you'll get the temporary file number that was involved.

You ran out of temp space - see the no space left on device? temp files are created as sparse files - as the OS fills them in, it can run out of space for them.

<quote src=expert oracle database architecture>

One of the nuances of true temp files is that if the OS permits it, the temporary files will be created sparse¿that is, they will not actually consume disk storage until they need to. You can see that easily in this example (on Red Hat Linux in this case):

ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  41999488  29008368  60% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm
 
ops$tkyte@ORA10G> create temporary tablespace temp_huge
  2  tempfile '/d01/temp/temp_huge' size 2048m
  3  /
 
Tablespace created.
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  41999616  29008240  60% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm

Note df is a Unix command to show ¿disk free.¿ This command showed that I have 29,008,368KB free in the file system containing /d01/temp before I added a 2GB temp file to the database. After I added that file, I had 29,008,240KB free in the file system.
Apparently it took only 128KB of storage to hold that file. But if we ls it

ops$tkyte@ORA10G> !ls -l /d01/temp/temp_huge
-rw-rw----    1 ora10g   ora10g   2147491840 Jan  2 16:34 /d01/temp/temp_huge

it appears to be a normal 2GB file, but it is in fact only consuming some 128KB of storage. The reason I point this out is because we would be able to actually create hundreds of these 2GB temporary files, even though we have roughly 29GB of disk space free. Sounds great¿free storage for all! The problem is as we start to use these temp files and they start expanding out, we would rapidly hit errors stating ¿no more space.¿ Since the space is allocated or physically assigned to the file as needed by the OS, we stand a definite chance of running out of room (especially if after we create the temp files someone else fills up the file system with other stuff).
How to solve this differs from OS to OS. On Linux, some of the options are to use dd to fill the file with data, causing the OS to physically assign disk storage to the file, or use cp to create a nonsparse file, for example:

ops$tkyte@ORA10G> !cp --sparse=never /d01/temp/temp_huge /d01/temp/temp_huge2
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  44099336  26908520  63% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm
 
ops$tkyte@ORA10G> drop tablespace temp_huge;
 
Tablespace dropped.
 
ops$tkyte@ORA10G> create temporary tablespace temp_huge
  2  tempfile '/d01/temp/temp_huge2' reuse;
 
Tablespace created.
 
ops$tkyte@ORA10G> !df
Filesystem           1K-blocks      Used Available Use% Mounted on
/dev/hda2             74807888  44099396  26908460  63% /
/dev/hda1               102454     14931     82233  16% /boot
none                   1030804         0   1030804   0% /dev/shm

After copying the sparse 2GB file to /d01/temp/temp_huge2 and creating the temporary tablespace using that temp file with the REUSE option, we are assured that temp file has allocated all of its file system space and our database actually has 2GB of temporary space to work with.

</quote>

ops$tkyte%ORA10GR2> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          100

that does nothing to reorganize an object.

query user_indexes, it has analogous information for indexes ...

or, if you want more detail, you can use the dbms_space package, i use it in my "show_space" (just search for that, already posted many times)

ops$tkyte%ORA10GR2> create table t as select object_id from all_objects;

Table created.

ops$tkyte%ORA10GR2> create index t_idx on t(object_id);

Index created.

ops$tkyte%ORA10GR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             110
Total Blocks............................             128
Total Bytes.............................       1,048,576
Total MBytes............................               1
Unused Blocks...........................               7
Unused Bytes............................          57,344
Last Used Ext FileId....................               4
Last Used Ext BlockId...................           6,241
Last Used Block.........................               1

PL/SQL procedure successfully completed.

did you see the entire answer I gave above?


...
Full Blocks ..................... 110
Total Blocks............................ 128
Total Bytes............................. 1,048,576
Total MBytes............................ 1
Unused Blocks........................... 7
Unused Bytes............................ 57,344
.....

that is what I sort of tried to be telling you?

not 100% sure what you are saying - but it seems you are asking "if we drop segments - but there is a segment way way out there at the end of the file (preventing us from shrinking the file) will MOVING that segment that is way out there allow us to lower the HWM with a resize of the datafiles"

the answer is "maybe", you'll be able to lower it for sure (because if you take the segment that occupies the last extent in a file and move it, it can only use lower free extents in that file)

best bet would be to move those objects to ANOTHER tablespace, then you know the maximum space will be free.

"fails to work"

ok, here you go, when you answer this, I'll answer your question:

"my car won't start, why not"


now we are even :) I've given you the same level of detail you have given me - no, wait, I've given you a lot more since you actually have the source code and could *debug* it... so, you have given me less.

"as stated on the tin"???


How about you be a little more explicit as to what "fails to work" means precisely. Most likely, this is a privilege issue - there is more than one version of the code here - one that is definers rights and one that is invokers rights - you should be using the invokers rights (authid current_user) and that user executing the procedure would need access to the underlying views that the procedure queries of course.

No, it does not mean that it is not owned by a user.

You in fact have told us - it is owned by OPS$SABXFER


the user_ views shows objects OWNED by the currently logged in user. Unless you were logged in as OPS$SABXFER - you will not see this table in the USER_ views.

the all_ views show objects YOU HAVE ACCESS TO. Things you can select from for example. If you do not see the object in the ALL_ views that means your user account does not have access to it - you cannot select from it for example.

the DBA_ views show all objects - regardless of whether you can select from them or not.

ops$tkyte%ORA10GR2> create user a identified by a quota unlimited on users default tablespace users;

User created.

ops$tkyte%ORA10GR2> grant create session, create table to a;

Grant succeeded.

ops$tkyte%ORA10GR2> grant select on dba_objects to a;

Grant succeeded.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> connect a/a
Connected.
a%ORA10GR2> create table t ( x int );

Table created.

a%ORA10GR2>
a%ORA10GR2> select 'user objects', count(*) from user_objects union all
  2  select 'all  objects', count(*) from all_objects union all
  3  select 'dba  objects', count(*) from dba_objects;

'USEROBJECTS   COUNT(*)
------------ ----------
user objects          1
all  objects      40851
dba  objects      50441

<b>user A owns one object, user A has access to 40,851 objects - dba_objects reports however there are 50,441 objects (so there are about 10,000 objects user A cannot access in this database)</b>

a%ORA10GR2>
a%ORA10GR2> desc scott.emp
ERROR:
ORA-04043: object scott.emp does not exist

<b>for example, scott.emp doesn't exist as far as user A is concerned... it is not in the all_objects view</b>

a%ORA10GR2> select count(*) from all_objects where object_name = 'EMP' and owner = 'SCOTT';

  COUNT(*)
----------
         0

a%ORA10GR2> select count(*) from dba_objects where object_name = 'EMP' and owner = 'SCOTT';

  COUNT(*)
----------
         1

<b>but it is in the dba_objects view</b>

fs4 are 75 to 100% EMPTY
fs1 blocks are 0 to 25% EMPTY

a high fs4 value implies that that many blocks are below the high water mark and are almost empty. It could be that you might consider a shrink - you do not HAVE TO and in most cases you WOULD NOT.

It would only be in the cases where

o you frequently full scan this table and the number of fs4 blocks is really really high compared to the total number of blocks in the table. Eg: if fs4 was 225 but the table has 1,000,000 blocks - I don't really care, it won't make a difference - those 225 blocks - in the grand scheme of things

o you have permanently made the table smaller, you just removed lots of rows and you are NOT going to be inserting again any time soon. If you are going to be inserting again, just leave the space there, we'll reuse it - no need to move stuff around, we'll take care of it.

... The table size should be around num_rows * avg_row_len = 2437 Mb ...

you missed block overhead
you missed pctfree
you missed the row overheads that are not in avg row len (length bytes, null indicators)

... I had analyzed the table sometime back ...

you missed analyzing the table more currently, we cannot look at those numbers and consider them 'real' anymore, they were real 'some time back'


since so many are on the free list, it would seem that

a) table got full
b) table got purged

If most of the blocks are on the freelist, that would account for that. So at some point the table contained a lot more data and now, due to a purge or whatever, contains less.

then all I can say is:

table had much more data in it once upon a time. blocks were full, free blocks were near zero.

Then you purged, lots of blocks on freelist. So, the table is underutilized.

You cannot just add up avg_row * number_rows in this case, once upon a time ago, number_rows was MUCH larger, or avg_row was, or both were.

But now they are not.


if you do not trust showspace, you have the code, you can make the (documented) calls yourself to print this out.

what version are you in?
can we get a dbms_metadata.get_ddl on that table?

the difference between blocks in dba_tables and total blocks in show_space is expected (show_space shows all blocks, even those that are "ours" - used to manage space)

question: did someone use "analyze" once and you use dbms_stats later?

That must be it, dbms_stats only maintains columns the optimizer actually uses - num_freelist_blocks is NOT one of them.

Notice in the following that if you 'analyze', num_freelist_blocks is maintained, after the last delete - I used dbms_stats and num-freelist-blocks diverges from show_space (it stayed the same). If you analyze - they come back together

this does not mean "use analyze", absolutely - this does NOT mean that, dbms_stats is correct to use

(big table is a copy of all_objects with an ID column added, it has 100,000 rows in this example)

ops$tkyte%ORA9IR2> alter table big_table pctused 90;

Table altered.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> analyze table big_table compute statistics;

Table analyzed.

ops$tkyte%ORA9IR2> select  table_name, blocks, empty_blocks,num_freelist_blocks,
  2          to_char(last_analyzed, 'mm/dd/yy hh24:mi') last_analyzed
  3   from   dba_tables
  4    where table_name='BIG_TABLE'
  5     and  owner=user
  6  /

TABLE_NAME                         BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS LAST_ANALYZED
------------------------------ ---------- ------------ ------------------- --------------
BIG_TABLE                            1435          100                   0 03/05/09 17:33

ops$tkyte%ORA9IR2> exec show_space('BIG_TABLE' )
Free Blocks.............................               0
Total Blocks............................           1,536
Total Bytes.............................      12,582,912
Total MBytes............................              12
Unused Blocks...........................             100
Unused Bytes............................         819,200
Last Used Ext FileId....................               9
Last Used Ext BlockId...................           1,545
Last Used Block.........................              28

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> delete from big_table where mod(id,2) = 0 and rownum <= 100000/4;

25000 rows deleted.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> analyze table big_table compute statistics;

Table analyzed.

ops$tkyte%ORA9IR2> select  table_name, blocks, empty_blocks,num_freelist_blocks,
  2          to_char(last_analyzed, 'mm/dd/yy hh24:mi') last_analyzed
  3   from   dba_tables
  4    where table_name='BIG_TABLE'
  5     and  owner=user
  6  /

TABLE_NAME                         BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS LAST_ANALYZED
------------------------------ ---------- ------------ ------------------- --------------
BIG_TABLE                            1435          100                 717 03/05/09 17:33

ops$tkyte%ORA9IR2> exec show_space('BIG_TABLE' )
Free Blocks.............................             717
Total Blocks............................           1,536
Total Bytes.............................      12,582,912
Total MBytes............................              12
Unused Blocks...........................             100
Unused Bytes............................         819,200
Last Used Ext FileId....................               9
Last Used Ext BlockId...................           1,545
Last Used Block.........................              28

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> delete from big_table where mod(id,2) = 0 and rownum <= 100000/4;

25000 rows deleted.

ops$tkyte%ORA9IR2> commit;

Commit complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec dbms_stats.gather_table_stats( user, 'BIG_TABLE' )

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select  table_name, blocks, empty_blocks,num_freelist_blocks,
  2          to_char(last_analyzed, 'mm/dd/yy hh24:mi') last_analyzed
  3   from   dba_tables
  4    where table_name='BIG_TABLE'
  5     and  owner=user
  6  /

TABLE_NAME                         BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS LAST_ANALYZED
------------------------------ ---------- ------------ ------------------- --------------
BIG_TABLE                            1435          100                 717 03/05/09 17:33

ops$tkyte%ORA9IR2> exec show_space('BIG_TABLE' )
Free Blocks.............................           1,435
Total Blocks............................           1,536
Total Bytes.............................      12,582,912
Total MBytes............................              12
Unused Blocks...........................             100
Unused Bytes............................         819,200
Last Used Ext FileId....................               9
Last Used Ext BlockId...................           1,545
Last Used Block.........................              28

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> analyze table big_table compute statistics;

Table analyzed.

ops$tkyte%ORA9IR2> select  table_name, blocks, empty_blocks,num_freelist_blocks,
  2          to_char(last_analyzed, 'mm/dd/yy hh24:mi') last_analyzed
  3   from   dba_tables
  4    where table_name='BIG_TABLE'
  5     and  owner=user
  6  /

TABLE_NAME                         BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS LAST_ANALYZED
------------------------------ ---------- ------------ ------------------- --------------
BIG_TABLE                            1435          100                1435 03/05/09 17:33

... I have a table(composite partitioned) for which PCTFREE is set at 40%. This table is heavily modified but the row length is not going to be doubled . Row might be updated with new values but without changing the row length(maximum it will not change the row length, i could say this as I know the application). I would like to find out if this 40% setting is not very good for this table? ...


based on your description, if that is accurate, then 40% is too high, you don't need 40%, the default of 10% would be more than sufficient.


In answer to the (apparent) question "( Full block means block occupied minimum 60%(Since PCTFREE-40%) or Fully used 100%)???? " - let's think how we could figure that out pretty easily.

Probably the most straight forward way would be to create two scratch tables of the same exact size - full of the same exact data that is (they'll be different segment sizes of course!) - but different pctfree values. Then run show_space (we expect all blocks to be FULL since we just created this table...)

ops$tkyte%ORA10GR2> /*
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> drop table t1;
ops$tkyte%ORA10GR2> drop table t2;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t1
ops$tkyte%ORA10GR2> pctfree 10
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select * from all_objects;
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t2
ops$tkyte%ORA10GR2> pctfree 40
ops$tkyte%ORA10GR2> as
ops$tkyte%ORA10GR2> select * from all_objects;
ops$tkyte%ORA10GR2> */
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> exec show_space( 'T1' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             689
Total Blocks............................             768
Total Bytes.............................       6,291,456
Total MBytes............................               6
Unused Blocks...........................              59
Unused Bytes............................         483,328
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         395,657
Last Used Block.........................              69

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> exec show_space( 'T2' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................           1,038
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................              88
Unused Bytes............................         720,896
Last Used Ext FileId....................               4
Last Used Ext BlockId...................         396,937
Last Used Block.........................              40

PL/SQL procedure successfully completed.

Now, T1 has 689 full blocks and T2 has 1,038 full blocks - full blocks are those that are not available for future inserts.

ctl-f and search for

if the object is in an ASSM tablespace

use the show space that works with ASSM and post those results please.

and a copy of the create table would be useful


1) that has nothing to do with the extent size - the size of the extents is not relevant here. The size of the segment is, but the individual sizes of extents - no.

You don't mention if this table WAS large, and you purged lots of data from it.

2) pctfree is definitely NOT ignored, pctfree has the same effect as always. pctused is not used with automatic segment space management (it is not locally managed that obviates pctused, it is ASSM).

... (large number of extents were allocated
when it was not required so) ...

please back up that statement - how can you say that? I haven't read beyond that statement as I'm typing this in - but I can say - that is unfounded. If there is lots of space allocated right now, but few rows - all that means to me is that AT SOME POINT IN THE PAST THERE WERE A LOT OF ROWS, and you have deleted them.


I think in between those two points in time, you had LOTS MORE DATA in the table.

And you might be far off on your row counts for all I know, what are those row counts based on? If they are based on statistics - are they even close to accurate?

Please prove me wrong on that point.


The database is saying on the 15th of April, you had

Full Blocks ..................... 65,872

which is room for a lot more than the number of rows you say you had - I want to see your sql you used to get your information and to be somehow "verified" that the data you are supplying represented reality.


On, and ... does not a create table statement make. I want to see the create table statement (pctused, maxents, etc - not valid, only pctfree made sense in that create statement, the rest - not used)

ok, when you insert into this table, what is the process - how does the data get in here?

I'm guessing - /*+ APPEND */ hints, or direct=y with sqlldr or parallel

describe how the data gets added here, and give us the entire table, and describe in painstaking detail how the data gets in.

you rebuilt and index

and it got smaller - that is what most people hope to actually achieve.....


space is managed very very differently in indexes - the blocks are always "full", we don't do free space management in an index like you would in a table. Data in an index has a place to go - a location, we store the data sorted - we cannot just find a block with "free space" and use it.



In the following, I'll fill up a table randomly - cause the index leaf blocks to fill up, split, fill up, split, fill up, split. We'll use dbms_space to report space and then INDEX_STATS to see what is really up (bear in mind, analyze index validate structure is an OFFLINE OPERATION, IT WILL LOCK THE INDEX)

ops$tkyte%ORA9IR2> create table t as select object_name from all_objects where 1=0;

Table created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create index t_idx on t(object_name);

Index created.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> begin
  2          for x in (select object_name from all_objects order by dbms_random.random)
  3          loop
  4                  insert into t values ( x.object_name );
  5                  commit;
  6          end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               3
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             177
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................               7
Last Used Ext BlockId...................             393
Last Used Block.........................              64

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> analyze index t_idx validate structure;

Index analyzed.

ops$tkyte%ORA9IR2> select lf_rows, lf_blks, lf_rows_len, lf_rows/lf_blks rpb from index_stats;

   LF_ROWS    LF_BLKS LF_ROWS_LEN        RPB
---------- ---------- ----------- ----------
     27960        177      980362 157.966102

ops$tkyte%ORA9IR2> alter index t_idx rebuild online parallel 4 nologging;

Index altered.

ops$tkyte%ORA9IR2> exec show_space( 'T_IDX', user, 'INDEX' );
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               1
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             137
Total Blocks............................             152
Total Bytes.............................       1,245,184
Total MBytes............................               1
Unused Blocks...........................               1
Unused Bytes............................           8,192
Last Used Ext FileId....................               7
Last Used Ext BlockId...................             777
Last Used Block.........................              23

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> analyze index t_idx validate structure;

Index analyzed.

ops$tkyte%ORA9IR2> select lf_rows, lf_blks, lf_rows_len, lf_rows/lf_blks rpb from index_stats;

   LF_ROWS    LF_BLKS LF_ROWS_LEN        RPB
---------- ---------- ----------- ----------
     27960        137      980362 204.087591

See the rows/block (RPB) - it increased after the rebuild as we densely packed the index - the blocks are "full" as far as the database is concerned (full is nto relevant in an index, free space isn't managed that way - like a table is)


Now, you have to ask yourself "did you do good by making the index small"

Probably not, probably all you did was waste time, resources and caused increased redo log generation.


If this is an index on randomly arriving data (like my example, an index on LAST_NAME would have randomly arriving data - most all indexes have randomly arriving data - the exceptions are indexes on a surrogate key populated by a sequence or an index on a DATE/TIMESTAMP column populated by sysdate - as they are always increasing values, they arrive predictably - already sorted) - then the rebuild just made the index compact - but it will IMMEDIATELY START TO GET WIDE AGAIN as the data is inserted/updated - causing lots of index internal updates (block splits)

... now there are Difference of 16 in unused block. but the total blocks is the
same.
which is true?...

you lost me there.



In the first one, the table has a single extent with 8 blocks in it (total blocks).

There are 5 unused ones. Therefore, you have 3 blocks in use - under your high water mark.


In the second case, you have multiple extents, for a total of 512 allocated blocks. 64 of these are unused. Therefore, you have 512-64 = 448 blocks in use.


Don't know what you mean by "there are different of 16" and "total blocks is the same"?

we use some of those blocks ourself, they contain *our* data.

either one is closer than close enough, you get the gist of how large the table is, a very good one, with either.

by the way, do not use analyze to gather statistics, only use dbms_stats. Analyze is deprecated for this purpose.


if you really want to see how many blocks will be scanned - force a full scan of the table using

set autotrace traceonly
select /*+ full(t) */ count(*) from t;

dba_tables shows the output of the last statistics gathering, from some point in the past.

dbms_space is 'real time'

bitmap blocks used by ASSM to manage the space - they are your "freelists", we keep them in the table using ASSM to manage the other blocks that have your data.

... Can I use show_space output to determine when to reorganize a table ? ...

not really, well, maybe if the table is completely FS1 blocks... You tell me, what is your criteria for reorging?

your fs4 blocks are 75 to 100% EMPTY, they are your totally free blocks.

it is working as expected (free space is being reused over time) - I don't see any reason to go with manual space management at this point.

ASSM (automatic segement space management) uses many blocks IN the segment itself to manage the freespace - there will be blocks all over the segment - in the front, in the middle, everywhere - used to manage the space.

As opposed to manual segment space management that managed the space in freelists outside of the segment itself.

Well, it depends on how you define "size of an existing table"

To me, that is simply "total blocks", that is how much space the table consumes currently.

Someone else might call it "db_block_size*(total_blocks-unused_blocks)", but they probably as assuming that all allocated and used blocks are full or nearly so. Otherwise, that number is rather meaningless. Consider a table you put 1,000,000,000 rows into. total-unused = BIG number. Now, delete all of the rows and commit. total-unused = BIG number - but what sense does that number represent now?



I would just myself use total blocks.

object_space_usages is an internal, undocumented routine. It is used by AWR reporting.

SPACE_USAGE is the API call you and I are to be using.

this is the routine I used to show space utilization regardless of ASSM or not:

ops$tkyte%ORA11GR2> create or replace procedure show_space
  2  ( p_segname in varchar2,
  3    p_owner   in varchar2 default user,
  4    p_type    in varchar2 default 'TABLE',
  5    p_partition in varchar2 default NULL )
  6  -- this procedure uses authid current user so it can query DBA_*
  7  -- views using privileges from a ROLE and so it can be installed
  8  -- once per database, instead of once per user that wanted to use it
  9  authid current_user
 10  as
 11      l_free_blks                 number;
 12      l_total_blocks              number;
 13      l_total_bytes               number;
 14      l_unused_blocks             number;
 15      l_unused_bytes              number;
 16      l_LastUsedExtFileId         number;
 17      l_LastUsedExtBlockId        number;
 18      l_LAST_USED_BLOCK           number;
 19      l_segment_space_mgmt        varchar2(255);
 20      l_unformatted_blocks number;
 21      l_unformatted_bytes number;
 22      l_fs1_blocks number; l_fs1_bytes number;
 23      l_fs2_blocks number; l_fs2_bytes number;
 24      l_fs3_blocks number; l_fs3_bytes number;
 25      l_fs4_blocks number; l_fs4_bytes number;
 26      l_full_blocks number; l_full_bytes number;
 27  
 28          -- inline procedure to print out numbers nicely formatted
 29          -- with a simple label
 30      procedure p( p_label in varchar2, p_num in number )
 31      is
 32      begin
 33          dbms_output.put_line( rpad(p_label,40,'.') ||
 34                                to_char(p_num,'999,999,999,999') );
 35      end;
 36  begin
 37     -- this query is executed dynamically in order to allow this procedure
 38     -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES
 39     -- via a role as is customary.
 40     -- NOTE: at runtime, the invoker MUST have access to these two
 41     -- views!
 42     -- this query determines if the object is a ASSM object or not
 43     begin
 44        execute immediate
 45            'select ts.segment_space_management
 46               from dba_segments seg, dba_tablespaces ts
 47              where seg.segment_name      = :p_segname
 48                and (:p_partition is null or
 49                    seg.partition_name = :p_partition)
 50                and seg.owner = :p_owner
 51                and seg.tablespace_name = ts.tablespace_name'
 52               into l_segment_space_mgmt
 53              using p_segname, p_partition, p_partition, p_owner;
 54     exception
 55         when too_many_rows then
 56            dbms_output.put_line
 57            ( 'This must be a partitioned table, use p_partition => ');
 58            return;
 59     end;
 60  
 61  
 62     -- if the object is in an ASSM tablespace, we must use this API
 63     -- call to get space information, else we use the FREE_BLOCKS
 64     -- API for the user managed segments
 65     if l_segment_space_mgmt = 'AUTO'
 66     then
 67       dbms_space.space_usage
 68       ( p_owner, p_segname, p_type, l_unformatted_blocks,
 69         l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 70         l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 71         l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 72  
 73       p( 'Unformatted Blocks ', l_unformatted_blocks );
 74       p( 'FS1 Blocks (0-25)  ', l_fs1_blocks );
 75       p( 'FS2 Blocks (25-50) ', l_fs2_blocks );
 76       p( 'FS3 Blocks (50-75) ', l_fs3_blocks );
 77       p( 'FS4 Blocks (75-100)', l_fs4_blocks );
 78       p( 'Full Blocks        ', l_full_blocks );
 79    else
 80       dbms_space.free_blocks(
 81         segment_owner     => p_owner,
 82         segment_name      => p_segname,
 83         segment_type      => p_type,
 84         freelist_group_id => 0,
 85         free_blks         => l_free_blks);
 86  
 87       p( 'Free Blocks', l_free_blks );
 88    end if;
 89  
 90    -- and then the unused space API call to get the rest of the
 91    -- information
 92    dbms_space.unused_space
 93    ( segment_owner     => p_owner,
 94      segment_name      => p_segname,
 95      segment_type      => p_type,
 96      partition_name    => p_partition,
 97      total_blocks      => l_total_blocks,
 98      total_bytes       => l_total_bytes,
 99      unused_blocks     => l_unused_blocks,
100      unused_bytes      => l_unused_bytes,
101      LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
102      LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
103      LAST_USED_BLOCK => l_LAST_USED_BLOCK );
104  
105      p( 'Total Blocks', l_total_blocks );
106      p( 'Total Bytes', l_total_bytes );
107      p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
108      p( 'Unused Blocks', l_unused_blocks );
109      p( 'Unused Bytes', l_unused_bytes );
110      p( 'Last Used Ext FileId', l_LastUsedExtFileId );
111      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
112      p( 'Last Used Block', l_LAST_USED_BLOCK );
113  end;
114  /

Procedure created.