[20171206]最小数据文件.txt

[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