Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, sam.

Asked: May 19, 2000 - 2:11 pm UTC

Answered by: Tom Kyte - Last updated: May 21, 2013 - 5:16 pm UTC

Category: Database - Version: 8.1.5

Viewed 50K+ times! This question is

You Asked

What is the use of this stored proc?
It tells you how many blocks have free space for updates, right ?
But it does not tell you how much free space in each block. We can
get free space info. from dba_free_space.
Can you show how this proc can be of value to us?
Another procs in this package is unused_space.
If it reports 35 blocks. Does it mean 35 blocks have never
had data in it ?
It seems that it doesn't report any empty blocks above
the high water mark, does it?
How can we make use of this info ? Can you give some examples
that we can use these procedures to help manage space.

Thanks, Tom.



and we said...

Here is an example showing how to use dbms_space and how to interpret the output. Basically between the 2 procedures free blocks and unused space, we'll be able to get:

Free Blocks...... Number of blocks on the freelist
Total Blocks..... Total blocks allocated to the table
Total Bytes...... Total bytes allocated to the table
Unused Blocks.... Blocks that have never contained data
Unused Bytes..... The above in bytes

It does not tell you how many blocks have free space for updates. We can tell you how many blocks are candidates for INSERTS (they are on the freelist) and blocks on the freelist have space for updates -- but -- there are blocks in the table that have space for updates but that are not on the freelist. We cannot see them in any report.

It does not tell you how much space is free in each block (nothing does, typically there are thousands or hundreds of thousands of blocks in a table -- an analysis of the free space block by block is not practical. We can get an average free space but not block by block).

This report does show blocks above the high water mark. Unused Blocks are exactly the block above the high water mark.

You can get most of the information supplied by this package by analyzing the table and using queries against user_tables and user_segments. The freelist analysis is more detailed using this package as you can look at each freelist independently.

Below is a procedure you can use to make using dbms_space a little easier. After that I create a table and show how space is being used in it after various operations. Comments in bold explain the output.

ops$tkyte@8i> create or replace
2 procedure show_space
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE' )
6 as
7 l_free_blks number;
8
9 l_total_blocks number;
10 l_total_bytes number;
11 l_unused_blocks number;
12 l_unused_bytes number;
13 l_LastUsedExtFileId number;
14 l_LastUsedExtBlockId number;
15 l_LAST_USED_BLOCK number;
16 procedure p( p_label in varchar2, p_num in number )
17 is
18 begin
19 dbms_output.put_line( rpad(p_label,40,'.') ||
20 p_num );
21 end;
22 begin
23 dbms_space.free_blocks
24 ( segment_owner => p_owner,
25 segment_name => p_segname,
26 segment_type => p_type,
27 freelist_group_id => 0,
28 free_blks => l_free_blks );
29
30 dbms_space.unused_space
31 ( segment_owner => p_owner,
32 segment_name => p_segname,
33 segment_type => p_type,
34 total_blocks => l_total_blocks,
35 total_bytes => l_total_bytes,
36 unused_blocks => l_unused_blocks,
37 unused_bytes => l_unused_bytes,
38 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
39 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
40 LAST_USED_BLOCK => l_LAST_USED_BLOCK );
41
42 p( 'Free Blocks', l_free_blks );
43 p( 'Total Blocks', l_total_blocks );
44 p( 'Total Bytes', l_total_bytes );
45 p( 'Unused Blocks', l_unused_blocks );
46 p( 'Unused Bytes', l_unused_bytes );
47 p( 'Last Used Ext FileId', l_LastUsedExtFileId );
48 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
49 p( 'Last Used Block', l_LAST_USED_BLOCK );
50 end;
51 /

Procedure created.

ops$tkyte@8i>
ops$tkyte@8i> create table t ( x int, y char(2000) default '*' )
2 storage ( initial 40k next 40k minextents 5 )
3 tablespace system;

Table created.

I create a table with >1 extent to make it interesting. I also put a char(2000) in there to make the minimum row length be 2000 bytes (chars always take their max space right away). This just makes my rows "big"

ops$tkyte@8i> insert into t (x) values ( 1 );
1 row created.

I create one row just to use a little space in the table

ops$tkyte@8i> analyze table t compute statistics;
Table analyzed.

ops$tkyte@8i> compute sum of blocks on report
ops$tkyte@8i> break on report
ops$tkyte@8i> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 and segment_type = 'TABLE'
5 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32

This shows that there are 32 blocks allocated in 5 extents to this table (as expected)

ops$tkyte@8i> clear breaks
ops$tkyte@8i> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
1 30 6091 1

Since I analyzed the table, I have acccess to the above information. You'll find that it maps exactly to the data below. There are a total of 32 blocks allocated to the table (below and as confirmed by user_extents above). There are 30 EMPTY_BLOCKS (above)/ UNUSED_BLOCKS (below). These are blocks above the HWM. This leaves 2 blocks unaccounted for -- 1 block has data in it, the other has the extent map for the table (the first block of each table is used by the system itself).

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................1
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................30
Unused Bytes............................245760
Last Used Ext FileId....................1
Last Used Ext BlockId...................64816
Last Used Block.........................2

PL/SQL procedure successfully completed.

ops$tkyte@8i> insert into t (x)
2 select rownum
3 from all_users
4 where rownum < 50
5 /

49 rows created.

ops$tkyte@8i> commit;
Commit complete.

So now we have 50 rows with 2k each -- I'm using a 8k blocksize so I expect about 3 rows / block. That means about 18 blocks of data plus 1 for the system = about 19 blocks should be "used" now. Below I see that I have

o 3 blocks on the freelist. they have more space for new inserts (they have not hit their pctused yet and may be inserted into)

o 12 unused blocks, leaving 20 blocks "used". Since I have 3 on the freelist -- we probably used a little more then the 18 for data -- we used 19 for the 50 rows. We have one for the system -- all accounted for.


ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................3
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

PL/SQL procedure successfully completed.

ops$tkyte@8i> delete from t;
50 rows deleted.

ops$tkyte@8i> commit;
Commit complete.

Now we can see what a delete does to our utilization.

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................19
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

PL/SQL procedure successfully completed.

The above shows that the delete simply put all of our blocks on the free list. We have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 blocks. All accounted for. Note that the HWM stayed the same -- we don't have 31 unused blocks -- we have 12 as before. The HWM for a table will never decrease unless we.....


ops$tkyte@8i> truncate table t;
Table truncated.

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................0
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................31
Unused Bytes............................253952
Last Used Ext FileId....................1
Last Used Ext BlockId...................64816
Last Used Block.........................1

PL/SQL procedure successfully completed.

Truncate it. That puts all of the blocks below the HWM. Now we have 31 unused blocks + 1 system block = 32 blocks total. None on the free list since none of them have any data.

and you rated our response

  (158 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Table space used for a particular table

March 17, 2001 - 11:05 am UTC

Reviewer: spmurthy from Singapore

Hi Tom,
Thanks for your reply it is more useful to me to know the table space.


Regards



dbms_space usage

March 22, 2001 - 5:22 pm UTC

Reviewer: B.N.Sarma from USA

Tom,

Excellent , nothing less.

It would have been nice had you shown a select statement with autot on doing FTS upto hwm, even if you have delted all the rows and the same with truncate. It would have become a good notes.

Your explanation with examples makes things very clear.

Why don't you write a book :-)

Regards
BN

March 23, 2001 - 7:28 am UTC

Reviewer: Helena Markova from Bratislava, Slovakia


dbms_space.free_space

May 09, 2001 - 11:44 pm UTC

Reviewer: D.C.L. from Seattle, USA

Right on. Awesome grip of the subject matter.

May 10, 2001 - 6:27 am UTC

Reviewer: Vikram from Delhi, India

Excellent

August 21, 2001 - 2:29 am UTC

Reviewer: k.v.s.Raju from Sydney, Australia

Its excellent


dbms_space

September 19, 2001 - 5:53 pm UTC

Reviewer: Jim from MA

Very, Very Good!!!

Errors in show_space

September 20, 2001 - 4:27 am UTC

Reviewer: A reader

Tom, tried using your show_space procedure. It compiled successfully but on using it I get following errors:

SQL> exec show_space('T')
BEGIN show_space('T'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "TOM.SHOW_SPACE", line 22
ORA-06512: at line 1

the table T exists under schema TOM and the show_space procedure was compiled under user TOM.

DBMSUTIL/PRVTUTIL etc. have all been run. What am I missing ?

 

Tom Kyte

Followup  

September 20, 2001 - 6:47 am UTC

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> 

A little question

October 18, 2001 - 5:36 am UTC

Reviewer: Igor from France

I don't understand how you knew it would be 32 blocks
for one row of 2000 chars and number ?

Tom Kyte

Followup  

October 18, 2001 - 7:56 am UTC

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



5 extents 40k each (8k block size) why not 5X5=25 blocks?

October 22, 2001 - 2:43 pm UTC

Reviewer: YK LIU from CA, USA


Free Space

October 30, 2001 - 11:21 am UTC

Reviewer: an from DE

it's excellent!

ORA-14107: partition specification is required for a partitioned object

November 19, 2001 - 11:08 am UTC

Reviewer: A reader

SQL> CREATE TABLE T (X VARCHAR2(20));

Table created.

SQL> EXEC SHOW_SPACE('T');

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL>  EXEC SHOW_SPACE('T');
Free Blocks.............................0
Total Blocks............................10
Total Bytes.............................81920
Unused Blocks...........................9
Unused Bytes............................73728
Last Used Ext FileId....................5
Last Used Ext BlockId...................126659
Last Used Block.........................1

PL/SQL procedure successfully completed.

T_P -- is a partitioned table

SQL> EXEC SHOW_SPACE('T_P');
BEGIN SHOW_SPACE('T_P'); END;

*
ERROR at line 1:
ORA-14107: partition specification is required for a partitioned object
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "myschema.SHOW_SPACE", line 22
ORA-06512: at line 1

Why Iam I running into this error while trying to use show_space on a partitioned table. 

Tom Kyte

Followup  

November 19, 2001 - 3:59 pm UTC

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. 

Reader

December 26, 2001 - 8:31 am UTC

Reviewer: Reader from USA

Tom,

Could you clarify, why the *first block* of a datafile
being used for the OS., 2nd for segment header, 3rd... for
data.

I created a table ts1 in tablespace ts1 , db_block_size 8k

Results:

SQL> set serveroutput on size 1000000
SQL> exec show_space('TS1','SYS','TABLE');
Free Blocks.............................1
Total Blocks............................2
Total Bytes.............................16384
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................2
Last Used Ext BlockId...................2
Last Used Block.........................2

PL/SQL procedure successfully completed.


SQL> select lpad(file_name,40), bytes/1024/8 BLOCKS , TABLESPACE_NAME from 
     dba_data_files where TABLESPACE_NAME = 'TS1';

LPAD(FILE_NAME,40)                           BLOCKS TABLESPACE_NAME
---------------------------------------- ---------- -----------------------
               /u07/oradata/iiim/ts1.dbf          3 TS1



SQL> select SEGMENT_NAME,TABLESPACE_NAME,BLOCK_ID,BLOCKS, extent_id from 
     dba_extents where SEGMENT_NAME = 'TS1';

SEGM TABLESPACE_NAME                  BLOCK_ID     BLOCKS  EXTENT_ID
---- ------------------------------ ---------- ---------- ----------
TS1  TS1                                     2          2          0

SQL> select TABLE_NAME,INITIAL_EXTENT,NEXT_EXTENT from dba_tables 
     where TABLE_NAME = 'TS1';

TABLE_NAME                     INITIAL_EXTENT NEXT_EXTENT
------------------------------ -------------- -----------
TS1                                     16384        8192

SQL> alter table ts1 allocate extent (size 8192K);
alter table ts1 allocate extent (size 8192K)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TS1 by 1024 in tablespace TS1

The datafile has 3 blocks, segment has used 2 blocks (including segment header). When I try to allocate one
extent = 1 block, get ora-1653

Thanks 

Tom Kyte

Followup  

December 26, 2001 - 9:11 am UTC

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.

Reader

December 26, 2001 - 9:28 am UTC

Reviewer: Reader from USA

Tom,

I am sorry, I did try 8k and got this ora-1653

SQL> alter table ts1 allocate extent (size 8k);
alter table ts1 allocate extent (size 8k)
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TS1 by 1 in tablespace TS1

I have found the 1st block seemed to have been used 
by OS, not sure if this is platform specific (Silicon Graphics)

Thanks 

Tom Kyte

Followup  

December 26, 2001 - 12:24 pm UTC

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

Reader

December 26, 2001 - 1:17 pm UTC

Reviewer: Reader from USA

Tom,

Create Tablespace command:

Create tablespace TS1 datafile '/u07/oradata/iiim/ts1.dbf' size 24k;
Create table ts1 storage(initial 8k next 8k pctincrease 0)
tablespace ts1;

This is purely of academic interest. Ofcourse, we do not need to spend
too much time on this. I agree

Although, if the datafile is for example 1000M and the
segment in the tablespace is initial 500M next 500M,
since 1 block (8/1024M) is used for whatever reason other than
database EXTENTS, there is fragmentation induced; 500M that can
be allocated for segments and 499.99M gets unusable; unless
size the datafile to be 1001M to start with.

Thanks

Tom Kyte

Followup  

December 26, 2001 - 1:38 pm UTC

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>  

Extremely Useful

December 26, 2001 - 1:53 pm UTC

Reviewer: Reader from USA

Tom,

Thanks very much
I do plan on using LMT in new databases

December 27, 2001 - 9:09 am UTC

Reviewer: Dixit from NJ,USA

Very Good

Wow !

March 20, 2002 - 2:14 pm UTC

Reviewer: Mini from OH

Exactly what I was looking for.

Thank you so much Tom

How the extents are allocated in bytes

April 30, 2002 - 8:06 am UTC

Reviewer: Santosh Jadhav from India

it was a very good explanation TOM. but i have one doubt

ops$tkyte@8i> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 and segment_type = 'TABLE'
5 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32

this is what after creating table T with 40k initial and next extent. so why it is 81920 (3rd extent) and 57344(4th extent). And why 7 and 10 blocks are allocated respectively


Tom Kyte

Followup  

April 30, 2002 - 11:30 am UTC

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.

good stuff

September 16, 2002 - 2:44 pm UTC

Reviewer: Doug from CT, USA


System block is always 1 ?

January 09, 2003 - 12:50 am UTC

Reviewer: Tony from India

Does Oracle allocate only one system block even for very big table?



Tom Kyte

Followup  

January 09, 2003 - 7:25 am UTC

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

What AUTO SEGMENT SPACE MANAGEMENT ts's?

March 20, 2003 - 12:06 am UTC

Reviewer: Matt from Australia

What should the value of freelist_group_id be in each call to dbms_space.free_blocks? You default your value to 1, should this be 1 for tables in all cases?

I just executed dbms_space.free_blocks for a segment in a LOCALLY managed TS with AUTO SEGMENT SPACE MANAGEMENT
and got the following error:

10618, 00000, "Operation not allowed on this segment"
// *Cause: This DBMS_SPACE operation is not permitted on segments in
// tablespaces with AUTO SEGMENT SPACE MANAGEMENT
// *Action: Recheck the segment name and type and re-issue the statement

How do you identify free blocks in this case?

Tom Kyte

Followup  

March 20, 2003 - 8:27 am UTC

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

your show_space procedure and partitoned tables

May 06, 2003 - 7:47 am UTC

Reviewer: PINGU

Hi

I am trying to use your show_space procedure but it seems that it does not work with partitioned tables?

I think the cursor

for x in ( select tablespace_name
from dba_tablespaces
where tablespace_name = ( select tablespace_name
from dba_segments
where segment_type = p_type
and segment_name = p_segname
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )

the subquery should we add distinct?

Tom Kyte

Followup  

May 06, 2003 - 8:12 am UTC

go for it. I adjust it as I need.

after a closer look...

May 06, 2003 - 10:20 am UTC

Reviewer: PINGU

Hi

I had a closer look and I dont understand why we need the for loop

for x in ( select tablespace_name
from dba_tablespaces
where tablespace_name in ( select distinct tablespace_name
from dba_segments
where segment_type = p_type
and segment_name = p_segname
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' )
)
loop
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
partition_name => p_partition,
freelist_group_id => 0,
free_blks => l_free_blks );
end loop;

I think we dont use anything from the loop.....? Or iterating for anything

Tom Kyte

Followup  

May 06, 2003 - 10:48 am UTC

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.

May 19, 2003 - 4:54 pm UTC

Reviewer: A reader

Hi Tom,

 I am getting error when i use show_space procedure

SQL> exec show_space('T1');
Error ORA-10618: Operation not allowed on this segment -10618
BEGIN show_space('T1'); END;

*
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "GAURANG.SHOW_SPACE", line 49
ORA-06512: at line 1

Thanks 

Tom Kyte

Followup  

May 20, 2003 - 7:25 am UTC

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

May 20, 2003 - 9:37 am UTC

Reviewer: A reader

T1 IS TABLE .

I am using oracle 9iR2

Tom Kyte

Followup  

May 20, 2003 - 1:06 pm UTC

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.

 

FREE BLOCK Vs PERFORMANCE

June 12, 2003 - 9:32 pm UTC

Reviewer: Raghu from Milwaukee, WI USA

Hi Tom

What is the impact of the Large High water mark or FREE Blocks or UNUSED block on the table performacne (INSERT/UPDATE/SELECT). I have a case in my environment where a batch takes 9 minutes in one instance (EDEV) amd ot takes 2 hrs in another (EPROD). The outputs from yr procedure and from ALL_TABLES is given below for both the instance.

EDEV

Free Blocks 4
Total Blocks 1984
Total Bytes 16252928
Unused Blocks 1
Unused Bytes 8192
Last Used Ext FileId 112
Last Used Ext BlockId 5193
Last Used Block 15



EPROD

Free Blocks 20
Total Blocks 2352
Total Bytes 19267584
Unused Blocks 11
Unused Bytes 90112
Last Used Ext FileId 346
Last Used Ext BlockId 3065
Last Used Block 5


BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
2340 6 4462 2103

select blocks, empty_blocks,
avg_space, num_freelist_blocks
from user_tables
where table_name = 'BACKLOG_ITEM'

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
3956 11 4570 2004

2. If my process does not prevent me to issue a TRUNCATE statement, but my batch involves lots of DELETES and INSERT, then how can I prevent large values for high water mark?. Do we have any utility/script that we can run to reduce the high water mark of a table.


Tom Kyte

Followup  

June 13, 2003 - 7:46 am UTC

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.

Thanks

June 13, 2003 - 8:14 am UTC

Reviewer: Raghu from Milwaukee, WI USA

Hi Tom,

Thanks for your response. I absolutely agree with you that the performance of the batch I had given in my example could be improved by changing the algorithm. I am sorry that my example diverted you from the "real" reason for my comment.

I was wondering how I can make use of the output of your query in my DB maintenenace. For instance. Is there a co-relation between the number of Free blocks and performance.

In my original example, Can I attribute the difference in batch performance between two instance to number of Free blocks?(Again I accept that the performance could be improved further).

Another example, I have a table that has around 6 million rows. The average Row length is 1250 bytes. We do lots of "Delete/Insert/Update" on this table. We recently re-partitioned this table (using Export, CREATE Table and Import). We noticed a huge performance changes for SELECTS..Over the period of time, it performance is going back to the "Normal". Can I attribute this to FREE Blocks and Do you think that we should constantly look for such blocks and "clean it" and how can we do it?.
Note: At this time, I am trying to find the Root cause for the performance changes. When I read this article of yours I felt that I might have discovered "one" of the casues and wanted a confirmation from you. I also like to point out that the DB are very similar in terms of size, parameter etc. The code is also same.

Tom Kyte

Followup  

June 13, 2003 - 8:43 am UTC

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.

Dear Tom, what could be wrong here?

July 24, 2003 - 8:42 am UTC

Reviewer: Saminathan Seerangan from NJ,USA

Tom,

I am not able to use show_space procedure. Please help me out.

SQL> create user a identified by a;
User created.

SQL> grant create session, create procedure,
  2  create table to a;
Grant succeeded.

SQL> alter user a quota unlimited on users;
User altered.

SQL> connect a/a
Connected.

SQL> @D:\share\oracle\asktom\show_space.sql
 51  /
Procedure created.

SQL> create table t ( x int ) tablespace users;
Table created.

SQL> exec show_space( 'T' )
BEGIN show_space( 'T' ); END;

*
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "A.SHOW_SPACE", line 22
ORA-06512: at line 1


SQL> desc t
 Name                                      Null?    Type
 ------------------------------------- -------- --------
 X                                       NUMBER(38)

SQL> insert into t values(100);
1 row created.


SQL> desc dbms_space
PROCEDURE FREE_BLOCKS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 FREELIST_GROUP_ID              NUMBER                  IN
 FREE_BLKS                      NUMBER                  OUT
 SCAN_LIMIT                     NUMBER                  IN     DEFAULT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE SPACE_USAGE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 UNFORMATTED_BLOCKS             NUMBER                  OUT
 UNFORMATTED_BYTES              NUMBER                  OUT
 FS1_BLOCKS                     NUMBER                  OUT
 FS1_BYTES                      NUMBER                  OUT
 FS2_BLOCKS                     NUMBER                  OUT
 FS2_BYTES                      NUMBER                  OUT
 FS3_BLOCKS                     NUMBER                  OUT
 FS3_BYTES                      NUMBER                  OUT
 FS4_BLOCKS                     NUMBER                  OUT
 FS4_BYTES                      NUMBER                  OUT
 FULL_BLOCKS                    NUMBER                  OUT
 FULL_BYTES                     NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE UNUSED_SPACE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 TOTAL_BLOCKS                   NUMBER                  OUT
 TOTAL_BYTES                    NUMBER                  OUT
 UNUSED_BLOCKS                  NUMBER                  OUT
 UNUSED_BYTES                   NUMBER                  OUT
 LAST_USED_EXTENT_FILE_ID       NUMBER                  OUT
 LAST_USED_EXTENT_BLOCK_ID      NUMBER                  OUT
 LAST_USED_BLOCK                NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT

SQL> 

Tom Kyte

Followup  

July 24, 2003 - 9:29 am UTC

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

Thank you so much

July 24, 2003 - 10:46 am UTC

Reviewer: Saminathan Seerangan from NJ,USA

Small typo in the above procedure
Line # 46 'select ts.segment_space_management appears 2 times.


Tom Kyte

Followup  

July 24, 2003 - 4:23 pm UTC

thanks -- i fixed that up

Access to dba_* views needed

September 17, 2003 - 2:50 am UTC

Reviewer: Dusan from Czech republic

Well, this is a bit frustrating! I used to use this excellent utility (show_space) at many client sites (8i), to investigate space issues. Basically, every schema might use it! Now, on 9i, I have to ask access for those views. Isn't it possible to work around this?

Tom Kyte

Followup  

September 17, 2003 - 7:08 am UTC



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

Seems it does not work for partitioned table

September 17, 2003 - 5:39 am UTC

Reviewer: Dusan from Czech rep.

Problems appeared when I tried to use this utility for partitioned table.

1 select TABLE_OWNER, PARTITION_NAME
2 from all_tab_partitions
3 where table_owner='VALASEKD'
4* and table_name='DV_ALL_OBJECTS_PART'
sys@DV9IR2.US.ORACLE.COM> /

TABLE_OWNER PARTITION_NAME
------------------------------ ----------------
VALASEKD P030915
VALASEKD P030916
VALASEKD PMAX


1 select ts.segment_space_management
2 from dba_segments seg, dba_tablespaces ts
3 where seg.segment_name = 'DV_ALL_OBJECTS_PART'
4 and ('PMAX' is null or
5 seg.partition_name = 'PMAX')
6 and seg.owner = 'VALASEKD'
7* and seg.tablespace_name = ts.tablespace_name
sys@DV9IR2.US.ORACLE.COM> /

SEGMEN
------
AUTO

sys@DV9IR2.US.ORACLE.COM> ed
Zapsáno soubor afiedt.buf

1* exec show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX');
sys@DV9IR2.US.ORACLE.COM> exec show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX');
BEGIN show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX'); END;

*
ERROR na øádku 1:
ORA-03204: zadání typu segmentu by mìlo vyznaèovat dìlení na partition
ORA-06512: na "SYS.DBMS_SPACE", line 97
ORA-06512: na "SYS.SHOW_SPACE", line 64
ORA-06512: na line 1

DIfferent Extent Sizes

October 02, 2003 - 9:31 pm UTC

Reviewer: Kailash from East Brunswick, NJ

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

Tom Kyte

Followup  

October 03, 2003 - 8:05 am UTC

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

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


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

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

More explanation please

June 01, 2004 - 2:23 am UTC

Reviewer: parag jayanat patankar from India

Hi Tom,

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

11:21:50 test@prod> exec show_space('V9SPD140');
Free Blocks.............................4051
Total Blocks............................7418
Total Bytes.............................30384128
Unused Blocks...........................0
Unused Bytes............................0
Last Used Ext FileId....................5
Last Used Ext BlockId...................89500
Last Used Block.........................3

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

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................19
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

PL/SQL procedure successfully completed.

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

In my data

Total Blocks are : 7418 =

Blocks on free list : 4051 +
Unused Blocks : 0 +
System Block : 1

Then where are other blocks gone ? Total blocks should be matched as per your formula

Kindly explain as I have missed out some imp part.

thanks & regards
parag jayant patankar

Tom Kyte

Followup  

June 01, 2004 - 8:14 am UTC

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



It's amazing what you find when you actually read the documentation .. ;)

July 13, 2004 - 11:19 am UTC

Reviewer: Greg from Toronto, Ontario

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

Do I find my problem conveniently answered by Tom?
No.

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

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

*sigh* ... Thanks, Tom!!

.. I needed that!!
:)


Good explanation

August 02, 2004 - 6:07 am UTC

Reviewer: Cyrille from FRANCE

Thanks for all your explanation.

HWM below or above?

August 26, 2004 - 9:30 pm UTC

Reviewer: RD from NZ

Hi Tom,
At the beginning of the thread you said
"
Truncate it. That puts all of the blocks below the HWM. Now we have 31
unused blocks + 1 system block = 32 blocks total. None on the free list since
none of them have any data."

Should that have been "that puts all of the blocks ABOVE the HWM"

cos truncate will always lower the HWM or am I wrong??

Thanks,
Regards,
RD.

Tom Kyte

Followup  

August 27, 2004 - 7:33 am UTC

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

Free Space

August 30, 2004 - 8:01 am UTC

Reviewer: Nazmul Hoque from Bangladesh

Dear Tom,

I Need Your Help, I am useing Oracle 8.1.7, I have three Table space in my data base size are 38 MB, 204 MB & 51 MB, But my database useing space from my server more than 3 GB.
And file size Temp01.dbf is 1.19 GB and users01.dbf file is
1.17 GB.
My idea this is abnormal, Please help me to free space from my server.

Thanks/Hoque

Tom Kyte

Followup  

August 30, 2004 - 8:56 am UTC

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.

Questions

August 30, 2004 - 10:44 pm UTC

Reviewer: A reader

1. Why do you have the 'EXCEPTION WHEN OTHERS THEN NULL' in your code above? You are very much against hiding exceptions, arent you?

2. I understand the AUTHID CURRENT_USER part so that it can be installed once per database and each calling user doesnt get any extra privileges due to it being definer's right.

But how does AUTHID CURRENT_USER address "this procedure uses authid current user so it can query DBA_* views using privileges from a ROLE"

3. "-- 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"

Instead, why not simply use the ALL_* views so that the EXECUTE IMMEDIATE is not needed at all? Every user calling this proc automatically sees what they are supposed to see i.e. the ALL_* views do all the heavy lifting instead of us?

4. What benefit is PIPELINED giving us in the 9iR2 version of this utility?

5. Why is the PRAGMA AUTONOMOUS_TRANSACTION needed?

Thanks

Tom Kyte

Followup  

August 31, 2004 - 8:34 am UTC

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.

Free Space (Table Space)

August 31, 2004 - 7:19 am UTC

Reviewer: Nazmul Hoque from Dhaka - Bangladesh

** do you have a dba you can work with?
I am the programmer and DBA both !!!


**** 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.

Please Advise me how I DROP and RRCREATE, Temp is created by the stystem, If I dtop that, is it create any problem ?

*** search this site for maxshrink, it'll tell you if you can shrink your users01.dbf file.
Please write with details.....

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

In my server now only 400 MB is free on that drive.

PLEASE HELP ME....

Thanks
Nazmul Hoque

Tom Kyte

Followup  

August 31, 2004 - 10:02 am UTC

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".   

define

September 02, 2004 - 1:22 am UTC

Reviewer: ABC

Tom

Please provide single line definitio of HWM

Tom Kyte

Followup  

September 02, 2004 - 8:05 am UTC

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.

Temp Tablespace Create and Drop

September 02, 2004 - 7:53 am UTC

Reviewer: Nazmul Hoque from Bangladesh, Dhaka

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

to downsize your temp, create new:

ops$tkyte@ORA9IR2> create temporary tablespace temp tempfile size 100m;
Tablespace created.
and create new
 
--- getting below message :
SQL> create temporary tablespace temp tempfile size 100m;
create temporary tablespace temp tempfile size 100m
                                          *
ERROR at line 1:
ORA-02236: invalid file name

**** Please help me ****

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".   
*** WE are using server Compaq Proliant 1600, Now a days it so difficault to add HDD at My end.

**** Dear Tom,
I know how create table space and drop tabel space from SQL and From DBA Studio both, My problem is Temp tablespace, I never create Temp tablespace or drop before. So far I have 
seen it is created by the system at time of installation. If i drop and Recreate temp with 100 MB space my problem will be solved, Please give some more trips to Drop and Creat Temp Tablespace. Please Please Please..

Thanks
Nazmul Hoque 

Tom Kyte

Followup  

September 02, 2004 - 9:37 am UTC

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.



TEMPORARY TABLESPACE

September 03, 2004 - 7:48 am UTC

Reviewer: NAZMUL HOQUE from Bangladesh

MY TABLESPACE LIST AS BELOW :

================================

TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS
---------- -------------- ----------- ----------- ----------- ------------ ------------------
SYSTEM 65536 65536 1 2.147E+09 50 65536 ONLINE
RBS 524288 524288 8 4096 50 524288 ONLINE
USERS 131072 131072 1 4096 0 131072 ONLINE
TEMP 65536 65536 1 0 65536 ONLINE
TOOLS 32768 32768 1 4096 0 32768 ONLINE
INDX 131072 131072 1 4096 0 131072 ONLINE
DRSYS 65536 65536 1 2.147E+09 50 65536 ONLINE
DHAKA 40960 40960 1 505 50 0 ONLINE
SSLDATA 40960 40960 1 505 50 0 ONLINE


================================

NORMALY WE USE BELOW COMMAND FROM SQL TO CREATE A TABLESPACE
(PATH MENTIONED AS PER OUR SERVER LOCATION) :

CREATE TABLESPACE TEST
DATAFILE 'D:\ORACLE\ORADATA\NTSERVER\DATA1.DAT' SIZE 100M
AUTOEXTEND ON NEXT 10M

SHALL I USE BELOW FOR TEMPORARY TABLESPACE
==========================================

CREATE TEMPORARY TABLESPACE TEMP
DATAFILE 'D:\ORACLE\ORADATA\NTSERVER\TEMP01.DBF' SIZE 100M
AUTOEXTEND ON NEXT 5M

TO DROP TABELSPACE :

Drop Tablespace TEST;

FOR TEMPORARY TABLESPACE:
========================

Drop TEMPORARY Tablespace TEMP;

DEAR TOM, PLEASE ADVISE ME, I AM DOING WRONG OR RIGHT. IF WRONG THAN PLEASE WRITE ONLY TWO LINES TO CORRECT ME.

THANKS
NAMZUL HOQUE

Tom Kyte

Followup  

September 03, 2004 - 11:59 am UTC

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...)

ORA-00943

September 10, 2004 - 3:32 am UTC

Reviewer: VKOUL from LACEY, WA USA.

How to avoid ORA-00943 error while calculating free blocks thru DBMS_SPACE.FREE_SPACE on clusters owned by different user. e.g.

SQL> show user
USER is "TEST123"
SQL> var x number;
SQL> 
SQL> exec dbms_space.free_blocks ('TEST','TEST_CLUSTER','CLUSTER',0,:x);
BEGIN dbms_space.free_blocks ('TEST','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
SQL> 
SQL> conn test/test@dev
Connected.
SQL> 
SQL> 
SQL> 
SQL> exec dbms_space.free_blocks ('TEST','TEST_CLUSTER','CLUSTER',0,:x);

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
         3

SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL> 

Q1. Is there any privilege required to do this task from a user different than the owner of the cluster ?

I have tried GRANT ALTER ANY CLUSTER TO TEST123, but it didn't work.

Thanks 

Tom Kyte

Followup  

September 10, 2004 - 8:48 am UTC

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.
 
 

SYS clusters giving same error even after "ANALYZE ANY" privilege

September 11, 2004 - 2:32 am UTC

Reviewer: VKOUL from LACEY, WA USA

Thanks for the response

But even for a freshly created cluster in SYS, it is throwing an error.

SQL> show user
USER is "SYS"
SQL> l
  1  create cluster test_cluster (dept number(2))
  2  size 1024
  3* index
SQL> 
SQL> /

Cluster created.

SQL>  create index test_cluster_index on cluster test_cluster;

Index created.

SQL> create table x
  2  cluster test_cluster(deptno)
  3  as select * from scott.dept;

Table created.

SQL> create table y
  2  cluster test_cluster(deptno)
  3  as select * from scott.emp;

Table created.

SQL> 
SQL> exec dbms_space.free_blocks('SYS','TEST_CLUSTER','CLUSTER', 0, :x);

PL/SQL procedure successfully completed.

SQL> print x

         X
----------
         1

SQL> 
SQL> grant analyze any to test;

Grant succeeded.

SQL>
SQL> conn test/test
Connected.
SQL> 
SQL> show user
USER is "TEST"
SQL> var x number
SQL> 
SQL> exec dbms_space.free_blocks('SYS','TEST_CLUSTER','CLUSTER', 0, :x);
BEGIN dbms_space.free_blocks('SYS','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


SQL> 
SQL> disc
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
SQL>  

Tom Kyte

Followup  

September 11, 2004 - 10:34 am UTC

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).

We are really blessed to have you ...

September 12, 2004 - 10:56 pm UTC

Reviewer: VKOUL from LACEY, WA USA

Great way to know about a init.ora parameter.

What are the FS1 to FS4?

October 10, 2004 - 2:23 pm UTC

Reviewer: Zohar from Raanana, Israel

I've looked (and created) the Show_Space utility for the LTM but couldn't find an explaination for what the new lines stnads for:
What are the "FS1(2,3,4) Blocks" ?
what are "Unformatted Blocks"?

Thanks!
Zohar


Tom Kyte

Followup  

October 10, 2004 - 6:32 pm UTC

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.

Another question

October 11, 2004 - 7:01 am UTC

Reviewer: Zohar from Raanana, Israel

I've seen you answered on it before but I still don't get it:
How come Total Blocks does not equal Unformatted Blocks + FS1 Blocks + FS2 Blocks + FS3 Blocks + FS4 Blocks + Full Blocks + System Block (in this example, I have 660 blocks left).

SQL> call show_space('TCOM_PERFORMANCE_DATA');
Unformatted Blocks .....................             784
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................             225
Full Blocks        .....................         172,410
Total Blocks............................         174,080
Total Bytes.............................   1,426,063,360
Total MBytes............................           1,360
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................         317,576
Last Used Block.........................           2,048

Thanks again,
Zohar. 

Tom Kyte

Followup  

October 11, 2004 - 8:23 am UTC

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

I have this problem with DBMS_SPACE

October 19, 2004 - 10:04 am UTC

Reviewer: Hans Wijte from The Netherlands

Hi Tom,

=>sqlplus system@ek_plshd

SQL*Plus: Release 8.1.7.0.0 - Production on Tue Oct 19 15:30:14 2004

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Enter password: 

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> select name from v$database;

NAME
---------
EK_PLSHD

SQL> show user
USER is "SYSTEM"
SQL> column object_name format a30
SQL> column object_type format a30
SQL> set lines 120
SQL> select object_name, object_type, status, created, last_ddl_time
  2    from dba_objects
  3   where object_name = 'DBMS_SPACE'
  4  /

OBJECT_NAME                    OBJECT_TYPE                    STATUS  CREATED   LAST_DDL_
------------------------------ ------------------------------ ------- --------- ---------
DBMS_SPACE                     PACKAGE                        VALID   03-JUN-04 19-OCT-04
DBMS_SPACE                     PACKAGE BODY                   VALID   03-JUN-04 19-OCT-04
DBMS_SPACE                     SYNONYM                        VALID   03-JUN-04 19-OCT-04

SQL> desc dbms_space
PROCEDURE FREE_BLOCKS
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 FREELIST_GROUP_ID              NUMBER                  IN
 FREE_BLKS                      NUMBER                  OUT
 SCAN_LIMIT                     NUMBER                  IN     DEFAULT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT
PROCEDURE UNUSED_SPACE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SEGMENT_OWNER                  VARCHAR2                IN
 SEGMENT_NAME                   VARCHAR2                IN
 SEGMENT_TYPE                   VARCHAR2                IN
 TOTAL_BLOCKS                   NUMBER                  OUT
 TOTAL_BYTES                    NUMBER                  OUT
 UNUSED_BLOCKS                  NUMBER                  OUT
 UNUSED_BYTES                   NUMBER                  OUT
 LAST_USED_EXTENT_FILE_ID       NUMBER                  OUT
 LAST_USED_EXTENT_BLOCK_ID      NUMBER                  OUT
 LAST_USED_BLOCK                NUMBER                  OUT
 PARTITION_NAME                 VARCHAR2                IN     DEFAULT

SQL> desc show_space
PROCEDURE show_space
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 P_SEGNAME                      VARCHAR2                IN
 P_OWNER                        VARCHAR2                IN     DEFAULT
 P_TYPE                         VARCHAR2                IN     DEFAULT

SQL> select * from dba_tab_privs where table_name = 'DBMS_SPACE';

GRANTEE                        OWNER                          TABLE_NAME
------------------------------ ------------------------------ ------------------------------
GRANTOR                        PRIVILEGE                                GRA
------------------------------ ---------------------------------------- ---
PUBLIC                         SYS                            DBMS_SPACE
SYS                            EXECUTE                                  NO

SQL> select * from dba_tab_privs where table_name = 'SHOW_SPACE';

GRANTEE                        OWNER                          TABLE_NAME
------------------------------ ------------------------------ ------------------------------
GRANTOR                        PRIVILEGE                                GRA
------------------------------ ---------------------------------------- ---
PUBLIC                         SYSTEM                         SHOW_SPACE
SYSTEM                         EXECUTE                                  NO

SQL> exec show_space('ETK_TEXT' , 'DIC' );
BEGIN show_space('ETK_TEXT' , 'DIC' ); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 55
ORA-06512: at "SYSTEM.SHOW_SPACE", line 20
ORA-06512: at line 1


SQL> exit
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production


Apparently there are some privileges on an underlying table or view missing, but I don't know which; recreating the utilities with dbmsutil.sql and prvtutil.plb doesn't help : I keep receiving this error.

Any ideas would be highly appreciated

Best regards

Hans 

Tom Kyte

Followup  

October 19, 2004 - 10:41 am UTC

</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.

Never mind

October 19, 2004 - 10:16 am UTC

Reviewer: Hans Wijte from The Netherlands

Hi Tom,

disregard the previous question; user system missed the "ANALYZE ANY" privilege; finally found it on metalink though I couldn't find it there before

Regards

Hans

To use or not to use ..

November 04, 2004 - 4:26 pm UTC

Reviewer: RD from NZ

Hi Tom,
Thanks to your great explainations I've totally committed myself to using LMT's forever. BUT
I really can't seem to understand:-
1) When to use or when not to use uniform extents or autoallocate.

2) When I use autoallocate the system decides what size extents to allocate for me. This is confusing because it's like system predicting the future. Or not exactly?

3) Autoallocate allocates different extent sizes.Thats back to DMT behaviour. Welcome fragmentation.

Regards,
RD.

Tom Kyte

Followup  

November 05, 2004 - 3:14 pm UTC

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.

dbms_space.unused_space throwing ora-00600 (bummer)

November 05, 2004 - 5:18 pm UTC

Reviewer: Michael from St. Louis, MO

show_space_for was useful, until it stopped working.

Apparently related to bug 2747873, corrected in 10g, but I'm on 9.2

DESCRIPTION: ORA-00600: internal error code, arguments: [kcbgcur_9], [314573068], [4], [4294959056], [16], [], [], [].

Oh well.

Tom Kyte

Followup  

November 05, 2004 - 6:07 pm UTC

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?

Question about your Orig answer

January 06, 2005 - 11:02 pm UTC

Reviewer: A reader

Hello Tom,

You said in your original (first) post
</quote
ops$tkyte@8i> insert into t (x)
2 select rownum
3 from all_users
4 where rownum < 50
5 /

49 rows created.

ops$tkyte@8i> commit;
Commit complete.

So now we have 50 rows with 2k each -- I'm using a 8k blocksize so I expect
about 3 rows / block. That means about 18 blocks of data plus 1 for the system
= about 19 blocks should be "used" now. Below I see that I have

o 3 blocks on the freelist. they have more space for new inserts (they have not
hit their pctused yet and may be inserted into)

o 12 unused blocks, leaving 20 blocks "used". Since I have 3 on the freelist --
we probably used a little more then the 18 for data -- we used 19 for the 50
rows. We have one for the system -- all accounted for.


ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................3
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5

PL/SQL procedure successfully completed.

</unquote

The Question
1)Why does free blocks + unused blocks + 1 system block
not add up to Total blocks ?
3 + 12 + 1 <> 32 ?
In all other examples that follow they do add up to the total blocks?

2) When you say :3 blocks on the freelist. Does that mean out of the used blocks 3 are on the freelist so candidates for inserts ? or these 3 are unused and are candidates for inserts ?

Tom Kyte

Followup  

January 07, 2005 - 8:56 am UTC

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.

why?

January 08, 2005 - 12:43 am UTC

Reviewer: oracle from China

SQL> create table t ( x int, y char(2000) default '*
  2  storage ( initial 40k next 40k minextents 5 )
  3  tablespace system;

&#34920;&#24050;&#21019;&#24314;&#12290;

SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'T'
  4  and segment_type = 'TABLE';

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8

SQL> drop table t;

&#34920;&#24050;&#20002;&#24323;&#12290;

SQL> create table t ( x int, y char(2000) default '*
  2  storage ( initial 40k next 40k minextents 10 )
  3  tablespace system;

&#34920;&#24050;&#21019;&#24314;&#12290;

SQL> select extent_id, bytes, blocks
  2  from user_extents
  3  where segment_name = 'T'
  4  and segment_type = 'TABLE';

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         1      65536          8
         2      65536          8
         3      65536          8
         4      65536          8
         5      65536          8
         6      65536          8

&#24050;&#36873;&#25321;7&#34892;&#12290;

SQL> 

Tom Kyte

Followup  

January 08, 2005 - 4:53 pm UTC

are you using DMT's or LMT's

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

It doesn't seem to work

February 28, 2005 - 3:18 pm UTC

Reviewer: Randy from Redwood City

It seemed to be helpful, but I'm not sure it applies to 9i
and the programs included didn't work.

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.

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

My plan is to configure the DB and call this my set point.
Then I'll run N simulations, each one starting at the set point
and changing a different one of my N variables, and measure the 
database size at the end.  Assuming the size increases linearly 
in each variable, I can come up with coefficients for the different 
configuration variables.

Showspace just isn't working- the details are below.

But is it necessary?  Is there really nothing that will 
just tell me how much space is being used for data?

What's wrong with just doing, for each user:

 select ('TABLES')area, (sum(bytes)/1024)TOTAL_KBytes, 
        (sum(user_extents.blocks))TOTAL_BLOCKS
 from user_extents, user_tables
 where segment_name=table_name and buffer_pool='KEEP'
 union
 select ('INDEXES')area, (sum(bytes)/1024)TOTAL_KBytes,
        (sum(user_extents.blocks))Total_BLOCKS
 from user_extents, user_indexes
 where segment_name=index_name and buffer_pool='KEEP';

Producing results like:
  AREA    TOTAL_KBYTES TOTAL_BLOCKS
  ------- ------------ ------------
  INDEXES          512           64
  TABLES          2240          280

Is this counting free space as well, which will 
over-estimate the space needed as the database grows?
Or is this missing some of the space required, such
as for views?

Do I just need to get show_space working and change
the table-name to cover all tables and indexes?
Thanks, Randy

====
I'm running Oracle9i Enterprise Edition Release 9.2.0.4.0
I'm using the showspace from the article above that you find
if you search for "-- this procedure uses authid".

I log in as "system"

SQL> create user a identified by a;

User created.

SQL> grant create session, create procedure, create table to a;
Grant succeeded.

SQL> grant analyze any to a;
Grant succeeded.

SQL> alter user a quota unlimited on users;
*
ERROR at line 1:  <=================== ERROR
ORA-00959: tablespace 'USERS' does not exist

SQL> connect a/a
Connected.

SQL> @showspace

Procedure created.

SQL> create table t ( x int ) tablespace users;
Table created.


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

====
 

Tom Kyte

Followup  

February 28, 2005 - 3:23 pm UTC

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.

Thanks much!

February 28, 2005 - 5:40 pm UTC

Reviewer: Randy from Redwood City

That'll do me quite nicely. Thanks, -r

March 02, 2005 - 3:31 pm UTC

Reviewer: A reader

Hi Tom

ops$tkyte@8i> exec show_space( 'T' )
Free Blocks.............................3
Total Blocks............................32
Total Bytes.............................262144
Unused Blocks...........................12
Unused Bytes............................98304
Last Used Ext FileId....................1
Last Used Ext BlockId...................64681
Last Used Block.........................5


What is this "Last Used Block" in the output ? If it is some block id how is it different from "Last Used Ext BlockId" ?

thanks
Regi

Tom Kyte

Followup  

March 02, 2005 - 5:24 pm UTC

did you check out the docs?

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




getting closer

March 04, 2005 - 5:30 pm UTC

Reviewer: Randy Strauss from Redwood City, CA, USA

Counting bytes from segments is too rough.
I need to not include the free space.

show_space is now working!  I finally figured out how
to grant a privelege for the user to see the sys* views,
and that I need to do
 SQL> exec begin dbms_output.enable( 1000000 ); end
 SQL> set serveroutput on
So I can find out the number of bytes in used segments
for TABLEs and INDEXs.  

But in the dba_segments table are also 
LOBINDEX and LOBSEGMENTs and though I see them
in DBA_SEGMENTS, when I run
  exec show_space('SYS_IL0000008976C00012$$', 'LOBINDEX');
I get an error:  
  the segment type specification is invalid

These LOBs are in a MANUAL tablespace- all my tablespaces are manual.
(I get the same error if I change the logic to route this call
 through the dbms_space.space_usage(...) call...)

Is there a way to know which of the LOB segments are actually used?  

Do I have to use auto mgmt?
( ALTER TABLESPACE foo AUTO ?)

But the manual says:

oracle9i/server.901/a90125/statements_73.htm#2078567
  You cannot specify a tablespace with automatic segment-space
  management if table contains any LOB columns.

oracle9i/server.901/a90125/statements_74a.htm#2075333
  You cannot store LOBs in AUTO segment-managed tablespaces

oracle9i/server.901/a90125/sql_elements2.htm#68007
  A table with LONG columns cannot be stored in a tablespace 
  with automatic segment-space management.

We use "long raw" columns (not my design...)

Thanks, -r 

Tom Kyte

Followup  

March 04, 2005 - 6:24 pm UTC

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.

Isn't estimating DB size something we all must do?

March 04, 2005 - 7:34 pm UTC

Reviewer: Randy Strauss from Redwood City, CA, USA

> the goal.. sizing for a install or growth

Our system has mainly 3 types of transactions, to perform and log, each of which stores different kinds of things in the database. I want to make a spreadsheet that takes the number of each type of transaction that will occur in a day and outputs an estimate of how much storage it'll require so the customer can predict storage costs over, say, two years.

Each of A, B, C will occur anywhere from 1M to 200M times in a year.

The customer has large, fast, expensive machines to handle this kind of load- maybe 50x more powerful than my machine. I need to run my simulations in an hour (or a day), so instead of milliona of transactions, I need to estimate after running a few hundred or a few thousand. 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.

My current strategy is to use show_space on every table and index owned by the user to watch the amount used grow over time, multiply it by some fudge factor like 1.5 and add some constant like 10G to it and call it a best effort.

When I watched the sum(bytes), and ran 50 transactions at a time, 10 times, sometimes the sum jumped by 100 Kb, sometimes it stayed the same and once it jumped by 1000Kb! The error was way too much for me to have a clue what it would be like over the long term.

Watching used space, ala show_space, it starts at 8Mb and the deltas are 592, 472, 408, 24, 520, 384, 448kb, so far- much better behaved. Why it needs 205Mb to store this 8-10Mb of data, and why the total number of bytes has grown by 20Mb over these runs bothers me. Hopefully when I graph the total bytes and the total used bytes I'll discover that Oracle is just keeping 200Mb of buffers and caches, rather than just 20x the space used.

You say free blocks are used... Do you mean a free block is any block that has some free space in it? I guess that would make sense- a faster way to find space to put something... Can I find out the pct-free? If all I'm doing is inserting, is there any chance that it'll just use up each block uniformly?

This just seems like a problem that every DB app has. It's strange that there's no simple function to estimate the size of the database- the amount used for data and an average amount of free space atop that, so a customer can do some budgeting. Or maybe a way to tell it be conservative with space so sum(bytes) will be more accurate?

We have to give customers some sort of estimate...

Thanks, -r


Tom Kyte

Followup  

March 04, 2005 - 8:21 pm UTC

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.

No silver bullet?

March 04, 2005 - 10:09 pm UTC

Reviewer: Randy from Redwood City, CA, USA

The questions were not insulting. The answers were mostly "no". I've read probably 25 AskTop articles so far as well as mostly intro-to-sql books and used a few books incl one oracle reference that had mainly facts w/o the wisdom. I've picked up a lot, but it still took me a long while to figure out how to get my user to view the sys tables.

I don't think any of us in my group know much about these things. I am supposed to add to our estimate spreadsheet, but my sense is that what was there is wrong. I'll order the book, but that's not going to help short-term. And I'll read about the questions. Any more that you want to leave me with are welcome. The values of our SBA_Tablespaces.Segment_Space_Management are all MANUAL. (It sounds like AUTO would be a better choice...) I have no idea if the databases in the field are the same.

I have a hard time believing that space is allocated on demand. I'm doing the same thing run after run and sometimes the sum(BYTES) for the lobs doesn't change, and sometimes it goes up by 5Mb. The space for the rest of the tables goes up almost always by about 400kb, and sometimes only 24kb. I'm doing the exact same thing each run, and the application should be, too.

Thanks much for all the answers, and questions, and spending part of your afternoon/eve with me.
-r

Tom Kyte

Followup  

March 05, 2005 - 6:53 am UTC

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).

multiple freelists ....

March 05, 2005 - 10:29 pm UTC

Reviewer: John

If you have more than one freelist defined for a table, does this dbms_space.free_space proc show blocks on each freelist? That would be great. Do you have any example? Thanks.

Total Blocks

May 21, 2005 - 5:11 am UTC

Reviewer: Mo from Malaysia

Hi Tom,

   Just wonder why the total BLOCKS+NUM_FREELIST_BLOCKS+EMPTY_BLOCKS (39+5+0) from dba_tables is not equal to the total BLOCKS (40) in dba_segments. I compute the statistics for the table before I select. Thanks.

SQL> select BLOCKS,NUM_FREELIST_BLOCKS,EMPTY_BLOCKS from dba_tables
  2  where table_name ='EMP'
  3  and owner='XX';

    BLOCKS NUM_FREELIST_BLOCKS EMPTY_BLOCKS
---------- ------------------- ------------
        39                   5            0

SQL> select blocks from dba_segments
  2  where segment_name='EMP'
  3  and owner='XX';

    BLOCKS
----------
        40

Rgds
Mo 

Tom Kyte

Followup  

May 21, 2005 - 8:53 am UTC

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



Regarding Reorganizing Table

May 26, 2005 - 9:18 am UTC

Reviewer: Kamal from Chennai, INDIA

Hi Tom

I read in this site that the best way to reset the HWM is to use ALTER TABLE T MOVE.
does this statement has any drawback like whether any related object of the table will become invalid or something like that...so that we need to take care of them afer executing the statement

Tom Kyte

Followup  

May 26, 2005 - 9:57 am UTC

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)

reader

June 24, 2005 - 2:22 pm UTC

Reviewer: A reader

Any idea why I am getting following error message
It is 9.2 and I am executing this logged on as sysdba

SQL> exec show_space('TABLE1','USER1','TABLE');
BEGIN show_space('TABLE1','USER1','TABLE'); END;
 
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "SYS.SHOW_SPACE", line 22
ORA-06512: at line 1
 

How the heck ... ???

July 05, 2005 - 2:57 pm UTC

Reviewer: Greg from Toronto

Ok, I'm pulling my hair out here, searched the documentation, can't seem to find anything that helps ... (I'm probably blind ... but hey .. glasses are still on backorder .. heh)

I ran this function like so:

select show_space_for ( 'tt' ) from dual;
------more------

SHOW_SPACE_FOR('TT')(OWNER, SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE, FREE_BLO
--------------------------------------------------------------------------------
SHOW_SPACE_TABLE_TYPE(SHOW_SPACE_TYPE('GREGS', 'TT', NULL, 'TABLE', 1, 5, 3, 2,
129866, 2))


1 row selected.

Gravy ... but what I really need is to "parse" it a bit ... and I can't seem to remember how to do that ...

That is, something like this:

select abc.col1.free_blocks
from ( select show_space_for('tt') col1 from dual ) abc
/

abc.col1.free_blocks
--------------------
1

.. of course, that doesn't work ... but I know there's a way to rip the data out of there ... I just can't seem to find anything that tells how to do it .. *sigh*

Help?


Tom Kyte

Followup  

July 05, 2005 - 3:14 pm UTC

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  / 

A right ... my bad ... forgot to mention ...

July 06, 2005 - 7:33 am UTC

Reviewer: Greg from Toronto

heh .. Oracle 8i ... *blush* ... yeah, that query in the last response just tosses out an irritating:

ERROR at line 3:
ORA-22905: cannot access rows from a non-nested table item

Is it possible in 8i?? Or do I have to wrap a funky-function around it to parse it?? (I can do that ... was just hoping there was an easier way that I was missing .. )

Well - that being said that the "easiest" way is upgrading to O9 .. *sigh* .. (we're working on it .. heh)


Tom Kyte

Followup  

July 06, 2005 - 8:03 am UTC

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 :) 

Thanks, Tom!!

July 06, 2005 - 8:57 am UTC

Reviewer: Greg from Toronto

Your patience is never ending ... Yes, I am blind ... (but I blame the fact I'm excessively tired from laying down Patio stones and such over the last couple weeks ..) :p

cast .... cast .... cast .... cast
I will not forget cast ....
(Hey, anyone got a hammer?? I think I need to beat this into my skull a bit more .. )

Thanks muchly .. sorry to bug ya ... :\


Shouldn't this work???

July 13, 2005 - 3:11 pm UTC

Reviewer: Robert Ware from St. Louis

Tom,

Thank you for the tool Show_space_for, it works great in most of my instances. However, I have discovered a problem with it in a 9.2.0.6 instance where the character set is al32utf8

Here is what I see:

qa> @zzz
qa> select owner,
2 segment_name,
3 segment_type,
4 partition_name
5 from dba_segments
6 where segment_name ='D_WEEKLY_POS_INVEN_AGGR'
7 and owner = 'POSDM'
8 and segment_type = 'TABLE SUBPARTITION'
9 and partition_name = 'P200522_NPPC_ABCINC_USA'
10 /
Press <RETURN> To Continue...

OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
-------- ------------------------------ ------------------ ------------------------------
POSDM D_WEEKLY_POS_INVEN_AGGR TABLE SUBPARTITION P200522_NPPC_ABCINC_USA

1 row selected.

qa> select SEGMENT_NAME, SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
2 from table( show_space_for( 'D_WEEKLY_POS_INVEN_AGGR','POSDM','TABLE SUBPARTITION','P200522_NPPC_ABCINC_USA' ) )
3 /

no rows selected

qa>

Am I doing something wrong?




Tom Kyte

Followup  

July 13, 2005 - 3:23 pm UTC

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.

How do you see the dbmsoutput from a PipeLined Function?

July 13, 2005 - 3:57 pm UTC

Reviewer: A reader


Tom Kyte

Followup  

July 13, 2005 - 4:47 pm UTC

exec null;

after you run it.

It is clear now

July 13, 2005 - 5:28 pm UTC

Reviewer: Robert Ware from St. Louis mo.

Tom,

After placing a couple of dbms_outputs in the function I decided to change the
when others then null;
to a
when others then raise;
and that did it for me.
Here is the problem I was having.

from table( show_space_for( 'D_WEEKLY_POS_INVEN_AGGR','POSDM','TABLE SUBPARTITION','P200522_NPPC_ABCINC_USA' ) )
*
ERROR at line 7:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "RDWARE.SHOW_SPACE_FOR", line 87
ORA-06512: at line 1

unix048@sposqa/home/rdware/dba_sql> oerr ora 10618
10618, 00000, "Operation not allowed on this segment"
// *Cause: This DBMS_SPACE operation is not permitted on segments in
// tablespaces with AUTO SEGMENT SPACE MANAGEMENT
// *Action: Recheck the segment name and type and re-issue the statement
unix048@sposqa/home/rdware/dba_sql>



Did you know this was the case with ASSM?

Thanks,
Robert


Tom Kyte

Followup  

July 14, 2005 - 9:52 am UTC

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.

dbms_space.free_space

July 26, 2005 - 10:23 am UTC

Reviewer: sasikanth from india

That really was helpful in finding the exact space usage.

Thanks for the tips

automation script

August 15, 2005 - 11:04 am UTC

Reviewer: phani marella from Louisville KY USA

. $HOME/.sql $1
. /hdg1/9.2.0/HDG1.env
sqlplus -s $gconnectionfo <<!
@$2
exit;
!

plz review this runscript. I am trying to automate sql script through this script. probably schedule the job(which i am not worried about). There are 3 parameters 1.sql file name
2.environment(example: databases like hdg1,hd11,hdq1....etc.)
3.output file name
.......................

reader

August 19, 2005 - 1:21 pm UTC

Reviewer: A reader

SQL> exec show_space('TABLE1','USER2','TABLE');
BEGIN show_space('TABLE1','USER2','TABLE'); END;
 
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "USER1.SHOW_SPACE", line 22
ORA-06512: at line 1

The procedure show_space is created by "USER1"
The procedure works well for tables created by "USER1"
What permission will I need if I want to use this procedure
on objects owned by another schema ("USER2") 

Tom Kyte

Followup  

August 20, 2005 - 4:33 pm UTC

did you use authid current_user?

ctl-f for authid

reader

August 19, 2005 - 3:22 pm UTC

Reviewer: A reader

Never mind about theprevious posting

authid current_user fixed it

reader

August 21, 2005 - 6:15 pm UTC

Reviewer: A reader

In the above posting, the show_space was created by "USER1"
and was executed by "USER1". Will not the current_user and
definer be same. If so how does authid current_user make the differnece

Tom Kyte

Followup  

August 21, 2005 - 7:26 pm UTC

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

Confused

October 04, 2005 - 11:53 pm UTC

Reviewer: Aru from NZ

Hi Tom,
I am using 9i R2 & LMT's,
When I do :-
SQL> create table t(x int, y char(2000) default '*') storage (initial 40K next 40K ) tablespace syst
em;

Table created.

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

But when I do:-

  1* create table t(x int, y char(2000) default '*') storage (initial 40K next 40K minextents 5) tab
SQL> /

Table created.

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

also when I do:-

SQL> create table t(x int, y char(2000) default '*') storage (initial 40K minextents 5) tablespace s
ystem;

Table created.
SQL> select extent_id,bytes,blocks from dba_extents where segment_name='T';

 EXTENT_ID      BYTES     BLOCKS
---------- ---------- ----------
         0      65536          8
         0    1048576        128
         1    1048576        128
         2    1048576        128
         3    1048576        128
         4    1048576        128
         0      65536          8
         0      65536          8

8 rows selected.

Why is there so much difference in the output's. Also I always thought that an extent_id is always unique. But it is not as I see from the extent_id columns, why multiple extent_id of 0???.
I am trying to learn about dbms_space and show_space but did'ent get past this. Please help Tom,
Regards,
Aru.


 

Tom Kyte

Followup  

October 05, 2005 - 7:09 am UTC

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.. 

what is dba_segments_old in 10g?

October 06, 2005 - 6:57 pm UTC

Reviewer: Ryan from northern virginia

OTN has the exact same spec as for DBA_SEGMENTS?

</code> http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4098.htm <code>

The only difference I see(from querying the view) is that dba_segments_old tracks the increases in bytes used by segments that we had before we upgraded to 10g. The regular DBA_SEGMENTS view has not increased the number of bytes used by those segments. It only increments when we create a segment(table, etc...).

For example, If I do

select sum(bytes)
from dba_segments
where owner = '<data user>'

If these tables were created prior to upgrading to 10G and I insert rows to them, the amount of data shown in DBA_SEGMENTS does not increase.

However, it does increase if I query the bytes column in DBA_SEGMENTS_OLD

Now the peculiar thing is that if I have a user with 10MB of segment space used and then I upgrade to 10g, then create a new table that uses 1 MB of storage, DBA_SEGMENTS increases by 1 MB and keeps increasing when the new table uses more space. But it appears to be:

Bytes in Segments from before upgrade + bytes in segments created after upgrade.

any idea why oracle did this?


Tom Kyte

Followup  

October 06, 2005 - 7:33 pm UTC

can you see bug #2948717 in metalink?

Not quite what I was looking for...

October 07, 2005 - 11:17 am UTC

Reviewer: Ryan from Northern Virginia

If I am not supposed to post the note, please delete it. I don't know the rules.

The problem isn't that the query is slow. The problem is that the changes in bytes of segments created prior to migration to Oracle 10G are not visible in DBA_SEGMENTS. They are only visible in DBA_SEGMENTS_OLD.

Let me provide a better example. Appears to be a miscommunication.

ORACLE 9i
User: RALPH
Table: MYTABLE
SEGMENT SIZE in Bytes: 10 MB.

I upgrade to Oracle 10G.
I insert 1 MB of data to RALPH.MYTABLE

I query DBA_SEGMENTS, it says that RALPH.MYTABLE has 10 MB of data. However, if I query the segment in DBA_SEGMENTS_OLD it says that RALPH.MYTABLE has 11 MB.

Now if I create a new table in RALPH called MYTABLE10G and insert 1 MB of data. DBA_SEGMENTS shows this table has 1 MB of data AND it shows that MYTABLE still has 10 MB of data.

What is DBA_SEGMENTS_OLD created for? I am having trouble reading the query definition and trying to figure out the difference? Why did Oracle do this? Looks like how Oracle tracks segments sizes has changed somewhat from 9i to 10G?

I might stick this on Dizwell also. See if anyone else knows... Hope that is ok.

Bug 2948717 Queries against DBA_SEGMENTS can be slow accessing BYTES/BLOCKS/EXTENTS columns
This note gives a brief overview of bug 2948717.

Affects:
Product (Component) Oracle Server (Rdbms)
Range of versions believed to be affected Versions >= 9 but < 10.1.0.2
Versions confirmed as being affected 9.2.0.4

Platforms affected Generic (all / most platforms affected)

Fixed:
This issue is fixed in 9.2.0.5 (Server Patch Set)
10.1.0.2


Symptoms: Related To:
Performance Of Query/ies Affected
Space Management (Locally Managed Tablespaces)
DBA_SEGMENTS


Description
Queries against DBA_SEGMENTS can be slow accessing any "SIZE"
column (ie: BYTES, BLOCKS or EXTENTS) when the database contains
locally managed tablespaces.

The poor performance occurs as for locally managed tablespaces
disk I/O is needed for each and every segment to get the count
of extents and blocks.

Workaround:
Use dictionary managed tablespaces
or
Do not include BYTES, BLOCKS or EXTENTS in queries.

Note:
With this fix in place you must run
dbms_space_admin.tablespace_fix_segment_extblks(<tablespace name>);
to set the dictionary data for a given tablespace to avoid access
to each segment on queries for segments which existed prior to this
fix.

eg:
set serveroutput on
BEGIN
for i in (select tablespace_name ts from dba_tablespaces ) loop
begin
dbms_output.put('Fixing '||i.ts||' ... ');
dbms_space_admin.TABLESPACE_FIX_SEGMENT_EXTBLKS(i.ts);
dbms_output.put_line('Fixed.');
exception
when others then
dbms_output.put_line(dbms_utility.FORMAT_ERROR_STACK);
end;
end loop;
end;
/

Note2:
This fix does not work for segments migrated from 8i and
not having had space management occur against them.
TABLESPACE_FIX_SEGMENT_EXTBLKS does not update dictionary
data for such segments. See bug 4047545 .



Tom Kyte

Followup  

October 07, 2005 - 9:53 pm UTC

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)

October 07, 2005 - 12:14 pm UTC

Reviewer: A reader

How can we get the size of actual (want to exclude the free space within each block too ) data contents in each segmenet?



Tom Kyte

Followup  

October 07, 2005 - 9:55 pm UTC

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.

October 07, 2005 - 1:08 pm UTC

Reviewer: A reader

How to get the total database size of a database excluding
the empty blocks (exclude blocks which doesn't have any data ) ?

Tom Kyte

Followup  

October 08, 2005 - 8:05 am UTC

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

October 07, 2005 - 4:25 pm UTC

Reviewer: A reader

To calculate the actual data size of the whole database,
can we do some thing like

select (a.total - b.free - " ? " ) "data gig" ,total ,free
from (select sum(bytes)/1024/1024/1024 total from dba_data_files) a,
(select sum(bytes) /1024/1024/1024 free from dba_free_space) b ;

Where "?" is the total of unused blocks from dbms_space procedure for all database objects.

Is the following is the best way to calculate unused blocks?

Set serverout on
declare
cursor c1 is
select owner,segment_name ,decode (segment_type,'LOBSEGMENT','LOB','LOBINDEX','LOB',segment_type )
from dba_segments
where owner = 'X';
l_total_block 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_total_unused_blocks number := 0;
l_total_blocks number :=0;

begin
for c1_rec in c1 loop
dbms_space.unused_space
( segment_owner => c1_rec.owner,
segment_name => c1_rec.segment_name,
segment_type => c1_rec.segment_type,
partition_name => null,
total_blocks => l_total_block,
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_total_unused_blocks := l_total_unused_blocks + l_unused_blocks;
l_total_blocks := l_total_blocks + l_total_block;
end loop;
dbms_output.put_line('unused blocks :'|| l_total_unused_blocks * 8 /1024/1024 || 'gig');
end;
/



Tom Kyte

Followup  

October 08, 2005 - 8:48 am UTC

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"

October 10, 2005 - 8:57 am UTC

Reviewer: A reader

"what is the goal here. "

Thanks! Actually we want to create a development instance out of the production one and manager wants to knows the actual data volume (after removing unallocated space from tablespaces & removing the unused space with each segment) in production.


Tom Kyte

Followup  

October 10, 2005 - 9:06 am UTC

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.

October 10, 2005 - 9:42 am UTC

Reviewer: A reader

Production tables are all analyzed in ou case. Did you mean to calculate sum of (blocks - empty_blocks) ?

Can we use sum(avg_row_len * num_rows) to get teh actual data volume estimate ?

Tom Kyte

Followup  

October 10, 2005 - 10:56 am UTC

sum(avg_row_len * num_rows)

that would woefully UNDERESTIMATE BIG TIME the actual size needed.


Just calculate SUM(BLOCKS).

October 10, 2005 - 10:27 am UTC

Reviewer: A reader

Thanks! How to calculate the actual current space for the below kind of tables as we know free list blocks are not entirely empty?

a) had 1000000000 rows
b) now have 1 row


Tom Kyte

Followup  

October 10, 2005 - 11:23 am UTC

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

October 10, 2005 - 11:05 am UTC

Reviewer: A reader

But sum(blocks) includes the blocks above the high water mark and we want to exclude them in the size estimate.

Tom Kyte

Followup  

October 10, 2005 - 11:28 am UTC

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.

October 10, 2005 - 4:35 pm UTC

Reviewer: A reader

Thanks Tom for reminding me! As usual you are the best!

How to find space occupied by a subpartition?

October 12, 2005 - 5:04 am UTC

Reviewer: karmit from UK

Hi,
Is there a way to find the space occupied by a subpartition?
I'm not interested in actual space occupied by data, but just by the amount of space which will be freed if that particular subpartition is dropped.
Thanks,
Karmit

Tom Kyte

Followup  

October 12, 2005 - 7:28 am UTC

user_segments dba_segments, either of those

find space usage of subpartitions..

October 12, 2005 - 10:19 am UTC

Reviewer: karmit from UK

Hi,

Unless I'm doing something wrong - user_segments is not 
showing me the size!

In my Database (10G), I have...

SQL> SELECT SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS;

SUBPARTITION_NAME
------------------------------
RUN5_PART1
RUN5_PART2
RUN5_PART3
RUN5_PART4
RUN5_PART5
RUN5_PART6
RUN5_PART7
RUN5_PART8
RUN6_PART1
RUN6_PART2
RUN6_PART3

SUBPARTITION_NAME
------------------------------
RUN6_PART4
RUN6_PART5
RUN6_PART6
RUN6_PART7
RUN6_PART8
RUN7_PART1
RUN7_PART2
RUN7_PART3
RUN7_PART4
RUN7_PART5
RUN7_PART6

SUBPARTITION_NAME
------------------------------
RUN7_PART7
RUN7_PART8
RUN8_PART1
RUN8_PART2
...
and so on...

then I issue...

SQL> select sum(bytes) sizeb from USER_segments where segment_name LIKE 'RUN%' and segment_type='TABLE SUBPARTITION';

     SIZEB
----------


which gives nothing!


however, found another way to get the size..

dbms_space.unused_space (<tableowner>,<tablename>,'TABLE SUBPARTITION',v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block,<subpartname>);

this works.


I suspect I might need to give the name of the table instead of the name of the subpartition in the query to user_segments.. but then how do I find the size of the individual subparts? ... dbms_space is better I guess?

Thanks,
karmit 

Tom Kyte

Followup  

October 12, 2005 - 2:04 pm UTC

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. 

It takes a long time for calculating free space for some tables

October 26, 2005 - 7:08 am UTC

Reviewer: Bart from Berkel

I use your free_space script for gathering and storing free_space info of a whole schema.

Now there is one table with millions of records (by far not the greatest) for which free_Space takes almost an hour to calculate the free space information.
This is reproducable.

Oracle 9iR2

I don't know what kind of information you would like to have, to tell something about it.

This is the structure:
cocoxfrm@ COCATO1> desc alg_batch_Details
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER(14)
BAR_ID NOT NULL NUMBER(14)
VOLGNR NOT NULL NUMBER(9)
CODE_VERWERKING NOT NULL VARCHAR2(4)
REGEL VARCHAR2(2000)
VOLGNR_VERWERKING NOT NULL NUMBER(9)
CREATIE_BRON_ID NUMBER(14)
CREATIE_MOMENT DATE
CREATIE_DOOR NUMBER(14)
LAATSTE_MUTATIE_BRON_ID NUMBER(14)
LAATSTE_MUTATIE_MOMENT DATE
LAATSTE_MUTATIE_DOOR NUMBER(14)




Tom Kyte

Followup  

October 26, 2005 - 12:22 pm UTC

assm or manual segement space managed?

number of extents?

database version down to 4 digits?

platform?

number of freelists? freelist groups?

Answer followup

October 27, 2005 - 5:20 am UTC

Reviewer: Bart from Berkel

I think I have covered most information requests.

The machine is a IBM AIX, I'm not able to find out the exact type/os version right now.



SQL*Plus: Release 9.2.0.1.0 - Production on Thu Oct 27 08:23:21 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production



Table info

TABLE_NAME ALG_BATCH_DETAILS
TABLESPACE_NAME OZNDATA_L
PCT_FREE 10
PCT_USED 40
INI_TRANS 1
MAX_TRANS 255
INITIAL_EXTENT 14909440
NEXT_EXTENT 134217728
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE 0
FREELISTS 1
FREELIST_GROUPS 1
LOGGING NO
BACKED_UP N
NUM_ROWS 13966308
BLOCKS 820774
EMPTY_BLOCKS 3867
AVG_SPACE 1756
CHAIN_CNT 0
AVG_ROW_LEN 251
AVG_SPACE_FREELIST_BLOCKS 0
NUM_FREELIST_BLOCKS 0
DEGREE 1
INSTANCES 1
CACHE N
TABLE_LOCK ENABLED
SAMPLE_SIZE 3491577
LAST_ANALYZED 16-10-2005 9:37:20
PARTITIONED NO
TEMPORARY N
SECONDARY N
NESTED NO
BUFFER_POOL DEFAULT
ROW_MOVEMENT DISABLED
GLOBAL_STATS YES
USER_STATS NO
SKIP_CORRUPT DISABLED
MONITORING YES
DEPENDENCIES DISABLED
COMPRESSION DISABLED



Tablespace info

TABLESPACE_NAME OZNDATA_L
BLOCK_SIZE 16384
INITIAL_EXTENT 134217728
NEXT_EXTENT 134217728
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE 0
MIN_EXTLEN 134217728
STATUS ONLINE
CONTENTS PERMANENT
LOGGING LOGGING
FORCE_LOGGING NO
EXTENT_MANAGEMENT LOCAL
ALLOCATION_TYPE UNIFORM
PLUGGED_IN NO
SEGMENT_SPACE_MANAGEMENT MANUAL
DEF_TAB_COMPRESSION DISABLED




dba_segments

SEGMENT_NAME ALG_BATCH_DETAILS
PARTITION_NAME
SEGMENT_TYPE TABLE
TABLESPACE_NAME OZNDATA_L
HEADER_FILE 44
HEADER_BLOCK 106501
BYTES 13555990528
BLOCKS 827392
EXTENTS 101
INITIAL_EXTENT 14909440
NEXT_EXTENT 134217728
MIN_EXTENTS 1
MAX_EXTENTS 2147483645
PCT_INCREASE 0
FREELISTS 1
FREELIST_GROUPS 1
RELATIVE_FNO 44
BUFFER_POOL DEFAULT

Tom Kyte

Followup  

October 27, 2005 - 6:54 am UTC

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

How can I do this?

February 24, 2006 - 11:55 pm UTC

Reviewer: Reader from Pittsburgh, PA

Tom,

I looked at your show_space procedure and I was wondering if I can use that for our particular situation.
We want to find out how much space could be saved/reclaimed if a developer ran some DELETE scripts on a particular schema. This schema has over 800 tables with at least 1 index/table with all sorts of referential integrity (FK constraints). So his DELETE script would trigger a chain of DELETEs. Note that these deletes would be based on the data value. e.g. DELETE <table> where effective_date < '01-JAN-2004'.

I was going to suggest the developer that I would run your show_space procedure on each table in this schema. Add Free Blocks and Unused Blocks for each table in a for loop. This would give us how much space is free(i.e. candidate for future INSERTs). Then he would run his DELETE script. After that I would run the show_space procedure again on all the tables, add Free Blocks and Unused Blocks. Subtract the before-sum from after-sum and that would give us how much space can be saved by his DELETE scripts. Of course, we would have no way to find how much space is available for future UPDATEs. But he doesn't care about the any future UPDATEs.

Do you think this solution would work to find what he is looking for? How long can it take to run show_space on 800 tables?

Another DBA suggested that we do export of just that schema, record the dump file size. Then let the developer run his DELETE script, then do another similar export and the difference between these 2 dump file sizes would give us how much space could be saved/reclaimed from his DELETEs.

Which solution is better? Do you have any third solution?

Thanks


Tom Kyte

Followup  

February 25, 2006 - 11:13 am UTC

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)

February 25, 2006 - 4:39 pm UTC

Reviewer: Reader from Pittsburgh, PA

Tom,
I am confused. You just said to someone in this thread above,
"sum(avg_row_len * num_rows)
that would woefully UNDERESTIMATE BIG TIME the actual size needed"
Maybe I am misunderstanding, but isn't that what you are telling me to do?

Let me see if I understand these concepts correctly:
a) Free Blocks would give me blocks on the freelist. They are not completely free. In other words, they are the candidates for future INSERTS. Am I correct?
b) So if I include them in my space estimate, the only thing I will be missing is the including the space that will be used by future UPDATEs. And I am okay with that.
c) So, what's wrong with what I suggested first? Is it because calling "show_space" would take too much time for each table?

The main aim is to sum up 2 things to find the total free space:

1) all the space above HWM.(which is all free).Unused Blocks/bytes would give me that.
2) all the 'free' space below HWM.(This is divided into 2 categories:
a) space for future INSERTs(which is blocks on free list)
b) space for future UPDATEs(which I don't care about, or should I?)

Please correct me if I am wrong. Also, what do you think about exporting it twice to check this? Analyze tables would take much longer after the massive purge? EXPORT with DIRECT=Y took only 10 mins.

Thanks.

Tom Kyte

Followup  

February 26, 2006 - 10:00 am UTC

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)"


February 26, 2006 - 2:04 pm UTC

Reviewer: Reader from Pittsburgh, PA

You are correct! I was wrong in my assumption. Just because a block is on a freelist doesn't mean the whole block is free. Only blocks with percent used below the PCTUSED number is free in that block and I can't calculate block by block how much is free.
Also, these are not freshly loaded tables. There could have been previous DELETEs happened on the data (I don't know). BLOCKS would give me a good number for above HWM. And there isn't anything that can give me free space below HWM. So the only thing to do is multiply num_rows and avg_row_length. I would add about 2 to avg_row_length for overhead? And I would get what I want.
Thanks for your help!


ORA-28604

March 15, 2006 - 10:13 am UTC

Reviewer: Anil from Mumbai, India

Tom,
Thanks for all the inputs provided so far.

We have started getting this error in multiple cases. Things like GL Transafer program, Order Organizer and Order Import programs. I found that there are no bit map indexes on those tables. When we did alter table minimize rows_per_block, we got rid of the error. This has become very frequent. Is there any way that we can find it ahead of time and fix it before it pops up?

Please help.

best regards
Anil

Tom Kyte

Followup  

March 15, 2006 - 5:21 pm UTC

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

No Bit Map indexes

March 16, 2006 - 4:06 am UTC

Reviewer: Anil from Mumai, India

Tom,
I have checked it again, but there are no bitmap indexes.

Actually, it would be nice if there is anyway to see if we can predict this error before it comes up! Is it possible at all?

best regards
Anil

Tom Kyte

Followup  

March 16, 2006 - 11:42 am UTC

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.

March 16, 2006 - 12:34 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

>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.

Maybe Bug 1772967 ?

I don't know whether I'm allowed to put the Metalink note here, anyway it seems to apply - the bug is triggered by "a query [that] uses a plan that performs rowid to bitmap conversions" on a "table [that] does not have any bitmap indexes"; one of the workarounds is "run 'alter table <name> minimize records_per_block'" ...

Tom Kyte

Followup  

March 16, 2006 - 2:48 pm UTC

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

free blocks,

June 22, 2006 - 3:55 pm UTC

Reviewer: A reader

Using the show_space procedure which you have provided us for dealing ASSM tablespaces, I didn't see an output for the FREE BLOCKS for the objects that belong to ASSM tablespace.

Is it not possible?

thanks,


Tom Kyte

Followup  

June 22, 2006 - 4:21 pm UTC

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" 

show space interpretation

July 14, 2006 - 7:20 pm UTC

Reviewer: Bob from London, UK

Referring to Tom's interpretation:

"So now we have 50 rows with 2k each -- I'm using a 8k blocksize so I expect about 3 rows / block. That means about 18 blocks of data plus 1 for the system
= about 19 blocks should be "used" now. "

Which is in the top part of the thread. If you have a 8k blocksize, why do you only get 3 (2kB) rows in a block?



Tom Kyte

Followup  

July 15, 2006 - 3:20 pm UTC

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



show space proc

July 16, 2006 - 8:34 am UTC

Reviewer: Bob from London, UK

Thanks Tom - that makes more sense. So with reference to that particular example, if we do a delete operation - that puts the delete blocks back on the freelist. Please can you explain the purpose of a freelist with reference to PCTFREE and PCTUSED.

Also when I create a table, how do I decide what the STORAGE parameters should be if:

a) I am doing a lot of inserts
b) Doing a lot of updates
c) Doing both and deletes.

Thanks

Tom Kyte

Followup  

July 16, 2006 - 9:54 am UTC

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>

PCTFREE and PCTUSED

July 17, 2006 - 5:22 pm UTC

Reviewer: Bob from London, UK

Hi Tom,

Excellent explanation - I think I need to get the book!

Bob

dbms_space

July 19, 2006 - 6:22 am UTC

Reviewer: Bob from London, UK

On this related subject - how do I estimate storage requirements for a table. I know there is a formula out there with can be used for estimation purposes based on the average row length and number of inserted records

Tom Kyte

Followup  

July 19, 2006 - 9:17 am UTC

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



Storage Allocation for a table

July 26, 2006 - 12:20 pm UTC

Reviewer: Bob from London, UK

Hi Tom,

Please could you give an example of how you would allocate storage for a heap based table. In particular, I am interested in knowing how you calculate INITIAL and NEXT in the storage parameters.

Cheers

Tom Kyte

Followup  

July 26, 2006 - 1:33 pm UTC

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.

Table Storage Allocation

July 27, 2006 - 7:10 am UTC

Reviewer: Bob from London, UK

Hi Tom,

Thanks. But suppose you don't have the option of locally managed tablespaces. How would you allocate storage parameters if you had a dictionary managed tablespace?

Cheers

Tom Kyte

Followup  

July 27, 2006 - 11:59 am UTC

You always have the option.

I would not accept anything else.

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

Table storage allocation

July 29, 2006 - 7:22 am UTC

Reviewer: Bob from London, UK

Cheers, Tom for your answer. Have you written any DBA related books. I have your book "Effective Oracle by Design" which is great!



Tom Kyte

Followup  

July 29, 2006 - 9:09 am UTC

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

dbms_space.free_space

September 07, 2006 - 2:54 pm UTC

Reviewer: John Russell from Boston, MA

Thank you very much Tom for a most useful script. This really helped my customer figure out and project growth during data loading. thx JR

March 13, 2007 - 3:34 pm UTC

Reviewer: A reader


difference between 32 and 64 bit Oracle Databases

April 05, 2007 - 11:36 am UTC

Reviewer: Vanitha from India

Hi Tom,
Can you please give me differences between 32-bit and
64-bit Oracle databases.. Thanks in advance.

Regards,
Vanitha

report the change of the size of a table

April 23, 2007 - 4:32 pm UTC

Reviewer: Gordon from Toronto Canada

Hi Tom:

My goal is simple. Application support is deleting records from a mutil-millions records table to clean it up. They want to see the space released.

Normally, I use the following query to give them the size of the tablespace:

SELECT /*+ ordered */
to_char(sum(d.bytes)/1024/1024/1024,'999.99')||' G' whole
, to_char(sum(NVL((d.bytes - s.bytes), d.bytes))/1024/1024/1024,'999.99')||' G' used
, to_char(sum(NVL((d.bytes - s.bytes), d.bytes))/sum(d.bytes)*100,'99.9')||' %' "% Full"
FROM
sys.dba_data_files d
, v$datafile v
, ( select file_id, SUM(bytes) bytes
from sys.dba_free_space
GROUP BY file_id) s
WHERE
(s.file_id (+)= d.file_id)
AND (d.file_name = v.name)
and tablespace_name='DATA';

But, it doesn't show them the space availabe increased after more then ten millions records deletion:

WHOLE USED % Full
--------- --------- -------
257.81 G 244.18 G 94.7 %


I queried the dba_segment on the segment they delete records, MV_POSITIONS, which the most space consumer in DATA TS, it didn't show me any changes:

SELECT
segment_name
, sum(bytes) bytes
, sum(bytes)/1024/1024 MB
, count(*) seg_count
FROM
dba_segments
Where owner='RBC_MVIEW' and segment_name='MV_POSITIONS'
GROUP BY
segment_name;

Date #of Records Size MB
4/19/2007 164,524,494 77,147
4/20/2007 155,124,736 77,147
4/23/2007 102,493,110 77,147


Show_space gives me the following:

SQL> exec show_space('MV_POSITIONS','RBC_MVIEW','TABLE');

Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 8,312
FS2 Blocks (25-50) ..................... 436,299
FS3 Blocks (50-75) ..................... 1,045,864
FS4 Blocks (75-100)..................... 7,691,237
Full Blocks ..................... 682,818
Total Blocks............................ 9,874,816
Total Bytes............................. 80,894,492,672
Total MBytes............................ 77,147
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 67
Last Used Ext BlockId................... 145,288
Last Used Block......................... 128

PL/SQL procedure successfully completed

The tablesapce is LMT with ASSM.

Do these mean that

1. DBA_SEGMENT only reports the space lower than the HWM?
2. There is no way to report the precise space usage of this table?

Thanks s lot.



Tom Kyte

Followup  

April 23, 2007 - 4:53 pm UTC

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.

reclaim space

April 23, 2007 - 9:59 pm UTC

Reviewer: Gordon from Toronto, Canada

Thanks, Tom.

I need your elaboration please.

1. "the space is released..... back to the table." How can I tell? Queries on DBA_SEGMENTS did not change!

2. If I monitor the output of show_space, I may see changes ONLY at the block level, such as:

FS1 Blocks (0-25) ..................... 8,312
FS2 Blocks (25-50) ..................... 436,299
FS3 Blocks (50-75) ..................... 1,045,864
FS4 Blocks (75-100)..................... 7,691,237
Full Blocks ..................... 682,818

Am I right?

3. What does "reclaim space" mean? Will it shrink and release the HWM, so that I can the segment size change?

4. 10g's shrinking table/TBS is neat. I have used it often. This DB is on 9i. Is there any easy way to reclaim space?
Tom Kyte

Followup  

April 24, 2007 - 10:42 am UTC

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.

Recheck

April 24, 2007 - 6:31 am UTC

Reviewer: A.Varadarajan from Bangalore, India

Again
ops$tkyte@8i> select extent_id, bytes, blocks
2 from user_extents
3 where segment_name = 'T'
4 and segment_type = 'TABLE'
5 /

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
2 40960 5
3 81920 10
4 57344 7
0 40960 5
1 40960 5
----------
sum 32

Based on your link above I was able to understand why 4 th extent has 7 blocks. But why was the 3rd extent not broken up in to 5+5 blocks?
Quote
If an exact match is not found, Oracle then searches for a set of contiguous data blocks greater than the amount needed. If Oracle finds a group of contiguous blocks that is at least 5 blocks greater than the size of the extent needed, it splits the group of blocks into separate extents, one of which is the size it needs. If Oracle finds a group of blocks that is larger than the size it needs, but less than 5 blocks larger, it allocates all the contiguous blocks to the new extent.

In the current example, if Oracle does not find a set of exactly 20 contiguous data blocks, Oracle searches for a set of contiguous data blocks greater than 20. If the first set it finds contains 25 or more blocks, it breaks the blocks up and allocates 20 of them to the new extent and leaves the remaining 5 or more blocks as free space. Otherwise, it allocates all of the blocks (between 21 and 24) to the new extent.
unquote

Reclaim unused blocks not working?

April 24, 2007 - 11:33 am UTC

Reviewer: Gordon from Toronto Canada

Thank you so much again for your quick reply and clarification!

Before re-org the tables, I want to release unused blocks first...But somehow it didn't work?

------------------------------------------
SQL> analyze table RBC_MVIEW.MV_LOAD_STRUCTURE_CLASSES compute statistics;

Table analyzed

SQL> exec show_space('MV_LOAD_STRUCTURE_CLASSES','RBC_MVIEW');

Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 18
Full Blocks ..................... 3,696
Total Blocks............................ 3,840
Total Bytes............................. 31,457,280
Total MBytes............................ 30
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 56,584
Last Used Block......................... 64

PL/SQL procedure successfully completed

SQL> alter table RBC_MVIEW.MV_LOAD_STRUCTURE_CLASSES DEALLOCATE UNUSED;

Table altered

SQL> exec show_space('MV_LOAD_STRUCTURE_CLASSES','RBC_MVIEW');

Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 18
Full Blocks ..................... 3,696
Total Blocks............................ 3,840
Total Bytes............................. 31,457,280
Total MBytes............................ 30
Unused Blocks........................... 64
Unused Bytes............................ 524,288
Last Used Ext FileId.................... 5
Last Used Ext BlockId................... 56,584
Last Used Block......................... 64

PL/SQL procedure successfully completed

SQL>
----------------------------------------------------------
Why are those 64 blocks still there? The table is on Oracle 9i, in a LMT with ASSM.

Did I do something wrong?

Thanks a lot.
Tom Kyte

Followup  

April 24, 2007 - 12:57 pm UTC

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.

HWM, extents, blocks, etc

April 24, 2007 - 1:18 pm UTC

Reviewer: Gordon from Toronto, Canada

You got it! Its extent is in 1MB size, 128 blocks!

Now I understand more about HWM, extents and blocks, more important is how Oracle manages and uses them!

Thanks a lot to Tom and whoever else to contribute this forum!!!

show_space for other schema

May 08, 2007 - 2:50 pm UTC

Reviewer: Eric Peterson from Seattle, WA

Fantastic routine which works great for me when looking at my own tables.

But when I attempt to look at a different user's table or have a different user use my copy of this procedure, I get the following errors.

Yes, I have AUTHID CURRENT USER. Any ideas what path I've gone down incorrectly? From both users I am able to describe and select data from the table.


user1> EXEC user2.show_space ( 'TBL', 'USER1' );
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "USER2.SHOW_SPACE", line 54
ORA-06512: at line 1



user2>EXEC show_space ( 'TBL', 'USER1' );
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 97
ORA-06512: at "USER2.SHOW_SPACE", line 87
ORA-06512: at line 1


Tom Kyte

Followup  

May 11, 2007 - 8:32 am UTC

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.

free space for ASSM tablespace,

May 23, 2007 - 12:39 pm UTC

Reviewer: A reader

Using the SHOW_SPACE procedure, how to find out the free space for an INDEX/TABLE if it resides in ASSM tablespace?

What is FS1 BLOCKS, FS2,FS3 and FS4 blocks? What is the difference between them?

I did a small demo to find out the output of show_space and I need to know why show_space showed a same output even after I deleted 1000 rows from the table.

SQL> create table abc as select object_id,object_name,owner from dba_objects;

Table created.

SQL> select count(*) from abc;

COUNT(*)
----------
61823

SQL> create index idx_abc on abc(object_name) nologging;

Index created.

SQL> exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'abc',estimate_percent=>25,cascade=>true);

PL/SQL procedure successfully completed.
SQL> select AVG_LEAF_BLOCKS_PER_KEY,CLUSTERING_FACTOR,NUM_ROWS,FREELISTS,FREELIST_GROUPS,LEAF_BLOCKS
2 from dba_indexes where index_name='IDX_ABC';

AVG_LEAF_BLOCKS_PER_KEY CLUSTERING_FACTOR NUM_ROWS FREELISTS FREELIST_GROUPS LEAF_BLOCKS
----------------------- ----------------- ---------- ---------- --------------- -----------
1 32179 61823 1 1 294

SQL> exec show_space('IDX_ABC','SYS','INDEX',null)
Free Blocks............................. 0
Total Blocks............................ 384
Total Bytes............................. 3,145,728
Total MBytes............................ 3
Unused Blocks........................... 88
Unused Bytes............................ 720,896
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 70,281
Last Used Block......................... 40

PL/SQL procedure successfully completed.

SQL> delete from abc where rownum < 1000;

999 rows deleted.

SQL> commit;

Commit complete.

SQL> exec show_space('IDX_ABC','SYS','INDEX',null);
Free Blocks............................. 0
Total Blocks............................ 384
Total Bytes............................. 3,145,728
Total MBytes............................ 3
Unused Blocks........................... 88
Unused Bytes............................ 720,896
Last Used Ext FileId.................... 1
Last Used Ext BlockId................... 70,281
Last Used Block......................... 40

PL/SQL procedure successfully completed.

I was expecting unused blocks to be higher after I deleted 1000 rows. Why it is still same?

why FS1,FS2..FS4 blocks are not displayed in the output?

thanks,

Tom Kyte

Followup  

May 26, 2007 - 9:36 am UTC

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

Not able to execute show_space

May 27, 2007 - 3:23 am UTC

Reviewer: Reader from India from India

Hi Tom,
Greetings.
I creates show_space procedure as sys and wanted to execute it for tables owned by another user. I am getting following error. I am using Oracle 9iR2.

SQL> exec show_space('REVENUE_ACTIVITY','BSL','TABLE')
BEGIN show_space('REVENUE_ACTIVITY','BSL','TABLE'); END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "SYS.SHOW_SPACE", line 22
ORA-06512: at line 1

Though I am running the procedure as sys as user why there is "insufficient privileges" error?. Please help.

Tom Kyte

Followup  

May 27, 2007 - 9:17 am UTC

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.

May 28, 2007 - 2:34 pm UTC

Reviewer: A reader

If I create a 1000 rows for a table, but ROLLBACK the work, would Oracle return back all the memory (extents?) that it used up during the insert for that table.
Or would the High Water mark be raised above the last row that was inserted but then rolled back?
Tom Kyte

Followup  

May 30, 2007 - 10:16 am UTC

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>


follow up,

May 29, 2007 - 11:26 am UTC

Reviewer: A reader

Follow up question on "free space for ASSM tablespace".

So now I understand unused space is blocks below HWM. then how to find those blocks that were freed up after deleting 1000 blocks?

Actually the tablespace on which my object resides is ASSM (if it was not then it should have displayed dbms_space.free_blocks)

Thanks,
Tom Kyte

Followup  

May 30, 2007 - 11:03 am UTC

search site for show_space


I used ASSM in the above example myself.

Unused Blocks

August 02, 2007 - 10:56 am UTC

Reviewer: Chaman from Mumbai

Tom,
Your very first response made an interesting reading. Infact I was looking for similar kind of answer (How the information from DBMS_SPACE can be used). 

You said, "This report does show blocks above the high water mark.  Unused Blocks are exactly the 
block above the high water mark."

Just last week I have reorgainsed (ALTER TABLE MOVE) the table then how is that the Unused Blocks is not equal to zero? After table reorganisation the HMW will come down.

SQL> EXEC SHOW_SPACE ('ACCT_GRP1','ACCT', 'TABLE');
Free Blocks.............................7
Total Blocks............................563205
Total Bytes.............................4613775360
Unused Blocks...........................4846
Unused Bytes............................39698432
Last Used Ext FileId....................4
Last Used Ext BlockId...................102402
Last Used Block.........................1554

PL/SQL procedure successfully completed.

SQL> 
SQL> EXEC SHOW_SPACE ('ACCT_GRP2','ACCT', 'TABLE');
Free Blocks.............................5
Total Blocks............................1292
Total Bytes.............................10584064
Unused Blocks...........................191
Unused Bytes............................1564672
Last Used Ext FileId....................15
Last Used Ext BlockId...................155150
Last Used Block.........................69

PL/SQL procedure successfully completed.

Tom Kyte

Followup  

August 05, 2007 - 10:34 am UTC

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

how to get tablespace that is in problem...

August 07, 2007 - 1:31 pm UTC

Reviewer: Mahalingesh

Alert log
========================
Mon Aug 6 16:23:55 2007
Errors in file /u01/app/oracle/admin/xxxx/udump/xxxx_ora_12776.trc:
ORA-01114: IO error writing block to file 1022 (block # 852002)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 262144


Trace file
==================
box01:[/u01/app/oracle/admin/xxxx/bdump]> more /u01/app/oracle/admin/xxxx/udump/xxxx_ora_14251.trc
Dump file /u01/app/oracle/admin/xxxx/udump/xxxx_ora_14251.trc
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
ORACLE_HOME = /u01/app/oracle/product/9.2.0
System name: SunOS
Node name: box01
Release: 5.8
Version: Generic_117350-43
Machine: sun4u
Instance name: xxxx
Redo thread mounted by this instance: 1
Oracle process number: 11
Unix process pid: 14251, image: oracle@box01 (TNS V1-V3)

*** SESSION ID:(166.28492) 2007-08-06 16:40:04.962
*** 2007-08-06 16:40:04.962
ksedmp: internal or fatal error
ORA-01114: IO error writing block to file 1022 (block # 852002)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 262144
ORA-01114: IO error writing block to file 1022 (block # 852002)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 262144
No current SQL statement being executed.
----- PL/SQL Call Stack -----
object line object
handle number name
824e33e8 6572 package body SYS.DBMS_STATS
824e33e8 6664 package body SYS.DBMS_STATS
824e33e8 6758 package body SYS.DBMS_STATS
824e33e8 9588 package body SYS.DBMS_STATS
824e33e8 10016 package body SYS.DBMS_STATS
824e33e8 10205 package body SYS.DBMS_STATS
824e33e8 10795 package body SYS.DBMS_STATS
824e33e8 10982 package body SYS.DBMS_STATS
824e33e8 11036 package body SYS.DBMS_STATS
824e33e8 11013 package body SYS.DBMS_STATS
7c8357ec 5 procedure CPR.GATHER_STATS
80ff00b8 2 anonymous block
----- Call Stack Trace -----
calling call entry argument values in hex
location type point (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+248 CALL ksedst() 2A50544 ? 2A50520 ? C ? 0
ORA-01114: IO error writing block to file 1022 (block # 852002)
ORA-27063: skgfospo: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 262144

Tom Kyte

Followup  

August 07, 2007 - 2:14 pm UTC

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>

August 07, 2007 - 3:42 pm UTC

Reviewer: A reader

Perfect!

Thanks Tom,

I tried checking the max datafile setting, i could not find in init.ora not 'show parameters max' show that... Please, help
Tom Kyte

Followup  

August 07, 2007 - 3:57 pm UTC

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

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

August 07, 2007 - 5:05 pm UTC

Reviewer: A reader

In my case I dont see any relation, please help me if I am wrong alert log shows number 1022

SQL> select records_total from v$controlfile_record_section where type = 'DATAFILE';

RECORDS_TOTAL
-------------
          284

SQL> select count(*) from v$datafile;

  COUNT(*)
----------
       232

SQL> 
SQL> select FILE_ID from dba_temp_files;

   FILE_ID
----------
         1
         2
         3
         4
         5

SQL> select RELATIVE_FNO from dba_temp_files;

RELATIVE_FNO
------------
           1
           2
           3
           4
           5
SQL> select FILE# from v$tempfile;

     FILE#
----------
         1
         2
         3
         4
         5

October 10, 2007 - 7:14 am UTC

Reviewer: A reader

"So now we have 50 rows with 2k each -- I'm using a 8k blocksize so I expect about 3
rows / block. That means about 18 blocks of data plus 1 for the system = about 19 blocks
should be "used" now. Below I see that I have" - you said

=================

i don't understand this paragraph ....

another question,

what is the benefit from using analyzing table when you query user_extents table


more thanks,

HWM

November 14, 2007 - 6:59 am UTC

Reviewer: sainats from Singapore

select t1.owner,
t1.table_name,
round((blocks*8192)/1024/1024) Alloc_DataSize_in_MB,
round((num_rows*avg_row_len)/1024/1024) Actual_DataSize_in_MB,
decode(lobsize,null,'NA',lobsize) Alloc_LobSize_in_MB,
lobblocks,
round((num_rows*avg_row_len)/(blocks*8192) * 100,0) Data_HWM_Percent,
round(((blocks*8192)-(num_rows*avg_row_len))/1024/1024) Data_Wasteio
from dba_tables t1,
(
select round(sum(bytes)/1024/1024,0) lobsize, sum(blocks) lobblocks, t1.owner, lobtabname from dba_segments t1,
( select owner, table_name lobtabname, index_name segname from dba_lobs p1
union all
select owner, table_name lobtabname, segment_name segname from dba_lobs p2) t2
where segment_name = segname and t1.owner = t2.owner
group by lobtabname, t1.owner ) t3
where t1.table_name = t3.lobtabname (+)
and t1.owner = t3.owner(+)
and partitioned = 'NO'
--and table_name in ('PRTB_INSTRUCTION01', 'PRTH_PHYSICAL_UNIT', 'PRTB_XML_FLAT', 'TEST1', 'TEST2', 'TEST3', 'PRTB_OUTBOUND_INTERCHANGE')
and avg_row_len >0
and t1.blocks > 0
and num_rows > 0
and round((blocks*8192)/1024/1024) > 10
order by Data_wasteio desc

--
let me know whether it helps out in reorg the object.
Tom Kyte

Followup  

November 19, 2007 - 5:32 pm UTC

that does nothing to reorganize an object.


reader

January 30, 2008 - 11:56 am UTC

Reviewer: A reader

ops$tkyte@8i> select blocks, empty_blocks,
  2         avg_space, num_freelist_blocks
  3    from user_tables
  4   where table_name = 'T'
  5  /

    BLOCKS EMPTY_BLOCKS  AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
         1           30       6091                   1


For tables it is great to find the actual blocks used
For the indexes what is the best way to find actual used blocks ( without using index_stats for each and every indexes)


Tom Kyte

Followup  

January 30, 2008 - 1:59 pm UTC

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.


reader

January 30, 2008 - 2:29 pm UTC

Reviewer: A reader

user/dba_indexes has leaf_blocks , but do not have branch_blocks ( can get only from index_stats) I am trying to find sum(blocks) of all indexes per schema
Tom Kyte

Followup  

January 30, 2008 - 4:09 pm UTC

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?

reset hwm - rman backup

April 03, 2008 - 3:54 pm UTC

Reviewer: Selva from GA USA

Tom

We got a situation where we are trying to reset the HWM, as RMAN backups are backing up tablespace till HWM even after dropping unwanted objects.

My question is alter table move with in same tablespace will lower HWM?

Thanks

Selva
Tom Kyte

Followup  

April 03, 2008 - 9:21 pm UTC

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.

Fast Segment Growth

May 07, 2008 - 9:31 am UTC

Reviewer: David Mc from United Kingdom

Hi Tom,

Your code works a treat when applied as stated on the tin. However, we're trying to retro fit the code to any segemnet in the dB, and the code fails. We're using exec show_space('segmentname') as the new user we've created with dba_tablespace & dba_segment priv's, but the code fails to work. I think we're missing something. By the way the segment above is not owned by the new user.
Tom Kyte

Followup  

May 08, 2008 - 2:48 am UTC

"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.

user_extents & user_tables

June 24, 2008 - 9:00 am UTC

Reviewer: Sam Mousleh from France

Good day Tom, I would like to use your procedure show space, and I am trying to find a table in user_tables and user_extents... not found. but I can see it in dba_segments
and dba_tables.
Does that mean that the table I am looking for is not owned by a user, and it belong to system?

And if I look for it in all_objects:

select * from all_objects
where object_name like 'SAOPTDEM'
and object_type = 'TABLE'

I could see that it is owned by the a user "OPS$SABXFER".

Thank you

Sam
Tom Kyte

Followup  

June 24, 2008 - 10:40 am UTC

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>


June 24, 2008 - 11:49 am UTC

Reviewer: Sam Mousleh from France

Thank you Tom,
Your explanation is very clear and useful...

SAM

June 27, 2008 - 11:28 am UTC

Reviewer: A reader

Hi tom, I still confuse the concept of FS Blocks in show_space procedure.
If FS4 Blocks is 225.Does it mean that %75 or %100 of the 225 blocks are free.
So that table needs shrink to reclaim space.
Does ¿t mean that if the value of FS4 is high we have to
perform shrink?


FS1 Blocks (0-25) ..................... 1
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 225

Tom Kyte

Followup  

June 27, 2008 - 11:41 am UTC

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.


HWM : Slight confusion

March 04, 2009 - 6:14 pm UTC

Reviewer: Sanji from Shelton, CT

Tom,
We are on 9.2.0.7, HP-UX 11i, but still use dictionary managed tablespaces.
I'm in the process of moving data to Locally managed tablespaces, but considering the size of the tables/ indexes and load on the system, it's a gradual process.

Anyways, i executed the show_space procedure for one of the "hot" tables and this is the result

OPEN:SANJI:XFIN@DRLAWSON>exec lawson.show_space('AMASTBOOK','LAWSON','TABLE')
Free Blocks.............................664923
Total Blocks............................724234
Total Bytes.............................2966462464
Unused Blocks...........................24360
Unused Bytes............................99778560
Last Used Ext FileId....................107
Last Used Ext BlockId...................1492463
Last Used Block.........................31450

Now, from what you explained,

1> Blocks on freelist = 664923
2> Total blocks allocated to the table = 724234
3> Blocks over the HWM = 24360

So the number of blocks which actually contain data should be
Total blocks - Blocks on freelist - Blocks over HWM =
724234 - 664921 - 24360 = 34953

With a block size of 4K, the actual consumption in MBs is around (34953 * 4096 )/(1024*1024) => 137 Mb.

I had analyzed the table sometime back

OPEN:SANJI:XFIN@LAWSON1>select table_name, freelists, num_rows, blocks, empty_blocks, avg_space, avg_row_len
2 from dba_tables
3 where table_name='AMASTBOOK';

TABLE_NAME FREELISTS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN
---------- ---------- ---------- ---------- ------------ ---------- -----------
AMASTBOOK 1 12461693 699873 68494 353 205

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

From dba_segments

OPEN:SANJI:XFIN@DRLAWSON>@seginf
Enter value for segname: AMASTBOOK
old 4: where segment_name='&segname'
new 4: where segment_name='AMASTBOOK'

OWNER SEGMENT_NAME TABLESPACE_NAME SIZE_MB
---------- -------------------- -------------------- ----------
LAWSON AMASTBOOK TABLE_LMT 2780


I moved the table to a locally managed tablespace and the show_space revealed (i had to recreate the show_space procedure)

OPEN:SANJI:XFIN@DRLAWSON>exec lawson.show_space('AMASTBOOK','LAWSON','TABLE')
Unformatted Blocks ..................... 23
Full Blocks ..................... 709,019
Total Blocks............................ 711,680
Total Bytes............................. 2,915,041,280
Total MBytes............................ 2,780
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 130
Last Used Ext BlockId................... 377,361
Last Used Block......................... 4,720

So, the doubt is regarding the formula prior to moving the table to an LMT.

"So the number of blocks which actually contain data should be Total blocks - Blocks on freelist - Blocks over HWM = 724234 - 664921 - 24360 = 34953 "

The table clearly contained more data than calculated through this formula.

I'm sure i'm going wrong somewhere.

Rgds
Sanji
Tom Kyte

Followup  

March 04, 2009 - 8:42 pm UTC

... 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)


HWM : Slight confusion

March 05, 2009 - 1:34 pm UTC

Reviewer: Sanji from Shelton, CT

Thanks Tom.

So, missed block overhead + pctfree (5 in this case) + row overheads account for close to
2437 (actual table size) - 137 (actual data content according to show_space ) = 2300 Mb ?

OPEN:SANJI:XFIN@DRLAWSON>exec lawson.show_space('AMASTBOOK','LAWSON','TABLE')
Free Blocks.............................664923
Total Blocks............................724234
Total Bytes.............................2966462464
Unused Blocks...........................24360
Unused Bytes............................99778560
Last Used Ext FileId....................107
Last Used Ext BlockId...................1492463
Last Used Block.........................31450

Rgds
Sanji
Tom Kyte

Followup  

March 05, 2009 - 2:10 pm UTC

... 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.

HWM: Slight confusion

March 05, 2009 - 3:34 pm UTC

Reviewer: Sanji from Shelton, CT

I analyzed the table on the production environment, followed by the show_space procedure.

At the time of execution of these processes, the application wasn't accessing this table.

Following is the information.

OPEN:SANJI@LAWSON1>exec dbms_stats.gather_table_stats('LAWSON','AMASTBOOK',method_opt=>'for all columns size auto',cascade=>true,degree=>2)

PL/SQL procedure successfully completed.

OPEN:SANJI@LAWSON1>
select table_name, pct_free, pct_used, pct_increase, freelists, num_rows, blocks, empty_blocks,
avg_space, avg_row_len, avg_space_freelist_blocks, num_freelist_blocks, sample_size,
to_char(last_analyzed, 'mm/dd/yy hh24:mi') last_analyzed
from dba_tables
where table_name='AMASTBOOK'
and owner='LAWSON'
/

TABLE_NAME PCT_FREE PCT_USED PCT_INCREASE FREELISTS NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS SAMPLE_SIZE LAST_ANALYZED
---------- ---------- ---------- ------------ ---------- ---------- ---------- ------------ ---------- ----------- ------------------------- ------------------- ----------- --------------
AMASTBOOK 5 94 0 1 12503542 702283 68494 353 204 794 34511 12503542 03/05/09 14:47


OPEN:SANJI:PFIN@LAWSON1>exec lawson.show_space('AMASTBOOK','LAWSON','TABLE')
Free Blocks............................. 671,632
Total Blocks............................ 724,234
Total Bytes............................. 2,966,462,464
Total MBytes............................ 2,829
Unused Blocks........................... 21,950
Unused Bytes............................ 89,907,200
Last Used Ext FileId.................... 107
Last Used Ext BlockId................... 1,492,463
Last Used Block......................... 33,860

PL/SQL procedure successfully completed.

I see a lot of discrepancy between information from dba_tables and show_space.

Rgds
Sanji
Tom Kyte

Followup  

March 05, 2009 - 4:33 pm UTC

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.

HWM: Slight confusion

March 05, 2009 - 4:39 pm UTC

Reviewer: Sanji from Shelton, CT

Tom, i absolutely had no intention of doubting the show_space procedure's functionality. This site has been and would be my mentor.

It was a confusion for which i seeked your feedback.

Thanks again for the response.

Rgds
Sanji
Tom Kyte

Followup  

March 05, 2009 - 5:03 pm UTC

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

HWM: Slight Confusion

March 05, 2009 - 5:08 pm UTC

Reviewer: Sanji from Shelton, CT

OPEN:SANJI:PFIN@LAWSON1>select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for HPUX: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

OPEN:SANJI:PFIN@LAWSON1>select dbms_metadata.get_ddl('TABLE','AMASTBOOK','LAWSON') from dual;

DBMS_METADATA.GET_DDL('TABLE','AMASTBOOK','LAWSON')
--------------------------------------------------------------------------------

CREATE TABLE "LAWSON"."AMASTBOOK"
( "ASSET" NUMBER(10,0) NOT NULL ENABLE,
"BOOK" CHAR(10) NOT NULL ENABLE,
"BOOK_STATUS" CHAR(1) NOT NULL ENABLE,
"LIFE" NUMBER(4,0) NOT NULL ENABLE,
"LIFE_REM" NUMBER(7,3) NOT NULL ENABLE,
"BOOK_BASIS" NUMBER(15,2) NOT NULL ENABLE,
"METHOD" CHAR(10) NOT NULL ENABLE,
"METH_SWITCHED" CHAR(1) NOT NULL ENABLE,
"INSRV_DATE" DATE NOT NULL ENABLE,
"METH_TABLE_YR" NUMBER(2,0) NOT NULL ENABLE,
"CONVENTION" CHAR(2) NOT NULL ENABLE,
"DEPR_STARTDATE" DATE NOT NULL ENABLE,
"LIFE_YEARS" NUMBER(5,2) NOT NULL ENABLE,
"STATUS_CP_CALC" CHAR(1) NOT NULL ENABLE,
"LIFE_CP" NUMBER(5,3) NOT NULL ENABLE,
"TRAN_ND" NUMBER(1,0) NOT NULL ENABLE,
"DEPR_CP" NUMBER(15,2) NOT NULL ENABLE,
"DEPR_CP_ALLOW" NUMBER(15,2) NOT NULL ENABLE,
"DEPR_YTD" NUMBER(15,2) NOT NULL ENABLE,
"DEPR_YTD_ALLOW" NUMBER(15,2) NOT NULL ENABLE,
"DEPR_YTD_OVRRD" NUMBER(15,2) NOT NULL ENABLE,
"DEPR_LTD" NUMBER(15,2) NOT NULL ENABLE,
"DEPR_LTD_ALLOW" NUMBER(15,2) NOT NULL ENABLE,
"LAST_CLOSE" NUMBER(15,2) NOT NULL ENABLE,
"FIRST_LAST_FL" CHAR(1) NOT NULL ENABLE,
"INACT_YTD" NUMBER(5,3) NOT NULL ENABLE,
"INACT_LTD" NUMBER(7,3) NOT NULL ENABLE,
"SALVAGE_VALUE" NUMBER(15,2) NOT NULL ENABLE,
"BUS_PERCENT" NUMBER(5,2) NOT NULL ENABLE,
"PERSONAL_USE" CHAR(10) NOT NULL ENABLE,
"YR1_EXP" NUMBER(15,2) NOT NULL ENABLE,
"TC_TABLE" CHAR(10) NOT NULL ENABLE,
"TC_PROPERTY" NUMBER(15,2) NOT NULL ENABLE,
"TAX_CREDIT" NUMBER(15,2) NOT NULL ENABLE,
"INS_METHOD" CHAR(10) NOT NULL ENABLE,
"INS_ACT_VAL" NUMBER(15,2) NOT NULL ENABLE,
"INS_CMPTD_VAL" NUMBER(15,2) NOT NULL ENABLE,
"RPL_METHOD" CHAR(10) NOT NULL ENABLE,
"RPL_CMPTD_VAL" NUMBER(15,2) NOT NULL ENABLE,
"CREATION_DATE" DATE NOT NULL ENABLE,
"CREATION_TIME" NUMBER(6,0) NOT NULL ENABLE,
"CREATOR_ID" CHAR(10) NOT NULL ENABLE,
"DSP_YTD" NUMBER(5,3) NOT NULL ENABLE,
"ORIG_BASIS" NUMBER(15,2) NOT NULL ENABLE,
"GL_ADD_DATE" DATE NOT NULL ENABLE,
"ORIG_DEPR_YTD" NUMBER(15,2) NOT NULL ENABLE,
"ORIG_DEPR_LTD" NUMBER(15,2) NOT NULL ENABLE,
CONSTRAINT "ASBSET1" PRIMARY KEY ("ASSET", "BOOK")
USING INDEX PCTFREE 5 INITRANS 2 MAXTRANS 255 NOLOGGING
STORAGE(INITIAL 94371840 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "INDEX_LMT" ENABLE
) PCTFREE 5 PCTUSED 94 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING
STORAGE(INITIAL 1366294528 NEXT 5242880 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "AM_DATA"

Rgds
Sanji
Tom Kyte

Followup  

March 05, 2009 - 5:41 pm UTC

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



HWM: Clarified

March 05, 2009 - 5:50 pm UTC

Reviewer: Sanji from Shelton, CT

:) Thanks Tom....

Too high PCTFREE

March 17, 2009 - 6:15 am UTC

Reviewer: Chinni

Hi Tom,
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?

segment_name =>POS
segment_type =>TABLE SUBPARTITION
partition_name =>SYS_SUBP55
unformatted_blocks =>0
unformatted_bytes =>0
fs1_blocks(0-25% Free) =>0
fs1_bytes =>0
fs2_blocks(25-50% Free) =>4
fs2_bytes =>16384
fs3_blocks(50-75% Free) =>6
fs3_bytes =>24576
fs4_blocks(75-100% Free) =>202
fs4_bytes =>827392
full_blocks =>334641 -- ( Full block means block occupied minimum 60%(Since PCTFREE-40%) or Fully used 100%)????
full_bytes =>1370689536

total_blocks => 344320
total_bytes => 1410334720
unused_blocks => 8192
unused_bytes => 33554432
last_used_extent_file_id => 70
last_used_extent_block_id => 327953
last_used_block => 8192

I would like to modify the storage clause of this object if this setting (PCTFREE 40%) is not good here. Thank you very much.
Tom Kyte

Followup  

March 17, 2009 - 10:45 am UTC

... 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.



Thank You very much.. it is clear now

March 19, 2009 - 1:30 pm UTC

Reviewer: Chinni


April 17, 2009 - 5:10 am UTC

Reviewer: Ashok from India

Hi Tom,

I followed your steps to execute exactly what you specified for configuring and running show_space package.

I am getting the following error:


17-APR-09 14:14 : A@atlef > create table t ( x int ) tablespace users;

Table created.

17-APR-09 14:14 : A@atlef > exec show_space( 'T' )
BEGIN show_space( 'T' ); END;

*
ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 152
ORA-06512: at "A.SHOW_SPACE", line 22
ORA-06512: at line 1

This is what the documentation states:
ORA-10618: Operation not allowed on this segment
Cause: This DBMS_SPACE operation is not permitted on segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT

My table for which I am analysing space is configured on 10g on an LMT tablespace and with AUTO SEGMENT SPACE MANAGEMENT.

What is the best way to analyse a table's space with the above configuration?

Regards
Ashok



Tom Kyte

Followup  

April 17, 2009 - 9:49 am UTC

ctl-f and search for

if the object is in an ASSM tablespace




Space Management in 10g with LMT and segment management = AUTO

April 17, 2009 - 6:48 am UTC

Reviewer: Ashok from India

Hi Tom,

An advice from you on a space issue that we have encountered -

1. We orace working on oracle 10g RAC with ASM as shared storage.

2. We have a Permanent tablespace TB1 with extent management="LOCAL" and allocation type="SYSTEM" and segment management = "AUTO".

3. We have created a table "U1"."T1" which resides in this tablespace (TB1) and its size is growing very fast. So much so that we are forced to see what's wrong as the number of records is not growing that much.

4. We have analysed its space usage and found that the approx size of the segment as per (NUM_ROWS*AVG_LENGHT) is approx. 427 MB but it has been allocated large number of extents to the tune of 4288MB. As detailed below:

SIZE AS PER SIZE AS PER
DBA_EXTENTS (NUM_ROWS*AVG_LENGHT) LARGEST EXTENT
SCHEMA TABLE NUM_ROWS (MB) (MB) SIZE(MB)
---------- ----------- ---------- ----------- -------------------- ----------------
U1 T1 1842747 4288 427 64




5. On further analysis, we found that initially 1MB extents were allocated and then 8MB and now 64MB extents are allocated


NO_OF_EXTENTS EXTENT_SIZE_MB
------------- --------------
16 .0625
51 64
63 1
120 8

6. On further analysis, we found that only few blocks are "empty blocks"

select blocks, empty_blocks,avg_space, num_freelist_blocks from dba_tables where table_name = 'T1';


BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
--------- ------------ ---------- -------------------
547843 1013 5376 0


Question
--------
1. I understand that when the segment management is "AUTO", size of extent to be allocated is not fixed and oracle decides automatically. This is fine. But why did it allocate extents to the size=4288MB when the actual size as per avg row length = 427MB only?

2. With LMT, pctfree/pctused are ignored and cannot be specified, right? If so, then what rule does oracle use to refill oracle blocks when rows are deleted from the block or it becomes "empty" again?




Thanks in Advance
Ashok



Tom Kyte

Followup  

April 17, 2009 - 9:55 am UTC

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).


Space Management in 10g with LMT and segment management = AUTO

April 20, 2009 - 5:54 am UTC

Reviewer: Ashok from India

Hi Tom,

Follow up on the above thread regarding the space issue that we have observed, I am providing the complete information as requested -


Our table T1 is created on tablespace TB1 with the following configuration:

i) Tablespace TB1 is created with extent management="LOCAL" and allocation type="SYSTEM" and segment management = "AUTO" ASSM is used.

ii) A table T1 is was as follows:

CREATE TABLE T1(
ENTITY_ID VARCHAR2(8) NOT NULL,
....
....
)
PCTFREE 10 PCTUSED 40 TABLESPACE TB1 STORAGE(MINEXTENTS 1 MAXEXTENTS 1024 PCTINCREASE 0 BUFFER_POOL DEFAULT );


The segment space increased many fold (large number of extents were allocated when it was not required so).
Here is our findings -



RESULT ON 10/03/09 for table=T1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO_OF_EXTENTS EXTENT_SIZE_MB
------------- --------------
8 8
16 .0625
63 1


BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
-------- ------------ ---------- -------------------
16217 60 1011 0


NUM_ROWS AVG_ROW_LENGHT_KB SIZE_AS_PER_AVG_ROW_MB
-------- ----------------- ----------------------
436890 .237304688 101



Unformatted Blocks ..................... 384
FS1 Blocks (0-25) ..................... 1,513
FS2 Blocks (25-50) ..................... 2
FS3 Blocks (50-75) ..................... 0
FS4 Blocks (75-100)..................... 120
Full Blocks ..................... 14,198
Total Blocks............................ 16,384
Total MBytes............................ 128
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 27
Last Used Ext BlockId................... 142,089
Last Used Block......................... 1,024


Total blocks used (excluding unformated) = (1531 + 2 + 0 + 120 + 14198) = 15833 [124 MB (8K block)]



=================================================================================




RESULT ON 15/04/09 for table=T1
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~


NO_OF_EXTENTS EXTENT_SIZE_MB
------------- --------------
16 .0625
51 64
63 1
120 8


BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
-------- ------------ ---------- -------------------
547843 1013 5376 0


NUM_ROWS AVG_ROW_LENGHT_KB SIZE_AS_PER_AVG_ROW_MB
-------- ----------------- ----------------------
1824398 .236328125 421




Unformatted Blocks ..................... 459,019
FS1 Blocks (0-25) ..................... 846
FS2 Blocks (25-50) ..................... 266
FS3 Blocks (50-75) ..................... 33
FS4 Blocks (75-100)..................... 21,807
Full Blocks ..................... 65,872
Total Blocks............................ 548,864
Total MBytes............................ 4,288
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 721,161
Last Used Block......................... 8,192


Total blocks used (excluding unformated) = ( 846 + 266 + 33 + 21807 + 65872) = 88824 blocks [694 MB (8K block)]



Summary
=======
Nunber of Number Total Total Avg Length Number AvgLen Large Extents
Date unformated of used Blocks MBytes (KBybes) of Rows * num_rows (8 & 64 MB)
blocks blocks (Mbytes)
-------- ---------- ------ ------- ------ ---------- -------- --------- -----------------
10/03/09 384 15833 16,384 128 .237304688 436890 101 8*8MB & 0*64MB
15/04/09 459,019 88824 548,864 4,288 .236328125 1824398 421 120*8MB & 51*64MB


1. On 10/03/09, (Avg Len * Num_Row) was =101 MB and the total segment size was =128 MB with 384 unformated blocks
2. On 15/04/09, (Avg Len * Num_Row) was =421 MB and the total segment size was =4288 MB with 459,019 unformated blocks

Questions
==========


2. Why did oracle allocated so many blocks after 10/03/09 (approx 112 8MB extents + 51 64MB extents) = 4160MB when it only required approx 400 MB of space (note - 3.5GB of unformated blocks still exists) ?

3. How can we ensure this does not happen in future - meaning it does not allocate such large chunk of space when it does not require it?

4. Can we have "uniform" extent allocation rather than "auto"? I do not think this Would help or would it? Pros and cons of doing this?


Thanks in advance for your help
Ashok









Tom Kyte

Followup  

April 21, 2009 - 1:23 pm UTC

... (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)

Space Management in 10g with LMT and segment management = AUTO

April 22, 2009 - 1:24 am UTC

Reviewer: ashok from India

Hi Tom,

Thanks for the reply.

Regarding your statement -
"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."

I have 2 things to back my statement:

1. The total size allocated as on date to this segment is 4,288 MB . Number of unformated blocks (as on date as per show_space) = 459,019 meaning approx 3586MB space. This means that the highest demand for space ever was 700 MB (4288MB - 3586MB) inspite of how much data has been inserted/deleted till date. Am I wrong in this Analysis?

2. Please can you also clarify what is "unformated blocks"? Is this blocks above HWM?

3. I agree that number of rows in a table cannot be used to compare size of segment.

A lots of space allocated now but few rows does mean that AT SOME POINT IN THE PAST THERE WERE A LOT OF ROWS which may have been deleted. This is right in this case as well. Some data is daily inserted and deleted from this table.

Having said this, the total number number of "formated blocks" is only 88824 blocks [694 MB (8K block]. This means at any given point of time, there was never data beyond 694MB.

Then why allocation of such large nunber of extents (approx 3.5 GB) still not used?

I may not be fully aware of the terms you use like "Unformated blocks" and hence my analysis may not be correct? Can you explain please if I my understanding of term is not correct?


Thanks in advance
Ashok



Tom Kyte

Followup  

April 23, 2009 - 12:03 pm UTC

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.

dbms_space.space_usage

May 06, 2009 - 3:26 am UTC

Reviewer: A reader

Hi Tom,
       I used dbms_space.space_usage on an index.

SQL> exec XX_space_usage('JTM','JTM_MTL_SYSTEM_ITEMS_ACC_U1','INDEX');
0-25% FREE Blocks = 0 Bytes = 0
25-50% FREE Blocks = 913 Bytes = 7479296
50-75% FREE Blocks = 0 Bytes = 0
75-100% FREE Blocks = 0 Bytes = 0
Full Blocks = 297311 Bytes = 2435571712

I did a rebuild on the index:
alter index JTM.JTM_MTL_SYSTEM_ITEMS_ACC_U1 rebuild online parallel 4 nologging;

And again ran the dbms_space.space_usage.

SQL> exec XX_space_usage('JTM','JTM_MTL_SYSTEM_ITEMS_ACC_U1','INDEX');
0-25% FREE Blocks = 0 Bytes = 0
25-50% FREE Blocks = 16 Bytes = 131072
50-75% FREE Blocks = 0 Bytes = 0
75-100% FREE Blocks = 0 Bytes = 0
Full Blocks = 197920 Bytes = 1621360640

Please note that the full blocks have decreased a lot after the rebuild. I fail to understand why?
The DB is 9.2.0.8 (apps DB), the tablespace name is APPS_TS_TX_IDX. its characteristics are:
Locally Managed, Segment space management->Auto,Allocation->Uniform.
 

Tom Kyte

Followup  

May 11, 2009 - 9:36 am UTC

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)




dbms_space.space_usage

May 06, 2009 - 3:26 am UTC

Reviewer: Avnish from India

Hi Tom,
       I used dbms_space.space_usage on an index.

SQL> exec XX_space_usage('JTM','JTM_MTL_SYSTEM_ITEMS_ACC_U1','INDEX');
0-25% FREE Blocks = 0 Bytes = 0
25-50% FREE Blocks = 913 Bytes = 7479296
50-75% FREE Blocks = 0 Bytes = 0
75-100% FREE Blocks = 0 Bytes = 0
Full Blocks = 297311 Bytes = 2435571712

I did a rebuild on the index:
alter index JTM.JTM_MTL_SYSTEM_ITEMS_ACC_U1 rebuild online parallel 4 nologging;

And again ran the dbms_space.space_usage.

SQL> exec XX_space_usage('JTM','JTM_MTL_SYSTEM_ITEMS_ACC_U1','INDEX');
0-25% FREE Blocks = 0 Bytes = 0
25-50% FREE Blocks = 16 Bytes = 131072
50-75% FREE Blocks = 0 Bytes = 0
75-100% FREE Blocks = 0 Bytes = 0
Full Blocks = 197920 Bytes = 1621360640

Please note that the full blocks have decreased a lot after the rebuild. I fail to understand why?
The DB is 9.2.0.8 (apps DB), the tablespace name is APPS_TS_TX_IDX. its characteristics are:
Locally Managed, Segment space management->Auto,Allocation->Uniform.
 

dbms_space.space_usage

May 21, 2009 - 6:33 am UTC

Reviewer: A reader

Hi Tom,
Thanks for the response. I guess rebuilding an index is not a very good idea most of the times.

Thanks,
Avnish

A reader

November 28, 2009 - 7:05 am UTC

Reviewer: Jenny from CHINA

Hi Tom
  I got a problem of HWM.please Help me.
  I create a new table,and want to know its HWM.Oracle version is 10.2.0.1.
SQL> create table t_test1 as select * from all_objects where 1=2;
SQL>ANALYZE TABLE t_test1 ESTIMATE STATISTICS;
SQL> select blocks,empty_blocks from user_tables where table_name='T_TEST1';

    BLOCKS EMPTY_BLOCKS
---------- ------------
         0            8

SQL> call show_space('T_TEST1');
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................5
Unused Bytes............................40960
Last Used Ext FileId....................4
Last Used Ext BlockId...................57
Last Used Block.........................3

there are 8 empty blocks in user_tables, and there are 5 unused blocks.Difference of three number .
And then insert into t_test1 30000 rows

SQL> insert into t_test1 select *  from all_objects where rownum<=30000;
30000 rows created.

SQL> ANALYZE TABLE jenny.t_test1 ESTIMATE STATISTICS; 

SQL> select blocks,empty_blocks from user_tables where table_name='T_TEST1';
    BLOCKS EMPTY_BLOCKS
---------- ------------
       432           80
SQL> call show_space('T_TEST1');
Total Blocks............................512
Total Bytes.............................4194304
Unused Blocks...........................64
Unused Bytes............................524288
Last Used Ext FileId....................4
Last Used Ext BlockId...................649
Last Used Block.........................64
Call completed.

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

Tom Kyte

Followup  

November 28, 2009 - 2:00 pm UTC

... 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"?


November 28, 2009 - 7:40 pm UTC

Reviewer: Jenny from China

sorry. my english is bad.Thank for the response.
I want to say:
In the first case, there are 8 blocks above high water mark in user_tables.but there are 5 blocks above high water mark in show_space.
why the number of above high water mark is difference.
and how is oracle use of that 3 blocks(show_space shows there are 3 block under high water mark).
If i want to know how much blocks above high water wark,which number is true ? empty_blocks in user_tables or Unused Blocks in show_space?

Tom Kyte

Followup  

November 29, 2009 - 8:28 am UTC

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;




difference in num_freelist_blocks and dbms_space.free_blocks

January 07, 2010 - 1:18 pm UTC

Reviewer: Reader from INDIA

SQL> var free_blocks number
SQL> exec DBMS_SPACE.FREE_BLOCKS('SYS', 'AUD$', 'TABLE', 0, :free_blocks); 

PL/SQL procedure successfully completed.

SQL> print free_blocks

FREE_BLOCKS
-----------
          8

SQL>  select blocks,EMPTY_BLOCKS,NUM_FREELIST_BLOCKS  from dba_tables where table_name='AUD$';

    BLOCKS EMPTY_BLOCKS NUM_FREELIST_BLOCKS
---------- ------------ -------------------
      7465         6230                  18


Why there is a difference between these num_free_list_blocks and free_blocks. They should be same right?
If N_F_Blocks reported in dba_tables specify the blocks are candidate for inserts then what exactly free_blocks shows. Can you please explain this
Tom Kyte

Followup  

January 11, 2010 - 8:40 pm UTC

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

dbms_space is 'real time'

how to read the output of show_space

June 04, 2010 - 12:23 am UTC

Reviewer: peggy from Jakarta

Hi Tom,
I run the show_space procedure on a small table (0.0625 MB and 130 rows, oracle 10.2.0.3.0) and get this result :
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 0
FS2 Blocks (25-50) ..................... 0
FS3 Blocks (50-75) ..................... 1
FS4 Blocks (75-100)..................... 3
Full Blocks ..................... 1
Total Blocks............................ 8
Total Bytes............................. 65,536
Total MBytes............................ 0
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 6
Last Used Ext BlockId................... 126,041
Last Used Block......................... 8

I understand that I have total blocks of 8, and 1 block is used by oracle. 1 block (FS3) + 3 blocks (FS4) + 1 full block + 1 block for oracle = 6 blocks. Where do 2 blocks go to ?
Can I use show_space output to determine when to reorganize a table ?
This is from another table in production
Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 9
FS2 Blocks (25-50) ..................... 15
FS3 Blocks (50-75) ..................... 22
FS4 Blocks (75-100)..................... 5,783
Full Blocks ..................... 235,376
Total Blocks............................ 241,920
Total Bytes............................. 1,981,808,640
Total MBytes............................ 1,890
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 18
Last Used Ext BlockId................... 1,079,049
Last Used Block......................... 8,192
thanks
Tom Kyte

Followup  

June 08, 2010 - 12:35 pm UTC

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?

FS4 blocks

September 22, 2010 - 7:12 pm UTC

Reviewer: Matt from California Republic

Oracle 10.2.0.3 and TS with segment space management auto.

I have a situation where several tables have 95% of their data deleted daily. So data is inserted all day then once per day what is deemed unnecessary data (about 90-95%) of the total is deleted. The tablespace is ASSM and if I am understanding we should see such blocks go back on the "freelist" to be filled up again. But we see a good proportion remain in the FS4 area (76-100% free space). For instance here is one of the tables run through show_space procedure:

execute show_space('TABLE_XXX','SST');

Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 29
FS2 Blocks (25-50) ..................... 265,813
FS3 Blocks (50-75) ..................... 581,242
FS4 Blocks (75-100)..................... 1,545,335
Full Blocks ..................... 4,859,260
Total Blocks............................ 7,259,264
Total Bytes............................. 59,467,890,688
Total MBytes............................ 56,713
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 29
Last Used Ext BlockId................... 3,937,673
Last Used Block......................... 8,192

Is something wrong or would this be something you'd expect to see using ASSM in this fashion... AND if so should I move these to segment space management manual?

thanks
Matt
Tom Kyte

Followup  

September 23, 2010 - 10:45 am UTC

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

FS4 blocks

September 23, 2010 - 1:00 pm UTC

Reviewer: Matt from California Republic

Right I guess my issue is that it appears to me that 75-100% free blocks are not being reused. For instance here is output for show_space today:

Unformatted Blocks ..................... 0
FS1 Blocks (0-25) ..................... 42
FS2 Blocks (25-50) ..................... 278,600
FS3 Blocks (50-75) ..................... 609,957
FS4 Blocks (75-100)..................... 1,566,942
Full Blocks ..................... 4,796,138
Total Blocks............................ 7,259,264
Total Bytes............................. 59,467,890,688
Total MBytes............................ 56,713
Unused Blocks........................... 0
Unused Bytes............................ 0
Last Used Ext FileId.................... 29
Last Used Ext BlockId................... 3,937,673
Last Used Block......................... 8,192

There are less full blocks and more 75%+ free blocks. Shouldn't we see much much much more blocks as 0-25% free than 75-100% free given my situation?

Is there any deeper way to tell what kind of decisions ASSM is making? What about my question about whether ASSM is a good choice for such a table?

Thanks in advance.

FS4 blocks

September 23, 2010 - 4:59 pm UTC

Reviewer: Matt from California Republic

OK. I see my error. The total blocks are not increasing. Thus it is reusing free space.

Still would like to know if you see any benefit for changing to MSSM, though.

Thanks again
Tom Kyte

Followup  

September 27, 2010 - 9:59 am UTC

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.

first 64K in a LMTS datafile

October 28, 2010 - 8:29 am UTC

Reviewer: A reader

Hi Tom,
You mentioned that in a Locally managed tablespace, the first 64k of a datafile will be used by system.
and I am thinking if the segment space management is AUTO, then the first block in the extent will be used for bitmaps and space mangement in the segment. Please correct me if I am wrong.

Also, I checked the Oracle concepts guide and db admin guide, but couldn't find this information, can you point me where we can get this information please.

Thanks



Tom Kyte

Followup  

October 28, 2010 - 12:14 pm UTC

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.

Table Size

August 14, 2011 - 11:09 am UTC

Reviewer: Arvind Mishra from USA

Hi Tom,

You said:

Free Blocks...... Number of blocks on the freelist
Total Blocks..... Total blocks allocated to the table
Total Bytes...... Total bytes allocated to the table
Unused Blocks.... Blocks that have never contained data
Unused Bytes..... The above in bytes

If I want to know the size of an existing table then will it be the db_block_size*total_blocks ot db_block_size*(total_blocks-unused_blocks)?

Thanks,

Arvind

Tom Kyte

Followup  

August 14, 2011 - 8:39 pm UTC

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.

Table Size

August 14, 2011 - 11:18 pm UTC

Reviewer: Arvind Mishra from USA

Thanks Tom

September 24, 2012 - 6:14 am UTC

Reviewer: SUMIT from india

TOM..YOU ARE ROCK............
SO MANY THINGS I HAVE BEEN LEARNING HERE............

Space usage by table in tablespace

May 21, 2013 - 12:19 pm UTC

Reviewer: Sagar from India

Tom,

I require total space occupied by a table in a tablespace for which I do the below.

DECLARE
su NUMBER;
sa NUMBER;
cp NUMBER;
allocated_bytes NUMBER;
used_bytes NUMBER;
total_bytes NUMBER;

BEGIN
      SELECT sum(bytes) INTO allocated_bytes FROM
      (
       SELECT table_name, segment_type, segment_name, bytes FROM
        (
          SELECT l.table_name,
                 s.segment_type,
                 s.segment_name,
                 s.bytes
          FROM user_segments s,
               user_lobs l
          WHERE l.table_name='EMP'
          AND s.segment_name=l.segment_name
          UNION
          SELECT i.table_name,
                 s.segment_type,
                 s.segment_name,
                 s.bytes
          FROM user_segments s,
               user_indexes i
          WHERE i.table_name='EMP'
          AND s.segment_name=i.index_name
          UNION
          SELECT t.table_name,
                 s.segment_type,
                 s.segment_name,
                 s.bytes
          FROM USER_SEGMENTS S,
               USER_TABLES T
          WHERE t.table_name='EMP'
          AND s.segment_name=t.table_name
        )
      );

      dbms_space.object_space_usage('USERS', 'EMP', 'TABLE', NULL, su, sa, cp);

      used_bytes := su;
      total_bytes :=  allocated_bytes + su;

      dbms_output.put_line('Allocated Bytes: '||allocated_bytes/(1024*1024)|| ' MB');
      dbms_output.put_line('Used Bytes: '||used_bytes/(1024*1024)|| ' MB');
      dbms_output.put_line('Total Bytes: '||substr((total_bytes/(1024*1024)),1,6)|| ' MB');

END;
/

Allocated Bytes: 1.25 MB
Used Bytes: .00566196441650390625 MB
Total Bytes: 1.2556 MB

PL/SQL procedure successfully completed.



I use DBMS_SPACE.OBJECT_SPACE_USAGE to find out used bytes by the table in the tablespace.

There is another procedure "DBMS_SPACE.SPACE_USAGE". This shows the space usage of data blocks under the segment High Water Mark but can only be used with tablespaces created with ASSM.

Can you tell me what is the difference between SPACE_USAGE and OBJECT_SPACE_USAGE procedures , and the figure I get above using OBJECT_SPACE_USAGE - do I need to use SPACE_USAGE instead of the other to get total used space by the table?

Tia.
Tom Kyte

Followup  

May 21, 2013 - 5:16 pm UTC

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.

problem with show_space procedure

September 22, 2014 - 2:32 pm UTC

Reviewer: sky

hi Tom,

i have problem , could You help me, please.
When i running procedure space_show i get error

BEGIN show_space('table'); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "DEFBGK.SHOW_SPACE", line 44
ORA-06512: at line 1

how fix it ?

Thank You for help.

More to Explore

DBMS_SPACE

More on PL/SQL routine DBMS_SPACE here