dbms_space.free_space

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

Thanks, Tom.


and we said...

Here is an example showing how to use dbms_space and how to interpret the output. Basically between the 2 procedures free blocks and unused space, we'll be able to get: Free Blocks...... Number of blocks on the freelist Total Blocks..... Total blocks allocated to the table Total Bytes...... Total bytes allocated to the table Unused Blocks.... Blocks that have never contained data Unused Bytes..... The above in bytes It does not tell you how many blocks have free space for updates. We can tell you how many blocks are candidates for INSERTS (they are on the freelist) and blocks on the freelist have space for updates -- but -- there are blocks in the table that have space for updates but that are not on the freelist. We cannot see them in any report. It does not tell you how much space is free in each block (nothing does, typically there are thousands or hundreds of thousands of blocks in a table -- an analysis of the free space block by block is not practical. We can get an average free space but not block by block). This report does show blocks above the high water mark. Unused Blocks are exactly the block above the high water mark. You can get most of the information supplied by this package by analyzing the table and using queries against user_tables and user_segments. The freelist analysis is more detailed using this package as you can look at each freelist independently. Below is a procedure you can use to make using dbms_space a little easier. After that I create a table and show how space is being used in it after various operations. Comments in bold explain the output. ops$tkyte@8i> create or replace 2 procedure show_space 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default 'TABLE' ) 6 as 7 l_free_blks number; 8 9 l_total_blocks number; 10 l_total_bytes number; 11 l_unused_blocks number; 12 l_unused_bytes number; 13 l_LastUsedExtFileId number; 14 l_LastUsedExtBlockId number; 15 l_LAST_USED_BLOCK number; 16 procedure p( p_label in varchar2, p_num in number ) 17 is 18 begin 19 dbms_output.put_line( rpad(p_label,40,'.') || 20 p_num ); 21 end; 22 begin 23 dbms_space.free_blocks 24 ( segment_owner => p_owner, 25 segment_name => p_segname, 26 segment_type => p_type, 27 freelist_group_id => 0, 28 free_blks => l_free_blks ); 29 30 dbms_space.unused_space 31 ( segment_owner => p_owner, 32 segment_name => p_segname, 33 segment_type => p_type, 34 total_blocks => l_total_blocks, 35 total_bytes => l_total_bytes, 36 unused_blocks => l_unused_blocks, 37 unused_bytes => l_unused_bytes, 38 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 39 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 40 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 41 42 p( 'Free Blocks', l_free_blks ); 43 p( 'Total Blocks', l_total_blocks ); 44 p( 'Total Bytes', l_total_bytes ); 45 p( 'Unused Blocks', l_unused_blocks ); 46 p( 'Unused Bytes', l_unused_bytes ); 47 p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 48 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 49 p( 'Last Used Block', l_LAST_USED_BLOCK ); 50 end; 51 / Procedure created. ops$tkyte@8i> ops$tkyte@8i> create table t ( x int, y char(2000) default '*' ) 2 storage ( initial 40k next 40k minextents 5 ) 3 tablespace system; Table created. I create a table with >1 extent to make it interesting. I also put a char(2000) in there to make the minimum row length be 2000 bytes (chars always take their max space right away). This just makes my rows "big" ops$tkyte@8i> insert into t (x) values ( 1 ); 1 row created. I create one row just to use a little space in the table ops$tkyte@8i> analyze table t compute statistics; Table analyzed. ops$tkyte@8i> compute sum of blocks on report ops$tkyte@8i> break on report ops$tkyte@8i> select extent_id, bytes, blocks 2 from user_extents 3 where segment_name = 'T' 4 and segment_type = 'TABLE' 5 / EXTENT_ID BYTES BLOCKS ---------- ---------- ---------- 2 40960 5 3 81920 10 4 57344 7 0 40960 5 1 40960 5 ---------- sum 32 This shows that there are 32 blocks allocated in 5 extents to this table (as expected) ops$tkyte@8i> clear breaks ops$tkyte@8i> select blocks, empty_blocks, 2 avg_space, num_freelist_blocks 3 from user_tables 4 where table_name = 'T' 5 / BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS ---------- ------------ ---------- ------------------- 1 30 6091 1 Since I analyzed the table, I have acccess to the above information. You'll find that it maps exactly to the data below. There are a total of 32 blocks allocated to the table (below and as confirmed by user_extents above). There are 30 EMPTY_BLOCKS (above)/ UNUSED_BLOCKS (below). These are blocks above the HWM. This leaves 2 blocks unaccounted for -- 1 block has data in it, the other has the extent map for the table (the first block of each table is used by the system itself). ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................1 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................30 Unused Bytes............................245760 Last Used Ext FileId....................1 Last Used Ext BlockId...................64816 Last Used Block.........................2 PL/SQL procedure successfully completed. ops$tkyte@8i> insert into t (x) 2 select rownum 3 from all_users 4 where rownum < 50 5 / 49 rows created. ops$tkyte@8i> commit; Commit complete. So now we have 50 rows with 2k each -- I'm using a 8k blocksize so I expect about 3 rows / block. That means about 18 blocks of data plus 1 for the system = about 19 blocks should be "used" now. Below I see that I have o 3 blocks on the freelist. they have more space for new inserts (they have not hit their pctused yet and may be inserted into) o 12 unused blocks, leaving 20 blocks "used". Since I have 3 on the freelist -- we probably used a little more then the 18 for data -- we used 19 for the 50 rows. We have one for the system -- all accounted for. ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................3 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/SQL procedure successfully completed. ops$tkyte@8i> delete from t; 50 rows deleted. ops$tkyte@8i> commit; Commit complete. Now we can see what a delete does to our utilization. ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................19 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/SQL procedure successfully completed. The above shows that the delete simply put all of our blocks on the free list. We have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 blocks. All accounted for. Note that the HWM stayed the same -- we don't have 31 unused blocks -- we have 12 as before. The HWM for a table will never decrease unless we..... ops$tkyte@8i> truncate table t; Table truncated. ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................0 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................31 Unused Bytes............................253952 Last Used Ext FileId....................1 Last Used Ext BlockId...................64816 Last Used Block.........................1 PL/SQL procedure successfully completed. Truncate it. That puts all of the blocks below the HWM. Now we have 31 unused blocks + 1 system block = 32 blocks total. None on the free list since none of them have any data.

  Reviews     Bookmark Review | Bottom | TopTable space used for a particular table  March 17, 2001
Reviewer:  spmurthy  from Singapore

Hi Tom, Thanks for your reply it is more useful to me to know the table space. Regards
Bookmark Review | Bottom | Topdbms_space usage  March 22, 2001
Reviewer:  B.N.Sarma  from USA

Tom, Excellent , nothing less. It would have been nice had you shown a select statement with autot on doing FTS upto hwm, even if you have delted all the rows and the same with truncate. It would have become a good notes. Your explanation with examples makes things very clear. Why don't you write a book :-) Regards BN
Bookmark Review | Bottom | Top  March 23, 2001
Reviewer:  Helena Markova  from Bratislava, Slovakia

Bookmark Review | Bottom | Topdbms_space.free_space  May 09, 2001
Reviewer:  D.C.L.  from Seattle, USA

Right on. Awesome grip of the subject matter.
Bookmark Review | Bottom | Top  May 10, 2001
Reviewer:  Vikram  from Delhi, India

Excellent
Bookmark Review | Bottom | Top  August 21, 2001
Reviewer:  k.v.s.Raju  from Sydney, Australia

Its excellent
Bookmark Review | Bottom | Topdbms_space   September 19, 2001
Reviewer:  Jim  from MA

Very, Very Good!!!
Bookmark Review | Bottom | TopErrors in show_space  September 20, 2001
Reviewer:  A reader

Tom, tried using your show_space procedure. It compiled successfully but on using it I get following errors: SQL> exec show_space('T') BEGIN show_space('T'); END; * ERROR at line 1: ORA-00942: table or view does not exist ORA-06512: at "SYS.DBMS_SPACE", line 55 ORA-06512: at "TOM.SHOW_SPACE", line 22 ORA-06512: at line 1 the table T exists under schema TOM and the show_space procedure was compiled under user TOM. DBMSUTIL/PRVTUTIL etc. have all been run. What am I missing ?
Followup:

Something must be wrong -- give me a full example like this (that shows it works) [email protected]> create user a identified by a; User created. [email protected]> grant create session, create procedure, create table to a; Grant succeeded. [email protected]> alter user a quota unlimited on users; User altered. [email protected]> connect a/a Connected. [email protected]> @showspace Procedure created. [email protected]> create table t ( x int ) tablespace users; Table created. [email protected]> exec show_space( 'T' ) PL/SQL procedure successfully completed. [email protected]> set serveroutput on [email protected]> exec show_space( 'T' ); Free Blocks.............................0 Total Blocks............................64 Total Bytes.............................524288 Unused Blocks...........................63 Unused Bytes............................516096 Last Used Ext FileId....................7 Last Used Ext BlockId...................4809 Last Used Block.........................1 PL/SQL procedure successfully completed. so, do the whole create user/install the procedure/run the test and see if it reproduces. If not, either you were not logged in as TOM, TOM did not own T, etc... (is T a view or synonym in your case??)
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopA little question  October 18, 2001
Reviewer:  Igor  from France

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

the 5 extents were expected. the 32 blocks just happened.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Top5 extents 40k each (8k block size) why not 5X5=25 blocks?  October 22, 2001
Reviewer:  YK LIU  from CA, USA

Bookmark Review | Bottom | TopFree Space  October 30, 2001
Reviewer:  an  from DE

it's excellent!
Bookmark Review | Bottom | TopORA-14107: partition specification is required for a partitioned object  November 19, 2001
Reviewer:  A reader

SQL> CREATE TABLE T (X VARCHAR2(20)); Table created. SQL> EXEC SHOW_SPACE('T'); PL/SQL procedure successfully completed. SQL> set serveroutput on SQL> EXEC SHOW_SPACE('T'); Free Blocks.............................0 Total Blocks............................10 Total Bytes.............................81920 Unused Blocks...........................9 Unused Bytes............................73728 Last Used Ext FileId....................5 Last Used Ext BlockId...................126659 Last Used Block.........................1 PL/SQL procedure successfully completed. T_P -- is a partitioned table SQL> EXEC SHOW_SPACE('T_P'); BEGIN SHOW_SPACE('T_P'); END; * ERROR at line 1: ORA-14107: partition specification is required for a partitioned object ORA-06512: at "SYS.DBMS_SPACE", line 55 ORA-06512: at "myschema.SHOW_SPACE", line 22 ORA-06512: at line 1 Why Iam I running into this error while trying to use show_space on a partitioned table.
Followup:

Ok, time for an update of this utility! I had this sitting around already -- it does two things 1) adds partition support 2) makes it so this runs in SQL for anything... gives a result set instead of printing. You can easily make it dbms_output.put_line if you want... First we start with the types: create or replace type show_space_type as object ( owner varchar2(30), segment_name varchar2(30), partition_name varchar2(30), segment_type varchar2(30), free_blocks number, total_blocks number, unused_blocks number, last_used_ext_fileid number, last_used_ext_blockid number, last_used_block number ) / create or replace type show_space_table_type as table of show_space_type / And then the function: create or replace function show_space_for ( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 default NULL ) return show_space_table_type authid CURRENT_USER as pragma autonomous_transaction; type rc is ref cursor; l_cursor rc; l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_last_used_block number; l_sql long; l_conj varchar2(7) default ' where '; l_data show_space_table_type := show_space_table_type(); l_owner varchar2(30); l_segment_name varchar2(30); l_segment_type varchar2(30); l_partition_name varchar2(30); procedure add_predicate( p_name in varchar2, p_value in varchar2 ) as begin if ( instr( p_value, '%' ) > 0 ) then l_sql := l_sql || l_conj || p_name || ' like ''' || upper(p_value) || ''''; l_conj := ' and '; elsif ( p_value is not null ) then l_sql := l_sql || l_conj || p_name || ' = ''' || upper(p_value) || ''''; l_conj := ' and '; end if; end; begin l_sql := 'select owner, segment_name, segment_type, partition_name from dba_segments '; add_predicate( 'segment_name', p_segname ); add_predicate( 'owner', p_owner ); add_predicate( 'segment_type', p_type ); add_predicate( 'partition', p_partition ); execute immediate 'alter session set cursor_sharing=force'; open l_cursor for l_sql; execute immediate 'alter session set cursor_sharing=exact'; loop fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name; exit when l_cursor%notfound; begin dbms_space.free_blocks ( segment_owner => l_owner, segment_name => l_segment_name, segment_type => l_segment_type, partition_name => l_partition_name, freelist_group_id => 0, free_blks => l_free_blks ); dbms_space.unused_space ( segment_owner => l_owner, segment_name => l_segment_name, segment_type => l_segment_type, partition_name => l_partition_name, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK ); l_data.extend; l_data(l_data.count) := show_space_type( l_owner, l_segment_name, l_partition_name, l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks, l_lastUsedExtFileId, l_LastUsedExtBlockId, l_last_used_block ); exception when others then null; end; end loop; close l_cursor; return l_data; end; / Then we can: [email protected]> select SEGMENT_NAME, PARTITION_NAME SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS 2 from table( cast( show_space_for( 'HASHED',user,'%' ) as show_space_table_type ) ) 3 / SEGMENT_NA SEGMENT_TYPE FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS ---------- ----------------- ----------- ------------ ------------- HASHED PART_2 1 64 62 HASHED PART_3 1 64 62 HASHED PART_4 1 64 62 HASHED PART_1 1 64 62 [email protected]> And in 9i, we'd change the function to be pipelined: [email protected]> create or replace 2 function show_space_for 3 ( p_segname in varchar2, 4 p_owner in varchar2 default user, 5 p_type in varchar2 default 'TABLE', 6 p_partition in varchar2 default NULL ) 7 return show_space_table_type 8 authid CURRENT_USER 9 PIPELINED 10 as 11 pragma autonomous_transaction; 12 type rc is ref cursor; 13 l_cursor rc; 14 15 l_free_blks number; 16 l_total_blocks number; 17 l_total_bytes number; 18 l_unused_blocks number; 19 l_unused_bytes number; 20 l_LastUsedExtFileId number; 21 l_LastUsedExtBlockId number; 22 l_last_used_block number; 23 l_sql long; 24 l_conj varchar2(7) default ' where '; 25 l_owner varchar2(30); 26 l_segment_name varchar2(30); 27 l_segment_type varchar2(30); 28 l_partition_name varchar2(30); 29 30 procedure add_predicate( p_name in varchar2, p_value in varchar2 ) 31 as 32 begin 33 if ( instr( p_value, '%' ) > 0 ) 34 then 35 l_sql := l_sql || l_conj || p_name || ' like ''' || upper(p_value) || ''''; 36 l_conj := ' and '; 37 elsif ( p_value is not null ) 38 then 39 l_sql := l_sql || l_conj || p_name || ' = ''' || upper(p_value) || ''''; 40 l_conj := ' and '; 41 end if; 42 end; 43 begin 44 l_sql := 'select owner, segment_name, segment_type, partition_name 45 from dba_segments '; 46 47 add_predicate( 'segment_name', p_segname ); 48 add_predicate( 'owner', p_owner ); 49 add_predicate( 'segment_type', p_type ); 50 add_predicate( 'partition', p_partition ); 51 52 execute immediate 'alter session set cursor_sharing=force'; 53 open l_cursor for l_sql; 54 execute immediate 'alter session set cursor_sharing=exact'; 55 56 loop 57 fetch l_cursor into l_owner, l_segment_name, l_segment_type, l_partition_name; 58 dbms_output.put_line( l_segment_name || ',' || l_segment_type ); 59 exit when l_cursor%notfound; 60 begin 61 dbms_space.free_blocks 62 ( segment_owner => l_owner, 63 segment_name => l_segment_name, 64 segment_type => l_segment_type, 65 partition_name => l_partition_name, 66 freelist_group_id => 0, 67 free_blks => l_free_blks ); 68 69 dbms_space.unused_space 70 ( segment_owner => l_owner, 71 segment_name => l_segment_name, 72 segment_type => l_segment_type, 73 partition_name => l_partition_name, 74 total_blocks => l_total_blocks, 75 total_bytes => l_total_bytes, 76 unused_blocks => l_unused_blocks, 77 unused_bytes => l_unused_bytes, 78 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 79 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 80 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 81 82 pipe row ( show_space_type( l_owner, l_segment_name, l_partition_name, 83 l_segment_type, l_free_blks, l_total_blocks, l_unused_blocks, 84 l_lastUsedExtFileId, l_LastUsedExtBlockId, l_last_used_block ) ); 85 exception 86 when others then null; 87 end; 88 end loop; 89 close l_cursor; 90 91 return; 92 end; 93 / Function created. [email protected]> set arraysize 1 [email protected]> select SEGMENT_NAME, SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS 2 from table( show_space_for( '%',user,'%' ) ) 3 / SEGMENT_NAME SEGMENT_TYPE FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS --------------- ----------------- ----------- ------------ ------------- KEEP_SCN TABLE 1 64 62 EMPLOYEES TABLE 0 64 63 STINKY TABLE 0 64 63 OBJECT_TABLE TABLE 1 64 62 RUN_STATS TABLE 2 64 53 EMP TABLE 0 64 62 PROJ TABLE 0 64 62 X TABLE 1 64 62 WORDS TABLE 0 64 63 DOCS TABLE 0 64 63 KEYWORDS TABLE 0 64 63 DEPT TABLE 2 64 61 C TABLE 1 64 62 DSINVLINES TABLE 1 64 62 NUM_STR TABLE 1 64 23 T TABLE 4 64 28 T1 TABLE 0 64 63 T2 TABLE 0 64 63 BOM TABLE 1 64 62 PARTS TABLE 1 64 62 SYS_C001371 INDEX 0 64 62 SYS_C001372 INDEX 0 64 62 SYS_C001574 INDEX 0 64 62 SYS_C001694 INDEX 0 64 62 SYS_C001695 INDEX 0 64 62 BOM_PK INDEX 0 64 62 PARTS_PK INDEX 0 64 62 27 rows selected.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopReader  December 26, 2001
Reviewer:  Reader  from USA

Tom, Could you clarify, why the *first block* of a datafile being used for the OS., 2nd for segment header, 3rd... for data. I created a table ts1 in tablespace ts1 , db_block_size 8k Results: SQL> set serveroutput on size 1000000 SQL> exec show_space('TS1','SYS','TABLE'); Free Blocks.............................1 Total Blocks............................2 Total Bytes.............................16384 Unused Blocks...........................0 Unused Bytes............................0 Last Used Ext FileId....................2 Last Used Ext BlockId...................2 Last Used Block.........................2 PL/SQL procedure successfully completed. SQL> select lpad(file_name,40), bytes/1024/8 BLOCKS , TABLESPACE_NAME from dba_data_files where TABLESPACE_NAME = 'TS1'; LPAD(FILE_NAME,40) BLOCKS TABLESPACE_NAME ---------------------------------------- ---------- ----------------------- /u07/oradata/iiim/ts1.dbf 3 TS1 SQL> select SEGMENT_NAME,TABLESPACE_NAME,BLOCK_ID,BLOCKS, extent_id from dba_extents where SEGMENT_NAME = 'TS1'; SEGM TABLESPACE_NAME BLOCK_ID BLOCKS EXTENT_ID ---- ------------------------------ ---------- ---------- ---------- TS1 TS1 2 2 0 SQL> select TABLE_NAME,INITIAL_EXTENT,NEXT_EXTENT from dba_tables where TABLE_NAME = 'TS1'; TABLE_NAME INITIAL_EXTENT NEXT_EXTENT ------------------------------ -------------- ----------- TS1 16384 8192 SQL> alter table ts1 allocate extent (size 8192K); alter table ts1 allocate extent (size 8192K) * ERROR at line 1: ORA-01653: unable to extend table SYS.TS1 by 1024 in tablespace TS1 The datafile has 3 blocks, segment has used 2 blocks (including segment header). When I try to allocate one extent = 1 block, get ora-1653 Thanks
Followup:

I never said the first block would be used by the OS. In any case, you are asking for 8192k (8 MEG) of space, not 8k.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopReader  December 26, 2001
Reviewer:  Reader  from USA

Tom, I am sorry, I did try 8k and got this ora-1653 SQL> alter table ts1 allocate extent (size 8k); alter table ts1 allocate extent (size 8k) * ERROR at line 1: ORA-01653: unable to extend table SYS.TS1 by 1 in tablespace TS1 I have found the 1st block seemed to have been used by OS, not sure if this is platform specific (Silicon Graphics) Thanks
Followup:

what is the CREATE TABLESPACE command you used (and why are we losing sleep over 1 block)
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopReader  December 26, 2001
Reviewer:  Reader  from USA

Tom, Create Tablespace command: Create tablespace TS1 datafile '/u07/oradata/iiim/ts1.dbf' size 24k; Create table ts1 storage(initial 8k next 8k pctincrease 0) tablespace ts1; This is purely of academic interest. Ofcourse, we do not need to spend too much time on this. I agree Although, if the datafile is for example 1000M and the segment in the tablespace is initial 500M next 500M, since 1 block (8/1024M) is used for whatever reason other than database EXTENTS, there is fragmentation induced; 500M that can be allocated for segments and 499.99M gets unusable; unless size the datafile to be 1001M to start with. Thanks
Followup:

Yes, the first block of a file in a DICTIONARY managed tablespace is used by the system (us, Oracle) Just like the first 64k of a LOCALLY managed tablespaces. Additionally, on my system we allocated 32k for the datafiles -- not 24k. The following shows what you ask for -- what you get and how much is usable by you (i would highly recomment LMT's btw -- avoid DMT's): [email protected]> Create tablespace TS1 datafile '/tmp/ts1.dbf' size 24k reuse; Tablespace created. [email protected]> host ls -l /tmp/ts1.dbf -rw-r----- 1 ora817 32768 Dec 26 13:33 /tmp/ts1.dbf [email protected]> @free 1 MaxPoss Max Tablespace Name KBytes Used Free Used Largest Kbytes Used ---------------- ------- ---------- --------- ------ ------- -------- ------ ..... TS1 24 8 16 33.3 16 0 .0 ------- ---------- --------- sum 3,818,848 1,605,144 2,213,704 13 rows selected. see, 24k in size -- 8 is used, 16 free... [email protected]> alter tablespace ts1 add datafile '/tmp/ts2.dbf' size 24k reuse; Tablespace altered. [email protected]> host ls -l /tmp/ts2.dbf -rw-r----- 1 ora817 32768 Dec 26 13:33 /tmp/ts2.dbf [email protected]> @free 1 MaxPoss Max Tablespace Name KBytes Used Free Used Largest Kbytes Used ---------------- ------- --------- --------- ------ -------- -------- ------ TS1 48 16 32 33.3 16 0 .0 ------- --------- --------- sum 3,818,872 1,605,152 2,213,720 13 rows selected. now, 48k (24*2), 16k used (1 block / file ) [email protected]>
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopExtremely Useful  December 26, 2001
Reviewer:  Reader  from USA

Tom, Thanks very much I do plan on using LMT in new databases
Bookmark Review | Bottom | Top  December 27, 2001
Reviewer:  Dixit  from NJ,USA

Very Good
Bookmark Review | Bottom | TopWow !  March 20, 2002
Reviewer:  Mini  from OH

Exactly what I was looking for. Thank you so much Tom
Bookmark Review | Bottom | TopHow the extents are allocated in bytes  April 30, 2002
Reviewer:  Santosh Jadhav  from India

it was a very good explanation TOM. but i have one doubt ops$tkyte@8i> select extent_id, bytes, blocks 2 from user_extents 3 where segment_name = 'T' 4 and segment_type = 'TABLE' 5 / EXTENT_ID BYTES BLOCKS ---------- ---------- ---------- 2 40960 5 3 81920 10 4 57344 7 0 40960 5 1 40960 5 ---------- sum 32 this is what after creating table T with 40k initial and next extent. so why it is 81920 (3rd extent) and 57344(4th extent). And why 7 and 10 blocks are allocated respectively
Followup:

It is because I created the table in a dictionary managed tablespace and we allocate things to within +- 5 blocks (to avoid fragmenting the tablespace freespace totally) Read http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c02block.htm#2846

it covers the algorithm.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Topgood stuff  September 16, 2002
Reviewer:  Doug  from CT, USA

Bookmark Review | Bottom | TopSystem block is always 1 ?  January 09, 2003
Reviewer:  Tony  from India

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

at least one, it might get more later. depends on block size and number of extents.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopWhat AUTO SEGMENT SPACE MANAGEMENT ts's?  March 20, 2003
Reviewer:  Matt  from Australia

What should the value of freelist_group_id be in each call to dbms_space.free_blocks? You default your value to 1, should this be 1 for tables in all cases? I just executed dbms_space.free_blocks for a segment in a LOCALLY managed TS with AUTO SEGMENT SPACE MANAGEMENT and got the following error: 10618, 00000, "Operation not allowed on this segment" // *Cause: This DBMS_SPACE operation is not permitted on segments in // tablespaces with AUTO SEGMENT SPACE MANAGEMENT // *Action: Recheck the segment name and type and re-issue the statement How do you identify free blocks in this case?
Followup:

I just assumed one freelist. if you have more, this routine is not "sophisticated enough" there is a dbms_space.space_usage routine for ASSM
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Topyour show_space procedure and partitoned tables  May 06, 2003
Reviewer:  PINGU

Hi I am trying to use your show_space procedure but it seems that it does not work with partitioned tables? I think the cursor for x in ( select tablespace_name from dba_tablespaces where tablespace_name = ( select tablespace_name from dba_segments where segment_type = p_type and segment_name = p_segname and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' ) the subquery should we add distinct?
Followup:

go for it. I adjust it as I need.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Topafter a closer look...  May 06, 2003
Reviewer:  PINGU

Hi I had a closer look and I dont understand why we need the for loop for x in ( select tablespace_name from dba_tablespaces where tablespace_name in ( select distinct tablespace_name from dba_segments where segment_type = p_type and segment_name = p_segname and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' ) ) loop dbms_space.free_blocks ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, freelist_group_id => 0, free_blks => l_free_blks ); end loop; I think we dont use anything from the loop.....? Or iterating for anything
Followup:

it only calls free blocks IF the segment space management is not AUTO and the object exists. just add a "and rownum = 1" to the query. Lose the distinct, it is not relevant.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Top  May 19, 2003
Reviewer:  A reader

Hi Tom, I am getting error when i use show_space procedure SQL> exec show_space('T1'); Error ORA-10618: Operation not allowed on this segment -10618 BEGIN show_space('T1'); END; * ERROR at line 1: ORA-10618: Operation not allowed on this segment ORA-06512: at "GAURANG.SHOW_SPACE", line 49 ORA-06512: at line 1 Thanks
Followup:

having no clue what t1 is, i have no comment.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Top  May 20, 2003
Reviewer:  A reader

T1 IS TABLE . I am using oracle 9iR2
Followup:

sorry, maybe if you show us the entire thing -- are you using the script that does auto segment space management "show space", etc... have you read about the dbms_* packages I use (they are documented). It is pretty straightforward stuff. works for me in 9i ops$tkyte@ORA920> l 1 create or replace procedure show_space 2 ( p_segname in varchar2, 3 p_owner in varchar2 default user, 4 p_type in varchar2 default 'TABLE', 5 p_partition in varchar2 default NULL ) 6 authid current_user 7 as 8 l_free_blks number; 9 10 l_total_blocks number; 11 l_total_bytes number; 12 l_unused_blocks number; 13 l_unused_bytes number; 14 l_LastUsedExtFileId number; 15 l_LastUsedExtBlockId number; 16 l_LAST_USED_BLOCK number; 17 procedure p( p_label in varchar2, p_num in number ) 18 is 19 begin 20 dbms_output.put_line( rpad(p_label,40,'.') || 21 p_num ); 22 end; 23 begin 24 for x in ( select tablespace_name 25 from dba_tablespaces 26 where tablespace_name = ( select tablespace_name 27 from dba_segments 28 where segment_type = p_type 29 and segment_name = p_segname 30 and owner = p_owner 31 and SEGMENT_SPACE_MANAGEMENT <> 'AUTO' ) 32 ) 33 loop 34 dbms_space.free_blocks 35 ( segment_owner => p_owner, 36 segment_name => p_segname, 37 segment_type => p_type, 38 partition_name => p_partition, 39 freelist_group_id => 0, 40 free_blks => l_free_blks ); 41 dbms_output.put_line( 'Old fashioned' ); 42 end loop; 43 44 dbms_space.unused_space 45 ( segment_owner => p_owner, 46 segment_name => p_segname, 47 segment_type => p_type, 48 partition_name => p_partition, 49 total_blocks => l_total_blocks, 50 total_bytes => l_total_bytes, 51 unused_blocks => l_unused_blocks, 52 unused_bytes => l_unused_bytes, 53 LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, 54 LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, 55 LAST_USED_BLOCK => l_LAST_USED_BLOCK ); 56 57 p( 'Free Blocks', l_free_blks ); 58 p( 'Total Blocks', l_total_blocks ); 59 p( 'Total Bytes', l_total_bytes ); 60 p( 'Total MBytes', trunc(l_total_bytes/1024/1024) ); 61 p( 'Unused Blocks', l_unused_blocks ); 62 p( 'Unused Bytes', l_unused_bytes ); 63 p( 'Last Used Ext FileId', l_LastUsedExtFileId ); 64 p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); 65 p( 'Last Used Block', l_LAST_USED_BLOCK ); 66* end; ops$tkyte@ORA920> drop table t; Table dropped. ops$tkyte@ORA920> create table t ( x int ); Table created. ops$tkyte@ORA920> exec show_space('T') Free Blocks............................. Total Blocks............................8 Total Bytes.............................65536 Total MBytes............................0 Unused Blocks...........................5 Unused Bytes............................40960 Last Used Ext FileId....................9 Last Used Ext BlockId...................48 Last Used Block.........................3 PL/SQL procedure successfully completed.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopFREE BLOCK Vs PERFORMANCE  June 12, 2003
Reviewer:  Raghu  from Milwaukee, WI USA

Hi Tom What is the impact of the Large High water mark or FREE Blocks or UNUSED block on the table performacne (INSERT/UPDATE/SELECT). I have a case in my environment where a batch takes 9 minutes in one instance (EDEV) amd ot takes 2 hrs in another (EPROD). The outputs from yr procedure and from ALL_TABLES is given below for both the instance. EDEV Free Blocks 4 Total Blocks 1984 Total Bytes 16252928 Unused Blocks 1 Unused Bytes 8192 Last Used Ext FileId 112 Last Used Ext BlockId 5193 Last Used Block 15 EPROD Free Blocks 20 Total Blocks 2352 Total Bytes 19267584 Unused Blocks 11 Unused Bytes 90112 Last Used Ext FileId 346 Last Used Ext BlockId 3065 Last Used Block 5 BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS 2340 6 4462 2103 select blocks, empty_blocks, avg_space, num_freelist_blocks from user_tables where table_name = 'BACKLOG_ITEM' BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS 3956 11 4570 2004 2. If my process does not prevent me to issue a TRUNCATE statement, but my batch involves lots of DELETES and INSERT, then how can I prevent large values for high water mark?. Do we have any utility/script that we can run to reduce the high water mark of a table.
Followup:

1) both of those tables are so small, If I had a "batch" that took 9 minutes to process 20meg of data, I'd be concerned about that in itself. That is probably 8 minutes and 30 seconds longer then it should be. Use TKPROF and SQL_TRACE to find your low hanging fruit. 2) you have a very very small table, I think you are looking in the wrong place here. You have most likely a bad algorithm. You need to diagnose where the problem is, then fix it. Not "fix something" and see if it was the right thing.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopThanks  June 13, 2003
Reviewer:  Raghu  from Milwaukee, WI USA

Hi Tom, Thanks for your response. I absolutely agree with you that the performance of the batch I had given in my example could be improved by changing the algorithm. I am sorry that my example diverted you from the "real" reason for my comment. I was wondering how I can make use of the output of your query in my DB maintenenace. For instance. Is there a co-relation between the number of Free blocks and performance. In my original example, Can I attribute the difference in batch performance between two instance to number of Free blocks?(Again I accept that the performance could be improved further). Another example, I have a table that has around 6 million rows. The average Row length is 1250 bytes. We do lots of "Delete/Insert/Update" on this table. We recently re-partitioned this table (using Export, CREATE Table and Import). We noticed a huge performance changes for SELECTS..Over the period of time, it performance is going back to the "Normal". Can I attribute this to FREE Blocks and Do you think that we should constantly look for such blocks and "clean it" and how can we do it?. Note: At this time, I am trying to find the Root cause for the performance changes. When I read this article of yours I felt that I might have discovered "one" of the casues and wanted a confirmation from you. I also like to point out that the DB are very similar in terms of size, parameter etc. The code is also same.
Followup:

there could be -- but in this case, the numbers are far too small. I thought it was clear that I do not think that the small number of blocks you have on freelists here would have any material affect on anything.... No, you cannot attribute anything to anything here. There is insufficient data to say anything reasonable. You don't say which way the performance change was, what type of operations you do on this table, nothing. Again, totally insufficient data to say anything about anything (except hypothesize, which I don't like doing too much). Now, if you had some metrics (tkprofs!!! statspacks!!!) - hard numerical, incontravertible data points that would provide information as to the number of consistent gets, query plans, inputs, etc over time -- then, well, then we'd be cooking with gas... (but, the performance of queries is unaffected by the number of blocks on the freelist -- I could set pctfree/pctused so that every block is on the freelist. I could set the pctfree/pctused so that NO block is on the freelist. I could have BOTH of these tables -- one with every block on and other with every block off -- organized in such a way that block for block they are in effect "the same". They would perform identically. It is nothing to do with how many blocks are on the freelist (although you may be able to use that as an indicator if you understand how pctfree/pctused are set and how they are used). It has everything to do with how many blocks Oracle must process in order to answer your queries. You need a TKPROF. You need a TKPROF of this "batch" on dev, of this "batch" on prod. You need to compare them. That'll get you 90% of your way along in your root cause analysis.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopDear Tom, what could be wrong here?  July 24, 2003
Reviewer:  Saminathan Seerangan  from NJ,USA

Tom, I am not able to use show_space procedure. Please help me out. SQL> create user a identified by a; User created. SQL> grant create session, create procedure, 2 create table to a; Grant succeeded. SQL> alter user a quota unlimited on users; User altered. SQL> connect a/a Connected. SQL> @D:\share\oracle\asktom\show_space.sql 51 / Procedure created. SQL> create table t ( x int ) tablespace users; Table created. SQL> exec show_space( 'T' ) BEGIN show_space( 'T' ); END; * ERROR at line 1: ORA-10618: Operation not allowed on this segment ORA-06512: at "SYS.DBMS_SPACE", line 74 ORA-06512: at "A.SHOW_SPACE", line 22 ORA-06512: at line 1 SQL> desc t Name Null? Type ------------------------------------- -------- -------- X NUMBER(38) SQL> insert into t values(100); 1 row created. SQL> desc dbms_space PROCEDURE FREE_BLOCKS Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN FREELIST_GROUP_ID NUMBER IN FREE_BLKS NUMBER OUT SCAN_LIMIT NUMBER IN DEFAULT PARTITION_NAME VARCHAR2 IN DEFAULT PROCEDURE SPACE_USAGE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN UNFORMATTED_BLOCKS NUMBER OUT UNFORMATTED_BYTES NUMBER OUT FS1_BLOCKS NUMBER OUT FS1_BYTES NUMBER OUT FS2_BLOCKS NUMBER OUT FS2_BYTES NUMBER OUT FS3_BLOCKS NUMBER OUT FS3_BYTES NUMBER OUT FS4_BLOCKS NUMBER OUT FS4_BYTES NUMBER OUT FULL_BLOCKS NUMBER OUT FULL_BYTES NUMBER OUT PARTITION_NAME VARCHAR2 IN DEFAULT PROCEDURE UNUSED_SPACE Argument Name Type In/Out Default? ------------------------------ ----------------------- ------ -------- SEGMENT_OWNER VARCHAR2 IN SEGMENT_NAME VARCHAR2 IN SEGMENT_TYPE VARCHAR2 IN TOTAL_BLOCKS NUMBER OUT TOTAL_BYTES NUMBER OUT UNUSED_BLOCKS NUMBER OUT UNUSED_BYTES NUMBER OUT LAST_USED_EXTENT_FILE_ID NUMBER OUT LAST_USED_EXTENT_BLOCK_ID NUMBER OUT LAST_USED_BLOCK NUMBER OUT PARTITION_NAME VARCHAR2 IN DEFAULT SQL>
Followup:

create or replace procedure show_space ( p_segname in varchar2, p_owner in varchar2 default user, p_type in varchar2 default 'TABLE', p_partition in varchar2 default NULL ) -- this procedure uses authid current user so it can query DBA_* -- views using privileges from a ROLE and so it can be installed -- once per database, instead of once per user that wanted to use it authid current_user as l_free_blks number; l_total_blocks number; l_total_bytes number; l_unused_blocks number; l_unused_bytes number; l_LastUsedExtFileId number; l_LastUsedExtBlockId number; l_LAST_USED_BLOCK number; l_segment_space_mgmt varchar2(255); l_unformatted_blocks number; l_unformatted_bytes number; l_fs1_blocks number; l_fs1_bytes number; l_fs2_blocks number; l_fs2_bytes number; l_fs3_blocks number; l_fs3_bytes number; l_fs4_blocks number; l_fs4_bytes number; l_full_blocks number; l_full_bytes number; -- inline procedure to print out numbers nicely formatted -- with a simple label procedure p( p_label in varchar2, p_num in number ) is begin dbms_output.put_line( rpad(p_label,40,'.') || to_char(p_num,'999,999,999,999') ); end; begin -- this query is executed dynamically in order to allow this procedure -- to be created by a user who has access to DBA_SEGMENTS/TABLESPACES -- via a role as is customary. -- NOTE: at runtime, the invoker MUST have access to these two -- views! -- this query determines if the object is a ASSM object or not begin execute immediate 'select ts.segment_space_management from dba_segments seg, dba_tablespaces ts where seg.segment_name = :p_segname and (:p_partition is null or seg.partition_name = :p_partition) and seg.owner = :p_owner and seg.tablespace_name = ts.tablespace_name' into l_segment_space_mgmt using p_segname, p_partition, p_partition, p_owner; exception when too_many_rows then dbms_output.put_line ( 'This must be a partitioned table, use p_partition => '); return; end; -- if the object is in an ASSM tablespace, we must use this API -- call to get space information, else we use the FREE_BLOCKS -- API for the user managed segments if l_segment_space_mgmt = 'AUTO' then dbms_space.space_usage ( p_owner, p_segname, p_type, l_unformatted_blocks, l_unformatted_bytes, l_fs1_blocks, l_fs1_bytes, l_fs2_blocks, l_fs2_bytes, l_fs3_blocks, l_fs3_bytes, l_fs4_blocks, l_fs4_bytes, l_full_blocks, l_full_bytes, p_partition); p( 'Unformatted Blocks ', l_unformatted_blocks ); p( 'FS1 Blocks (0-25) ', l_fs1_blocks ); p( 'FS2 Blocks (25-50) ', l_fs2_blocks ); p( 'FS3 Blocks (50-75) ', l_fs3_blocks ); p( 'FS4 Blocks (75-100)', l_fs4_blocks ); p( 'Full Blocks ', l_full_blocks ); else dbms_space.free_blocks( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, freelist_group_id => 0, free_blks => l_free_blks); p( 'Free Blocks', l_free_blks ); end if; -- and then the unused space API call to get the rest of the -- information dbms_space.unused_space ( segment_owner => p_owner, segment_name => p_segname, segment_type => p_type, partition_name => p_partition, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK ); p( 'Total Blocks', l_total_blocks ); p( 'Total Bytes', l_total_bytes ); p( 'Total MBytes', trunc(l_total_bytes/1024/1024) ); p( 'Unused Blocks', l_unused_blocks ); p( 'Unused Bytes', l_unused_bytes ); p( 'Last Used Ext FileId', l_LastUsedExtFileId ); p( 'Last Used Ext BlockId', l_LastUsedExtBlockId ); p( 'Last Used Block', l_LAST_USED_BLOCK ); end; / try that one, you must be in 9i using ASSM with the older version from 8i -- assm does not have freelists
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopThank you so much  July 24, 2003
Reviewer:  Saminathan Seerangan  from NJ,USA

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

thanks -- i fixed that up
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopAccess to dba_* views needed  September 17, 2003
Reviewer:  Dusan  from Czech republic

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

instead of me querying the dba_views, you can pass in that information (what type of tablespace is it in) use the "8i" like version, just parameterize it to call the proper API
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopSeems it does not work for partitioned table  September 17, 2003
Reviewer:  Dusan  from Czech rep.

Problems appeared when I tried to use this utility for partitioned table. 1 select TABLE_OWNER, PARTITION_NAME 2 from all_tab_partitions 3 where table_owner='VALASEKD' 4* and table_name='DV_ALL_OBJECTS_PART' [email protected]> / TABLE_OWNER PARTITION_NAME ------------------------------ ---------------- VALASEKD P030915 VALASEKD P030916 VALASEKD PMAX 1 select ts.segment_space_management 2 from dba_segments seg, dba_tablespaces ts 3 where seg.segment_name = 'DV_ALL_OBJECTS_PART' 4 and ('PMAX' is null or 5 seg.partition_name = 'PMAX') 6 and seg.owner = 'VALASEKD' 7* and seg.tablespace_name = ts.tablespace_name [email protected]> / SEGMEN ------ AUTO [email protected]> ed Zapsáno soubor afiedt.buf 1* exec show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX'); [email protected]> exec show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX'); BEGIN show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX'); END; * ERROR na øádku 1: ORA-03204: zadání typu segmentu by mìlo vyznaèovat dìlení na partition ORA-06512: na "SYS.DBMS_SPACE", line 97 ORA-06512: na "SYS.SHOW_SPACE", line 64 ORA-06512: na line 1
Bookmark Review | Bottom | TopDIfferent Extent Sizes  October 02, 2003
Reviewer:  Kailash  from East Brunswick, NJ

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

did you read the followup I posted with a link to the docs? when you ask for 40k in a dictionary managed tablespace and we find a free extent that is within 5 blocks of your request (eg: 40k = 5 * 8k blocks -- anything that is free and has 5, 6, 7, 8, 9, 10 blocks will be used). This is to avoid the situation whereby you have a free extent of say 7 blocks -- you asked for 5, so we take 5 leaving "2 totally useless blocks" hanging out there. If the free extent was 11 blocks -- we would have given you your 5 and left 6 out there for the next guy. Otherwise you would end up with lots of 1/2/3 block free extents that couldn't be used for anything.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopMore explanation please  June 01, 2004
Reviewer:  parag jayanat patankar  from India

Hi Tom, Thanks for explaining this thing in great way. I have done analysis on my table and found following 11:21:50 test@prod> exec show_space('V9SPD140'); Free Blocks.............................4051 Total Blocks............................7418 Total Bytes.............................30384128 Unused Blocks...........................0 Unused Bytes............................0 Last Used Ext FileId....................5 Last Used Ext BlockId...................89500 Last Used Block.........................3 I am confused with above data as it is not matching with your formula as explained in this thread :- ops$tkyte@8i> exec show_space( 'T' ) Free Blocks.............................19 Total Blocks............................32 Total Bytes.............................262144 Unused Blocks...........................12 Unused Bytes............................98304 Last Used Ext FileId....................1 Last Used Ext BlockId...................64681 Last Used Block.........................5 PL/SQL procedure successfully completed. The above shows that the delete simply put all of our blocks on the free list. We have 19 blocks on the free list + 12 unused blocks + 1 system block = 32 blocks. In my data Total Blocks are : 7418 = Blocks on free list : 4051 + Unused Blocks : 0 + System Block : 1 Then where are other blocks gone ? Total blocks should be matched as per your formula Kindly explain as I have missed out some imp part. thanks & regards parag jayant patankar
Followup:

the other blocks have data on them and just are not on the freelist (not available for inserts)
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopIt's amazing what you find when you actually read the documentation .. ;)  July 13, 2004
Reviewer:  Greg  from Toronto, Ontario

Funny that ... I was having trouble with some privilege rights on this dbms_space package and pulling my hair out .. I finally come here and do a search .... Do I find my problem conveniently answered by Tom? No. What do I get instead: "have you read about the dbms_* packages I use (they are documented). It is pretty straightforward stuff." ... right, so a slap in the face, and the answer sitting right under my nose in the docs ... hehe *sigh* ... Thanks, Tom!! .. I needed that!! :)
Bookmark Review | Bottom | TopGood explanation  August 02, 2004
Reviewer:  Cyrille  from FRANCE

Thanks for all your explanation.
Bookmark Review | Bottom | TopHWM below or above?  August 26, 2004
Reviewer:  RD  from NZ

Hi Tom, At the beginning of the thread you said " Truncate it. That puts all of the blocks below the HWM. Now we have 31 unused blocks + 1 system block = 32 blocks total. None on the free list since none of them have any data." Should that have been "that puts all of the blocks ABOVE the HWM" cos truncate will always lower the HWM or am I wrong?? Thanks, Regards, RD.
Followup:

correct, the HWM would not even "exist" really.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopFree Space  August 30, 2004
Reviewer:  Nazmul Hoque  from Bangladesh

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

do you have a dba you can work with? you control the size of temp, that is entirely up to you. if you don't like temp being so large, drop it and recreate it. search this site for maxshrink, it'll tell you if you can shrink your users01.dbf file. it is normal for a database to consume space. you have indexes, you have free space in there. 3gig is tiny.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopQuestions  August 30, 2004
Reviewer:  A reader

1. Why do you have the 'EXCEPTION WHEN OTHERS THEN NULL' in your code above? You are very much against hiding exceptions, arent you? 2. I understand the AUTHID CURRENT_USER part so that it can be installed once per database and each calling user doesnt get any extra privileges due to it being definer's right. But how does AUTHID CURRENT_USER address "this procedure uses authid current user so it can query DBA_* views using privileges from a ROLE" 3. "-- this query is executed dynamically in order to allow this procedure to be created by a user who has access to DBA_SEGMENTS/TABLESPACES via a role as is customary" Instead, why not simply use the ALL_* views so that the EXECUTE IMMEDIATE is not needed at all? Every user calling this proc automatically sees what they are supposed to see i.e. the ALL_* views do all the heavy lifting instead of us? 4. What benefit is PIPELINED giving us in the 9iR2 version of this utility? 5. Why is the PRAGMA AUTONOMOUS_TRANSACTION needed? Thanks
Followup:

1) because here I have a simple report, rather then blow up part way thru on an object that cannot be "free space reported" on i skip it. 2) l_sql := 'select owner, segment_name, segment_type, partition_name from dba_segments '; add_predicate( 'segment_name', p_segname ); add_predicate( 'owner', p_owner ); add_predicate( 'segment_type', p_type ); add_predicate( 'partition', p_partition ); execute immediate 'alter session set cursor_sharing=force'; open l_cursor for l_sql; execute immediate 'alter session set cursor_sharing=exact'; I used dynamic sql -- so the CREATOR of the procedure need not have DBA_* access, just the INVOKER of the procedure. and they can use the privs from a role since roles can be in place during the execution of an invokers rights routine. 3) because this was written to be a DBA_ utility? DBA_ views have no filters on them. 4) more efficient, search for pipelined for many examples 5) it isn't, don't know what I was thinking leaving it in.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopFree Space (Table Space)  August 31, 2004
Reviewer:  Nazmul Hoque  from Dhaka - Bangladesh

** do you have a dba you can work with? I am the programmer and DBA both !!! **** you control the size of temp, that is entirely up to you. if you don't like temp being so large, drop it and recreate it. Please Advise me how I DROP and RRCREATE, Temp is created by the stystem, If I dtop that, is it create any problem ? *** search this site for maxshrink, it'll tell you if you can shrink your users01.dbf file. Please write with details..... *** it is normal for a database to consume space. you have indexes, you have free space in there. 3gig is tiny. In my server now only 400 MB is free on that drive. PLEASE HELP ME.... Thanks Nazmul Hoque
Followup:

dba and programmer both is going to be a problem. this is straight forward dba stuff really. to downsize your temp, create new: ops$tkyte@ORA9IR2> create temporary tablespace temp tempfile size 100m; Tablespace created. and create new ops$tkyte@ORA9IR2> drop tablespace temp2; Tablespace dropped. the details on maxshrink are as follows: search this site for maxshrink, read the first couple of pages you find. perhaps you need "more disk".
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | Topdefine  September 02, 2004
Reviewer:  ABC

Tom Please provide single line definitio of HWM
Followup:

High Water Mark -- the biggest the table ever was, HWM's are pushed "up" if you fill up a table with 1,000 blocks of data and then delete all of the data -- there will still be 1,000 blocks of "data" there that would be full scanned.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopTemp Tablespace Create and Drop  September 02, 2004
Reviewer:  Nazmul Hoque  from Bangladesh, Dhaka

dba and programmer both is going to be a problem. this is straight forward dba stuff really. -- No other option to downsize your temp, create new: ops$tkyte@ORA9IR2> create temporary tablespace temp tempfile size 100m; Tablespace created. and create new --- getting below message : SQL> create temporary tablespace temp tempfile size 100m; create temporary tablespace temp tempfile size 100m * ERROR at line 1: ORA-02236: invalid file name **** Please help me **** ops$tkyte@ORA9IR2> drop tablespace temp2; Tablespace dropped. the details on maxshrink are as follows: search this site for maxshrink, read the first couple of pages you find. perhaps you need "more disk". *** WE are using server Compaq Proliant 1600, Now a days it so difficault to add HDD at My end. **** Dear Tom, I know how create table space and drop tabel space from SQL and From DBA Studio both, My problem is Temp tablespace, I never create Temp tablespace or drop before. So far I have seen it is created by the system at time of installation. If i drop and Recreate temp with 100 MB space my problem will be solved, Please give some more trips to Drop and Creat Temp Tablespace. Please Please Please.. Thanks Nazmul Hoque
Followup:

you'll need to refer to your sql reference, I cannot (will not) be your dba. you are using 817, you didn't have Oracle Managed Files way back then. You need to name your tempfile. The sql reference (available on otn.oracle.com) will give you the syntax.
document.getElementById("latestLink").style.display = "inline" Bookmark Review | Bottom | TopTEMPORARY TABLESPACE  September 03, 2004
Reviewer:  NAZMUL HOQUE  from Bangladesh

MY TABLESPACE LIST AS BELOW : ================================ TABLESPACE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS ---------- -------------- ----------- ----------- ----------- ------------ ------------------ SYSTEM 65536 65536 1 2.147E+09 50 65536 ONLINE RBS 524288 524288 8 4096 50 524288 ONLINE USERS 131072 131072 1 4096 0 131072 ONLINE TEMP 65536 65536 1 0 65536 ONLINE TOOLS 32768 32768 1 4096 0 32768 ONLINE INDX 131072 131072 1 4096 0 131072 ONLINE DRSYS 65536 65536 1 2.147E+09 50 65536 ONLINE DHAKA 40960 40960 1 505 50 0 ONLINE SSLDATA 40960 40960 1 505 50 0 ONLINE ================================ NORMALY WE USE BELOW COMMAND FROM SQL TO CREATE A TABLESPACE (PATH MENTIONED AS PER OUR SERVER LOCATION) : CREATE TABLESPACE TEST DATAFILE 'D:\ORACLE\ORADATA\NTSERVER\DATA1.DAT' SIZE 100M AUTOEXTEND ON NEXT 10M SHALL I USE BELOW FOR TEMPORARY TABLESPACE ========================================== CREATE TEMPORARY TABLESPACE TEMP DATAFILE 'D:\ORACLE\ORADATA\NTSERVER\TEMP01.DBF' SIZE 100M AUTOEXTEND ON NEXT 5M TO DROP TABELSPACE : Drop Tablespace TEST; FOR TEMPORARY TABLESPACE: ======================== Drop TEMPORARY Tablespace TEMP; DEAR TOM, PLEASE ADVISE ME, I AM DOING WRONG OR RIGHT. IF WRONG THAN PLEASE WRITE ONLY TWO LINES TO CORRECT ME. THANKS NAMZUL HOQUE
Followup:

create temporary tablespace temp TEMPFILE '.......' ..... ^^^^^^^^ tempfile, not datafile. documentation is very useful for this stuff http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem5e.htm#2063472

(hint, hit the shiftlock key...)
document.getElementById("latestLink").style.display = "inline"

本文地址：http://com.8s8s.com/it/it20527.htm