Tablespace and Table 的存储属性设置的实验与理解
Tablespace and Table 的存储属性设置的实验与理解
实验目的：为了进一步搞清楚表空间、表的存储参数设定以及限制等，并观察空间分配的结果，观察DATA BLOCKS的数量和类型
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
--create some types of tablespaces below and watch what would happen to initial and extend the storage space;
-- 1. totally created as default setup by Oracle
SYS@PROD>create tablespace test1 datafile '/s01/app/oracle/oradata/PROD/disk1/test1.dbf' size 10M;
Tablespace created.
-- 2. mssm & extent allocate
SYS@PROD>create tablespace test2 datafile '/s01/app/oracle/oradata/PROD/disk1/test2.dbf' size 10M autoextend on next 2M
2 extent management local
3 segment space management manual;
Tablespace created.
-- 3. assm & extent uniform
SYS@PROD>create tablespace test3 datafile '/s01/app/oracle/oradata/PROD/disk1/test3.dbf' size 10M autoextend on next 2M
2 extent management local uniform size 512k
3 segment space management auto;
Tablespace created.
ZN@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name in ('TEST1','TEST2','TEST3');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST1 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM AUTO
TEST2 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM MANUAL
TEST3 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
ZN@PROD>show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
可以猜想，当向表test1、test2、test3分别插入一条记录时，每个表的segment均申请one extent的空间，其中test1、test2分配了8 blocks=65536(64k)，下面测试一下test3是不是会分配64 blocks=524288(512k)呢？
ZN@PROD>create table test1(X INT) tablespace test1;
Table created.
ZN@PROD>create table test2(X INT) tablespace test2;
Table created.
ZN@PROD>create table test3(X INT) tablespace test3;
Table created.
ZN@PROD>insert into test1 values(1);
1 row created.
ZN@PROD>insert into test2 values(2);
1 row created.
ZN@PROD>insert into test3 values(3);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('TEST1','TEST2','TEST3');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST3 TEST3 10 524288 524288 1 2147483645
TEST2 TEST2 10 65536 1048576 1 2147483645
TEST1 TEST1 10 65536 1048576 1 2147483645
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST1');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST2');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST3');
PL/SQL procedure successfully completed.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('TEST1','TEST2','TEST3');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST3 TEST3 10 524288 524288 1 2147483645 26 0
TEST2 TEST2 10 65536 1048576 1 2147483645 1 0
TEST1 TEST1 10 65536 1048576 1 2147483645 5 0
-- 从上面的查询看到，TEST1初始分配了5个DATA BLOCKS，与之前的实验结果吻合，TEST2初始分配了1个DATA BLOCK，也与之前的实验结果吻合。
ZN@PROD>ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME BUFFERS LEVEL 1';
Session altered.
ZN@PROD>SELECT * FROM V$DIAG_INFO where name ='Default Trace File';
INST_ID NAME VALUE
---------- ------------------ ------------------------------------------------------------
1 Default Trace File /s01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_ora_3108.trc
SYS@PROD>select obj#,owner#,name from obj$ where name in ('TEST1','TEST2','TEST3');
OBJ# OWNER# NAME
---------- ---------- ------------------
13594 32 TEST1
13595 32 TEST2
13596 32 TEST3
SYS@PROD>select segment_name,header_block,header_file,blocks from dba_segments where segment_name='TEST2';
SEGME HEADER_BLOCK HEADER_FILE BLOCKS
----- ------------ ----------- ----------
TEST2 128 7 8
SYS@PROD>select segment_name,header_block,header_file,blocks from dba_segments where segment_name='TEST1';
SEGME HEADER_BLOCK HEADER_FILE BLOCKS
----- ------------ ----------- ----------
TEST1 130 6 8
--下面重点看一下TEST3 ，看看DUMP出来的BH数量中，类型为DATA BLOCK的是否有26个
SYS@PROD>select segment_name,header_block,header_file,blocks from dba_segments where segment_name='TEST3';
SEGME HEADER_BLOCK HEADER_FILE BLOCKS
----- ------------ ----------- ----------
TEST3 133 8 64
vi trace file
find: obj: 13596-TABLE TEST3:
class=8: 4
class=9: 1
class=4: 1
BH (0x757d91e8) file#: 8 rdba: 0x02000080 (8/128) class: 8 ba: 0x75420000
BH (0x757e23f8) file#: 8 rdba: 0x02000081 (8/129) class: 8 ba: 0x75516000
BH (0x757ebf88) file#: 8 rdba: 0x02000082 (8/130) class: 8 ba: 0x7561c000
BH (0x757f6368) file#: 8 rdba: 0x02000083 (8/131) class: 8 ba: 0x75730000
BH (0x757f6498) file#: 8 rdba: 0x02000084 (8/132) class: 9 ba: 0x75732000
BH (0x757ec0b8) file#: 8 rdba: 0x02000085 (8/133) class: 4 ba: 0x7561e000
class=1: 16
BH (0x757e2198) file#: 8 rdba: 0x02000090 (8/144) class: 1 ba: 0x75512000
BH (0x757ebd28) file#: 8 rdba: 0x02000091 (8/145) class: 1 ba: 0x75618000
BH (0x757f6238) file#: 8 rdba: 0x02000092 (8/146) class: 1 ba: 0x7572e000
BH (0x757d8f88) file#: 8 rdba: 0x02000093 (8/147) class: 1 ba: 0x7541c000
BH (0x757e2068) file#: 8 rdba: 0x02000094 (8/148) class: 1 ba: 0x75510000
BH (0x757ebbf8) file#: 8 rdba: 0x02000095 (8/149) class: 1 ba: 0x75616000
BH (0x757f6108) file#: 8 rdba: 0x02000096 (8/150) class: 1 ba: 0x7572c000
BH (0x757d8e58) file#: 8 rdba: 0x02000097 (8/151) class: 1 ba: 0x7541a000
BH (0x757e1f38) file#: 8 rdba: 0x02000098 (8/152) class: 1 ba: 0x7550e000
BH (0x757ebac8) file#: 8 rdba: 0x02000099 (8/153) class: 1 ba: 0x75614000
BH (0x757f5fd8) file#: 8 rdba: 0x0200009a (8/154) class: 1 ba: 0x7572a000
BH (0x757d8d28) file#: 8 rdba: 0x0200009b (8/155) class: 1 ba: 0x75418000
BH (0x757e1e08) file#: 8 rdba: 0x0200009c (8/156) class: 1 ba: 0x7550c000
BH (0x757eb998) file#: 8 rdba: 0x0200009d (8/157) class: 1 ba: 0x75612000
BH (0x757f5ea8) file#: 8 rdba: 0x0200009e (8/158) class: 1 ba: 0x75728000
BH (0x757d8bf8) file#: 8 rdba: 0x0200009f (8/159) class: 1 ba: 0x75416000
-- TEST3的数据块一共找到22个，其中DATA BLOCK有16个，比查询出来的26个还少了10个，为什么？ ？
-- 接下来看一下8号文件，即TEST3表空间，还分配了哪些块
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST3 TEST3 10 524288 524288 1 2147483645 26 0
还发现其他的8号文件的块：
-- class=13:'file header block'
BH (0x757d9318) file#: 8 rdba: 0x02000002 (8/2) class: 13 ba: 0x75422000
-- class=12:'bitmap index block'
BH (0x757e2528) file#: 8 rdba: 0x02000003 (8/3) class: 12 ba: 0x75518000
-- 下面测试了其他几种指定表空间、表的STORAGE属性的写法，看看对表空间、表的创建有什么影响。
SYS@PROD>create tablespace test4 datafile '/s01/app/oracle/oradata/PROD/disk1/test4.dbf' size 10M
2 default storage(initial 1024k next 512k minextents 2);
Tablespace created.
SYS@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name in ('TEST4');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST4 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM AUTO
SYS@PROD>create tablespace test5 datafile '/s01/app/oracle/oradata/PROD/disk1/test5.dbf' size 10M
2 default storage(initial 2048k next 2048k minextents 5);
Tablespace created.
SYS@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name in ('TEST5');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST5 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM AUTO
SYS@PROD>create tablespace test6 datafile '/s01/app/oracle/oradata/PROD/disk1/test6.dbf' size 10M uniform size 2M;
Tablespace created.
SYS@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name in ('TEST6');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST6 8192 2097152 2097152 1 2147483645 2147483645 LOCAL UNIFORM AUTO
--可以看到上面用STORAGE指定INITIAL的表空间TEST4，TEST5均没生效，依旧使用的是默认的64k作为INITIAL_EXTENT参数，且NEXT_INITIAL为空。只有表空间TEST6使用了UNIFORM方式，才看到INITIAL_EXTENT, NEXT_INITIAL等于了指定的2M。
ZN@PROD>create table test4(x int) tablespace test4;
Table created.
ZN@PROD>create table test5(x int) tablespace test5;
Table created.
ZN@PROD>create table test6(x int) tablespace test6;
Table created.
ZN@PROD>insert into test4 values(4);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>insert into test5 values(5);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>insert into test6 values(6);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST4');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST5');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','TEST6');
PL/SQL procedure successfully completed.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('TEST4','TEST5','TEST6');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
TEST6 TEST6 10 2097152 2097152 1 2147483645 58 0
TEST5 TEST5 10 65536 1048576 1 2147483645 5 0
TEST4 TEST4 10 65536 1048576 1 2147483645 5 0
ZN@PROD>create table t66 (x int) tablespace test6 storage(initial 32k next 32k minextents 2);
Table created.
ZN@PROD>insert into t66 values(66);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T66');
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('T66');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
T66 TEST6 10 65536 32768 1 2147483645 58 0
-- 其实我们发现，上面所创建的一系列表空间和表，除了TEST6表的BLOCKS数量=58外，其他属性都还基本可以理解。从表T66可以看出，建表语句中指定的STORAGE会覆盖所在表空间的STORAGE属性。INITIAL_EXTENT的值等于STORAGE中指定的INITIAL*MINEXTENTS。
至于TEST6表的BLOCKS数量为什么=58，和之前的TEST4的26又有不同，可能与INITIAL参数的指定有关。
下面还可以继续做一个实验，修改表的INITIAL参数，看看BLOCKS有什么变化：
ZN@PROD>create table t666 (x int) tablespace test6 storage(initial 64k next 64k minextents 2);
Table created.
ZN@PROD>insert into t666 values(666);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T666');
PL/SQL procedure successfully completed.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('T666');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
T666 TEST6 10 131072 65536 1 2147483645 58 0
-- 发现BLOCKS还是等于58. 说明BLOCS数量与表的STORAGE属性无关，与表空间的分配属性有关。
-- 再建两个表空间，一个的unifor size=512k，一个=1M，和表空间TEST3， TEST6做一个横向比较：
ZN@PROD>create tablespace test7 datafile '/s01/app/oracle/oradata/PROD/disk1/test7.dbf' size 10M uniform size 1M;
Tablespace created.
ZN@PROD>create tablespace test8 datafile '/s01/app/oracle/oradata/PROD/disk1/test8.dbf' size 10M uniform size 512k;
Tablespace created.
ZN@PROD>create table t777(X INT) tablespace test7;
Table created.
ZN@PROD>create table t888(X INT) tablespace test8;
Table created.
ZN@PROD>insert into t777 values(777);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>insert into t888 values(888);
1 row created.
ZN@PROD>commit;
Commit complete.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T777');
PL/SQL procedure successfully completed.
ZN@PROD>exec dbms_stats.gather_table_stats('ZN','T888');
PL/SQL procedure successfully completed.
ZN@PROD>select table_name, TABLESPACE_NAME , PCT_FREE ,INITIAL_EXTENT ,NEXT_EXTENT ,MIN_EXTENTS,MAX_EXTENTS ,BLOCKS ,EMPTY_BLOCKS from user_tables where table_name in ('T777','T888','TEST3','T666');
TABLE TABLESP PCT_FREE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS BLOCKS EMPTY_BLOCKS
----- ------- ---------- -------------- ----------- ----------- ----------- ---------- ------------
T666 TEST6 10 131072 65536 1 2147483645 58 0
T777 TEST7 10 1048576 1048576 1 2147483645 60 0
T888 TEST8 10 524288 524288 1 2147483645 26 0
TEST3 TEST3 10 524288 524288 1 2147483645 26 0
ZN@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name in ('TEST7','TEST8','TEST3');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST3 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST7 8192 1048576 1048576 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST8 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
ZN@PROD>select tablespace_name,block_size,initial_extent, next_extent, min_extents,max_extents,max_size,extent_management,allocation_type,segment_space_management from user_tablespaces where tablespace_name in ('TEST6','TEST7','TEST8','TEST3');
TABLESP BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS MAX_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------- ---------- -------------- ----------- ----------- ----------- ---------- ---------- --------- ------
TEST3 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST6 8192 2097152 2097152 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST7 8192 1048576 1048576 1 2147483645 2147483645 LOCAL UNIFORM AUTO
TEST8 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
-- 自己看吧
评论暂时关闭