DB version: 11.2.0.4
Platform : Oracle Linux 6.5
Test done in a 2-node RAC DB
I have created a tablespace with Uniform Extent size of 4MB.
And later, when I added a datafile with INCREMENT SIZE of 256K (AUTOEXTEND ON NEXT 256K ) , I was hoping it would error out because the extent size itself is 4MB in size so the INCREMENT_BY should be at least 4MB in size. But, it didn't error out. Any idea why ?
SQL> create tablespace TEST_TS3 datafile '+DATA_DG' size 25G
autoextend on
maxsize 32767m
extent management local
uniform size 4m
segment space management auto; 2 3 4 5 6
Tablespace created.
SQL> select BLOCK_SIZE, INITIAL_EXTENT/1024/1024 , NEXT_EXTENT/1024/1024 from dba_tablespaces where TABLESPACE_NAME = 'TEST_TS3';
BLOCK_SIZE INITIAL_EXTENT/1024/1024 NEXT_EXTENT/1024/1024
---------- ------------------------ ---------------------
8192 4 4
--- Just discovered that the default INCREMENT size is 100MB (at least in 11.2.0.4)
SQL> select FILE_NAME, BYTES/power(1024,3) BytesInGB, increment_by INCREMENT_BY_BLOCKS, (increment_by*8192)/power(1024,2) IncrementByInMB
2 from dba_data_files where TABLESPACE_NAME = 'TEST_TS3';
FILE_NAME BYTESINGB INCREMENT_BY_BLOCKS INCREMENTBYINMB
-------------------------------------------------- ---------- ------------------- ---------------
+DATA_DG/hrcpdas/datafile/test_ts3.413.887991171 25 12800 100
SQL> ALTER TABLESPACE TEST_TS3
ADD DATAFILE '+DATA_DG'
SIZE 2g
AUTOEXTEND ON NEXT 256K
MAXSIZE 30G; 2 3 4 5 6
Tablespace altered.
SQL> select FILE_NAME, BYTES/power(1024,3) BytesInGB, increment_by INCREMENT_BY_BLOCKS, (increment_by*8192)/power(1024,2) IncrementByInMB
2 from dba_data_files where TABLESPACE_NAME = 'TEST_TS3';
FILE_NAME BYTESINGB INCREMENT_BY_BLOCKS INCREMENTBYINMB
-------------------------------------------------- ---------- ------------------- ---------------
+DATA_DG/hrcpdas/datafile/test_ts3.413.887991171 25 12800 100
+DATA_DG/hrcpdas/datafile/test_ts3.414.887992467 2 32 .25 ----> (which is 256KB )