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
September 10, 2002 - 2:01 pm UTC
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...
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!
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
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.
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?
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.
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,
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
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)
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,
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.
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.
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
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>
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
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
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)
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,
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,
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,
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
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
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?
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?
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
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
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;
/
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...
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?
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.
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?
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?
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?
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
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
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?
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
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.
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
to the above error...
Rajeshwaran Jeyabal, January 20, 2025 - 7:58 am UTC
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "schema_name.SHOW_SPACE", line 44
ORA-06512: at line 1
at the line 44 in the above code you got this
.....
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
.....
so the schema owner where this code was compiled don't have SELECT privilege on either dba_segments / dba_tablespaces, hence it could have errored.