[20171206]最小数据文件.txt
--//曾经写过一篇关于[20150113]关于oracle的存储结构.txt的文章,链接http://blog.itpub.net/267265/viewspace-1400603/
--//里面提到如果建立的数据文件如果SEGMENT SPACE MANAGEMENT AUTO (8K数据块),最小文件是88k.实际占用大小96K.
--//是否可以建立更小的数据文件呢?
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立测试脚本:
$ cat guess_keep_128.sql
drop tablespace t01 including contents and datafiles;
CREATE TABLESPACE T01 DATAFILE
'/mnt/ramdisk/book/T01.dbf' SIZE &1 k reuse AUTOEXTEND OFF
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;
create table emp2 tablespace t01 as select * from emp where rownum<=1;
column PARTITION_NAME noprint
select * from dba_extents where owner=user and segment_name='EMP2';
SCOTT@book> @ guess_keep_128.sql 80
drop tablespace t01 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'T01' does not exist
old 2: '/mnt/ramdisk/book/T01.dbf' SIZE &1 k reuse AUTOEXTEND OFF
new 2: '/mnt/ramdisk/book/T01.dbf' SIZE 80 k reuse AUTOEXTEND OFF
CREATE TABLESPACE T01 DATAFILE
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
create table emp2 tablespace t01 as select * from emp where rownum<=1
*
ERROR at line 1:
ORA-00959: tablespace 'T01' does not exist
no rows selected
--//如果建立88K
drop tablespace t01 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'T01' does not exist
old 2: '/mnt/ramdisk/book/T01.dbf' SIZE &1 k reuse AUTOEXTEND OFF
new 2: '/mnt/ramdisk/book/T01.dbf' SIZE 88 k reuse AUTOEXTEND OFF
Tablespace created.
Table created.
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMP2 TABLE T01 0 7 4 65536 8 7
--//也就是oracle前面0-3块保留.
--//第0块OS块,第1块文件头,第2块是位图头,第3块是位图区.
SCOTT@book> alter system dump datafile 7 block min 2 block max 3;
System altered.
$ grep "^frmt:" /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_31988.trc
frmt: 0x02 chkval: 0x8daf type: 0x1d=KTFB Bitmapped File Space Header
frmt: 0x02 chkval: 0x5956 type: 0x1e=KTFB Bitmapped File Space Bitmap
3.如果使用SEGMENT SPACE MANAGEMENT MANUAL呢?
--//不使用 EXTENT MANAGEMENT LOCAL AUTOALLOCATE,因为这样1个extent=64K.
$ cat guess_size.sql
drop tablespace t02 including contents and datafiles;
CREATE TABLESPACE T02 DATAFILE
'/mnt/ramdisk/book/T02.dbf' SIZE & K AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table emp2 tablespace t02 as select * from emp where rownum<=1;
column PARTITION_NAME noprint
select * from dba_extents where owner=user and segment_name='EMP2';
--//注:实际上根本不能建立UNIFORM SIZE 8K的数据文件,最少2块.这里仅仅不报错罢了.
--//先猜测看看,第0块OS块,第1块文件头,第2块是位图头,第3块是位图区.数据至少2块.这样建立的数据文件最少5块.
--//也就是40K.
SCOTT@book> @ guess_size.sql 40
drop tablespace t02 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'T02' does not exist
old 2: '/mnt/ramdisk/book/T02.dbf' SIZE &1 K AUTOEXTEND OFF
new 2: '/mnt/ramdisk/book/T02.dbf' SIZE 40 K AUTOEXTEND OFF
Tablespace created.
Table created.
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMP2 TABLE T02 0 8 4 16384 2 8
--//可以发现占2块.也就是每个extents最小16K.
$ ls -lh /mnt/ramdisk/book/T02.dbf
-rw-r----- 1 oracle oinstall 48K 2017-12-06 11:16:10 /mnt/ramdisk/book/T02.dbf
--//最小占48K.
--//再小一点到32K看看.
SCOTT@book> @ guess_size.sql 32
Tablespace dropped.
old 2: '/mnt/ramdisk/book/T02.dbf' SIZE &1 K AUTOEXTEND OFF
new 2: '/mnt/ramdisk/book/T02.dbf' SIZE 32 K AUTOEXTEND OFF
CREATE TABLESPACE T02 DATAFILE
*
ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
create table emp2 tablespace t02 as select * from emp where rownum<=1
*
ERROR at line 1:
ORA-00959: tablespace 'T02' does not exist
no rows selected
--//对于数据文件8K块大小,能建立的最小数据文件是40K,包括OS块头,48K.
--//当然如果数据块大小2K,这样建立的最小的数据文件就是10K.
alter system set db_2k_cache_size=1M scope=spfile;
--//重启..
$ cat guess_size1.sql
drop tablespace t03 including contents and datafiles;
CREATE TABLESPACE T03 DATAFILE
'/mnt/ramdisk/book/T03.dbf' SIZE &1 K AUTOEXTEND OFF
LOGGING
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2K
BLOCKSIZE 2K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON;
create table emp2 tablespace t03 as select * from emp where rownum<=1;
column PARTITION_NAME noprint
select * from dba_extents where owner=user and segment_name='EMP2';
SCOTT@book> @ guess_size1.sql 10
drop tablespace t03 including contents and datafiles
*
ERROR at line 1:
ORA-00959: tablespace 'T03' does not exist
old 2: '/mnt/ramdisk/book/T03.dbf' SIZE &1 K AUTOEXTEND OFF
new 2: '/mnt/ramdisk/book/T03.dbf' SIZE 10 K AUTOEXTEND OFF
Tablespace created.
Table created.
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BYTES BLOCKS RELATIVE_FNO
------ -------------------- ------------------ ------------------------------ ---------- ---------- ---------- ---------- ---------- ------------
SCOTT EMP2 TABLE T03 0 8 4 4096 2 8
$ ls -l /mnt/ramdisk/book/T03.dbf
-rw-r----- 1 oracle oinstall 12288 2017-12-06 11:25:45 /mnt/ramdisk/book/T03.dbf