Yesterday, a colleague asked some questions about Oracle Tablespace data file Self-growth, in the process of the way to organize, and other colleagues have the same questions can be directly consulted.
Experimental content:
- Creates a mytest table space that does not turn on self-growth by default.
- Add a data file to the MyTest table space, open self-growth, but do not set the size to grow from.
- Add a data file to the MyTest table space, turn on self-growth, and specify the size from which to grow.
The procedure is logged as follows: (the excess results have been filtered)
[Email protected]>CREATE tablespace MYTEST datafile '/ocp_data02/datafiles/mytest01. DBF ' SIZE 10M autoextend OFF;
Tablespace created.
[Email protected]>Select file_name,tablespace_name,bytes,autoextensible,increment_by from Dba_data_files;
file_name tablespace_name BYTES AUT increment_by
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ----------------------------------
/ocp_data02/datafiles/mytest01. DBF MYTEST 10485760 NO 0
[Email protected]>ALTER tablespace MYTEST ADD datafile '/ocp_data02/datafiles/mytest02. DBF ' SIZE 10M autoextend on;
Tablespace altered.
[Email protected]>ALTER tablespace MYTEST ADD datafile '/ocp_data02/datafiles/mytest03. DBF ' SIZE 10M autoextend on NEXT 10M;
Tablespace altered.
[Email protected]>Select file_name,tablespace_name,bytes,autoextensible,increment_by from Dba_data_files;
file_name tablespace_name BYTES AUT increment_by
--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- -----------------------------------
/ocp_data02/datafiles/mytest01. DBF MYTEST 10485760 NO 0
/ocp_data02/datafiles/mytest02. DBF MYTEST 10485760 YES 1
/ocp_data02/datafiles/mytest03. DBF MYTEST 10485760 YES 1280
?
Conclusion:
- The increment_by is 0 when it does not turn on self-growth.
- Turn on self-growth, do not set the growth size of the case, the increment_by system default to 1 block size, that is: 8k*1block=8k
- Turn on self-growth, set the growth SIZE of the case, Increment_by is size/block_size, that is: 1024k*10m/8k=1280.
Some default options for Oracle tablespace data file self-growth