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 | Top
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 | Top
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
Reviewer: Helena Markova from Bratislava, Slovakia
Bookmark Review | Bottom | Top
Reviewer: D.C.L. from Seattle, USA
Right on. Awesome grip of the subject matter.
Bookmark Review | Bottom | Top
Reviewer: Vikram from Delhi, India
Excellent
Bookmark Review | Bottom | Top
Reviewer: k.v.s.Raju from Sydney, Australia
Its excellent
Bookmark Review | Bottom | Top
Reviewer: Jim from MA
Very, Very Good!!!
Bookmark Review | Bottom | Top
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)
ops$tkyte@ORA817DEV.US.ORACLE.COM> create user a identified by a;
User created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> grant create session, create procedure,
create table to a;
Grant succeeded.
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter user a quota unlimited on users;
User altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> connect a/a
Connected.
a@ORA817DEV.US.ORACLE.COM> @showspace
Procedure created.
a@ORA817DEV.US.ORACLE.COM> create table t ( x int ) tablespace users;
Table created.
a@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' )
PL/SQL procedure successfully completed.
ops$tkyte@ORA817DEV.US.ORACLE.COM> set serveroutput on
ops$tkyte@ORA817DEV.US.ORACLE.COM> exec show_space( 'T' );
Free Blocks.............................0
Total Blocks............................64
Total Bytes.............................524288
Unused Blocks...........................63
Unused Bytes............................516096
Last Used Ext FileId....................7
Last Used Ext BlockId...................4809
Last Used Block.........................1
PL/SQL procedure successfully completed.
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??)
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
Reviewer: YK LIU from CA, USA
Bookmark Review | Bottom | Top
Reviewer: an from DE
it's excellent!
Bookmark Review | Bottom | Top
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:
ops$tkyte@ORA817DEV.US.ORACLE.COM> select SEGMENT_NAME, PARTITION_NAME
SEGMENT_TYPE, FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
2 from table( cast( show_space_for( 'HASHED',user,'%' ) as
show_space_table_type ) )
3 /
SEGMENT_NA SEGMENT_TYPE FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS
---------- ----------------- ----------- ------------ -------------
HASHED PART_2 1 64 62
HASHED PART_3 1 64 62
HASHED PART_4 1 64 62
HASHED PART_1 1 64 62
ops$tkyte@ORA817DEV.US.ORACLE.COM>
And in 9i, we'd change the function to be pipelined:
ops$tkyte@ORA9I.WORLD> create or replace
2 function show_space_for
3 ( p_segname in varchar2,
4 p_owner in varchar2 default user,
5 p_type in varchar2 default 'TABLE',
6 p_partition in varchar2 default NULL )
7 return show_space_table_type
8 authid CURRENT_USER
9 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.
ops$tkyte@ORA9I.WORLD> set arraysize 1
ops$tkyte@ORA9I.WORLD> select SEGMENT_NAME, SEGMENT_TYPE,
FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS
2 from table( show_space_for( '%',user,'%' ) )
3 /
SEGMENT_NAME SEGMENT_TYPE FREE_BLOCKS TOTAL_BLOCKS UNUSED_BLOCKS
--------------- ----------------- ----------- ------------ -------------
KEEP_SCN TABLE 1 64 62
EMPLOYEES TABLE 0 64 63
STINKY TABLE 0 64 63
OBJECT_TABLE TABLE 1 64 62
RUN_STATS TABLE 2 64 53
EMP TABLE 0 64 62
PROJ TABLE 0 64 62
X TABLE 1 64 62
WORDS TABLE 0 64 63
DOCS TABLE 0 64 63
KEYWORDS TABLE 0 64 63
DEPT TABLE 2 64 61
C TABLE 1 64 62
DSINVLINES TABLE 1 64 62
NUM_STR TABLE 1 64 23
T TABLE 4 64 28
T1 TABLE 0 64 63
T2 TABLE 0 64 63
BOM TABLE 1 64 62
PARTS TABLE 1 64 62
SYS_C001371 INDEX 0 64 62
SYS_C001372 INDEX 0 64 62
SYS_C001574 INDEX 0 64 62
SYS_C001694 INDEX 0 64 62
SYS_C001695 INDEX 0 64 62
BOM_PK INDEX 0 64 62
PARTS_PK INDEX 0 64 62
27 rows selected.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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)
Bookmark Review | Bottom | Top
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):
ops$tkyte@ORA817DEV.US.ORACLE.COM> Create tablespace TS1 datafile '/tmp/ts1.dbf'
size 24k reuse;
Tablespace created.
ops$tkyte@ORA817DEV.US.ORACLE.COM> host ls -l /tmp/ts1.dbf
-rw-r----- 1 ora817 32768 Dec 26 13:33 /tmp/ts1.dbf
ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
---------------- ------- ---------- --------- ------ ------- -------- ------
.....
TS1 24 8 16 33.3 16 0 .0
------- ---------- ---------
sum 3,818,848 1,605,144 2,213,704
13 rows selected.
see, 24k in size -- 8 is used, 16 free...
ops$tkyte@ORA817DEV.US.ORACLE.COM> alter tablespace ts1 add datafile
'/tmp/ts2.dbf' size 24k reuse;
Tablespace altered.
ops$tkyte@ORA817DEV.US.ORACLE.COM> host ls -l /tmp/ts2.dbf
-rw-r----- 1 ora817 32768 Dec 26 13:33 /tmp/ts2.dbf
ops$tkyte@ORA817DEV.US.ORACLE.COM> @free 1
MaxPoss Max
Tablespace Name KBytes Used Free Used Largest Kbytes Used
---------------- ------- --------- --------- ------ -------- -------- ------
TS1 48 16 32 33.3 16 0 .0
------- --------- ---------
sum 3,818,872 1,605,152 2,213,720
13 rows selected.
now, 48k (24*2), 16k used (1 block / file )
ops$tkyte@ORA817DEV.US.ORACLE.COM>
Bookmark Review | Bottom | Top
Reviewer: Reader from USA
Tom,
Thanks very much
I do plan on using LMT in new databases
Bookmark Review | Bottom | Top
Reviewer: Dixit from NJ,USA
Very Good
Bookmark Review | Bottom | Top
Reviewer: Mini from OH
Exactly what I was looking for.
Thank you so much Tom
Bookmark Review | Bottom | Top
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#2846it covers the algorithm.
Bookmark Review | Bottom | Top
Reviewer: Doug from CT, USA
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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
Bookmark Review | Bottom | Top
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
Bookmark Review | Bottom | Top
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
Bookmark Review | Bottom | Top
Reviewer: Dusan from Czech rep.
Problems appeared when I tried to use this utility for partitioned table.
1 select TABLE_OWNER, PARTITION_NAME
2 from all_tab_partitions
3 where table_owner='VALASEKD'
4* and table_name='DV_ALL_OBJECTS_PART'
sys@DV9IR2.US.ORACLE.COM> /
TABLE_OWNER PARTITION_NAME
------------------------------ ----------------
VALASEKD P030915
VALASEKD P030916
VALASEKD PMAX
1 select ts.segment_space_management
2 from dba_segments seg, dba_tablespaces ts
3 where seg.segment_name = 'DV_ALL_OBJECTS_PART'
4 and ('PMAX' is null or
5 seg.partition_name = 'PMAX')
6 and seg.owner = 'VALASEKD'
7* and seg.tablespace_name = ts.tablespace_name
sys@DV9IR2.US.ORACLE.COM> /
SEGMEN
------
AUTO
sys@DV9IR2.US.ORACLE.COM> ed
Zapsáno soubor afiedt.buf
1* exec show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX');
sys@DV9IR2.US.ORACLE.COM> exec show_space('DV_ALL_OBJECTS_PART',
'VALASEKD','TABLE','PMAX');
BEGIN show_space('DV_ALL_OBJECTS_PART', 'VALASEKD','TABLE','PMAX'); END;
*
ERROR na øádku 1:
ORA-03204: zadání typu segmentu by mìlo vyznaèovat dìlení na partition
ORA-06512: na "SYS.DBMS_SPACE", line 97
ORA-06512: na "SYS.SHOW_SPACE", line 64
ORA-06512: na line 1
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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)
Bookmark Review | Bottom | Top
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 | Top
Reviewer: Cyrille from FRANCE
Thanks for all your explanation.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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".
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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.
Bookmark Review | Bottom | Top
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...)
