[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