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 |
Table 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 | Top
dbms_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 | Top
dbms_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 | Top
dbms_space September 19, 2001
Reviewer: Jim from MA
Very, Very Good!!!
Bookmark Review | Bottom | Top
Errors 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??)**
Bookmark Review | Bottom | Top
A 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.
Bookmark Review | Bottom | Top
5 extents 40k each (8k block size) why not 5X5=25 blocks? October 22, 2001
Reviewer: YK LIU from CA, USA
Bookmark Review | Bottom | Top
Free Space October 30, 2001
Reviewer: an from DE
it's excellent!
Bookmark Review | Bottom | Top
ORA-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.
Bookmark Review | Bottom | Top
Reader 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.
Bookmark Review | Bottom | Top
Reader 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)
Bookmark Review | Bottom | Top
Reader 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] > **
Bookmark Review | Bottom | Top
Extremely 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 | Top
Wow ! March 20, 2002
Reviewer: Mini from OH
Exactly what I was looking for.
Thank you so much Tom
Bookmark Review | Bottom | Top
How the extents are allocated in bytes April 30, 2002
Reviewer: Santosh Jadhav fr