Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Alan.

Asked: September 10, 2002 - 11:41 am UTC

Last updated: July 16, 2013 - 2:24 pm UTC

Version: 9.2.0.1.0

Viewed 10K+ times! This question is

You Asked

Hi Tom,

I'm trying to use your procedure SHOW_SPACE and I'm encountering oracle error message saying "ORA-10618: Operation not allowed on this segment". I have tried to research on the error and the error states that "This DBMS_SPACE operation is not permitted on segments in tablespaces with AUTO SEGMENT SPACE MANAGEMENT". It is possible for you explain to me what this means.

Thanks and more power.


and Tom said...

Basically -- it means you cannot call dbms_space.free_blocks on an object in a AUTO SEGMENT SPACE managed tablespace (where segment space is not managed via freelists anymore but in a bitmap like a locally managed tablespace)

My solution:


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 )
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;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') ||
p_num );
end;
begin
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' )
)
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;

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( 'Free Blocks', l_free_blks );
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;
/



Rating

  (50 ratings)

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

Comments

a reader, September 10, 2002 - 1:13 pm UTC

Tom,
Is SEGMENT_SPACE_MANAGEMENT a column name in dba_segments?
I don't see it. When run your code to create procedure, get
compile error:
LINE/COL ERROR
-------- -----------------------------------------------------------------
24/16 PL/SQL: SQL Statement ignored
30/39 PLS-00201: identifier 'SEGMENT_SPACE_MANAGEMENT' must be declared

Tom Kyte
September 10, 2002 - 2:01 pm UTC

this only applies to 9i.

For 8i and before, see
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:231414051079 <code>



RE: SEGMENT_SPACE_MANAGEMENT

Mark A. Williams, September 10, 2002 - 1:26 pm UTC

Try looking in dba_tablespaces for SEGMENT_SPACE_MANAGEMENT...

HTH,

Mark

9i

Jan van Mourik, September 10, 2002 - 2:44 pm UTC

You can use dbms_space.space_usage for auto segments space tablespaces. So for 9i you could do something like this:

--- code piece
select ts.segment_space_management
into t_segment_space_management
from dba_segments seg
, dba_tablespaces ts
where seg.segment_name = t_segname
and seg.owner = t_owner
and seg.tablespace_name = ts.tablespace_name
;
--
if t_segment_space_management = 'AUTO'
then
dbms_space.space_usage (
t_owner,
t_segname,
t_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( '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 => t_owner,
segment_name => t_segname,
segment_type => t_type,
freelist_group_id => 0,
free_blks => l_free_blks
);
--
p( 'Free Blocks', l_free_blks );
end if;
--- end of code piece

To show related info...


Tom Kyte
September 10, 2002 - 3:33 pm UTC

Thanks! I'll add that

Space used by a table

Vinnie, December 23, 2003 - 3:56 pm UTC

Tom,

How can I use show_space to show the total # of bytes a table with blob data is currently using.

Happy Holidays!

Tom Kyte
December 23, 2003 - 5:28 pm UTC

you can use show space on a lob segment.

to make it show the table + log segment, you'd have to query dba_tab_cols, find the lobs, run show space again for the lobs....

I'd just call show_space on the lob segments myself or write a higher level routine that calls show space for the table and then any lob columns.

Lob Segment Size & Chunk Size

Vinnie, January 15, 2004 - 3:19 pm UTC

Tom,

If I run Show space on a LOB segment in which I just inserted 1 row I show the following:
Total Blocks ... 4
Total Bytes .... 65536
...
...
My db Block size is 16k
The blob I inserted was just a tad over 4k.
Is there anyway to have control of the allocation?
I tried changing the chunk to 32k & nothing seemed to change.
Thanks

Tom Kyte
January 16, 2004 - 1:01 am UTC

and the full example we can all reproduce with is?? the whole thing -- create table, etc.

Lob Space Example

Vinnie, January 16, 2004 - 8:49 am UTC

create table t (x int primary key, y clob, z varchar2(20))
lob (y) store as (tablespace lob_test chunk 32768);

declare
l_string long := rpad( '*', 3000, '*' );
l_clob clob;
l_abc varchar2(5) := 'abc';
begin
insert into t values ( 2, l_string, '****' );
l_string := l_string || l_string;
insert into t values ( 3, l_string, '********' );
insert into t values ( 4, empty_clob(), l_abc ) returning y into L_clob;
dbms_lob.writeAppend( l_clob, length(l_abc), l_abc );
end;
/

select a.segment_name
from user_segments a, user_lobs b
where a.segment_name = b.segment_name
and b.table_name = 'T';

SEGMENT_NAME
------------------------
SYS_LOB000005319C00002$$

exec show_space('SYS_LOB000005319C00002$$',<user>,'LOB')

Total blocks ...8
Total Bytes ....131072
Unused Blocks ..0
Unused Bytes ...0
Last Used Block 4

select dbms_lob.getlength(y) from t;

DBMS_LOB.GETLENGTH(Y)
---------------------
3000
6000
3

My question is:
How can I get the LOB not to allocate so much?
My avg. LOB size is between 16k - 20k.
Thanks again

PS. I had to hand type everything because of a closed net issue. Hope this is enough.



Tom Kyte
January 16, 2004 - 11:34 am UTC

you haven't show "free" blocks. I have 8 blocks allocated -- 2 free.

now, of the three rows you have -- only one is "out of line". It takes at least 2 blocks (32k chunk) in your 16 tablespace. The other 4 blocks - lets call them "overhead" -- the lob segment is using them.

second insert of the 6000 byte row added 2 blocks (one chunk), using 8 blocks.



Lob Segments

Vinnie, January 16, 2004 - 12:30 pm UTC

Dosen't the
exec show_space('SYS_LOB000005319C00002$$',<user>,'LOB')

Total blocks ...8
Total Bytes ....131072
Unused Blocks ..0
Unused Bytes ...0
Last Used Block 4
show the LOB Segement alone?
If so, why would I see a total of 8 Blocks alocated with 0 unused blocks for only a 6k CLOB?

Tom Kyte
January 16, 2004 - 12:49 pm UTC

consider it overhead (and consider it "not reproduced everywhere" -- i don't see the same thing you do)

Error --

Reader, April 03, 2004 - 5:29 am UTC

hi,

Version :- Oracle 9.2.0 on Sun box.

SQL> select * from user_role_privs;

GRANTED_ROLE                   ADM DEF OS_
------------------------------ --- --- ---
CONNECT                        NO  YES NO
DBA                            NO  YES NO
RESOURCE                       NO  YES NO

SQL> 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    p_partition in varchar2 default NULL )
  7  authid current_user
  8  as
  9      l_free_blks                 number;
 10  
 11      l_total_blocks              number;
 12      l_total_bytes               number;
 13      l_unused_blocks             number;
 14      l_unused_bytes              number;
 15      l_LastUsedExtFileId         number;
 16      l_LastUsedExtBlockId        number;
 17      l_LAST_USED_BLOCK           number;
 18      procedure p( p_label in varchar2, p_num in number )
 19      is
 20      begin
 21          dbms_output.put_line( rpad(p_label,40,'.') ||
 22                                p_num );
 23      end;
 24  begin
 25      for x in ( select tablespace_name
 26                   from dba_tablespaces
 27                  where tablespace_name = ( select tablespace_name
 28                                              from dba_segments
 29                                             where segment_type = p_type
 30                                               and segment_name = p_segname
 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      end loop;
 42  
 43      dbms_space.unused_space
 44      ( segment_owner     => p_owner,
 45        segment_name      => p_segname,
 46        segment_type      => p_type,
 47            partition_name    => p_partition,
 48        total_blocks      => l_total_blocks,
 49        total_bytes       => l_total_bytes,
 50        unused_blocks     => l_unused_blocks,
 51        unused_bytes      => l_unused_bytes,
 52        LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 53        LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 54        LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 55  
 56      p( 'Free Blocks', l_free_blks );
 57      p( 'Total Blocks', l_total_blocks );
 58      p( 'Total Bytes', l_total_bytes );
 59      p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
 60      p( 'Unused Blocks', l_unused_blocks );
 61      p( 'Unused Bytes', l_unused_bytes );
 62      p( 'Last Used Ext FileId', l_LastUsedExtFileId );
 63      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
 64      p( 'Last Used Block', l_LAST_USED_BLOCK );
 65  end;
 66  /

Warning: Procedure created with compilation errors.

SQL> sho user
USER is "JAG"
SQL> show err
Errors for PROCEDURE SHOW_SPACE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
24/14    PL/SQL: SQL Statement ignored
27/50    PL/SQL: ORA-00942: table or view does not exist

I have resource,connect and DBA role.
but It could not select dba_* tables.
 

Tom Kyte
April 03, 2004 - 8:51 am UTC

Data Size

mo, July 08, 2004 - 10:42 am UTC

Tom:

What is the easiest way to get actual data size in a table/database?

is there any system view for that?

Thanks,


Tom Kyte
July 08, 2004 - 11:46 am UTC

define "actual data size".

if you mean the number of blocks, query dba_segments. to me that is "relevant", that is how many blocks are dedicated to this segment.

some people mean "size of data, if I unloaded to a file" -- for that you analyze and multiply avg row size by rows (user_tables)

some people mean "number of blocks below high water mark" -- show_space shows that.



data size

mo, July 08, 2004 - 12:11 pm UTC

Tom:

1. Customer wants to know how big the data is. I guess the average row size * number of records will give you that. however do you have to do that manually? How do you compute this?

2. What would number of blocks give you in realtion to data size?

3. Show_Space will only show allocated space only? corect?

4. Would v$datafiles give you that info?

Thank you

Tom Kyte
July 08, 2004 - 12:28 pm UTC

1) you mulitply the two columns from user_tables after analyzing the table.

2) if you load a zillion rows (lots of blocks) and then delete them all -- you have "no data, but lots of blocks". so, the amount of blocks is an upper bound on the loaded data.

3) correct

4) it would show you how big a file is, thats about it.

data size

mo, July 08, 2004 - 1:01 pm UTC

Tom:

1. Are the two columns:
AVG_ROW_LEN * NUM_ROWS = 290 * 444 = 128760 blocks

to get bytes you multiply this by block size (32 K) or 16 K?

PCT_FREE : 10
PCT_USED : 40
INI_TRANS : 1
MAX_TRANS : 255
INITIAL_EXTENT : 40960
NEXT_EXTENT : 106496
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
LOGGING : YES
BACKED_UP : N
NUM_ROWS : 444
BLOCKS : 19
EMPTY_BLOCKS : 0
AVG_SPACE : 1266
CHAIN_CNT : 6
AVG_ROW_LEN : 290
AVG_SPACE_FREELIST_BLOCKS : 6993
NUM_FREELIST_BLOCKS : 2

2. To do this for the whole database you run a query that sums up the multiplication of these two columns all tables in USER_TABLES?

3. Is not there a way to do this instead?
Data Size = Allocated Size - Free Space (for data file)

Tom Kyte
July 08, 2004 - 1:17 pm UTC

1)

= that many *bytes*

avg_row_len is in bytes.
number of rows is how many rows.

bytes * number of rows = total in bytes.

2) yes.

3) you said you were interested in a different size. sum(bytes) from dba_segments = allocated files - free space already.

think about it......

data size

mo, July 08, 2004 - 4:39 pm UTC

Tom:

I am little confused. From User_tables I get data size for a table 128 K. From dba_segments I get 40 K.

What I meant before is that instead of doing the SUM query on USER_TABLES, should not I be able to take the datafile storing all these tables. Look at the allocated size and subtract free space which should give me the same answer in my SUM query. Correct?

OWNER : DEV
SEGMENT_NAME : INT
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : USERDATA
HEADER_FILE : 2
HEADER_BLOCK : 367
BYTES : 40960
BLOCKS : 5
EXTENTS : 1
INITIAL_EXTENT : 40960
NEXT_EXTENT : 40960
MIN_EXTENTS : 1
MAX_EXTENTS : 505
PCT_INCREASE : 0
FREELISTS : 1
FREELIST_GROUPS : 1
RELATIVE_FNO : 2
BUFFER_POOL : DEFAULT

2. If I have about 30 tables in my schema do I have to do "analyze table t compute statistics" 30 times. I think on another article you say to use DBMS_STATISTICS but it does not seem that easy and I have to create my own tables for that. Is this correct and is it worth it?

Thank you,

Tom Kyte
July 08, 2004 - 8:45 pm UTC

1) show me. -- show the output from user_tables that differs from dba_segments.

if you look at the allocated size and take away "dba_free_space", all you get is the number of blocks allocated to a segment which is what you get from dba_segments which is exactly what you said "i don't want"

2) dbms_stats.gather_schema_stats( user );

bamm, done, one command. you do not create any "tables" for this - not unless you want to export statistics that is.

Calculate the space usage by the users.

A reader, July 16, 2004 - 8:25 am UTC

Hi Tom,

I have a requirement where i want to calculate the space used by each user .
Basically many departments are paying together for the usage of the space on the server. Now each department needs to know the about of data stored by their department.
This includes the relational data also. I can easily calculatet he files stored by them using dbms_lob.getlength and then grouping by their department name.

But what i want is also to calculate all the data they have in all the tables of the database.

any suggestions.

Tom Kyte
July 16, 2004 - 11:20 am UTC

just query dba_segments and add stuff up by user.

A reader, July 19, 2004 - 9:52 am UTC

Tom, i would be grtful if you can show a small example with emp itself.
in emp table space occupeied by each department will help me in catch the logic.

thanks in advance.

Tom Kyte
July 19, 2004 - 10:25 am UTC

are you asking "how much space of the emp table is taken up by each deptno?"

that is not really "answerable", it is not really meaningful either.

but you would have to

select deptno, sum( vsize(empno)+vsize(ename)+....all columns here .... )
from emp
group by deptno;


that could be used to get relative "percentages"

ps$tkyte@ORA9IR2> select deptno, sum_size, ratio_to_report(sum_size) over ()
2 from (
3 select deptno,
4 sum( nvl(vsize(empno),0)+nvl(vsize(ename),0)+nvl(vsize(job),0)
5 +nvl(vsize(mgr),0)+nvl(vsize(hiredate),0)
6 +nvl(vsize(sal),0)+nvl(vsize(comm),0)+nvl(vsize(deptno),0)) sum_size
7 from emp
8 group by deptno );

DEPTNO SUM_SIZE RATIO_TO_REPORT(SUM_SIZE)OVER()
---------- ---------- -------------------------------
10 92 .210045662
20 152 .347031963
30 194 .442922374


that shows deptno=30 consumes about 44% of the storage in this emp table.

Having problems

Craig, November 17, 2004 - 6:16 am UTC

I created your procedure as sys, it created with no errors, when I run the procedure exec show_space('TRADE'); I get 
RIMSTEST_SQL>exec show_space('TRADE');
BEGIN show_space('TRADE'); END;

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

Although I can select from the table 

Tom Kyte
November 17, 2004 - 10:44 am UTC

do not do not do not DO NOT create things as sys, system, ctxsys, etc etc etc unless the documentation instructs you to.

they are "internal accounts". they are special, sys in particular is magic.

what is line 74 in your case? remember this is an authid current_user procedure, you need to have access to dba_ views in order to run this (the INVOKER does)

resolving issue on using show_space

Nikunj, January 20, 2005 - 7:59 am UTC

Dear Tom,

I had try to impliment show space procedure but i am getting the same error as others i try certain things but can't get the success.

can u please give me steps to immliment the same and also which things need to be care i.e. rights assignment etc.

I have Oracle EE 9.2 on windows 2000.

regards,

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jan 20 18:21:47 2005

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


Connected to:
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> 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    p_partition in varchar2 default NULL )
  7  authid current_user
  8  as
  9      l_free_blks                 number;
 10  
 11      l_total_blocks              number;
 12      l_total_bytes               number;
 13      l_unused_blocks             number;
 14      l_unused_bytes              number;
 15      l_LastUsedExtFileId         number;
 16      l_LastUsedExtBlockId        number;
 17      l_LAST_USED_BLOCK           number;
 18      procedure p( p_label in varchar2, p_num in number )
 19      is
 20      begin
 21          dbms_output.put_line( rpad(p_label,40,'.') ||
 22                                p_num );
 23      end;
 24  begin
 25      for x in ( select tablespace_name
 26                   from dba_tablespaces
 27                  where tablespace_name = ( select tablespace_name
 28                                              from dba_segments
 29                                             where segment_type = p_type
 30                                               and segment_name = p_segname
 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      end loop;
 42  
 43      dbms_space.unused_space
 44      ( segment_owner     => p_owner,
 45        segment_name      => p_segname,
 46        segment_type      => p_type,
 47            partition_name    => p_partition,
 48        total_blocks      => l_total_blocks,
 49        total_bytes       => l_total_bytes,
 50        unused_blocks     => l_unused_blocks,
 51        unused_bytes      => l_unused_bytes,
 52        LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 53        LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 54        LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 55  
 56      p( 'Free Blocks', l_free_blks );
 57      p( 'Total Blocks', l_total_blocks );
 58      p( 'Total Bytes', l_total_bytes );
 59      p( 'Total MBytes', trunc(l_total_bytes/1024/1024) );
 60      p( 'Unused Blocks', l_unused_blocks );
 61      p( 'Unused Bytes', l_unused_bytes );
 62      p( 'Last Used Ext FileId', l_LastUsedExtFileId );
 63      p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
 64      p( 'Last Used Block', l_LAST_USED_BLOCK );
 65  end;
 66  /

Procedure created.

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 59
ORA-06512: at "TEST.SHOW_SPACE", line 42
ORA-06512: at line 1


SQL>  

Tom Kyte
January 20, 2005 - 10:38 am UTC

do you really have a table named "t", in lowercase?

ops$tkyte@ORA9IR2> 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 97
ORA-06512: at "OPS$TKYTE.SHOW_SPACE", line 64
ORA-06512: at line 1
 
 
ops$tkyte@ORA9IR2> exec show_space( 'T' );
 
PL/SQL procedure successfully completed.
 
 

A reader, March 01, 2005 - 4:33 pm UTC

Hi Tom,

"show_space" shows the details only for a particular table.

What is the easiest way to get a similar output for all the tables in a schema (instead of passing the table name one by one to show_space ) ?

thanks
Regi

Tom Kyte
March 01, 2005 - 5:46 pm UTC

you would have to either

a) put it in a loop and call it,

begin
for x in ( select ... from ... )
loop
show_space( .... );
end loop;
end;
/


b) create an object type that holds the output, and turn it into a pipelined function, first whack at it was here:

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

A reader, March 02, 2005 - 3:15 pm UTC

Thanks

show space problem

riyaz, March 16, 2005 - 8:45 am UTC

Hi Tom,
Excellent procedure, using nicely.

But it is giving problem, if the same table exists in more than one schema. Please guide.
e.g., table "FA" exists TRN0102, TRN0203 and TRN0405.

idle> exec show_space( 'FA' , 'TRN0405');
BEGIN show_space( 'FA' , 'TRN0405'); END;

*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at "SYS.SHOW_SPACE", line 24
ORA-06512: at line 1


Tom Kyte
March 16, 2005 - 8:47 am UTC

set define off

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;
/
set define on


has the fix for that (missing predicate on owner in the old one)

Rob K, March 16, 2005 - 6:08 pm UTC

I think you need to add another paramter to the
dbms_space.free_blocks call to handle partitions.

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,
partition_name => p_partition)

Tom Kyte
March 17, 2005 - 8:28 am UTC

darn, how'd i lose that from the original, thanks

On the number of blocks

IK, September 06, 2005 - 6:47 am UTC

Tom,

I just took a sample table (non partitioned) in my schema to see the block count and space usage.

I got different results for

select count( distinct dbms_rowid.ROWID_BLOCK_NUMBER(rowid)) from TEMP;
Ans = 341

and

exec show_space('TEMP');
Free Blocks.............................
Total Blocks............................523
Total Bytes.............................4284416
Total MBytes............................4
Unused Blocks...........................8
Unused Bytes............................65536
Last Used Ext FileId....................1
Last Used Ext BlockId...................27559
Last Used Block.........................5

PL/SQL procedure successfully completed.

I was just wondering why DBMS_ROWID returned me 341 rows wheras there are in total 523 - 8 = 515 used blocks.

Could the answer be row chaining? How do i determine?

Essentially the ROWID does NOT change during row migration and chaining, right?

Thanks,

Tom Kyte
September 06, 2005 - 8:37 am UTC

there are so many reasons I could come up with.

insert 1,000,000 rows into a table.
delete them all.

count(rowid) = 0, but space is allocated.


insert 1,000,000 rows into a table, each row is 14k in size, you have an 8k block size tablespace - you'll have twice as many blocks as rows.


and so on.


If you want the number of blocks allocated and in use by the table, show_space.

If you want to know the number of rows, count(*).

If you want the know the number of blocks that have head rowid pieces -- your query.


If you want to know if the table has chained rows -- analyze command tells you that.


rowid does not change during row migration and chaining, correct.

show space runs forever,

sns, October 27, 2005 - 10:46 am UTC

I have installed the "show_space_for" function in my database.

Last night I ran it for a particular schema in a database to figure out any objects that have lots of free blocks.

The size of the schema is close to 450Gig.

The function ran for more than 13 hours and failed because of SNAPSHOT TOO OLD ERROR.

Is this because of the size of the schema?

How to overcome this kind of problem?

Thanks,

Tom Kyte
October 27, 2005 - 1:15 pm UTC

if you have really really big freelists - it could take quite a while to walk them to report on the free space.


if you believe that to be the case:

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

modify your copy to stop walking the freelist ( the scan limit) and just report back "MORE THAN NNNNN" instead of the exact count.

free blocks,

sns, October 27, 2005 - 1:46 pm UTC

Thanks for the suggestion. Just want to confirm; you said

<quote>
modify your copy to stop walking the freelist ( the scan limit) and just report
back "MORE THAN NNNNN" instead of the exact count
<quote>

So in my dbms_space.free_blocks procedure, if I put 1000(for example) for the scan_limit parameter, does it output only the objects that are having more than 1000 free blocks?

Thanks,

Tom Kyte
October 27, 2005 - 2:12 pm UTC

no, it stops reporting back the number of free blocks a given segment has at 1000.

it stops it from going and going and going and going.

ORA-01403

orafan, February 17, 2006 - 4:55 pm UTC

Hi Tom,

I installed this procedure, but for some reason it always fails with ORA-01403 when I try to run it:

SQL> exec show_space( 'SCOTT', 'EMP' );
BEGIN show_space( 'SCOTT', 'EMP' ); END;

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

Tom Kyte
February 17, 2006 - 5:30 pm UTC

create or replace
procedure show_space
( p_segname in varchar2, <<<<==== name
p_owner in varchar2 default user, <<<<==== optionally owner
p_type in varchar2 default 'TABLE',
p_partition in varchar2 default NULL )


you are passing OWNER, NAME

still error

orafan, February 17, 2006 - 6:43 pm UTC

SQL> exec show_space( 'EMP', 'SCOTT' );
BEGIN show_space( 'EMP', 'SCOTT' ); END;

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

Tom Kyte
February 18, 2006 - 8:21 am UTC

and therefore, given the user account you are running this as, you do not have access to SCOTT.EMP from this procedure.

run as scott, see it working there?

Error Running Proc

Russ Bass, April 05, 2006 - 3:20 pm UTC

I created the proc as our dba user (dbadmin) and I receive this message:

ERROR at line 1:
ORA-10618: Operation not allowed on this segment
ORA-06512: at "SYS.DBMS_SPACE", line 136
ORA-06512: at "DBADMIN.SHOW_SPACE", line 23
ORA-06512: at line 1

This occurs on all the tables I try to examine.

Thoughts?


I'm a dufuss, Ignore last post

Russ Bass, April 05, 2006 - 3:25 pm UTC

I jumped to the bottom and should have read the begining. My issue was resolved with the first post.

Sorry about that.

Output looks useful. What am I doing wrong?

Elaine H, July 17, 2006 - 12:31 pm UTC

I created the procedure under the user DI.  I granted select privs on dba_tablespaces and dba_segments to DI.  I also specifically granted exec on dbms_space to DI.

the table i want to run this for is partitioned.  i get the following:

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

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


or

SQL> exec show_space ('DI_NAME2','DI_NAME2_PART01');
BEGIN show_space ('DI_NAME2','DI_NAME2_PART01'); END;

*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SPACE", line 59
ORA-06512: at "DI.SHOW_SPACE", line 42
ORA-06512: at line 1


what am i still doing wrong? 

Tom Kyte
July 17, 2006 - 3:13 pm UTC

desc show_space

it'll tell you what it expects. I used good parameter names.

still can't input partition names.

Elaine H, July 18, 2006 - 2:20 pm UTC

the desc of show_space is as follows:

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
 P_PARTITION                    VARCHAR2                IN     DEFAULT


 the procedure completes successfully for non-partitioned objects.  Am i just using an invalid syntax?  is the name of the partition actually different than shown in dba_segments? 

Tom Kyte
July 19, 2006 - 8:55 am UTC

broke it for manaul segment space managed tablespaces, fix is:

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,
partition_name => p_partition ); /* <<<=== add this */
p( 'Free Blocks', l_free_blks );
end if;

-- and then the unused space API call to get the rest of the

Small change in procedure to make it work

Ray Fox, October 03, 2006 - 5:57 am UTC

Hi Tom
Small comment. I had to change show_space to make it work. The change is in the loop select:

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 owner = p_owner)
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO'
)

I had two schemas with the same segment_name in the same tablespace - the query for tablespace_name returned to many rows error (ORA-01427: single-row subquery returns more than one row). Added the owner=p_owner condition and also moved the bracket up to after p_owner.

Alternatively, if the equals is replaced by an 'in' as below :
select tablespace_name
from dba_tablespaces
where tablespace_name in ( select tablespace_name
from dba_segments
where segment_type = p_type
and segment_name = p_segname
and SEGMENT_SPACE_MANAGEMENT <> 'AUTO')
Then the loop works, but is only executed once. I think that if the other schema was in a different tablespace, then the dbms_space.free_blocks call would fail because the p_owner would not change.
Hope I haven't missed or changed some hidden functionality.
Ray

Seen the fix - please ignore last review

Ray Fox, October 03, 2006 - 6:05 am UTC


How do you use the show_space proc on partitioned table?

bill, October 18, 2006 - 2:32 pm UTC

I tried something like

EXEC Show_Space('FACT_TABLE_PARTITIONED','ABC','TABLE','PARTITION')

ORA-03204: the segment type specification should indicate partitioning
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "ITDBA.SHOW_SPACE", line 23
ORA-06512: at line 1


Tom Kyte
October 18, 2006 - 3:54 pm UTC

ops$tkyte%ORA10GR2> exec show_space( 'T', user, 'TABLE PARTITION', 'PART1' );
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...................              57
Last Used Block.........................               3

PL/SQL procedure successfully completed.
 

bill

bill, October 20, 2006 - 1:07 pm UTC

Thanks for your prompt reply, Tom. I wonder if you can help further. Thanks.

schema1 has DBA role so it can see all objects in schema2.
The following SQL is run under schema1 against the object of schema2.'P06_05' is one of the partition name of the partitioned table,'INVENTORY_WEEKLY_FACT'.

SET serveroutput ON
EXEC schema1.Show_Space('INVENTORY_WEEKLY_FACT','schema2','TABLE PARTITION','P06_05')

I received:

ORA-03205: partition name is required when partitioned type is specified
ORA-06512: at "SYS.DBMS_SPACE", line 74
ORA-06512: at "ITDBA.SHOW_SPACE", line 80
ORA-06512: at line 1


Tom Kyte
October 20, 2006 - 1:23 pm UTC

we'd have to "debug" it to see what precisely we are passing into the dbms call that is failing - why don't you isolate that call by itself and test it out?

(I don't really use this across schemas much to tell you the truth, could be something "wrong" with it in that regards...)

Show_space procedure fails with following error

Johny Alex, May 18, 2007 - 7:43 am UTC

Tom,

The show_space procedure in your book "Expert Oracle database Architecture " (9i and 10g page xxxix) fails with an error
"PLS-00363: expression 'P_PARTITION' cannot be used as an assignment target"

Could you shed some light on this?

Cheers
Johny

johny, May 18, 2007 - 11:58 am UTC

Sorry I forgot to mention the Code.
following is your code.

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

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

BEGIN
EXECUTE IMMEDIATE
'SELECT ts.segment_space_management '||
'FROM dba_segments seg, dba_table_spaces 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 partitioned Table, use p_partition => ');
RETURN;
END;

IF l_segment_space_mgmt = 'AUTO'
THEN
DBMS_SPACE.space_usage (
p_owner,
p_segname,
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;


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 show_space;
/



Tom Kyte
May 18, 2007 - 4:26 pm UTC

share with us a full example - create table and call to make this fail

A reader, May 18, 2007 - 4:44 pm UTC

Sorry tom,

It was my fault. I forgot to use the "p_type" parameter in the procedure call to DBMS_SPACE.SPACE_USAGE.

It is friday after noon.

thanks for you help
Johny

show_space

A reader, May 28, 2009 - 3:43 pm UTC

Tom,
Where can I get the latest version of show_space which has all the fixes for ASSM, LMT, partitions etc.? Oracle version 10gr2.

Thanks...

Tom Kyte
May 28, 2009 - 5:19 pm 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;
/

Reason for difference in number of blocks

Narendra Prabhudesai, September 16, 2009 - 10:59 am UTC

Tom,

Oracle Database 10.2.0.4
I have created a table, named T, and loaded data into the same. The table has been created in ASSM locally managed tablespace.
I analyzed table (using dbms_stats.gather_table_stats(user, 'T', cascade=>true) command).
Now when I query the ALL_TABLES view, the value for BLOCKS column is 49945. When I use DBMS_SPACE.SPACE_USAGE procedure and add the number of blocks, the total is 49646 blocks (in fact, the unused blocks count is 0). What is the reason for this difference in number of blocks?

Reason for difference in number of blocks

Narendra Prabhudesai, September 16, 2009 - 11:04 am UTC

Tom,

Just some more info. When I queried DBA_SEGMENTS for table T, it shows number of blocks as 50176.
How do I account for these 3 different numbers for blocks of table T?
Tom Kyte
September 16, 2009 - 5:29 pm UTC

dba_segments - actual space allocated to segment, space on disk - our data used to manage your data and your data together.

user_tables - blocks, your blocks, blocks you are using to store your data. Our blocks (ASSM bitmap blocks used to manage space in the segment) are not there.

the dbms_space should correspond to all_tables (blocks and empty blocks)



show us otherwise.


ops$tkyte%ORA11GR1> create table t
  2  as
  3  select *
  4    from all_objects
  5   where 1=0;

Table created.

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

68486 rows created.

ops$tkyte%ORA11GR1> commit;

Commit complete.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> exec dbms_stats.gather_table_stats( user, 'T' );

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select blocks, empty_blocks from all_tables where owner = user and table_name = 'T';

    BLOCKS EMPTY_BLOCKS
---------- ------------
      1126            0

ops$tkyte%ORA11GR1> select blocks from dba_segments where owner = user and segment_name = 'T';

    BLOCKS
----------
      1152

ops$tkyte%ORA11GR1> select extent_id, blocks, sum(blocks) over (order by extent_id)
  2     from dba_extents where owner = user and segment_name = 'T' order by 1;

 EXTENT_ID     BLOCKS SUM(BLOCKS)OVER(ORDERBYEXTENT_ID)
---------- ---------- ---------------------------------
         0          8                                 8
         1          8                                16
         2          8                                24
         3          8                                32
         4          8                                40
         5          8                                48
         6          8                                56
         7          8                                64
         8          8                                72
         9          8                                80
        10          8                                88
        11          8                                96
        12          8                               104
        13          8                               112
        14          8                               120
        15          8                               128
        16        128                               256
        17        128                               384
        18        128                               512
        19        128                               640
        20        128                               768
        21        128                               896
        22        128                              1024
        23        128                              1152

24 rows selected.

ops$tkyte%ORA11GR1> declare
  2      l_unformatted_blocks number;
  3      l_unformatted_bytes number;
  4      l_fs1_blocks number; l_fs1_bytes number;
  5      l_fs2_blocks number; l_fs2_bytes number;
  6      l_fs3_blocks number; l_fs3_bytes number;
  7      l_fs4_blocks number; l_fs4_bytes number;
  8      l_full_blocks number; l_full_bytes number;
  9  begin
 10       dbms_space.space_usage
 11       ( user, 'T', 'TABLE', l_unformatted_blocks,
 12         l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 13         l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 14         l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes );
 15
 16       dbms_output.put_line( 'Unformatted Blocks '|| l_unformatted_blocks );
 17       dbms_output.put_line( 'FS1 Blocks (0-25)  '|| l_fs1_blocks );
 18       dbms_output.put_line( 'FS2 Blocks (25-50) '|| l_fs2_blocks );
 19       dbms_output.put_line( 'FS3 Blocks (50-75) '|| l_fs3_blocks );
 20       dbms_output.put_line( 'FS4 Blocks (75-100)'|| l_fs4_blocks );
 21       dbms_output.put_line( 'Full Blocks        '|| l_full_blocks );
 22       dbms_output.put_line( 'total              '||
 23                             (l_unformatted_blocks+ l_fs1_blocks+ l_fs2_blocks+
 24                              l_fs3_blocks+ l_fs4_blocks+ l_full_blocks) );
 25  end;
 26  /
Unformatted Blocks 94
FS1 Blocks (0-25)  1
FS2 Blocks (25-50) 1
FS3 Blocks (50-75) 0
FS4 Blocks (75-100)28
Full Blocks        1002
total              1126

PL/SQL procedure successfully completed.



Re: Reason for difference in number of blocks

Narendra Prabhudesai, September 17, 2009 - 7:37 am UTC

Hello Tom,

Please find my test results below:
SQL> select * from v$version ;

BANNER                                                                                                                             
----------------------------------------------------------------                                                                   
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi                                                                   
PL/SQL Release 10.2.0.4.0 - Production                                                                                             
CORE 10.2.0.4.0 Production                                                                                                         
TNS for Solaris: Version 10.2.0.4.0 - Production                                                                                   
NLSRTL Version 10.2.0.4.0 - Production                                                                                             

SQL> REM I ran your example and it produced results as shown by you
SQL> REM and hence I am not including them here.
SQL> REM However, when I change the way the table is created, I can
SQL> REM reproduce my findings
SQL> create table t1 as select * from all_objects ;

Table created.

SQL> exec dbms_stats.gather_table_stats(user, 'T1');

PL/SQL procedure successfully completed.

SQL> select blocks, empty_blocks from all_tables where owner = user and table_name = 'T1';

    BLOCKS EMPTY_BLOCKS                                                                                                            
---------- ------------                                                                                                            
       658            0                                                                                                            

SQL> select blocks from dba_segments where owner = user and segment_name = 'T1';

    BLOCKS                                                                                                                         
----------                                                                                                                         
       768                                                                                                                         

SQL> select extent_id, blocks, sum(blocks) over (order by extent_id) from dba_extents where owner = user and segment_name = 'T1' order by 1;

 EXTENT_ID     BLOCKS SUM(BLOCKS)OVER(ORDERBYEXTENT_ID)                                                                            
---------- ---------- ---------------------------------                                                                            
         0          8                                 8                                                                            
         1          8                                16                                                                            
         2          8                                24                                                                            
         3          8                                32                                                                            
         4          8                                40                                                                            
         5          8                                48                                                                            
         6          8                                56                                                                            
         7          8                                64                                                                            
         8          8                                72                                                                            
         9          8                                80                                                                            
        10          8                                88                                                                            
        11          8                                96                                                                            
        12          8                               104                                                                            
        13          8                               112                                                                            
        14          8                               120                                                                            
        15          8                               128                                                                            
        16        128                               256                                                                            
        17        128                               384                                                                            
        18        128                               512                                                                            
        19        128                               640                                                                            
        20        128                               768                                                                            

21 rows selected.

SQL> declare
  2        l_unformatted_blocks number;
  3        l_unformatted_bytes number;
  4        l_fs1_blocks number; l_fs1_bytes number;
  5        l_fs2_blocks number; l_fs2_bytes number;
  6        l_fs3_blocks number; l_fs3_bytes number;
  7        l_fs4_blocks number; l_fs4_bytes number;
  8        l_full_blocks number; l_full_bytes number;
  9    begin
 10         dbms_space.space_usage
 11         ( user, 'T1', 'TABLE', l_unformatted_blocks,
 12           l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 13           l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 14           l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes );
 15  
 16         dbms_output.put_line( 'Unformatted Blocks '|| l_unformatted_blocks );
 17         dbms_output.put_line( 'FS1 Blocks (0-25)  '|| l_fs1_blocks );
 18         dbms_output.put_line( 'FS2 Blocks (25-50) '|| l_fs2_blocks );
 19         dbms_output.put_line( 'FS3 Blocks (50-75) '|| l_fs3_blocks );
 20         dbms_output.put_line( 'FS4 Blocks (75-100)'|| l_fs4_blocks );
 21         dbms_output.put_line( 'Full Blocks        '|| l_full_blocks );
 22         dbms_output.put_line( 'total              '||
 23                               (l_unformatted_blocks+ l_fs1_blocks+ l_fs2_blocks+
 24                                l_fs3_blocks+ l_fs4_blocks+ l_full_blocks) );
 25    end;
 26  /
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        638                                                                                                             
total              638                                                                                                             

PL/SQL procedure successfully completed.

SQL> spool off



By first creating the table and adding data later, SPACE_USAGE procedure shows unformatted blocks and its total blocks are equal to those in ALL_TABLES.
But when data is added while creating the table (CREATE TABLE...AS SELECT), SPACE_USAGE shows no unformatted blocks and its total blocks are not equal to those in ALL_TABLES.
Tom Kyte
September 17, 2009 - 1:04 pm UTC

looks like a formatting nuance of a direct path load, same thing happens with a direct path load.

They are there, if you use dbms_rowid.rowid_block_number you can see which interior blocks are there but have no rows on them - these would be the ASSM bitmap blocks for space management. Not sure why they are not showing up with direct path operations

Narendra Prabhudesai, September 18, 2009 - 8:39 am UTC

Tom,

A
Select count(distinct dbms_rowid.rowid_block_number(rowid)) from t1
showed total number of blocks as 638, same as those shown by DBMS_SPACE.SPACE_USAGE procedure. Subsequently when I issued a
select count(*) from t1
which full scans the table, with autotrace on, it showed 643 consistent gets (with 0 phyiscal reads).
Does this mean that statistics collection (DBMS_STATS.GATHER_TABLE_STATS) is resulting in incorrect number of blocks, following a direct path load?
Tom Kyte
September 18, 2009 - 12:37 pm UTC

no, it is nothing to be concerned about, it is a trivially small number of blocks here - they are just formatted in a way that dbms_stats isn't reporting them - as a result of the direct path.

counting

Select count(distinct dbms_rowid.rowid_block_number(rowid)) from t1

will never report accurately on the number of blocks - a block need not contain any rows and a row might occupy two blocks (or three, or N blocks)

Re: Reason for difference in number of blocks

Narendra Prabhudesai, September 21, 2009 - 2:58 am UTC

no, it is nothing to be concerned about, it is a trivially small number of blocks here - they are just formatted in a way that dbms_stats isn't reporting them - as a result of the direct path.

Tom,
My intention, at this stage, was to understand what is going on or the theory. However, your statement above has confused me a bit. In my example earlier, DBMS_STATS, in fact, reported more number of blocks (658) than those reported by DBMS_SPACE.SPACE_USAGE procedure (638). The DBMS_STATS figure looks odd as a full scan against this table resulted in 643 consistent gets. A full scan, I believe, will have to visit each table block at least once, which means the count (of blocks) reported by DBMS_SPACE.SPACE_USAGE looks more apporpriate. But then does it mean number of blocks statistics in ALL_TABLES is not (necessarily) used by CBO?
Tom Kyte
September 28, 2009 - 12:53 pm UTC

.... But then does it mean number of blocks statistics in ALL_TABLES is not (necessarily) used by CBO?
.......


sure it is.
ops$tkyte%ORA10GR2> create table t ( x int );

Table created.

ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numblks => 1000 );

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select blocks from user_tables where table_name= 'T';

    BLOCKS
----------
      1000

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2000 | 26000 |    71   (2)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T    |  2000 | 26000 |    71   (2)| 00:00:01 |
--------------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off
ops$tkyte%ORA10GR2> exec dbms_stats.set_table_stats( user, 'T', numblks => 100000000);

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> select blocks from user_tables where table_name= 'T';

    BLOCKS
----------
 100000000

ops$tkyte%ORA10GR2> set autotrace traceonly explain
ops$tkyte%ORA10GR2> select * from t;

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2000 | 26000 |  6925K  (1)| 14:09:55 |
|   1 |  TABLE ACCESS FULL| T    |  2000 | 26000 |  6925K  (1)| 14:09:55 |
--------------------------------------------------------------------------

ops$tkyte%ORA10GR2> set autotrace off



but again, since the difference here is *so trivially small*, this will have *no effect* on anything. It is "noise"

SHOW_SPACE procedure needs one more modification

Antony, June 16, 2010 - 12:54 pm UTC

Hi Tom,

Your show_space procedure has been extensively used by most.
But,it still needs one more modification.

The procedure will fail,when the table name and index name is same and using ASSM.For an example,in PeopleSoft (Now owned by Oracle) application unique index on application tables are named as same as the table name.(PS_JOB).

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

The above section of the script will return more than one row and then it will fail even for non-partitioned tables.

Also in the "using" caluse of the above section,p_partition is appeared twice.One of the entires must be replaced with p_type.

I modified it and then ran the procedure.It's failed with the following error:

*
ERROR at line 1:
ORA-01008: not all variables bound
ORA-06512: at "TEST.SHOW_SPACE", line 44
ORA-06512: at line 1

Could you please correct it one more final time?
Tom Kyte
June 22, 2010 - 12:28 pm UTC

I referenced :p_partition twice, it needs be there twice.


here is the correction:

set define off

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<b>
              and seg.segment_type = :p_type</b>
              and seg.tablespace_name = ts.tablespace_name'
             into l_segment_space_mgmt
            using p_segname, p_partition, p_partition, p_owner, p_type;
   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;
/
set define on

A reader, June 16, 2010 - 9:11 pm UTC

Missed out another join condition segment_type = :p_type

Space

Andrew, August 10, 2010 - 4:24 pm UTC

Tom, this is not showing the space used by table _and_ by it's indexes, correct?
Do you think you can quickly add this functionality %) ?

Thank you.
Andrew
Tom Kyte
August 18, 2010 - 11:33 pm UTC

it shows the space used by a SEGMENT.

If you want it to do more - then just call it for each SEGMENT in question - write a small loop that iterates over the segments you find relevant and calls it. Not very hard.


Index Segment Space Usage

A reader, October 30, 2012 - 5:11 pm UTC

In the example below, after every record in the table is deleted, why are there 26 FS2 blocks? Shouldn't there be 26 FS4 blocks instead since all index entries were removed?

SQL> 
SQL> 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_segment_space_mgmt        VARCHAR2(255);
  9     l_free_blks                 number;
 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     l_unformatted_blocks        number;
 18     l_unformatted_bytes         number;
 19     l_fs1_blocks                number;
 20     l_fs1_bytes                 number;
 21     l_fs2_blocks                number;
 22     l_fs2_bytes                 number;
 23     l_fs3_blocks                number;
 24     l_fs3_bytes                 number;
 25     l_fs4_blocks                number;
 26     l_fs4_bytes                 number;
 27     l_full_blocks               number;
 28     l_full_bytes                number;
 29  
 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                                p_num );
 35      end;
 36  
 37  begin
 38     begin
 39        execute immediate
 40            'select ts.segment_space_management
 41               from dba_segments seg, dba_tablespaces ts
 42              where seg.segment_name      = :p_segName
 43                and (:p_partition is null or
 44                    seg.partition_name = :p_partition)
 45                and seg.owner = :p_owner
 46                and seg.tablespace_name = ts.tablespace_name'
 47               into l_segment_space_mgmt
 48              using p_segName, p_partition, p_partition, p_owner;
 49     exception
 50         when too_many_rows then
 51            dbms_output.put_line
 52            ( 'This must be a partitioned table, use p_partition => ');
 53            return;
 54     end;
 55  
 56  
 57     if l_segment_space_mgmt = 'AUTO'
 58     then
 59       dbms_space.space_usage
 60       ( p_owner, p_segName, p_type, l_unformatted_blocks,
 61         l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes,
 62         l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes,
 63         l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition);
 64  
 65    else
 66       dbms_space.free_blocks(
 67         segment_owner     => p_owner,
 68         segment_name      => p_segName,
 69         segment_type      => p_type,
 70         freelist_group_id => 0,
 71         free_blks         => l_free_blks);
 72  
 73    end if;
 74  
 75    dbms_space.unused_space
 76    ( segment_owner     => p_owner,
 77      segment_name      => p_segName,
 78      segment_type      => p_type,
 79      partition_name    => p_partition,
 80      total_blocks      => l_total_blocks,
 81      total_bytes       => l_total_bytes,
 82      unused_blocks     => l_unused_blocks,
 83      unused_bytes      => l_unused_bytes,
 84      LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
 85      LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
 86      LAST_USED_BLOCK => l_LAST_USED_BLOCK );
 87  
 88     p('Total Blocks', l_total_blocks);
 89     p('Total Bytes', l_total_bytes);
 90     p('FS1 Blocks', l_fs1_blocks);
 91     p('FS2 Blocks', l_fs2_blocks);
 92     p('FS3 Blocks', l_fs3_blocks);
 93     p('FS4 Blocks', l_fs4_blocks);
 94  end;
 95  /

Procedure created.

SQL> 
SQL> show error
No errors.
SQL> 
SQL> set serveroutput on
SQL> 
SQL> DROP TABLE x;

Table dropped.

SQL> 
SQL> CREATE TABLE x AS
  2  SELECT * FROM all_objects;

Table created.

SQL> 
SQL> CREATE INDEX x_idx ON x(object_name);

Index created.

SQL> 
SQL> BEGIN
  2     show_space (
  3        p_segname => 'X_IDX',
  4        p_type    => 'INDEX'
  5     );
  6  END;
  7  /
Total Blocks............................32
Total Bytes.............................262144
FS1 Blocks..............................0
FS2 Blocks..............................1
FS3 Blocks..............................0
FS4 Blocks..............................0

PL/SQL procedure successfully completed.

SQL> 
SQL> DELETE FROM x;

5904 rows deleted.

SQL> COMMIT;

Commit complete.

SQL> 
SQL> BEGIN
  2     show_space (
  3        p_segname => 'X_IDX',
  4        p_type    => 'INDEX'
  5     );
  6  END;
  7  /
Total Blocks............................32
Total Bytes.............................262144
FS1 Blocks..............................0
FS2 Blocks..............................26
FS3 Blocks..............................0
FS4 Blocks..............................0

PL/SQL procedure successfully completed.

SQL> 
SQL> spool off

Tom Kyte
October 31, 2012 - 5:30 pm UTC

you might have removed index leaf entries (or not, we don't necessarily clean them up in real time) but you would still have the root and branch blocks to think about.

free space in an index block isn't really relevant, we ALWAYS fill them up - data has to go into a very specific location in an index - pctfree doesn't count after the index is created or rebuilt - we always fill the blocks up 100%

A reader, November 01, 2012 - 12:16 am UTC

"you might have removed index leaf entries (or not, we don't necessarily clean them up in real time)"

If leaf entries are not cleaned up in real time, what happens in an index full scan? Would Oracle know which entries have been removed?


Tom Kyte
November 01, 2012 - 10:07 pm UTC

yes, we would know which were deleted, we do the right thing

index space after coalesce

A reader, June 28, 2013 - 12:14 pm UTC

Tom,
You mentioned in previous thread that
"free space in an index block isn't really relevant, we ALWAYS fill them up - data has to go into a very specific location in an index - pctfree doesn't count after the index is created or rebuilt - we always fill the blocks up 100%"

I executed a index coalesce command on oracle 11g.
I was expecting end result would be more full blocks but
i am getting less full blocks and more of 26-50% free blocks , when i ran the dbms_space.space_usage for that index.


Initial space usage report for index

Unformatted Blocks = 62
Blocks with 00-25% free space = 0
Blocks with 26-50% free space = 1130
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 0
Full Blocks = 1068

Ran the coalesce command.

Final space usage report for index

Unformatted Blocks = 62
Blocks with 00-25% free space = 0
Blocks with 26-50% free space = 1576
Blocks with 51-75% free space = 0
Blocks with 76-100% free space = 0
Full Blocks = 622

thanks

Tom Kyte
July 01, 2013 - 9:19 pm UTC

use alter index validate structure (warning! that locks the index - beware of that). the freeness or fullness of index blocks is not a sensible measurement. What is - the blocks IN USE in the index.

for example:

ops$tkyte%ORA11GR2> create table t as
  2  select rownum x, object_name
  3    from big_table.big_table where rownum < 500000;
Table created.

ops$tkyte%ORA11GR2> create index t_idx on t(x,object_name);
Index created.

ops$tkyte%ORA11GR2> select num_rows from user_indexes where index_name = 'T_IDX';

  NUM_ROWS
----------
    499999


ops$tkyte%ORA11GR2> analyze index t_idx validate structure;
Index analyzed.

ops$tkyte%ORA11GR2> create table t2
  2  as
  3  select 'before' when, index_stats.* from index_stats;
Table created.


ops$tkyte%ORA11GR2> delete from t where mod(x,2) = 0;
249999 rows deleted.

<b>deleted every other row - imagine what the index looks like now...</b>


ops$tkyte%ORA11GR2> commit;
Commit complete.

ops$tkyte%ORA11GR2> analyze index t_idx validate structure;
Index analyzed.

ops$tkyte%ORA11GR2> insert into t2
  2  select 'middle' when, index_stats.* from index_stats;
1 row created.


ops$tkyte%ORA11GR2> alter index t_idx coalesce;
Index altered.


ops$tkyte%ORA11GR2> analyze index t_idx validate structure;
Index analyzed.

ops$tkyte%ORA11GR2> insert into t2
  2  select 'coales' when, index_stats.* from index_stats;
1 row created.


ops$tkyte%ORA11GR2> alter index t_idx rebuild;
Index altered.

ops$tkyte%ORA11GR2> analyze index t_idx validate structure;
Index analyzed.

ops$tkyte%ORA11GR2> insert into t2
  2  select 'rebuil' when, index_stats.* from index_stats;
1 row created.

ops$tkyte%ORA11GR2> with data
  2  as
  3  ( select when, thing, val
  4      from t2
  5    unpivot ( val for thing in
  6              ( LF_ROWS, LF_BLKS, LF_ROWS_LEN, LF_BLK_LEN,
  7                BR_ROWS, BR_BLKS, BR_ROWS_LEN, BR_BLK_LEN,
  8                DEL_LF_ROWS, DEL_LF_ROWS_LEN, DISTINCT_KEYS,
  9                MOST_REPEATED_KEY, BTREE_SPACE, USED_SPACE,
 10                PCT_USED, ROWS_PER_KEY, BLKS_GETS_PER_ACCESS,
 11                PRE_ROWS, PRE_ROWS_LEN, OPT_CMPR_COUNT, OPT_CMPR_PCTSAVE )
 12            )
 13  )
 14  select THING, before, middle, coalesce, rebuild, rebuild-coalesce diff
 15    from data
 16   pivot( max(val) for when in ( 'before' as before, 'middle' as middle,
 17                                 'coales' as coalesce, 'rebuil' as rebuild )
 18        )
 19   order by thing
 20  /

THING                    BEFORE     MIDDLE   COALESCE    REBUILD       DIFF
-------------------- ---------- ---------- ---------- ---------- ----------
BLKS_GETS_PER_ACCESS          4          4          4          4          0
BR_BLKS                       6          6          6          4         -2
BR_BLK_LEN                 8028       8028       8028       8028          0
BR_ROWS                    2823       2823       1414       1411         -3
BR_ROWS_LEN               33797      33797      16916      16888        -28
BTREE_SPACE            22628872   22628872   11362508   11322464     -40044
DEL_LF_ROWS                   0     249999          0          0          0
DEL_LF_ROWS_LEN               0   10110067          0          0          0
DISTINCT_KEYS            499999     499999     250000     250000          0
LF_BLKS                    2824       2824       1415       1412         -3
LF_BLK_LEN                 7996       7996       7996       7996          0
LF_ROWS                  499999     499999     250000     250000          0
LF_ROWS_LEN            20219383   20219383   10109316   10109316          0
MOST_REPEATED_KEY             1          1          1          1          0
OPT_CMPR_COUNT                0          0          0          0          0
OPT_CMPR_PCTSAVE              0          0          0          0          0
PCT_USED                     90         90         90         90          0
PRE_ROWS                      0          0          0          0          0
PRE_ROWS_LEN                  0          0          0          0          0
ROWS_PER_KEY                  1          1          1          1          0
USED_SPACE             20253180   20253180   10126232   10126204        -28

21 rows selected.



notice how the LF_BLKS dropped - we coalesced the space and pulled the now empty blocks out of the index.

Index Coalesce

A reader, July 06, 2013 - 10:55 am UTC

Tom,

Thank you very much for explaning and demonstrating.
I can not execute the validate structure command in production , because of the locking issue. I will try to run this in our test bed and try to get more statistics.

I am presuming that my coalesce command is reducing the leaf block count and the blocks are logically empty and are on freelist but space bitmaps may not be representing that correctly. I will try to perform validate structure command and will update you.

Thanks again for your explanation.
Tom Kyte
July 16, 2013 - 2:24 pm UTC

space is just not managed the same in an index. period. freelists don't really come into it. a block is never "x%" full in an index - it is either in the index and being used or not. It is a binary thing.

procedure show_space

blue, October 20, 2014 - 3:33 pm UTC

hi Tom,

i want to use Your procedure :

set define off

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.segment_type = :p_type
and seg.tablespace_name = ts.tablespace_name'
into l_segment_space_mgmt
using p_segname, p_partition, p_partition, p_owner, p_type;
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;
/
set define on



but, it's not working

i use :

execute show_space('table_name','schema_name');

and i get error

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

Thanks for help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library