dbms_space.free_space[7]

index 0 64 62 27 rows selected.

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 

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. 

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 

what is the create tablespace command you used (and why are we losing sleep over 
1 block) 

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 

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>