...I know that If I specify "autoextend on", the datafile will extend 1 block by
default.
....
not true in current releases.
ops$tkyte%ORA10GR2> create tablespace tt1 datafile '/tmp/tt1.dbf' size 1m autoextend on;
Tablespace created.
ops$tkyte%ORA10GR2> create tablespace tt2 datafile size 1m autoextend on;
Tablespace created.
ops$tkyte%ORA10GR2> create tablespace tt3 datafile size 150m autoextend on;
Tablespace created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select tablespace_name, file_name, increment_by from dba_data_files where tablespace_name like 'TT_';
TABLESPACE_NAME FILE_NAME INCREMENT_BY
------------------------------ ------------------------------ ------------
TT1 /tmp/tt1.dbf 1
TT2 /home/ora10gr2/oradata/ora10gr 128
2/ORA10GR2/datafile/o1_mf_tt2_
5d1w4c2d_.dbf
TT3 /home/ora10gr2/oradata/ora10gr 12800
2/ORA10GR2/datafile/o1_mf_tt3_
5d1w4cgh_.dbf
for Oracle Managed Files (OMF), the default autoextend size is the minimum of 100M or the size of datafile.
And it'll will autoextend in a single call - using the INCREMENT_BY in multiples.
you can see this (my blocksize is 8k) by doing something like:
ops$tkyte%ORA10GR2> create tablespace tt1 datafile '/tmp/tt1.dbf' size 1m autoextend on
2 extent management local uniform size 64k;
Tablespace created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create table t ( x number, y char(2000) ) tablespace tt1;
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> declare
2 l_start number;
3 l_curr number;
4 begin
5 select bytes into l_start
6 from dba_data_files
7 where file_name = '/tmp/tt1.dbf';
8
9 dbms_output.put_line( 'start size ' || l_start );
10
11 for i in 1 .. 330
12 loop
13 insert into t values ( i, 'x' );
14 commit;
15 end loop;
16 end;
17 /
start size 1048576
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> select bytes
2 from dba_data_files
3 where file_name = '/tmp/tt1.dbf';
BYTES
----------
1048576
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> @getspid
ops$tkyte%ORA10GR2> select a.spid dedicated_server,
2 b.process clientpid
3 from v$process a, v$session b
4 where a.addr = b.paddr
5 and b.sid = (select sid from v$mystat where rownum=1)
6 /
DEDICATED_SE CLIENTPID
------------ ------------
22604 614
ops$tkyte%ORA10GR2> pause
ops$tkyte%ORA10GR2> insert into t values ( 331, 'x' );
1 row created.
I know (by testing) that 330 rows fit in the 1m datafile, 331 rows would need to autoextend by 64k (my uniform size)
when I paused, I attached to my dedicated server (it'll do the file resize) with strace.
I observed:
stat64("/tmp/tt1.dbf", {st_mode=S_IFREG|0660, st_size=1056768, ...}) = 0
statfs("/tmp/tt1.dbf", {f_type="EXT2_SUPER_MAGIC", f_bsize=4096, f_blocks=34430738, f_bfree=20666604, f_bavail=18917612, f_files=17498112, f_ffree=16974608, f_fsid={0, 0}, f_namelen=255, f_frsize=4096}) = 0
open("/tmp/tt1.dbf", O_RDWR|O_LARGEFILE) = 19
lseek(19, 0, SEEK_SET) = 0
write(19, "\0\242\0\0\0\0\300\377\0\0\0\0\0\0\0\0\376\372\0\0\0 \0"..., 8192) = 8192
ftruncate64(19, 1122304) = 0
close(19) = 0
ftruncate64 can grow or shrink a file, it grew the file by 64k - the next extent size.