实验目的：为了进一步搞清楚表空间、表的存储参数设定以及限制等，并观察空间分配的结果，观察DATA BLOCKS的数量和类型Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL 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 OracleSYS@PROD>create tablespace test1 datafile "/s01/app/oracle/oradata/PROD/disk1/test1.dbf" size 10M;
Tablespace created. -- 2. mssm & extent allocateSYS@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 uniformSYS@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 AUTOTEST2 8192 65536 1 2147483645 2147483645 LOCAL SYSTEM MANUALTEST3 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 2147483645TEST2 TEST2 10 65536 1048576 1 2147483645TEST1 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 0TEST2 TEST2 10 65536 1048576 1 2147483645 1 0TEST1 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 filefind: obj: 13596-TABLE TEST3:class=8: 4class=9: 1class=4: 1BH （0x757d91e8） file#: 8 rdba: 0x02000080 （8/128） class: 8 ba: 0x75420000BH （0x757e23f8） file#: 8 rdba: 0x02000081 （8/129） class: 8 ba: 0x75516000BH （0x757ebf88） file#: 8 rdba: 0x02000082 （8/130） class: 8 ba: 0x7561c000BH （0x757f6368） file#: 8 rdba: 0x02000083 （8/131） class: 8 ba: 0x75730000BH （0x757f6498） file#: 8 rdba: 0x02000084 （8/132） class: 9 ba: 0x75732000BH （0x757ec0b8） file#: 8 rdba: 0x02000085 （8/133） class: 4 ba: 0x7561e000 class=1: 16BH （0x757e2198） file#: 8 rdba: 0x02000090 （8/144） class: 1 ba: 0x75512000BH （0x757ebd28） file#: 8 rdba: 0x02000091 （8/145） class: 1 ba: 0x75618000BH （0x757f6238） file#: 8 rdba: 0x02000092 （8/146） class: 1 ba: 0x7572e000BH （0x757d8f88） file#: 8 rdba: 0x02000093 （8/147） class: 1 ba: 0x7541c000BH （0x757e2068） file#: 8 rdba: 0x02000094 （8/148） class: 1 ba: 0x75510000BH （0x757ebbf8） file#: 8 rdba: 0x02000095 （8/149） class: 1 ba: 0x75616000BH （0x757f6108） file#: 8 rdba: 0x02000096 （8/150） class: 1 ba: 0x7572c000BH （0x757d8e58） file#: 8 rdba: 0x02000097 （8/151） class: 1 ba: 0x7541a000BH （0x757e1f38） file#: 8 rdba: 0x02000098 （8/152） class: 1 ba: 0x7550e000BH （0x757ebac8） file#: 8 rdba: 0x02000099 （8/153） class: 1 ba: 0x75614000BH （0x757f5fd8） file#: 8 rdba: 0x0200009a （8/154） class: 1 ba: 0x7572a000BH （0x757d8d28） file#: 8 rdba: 0x0200009b （8/155） class: 1 ba: 0x75418000BH （0x757e1e08） file#: 8 rdba: 0x0200009c （8/156） class: 1 ba: 0x7550c000BH （0x757eb998） file#: 8 rdba: 0x0200009d （8/157） class: 1 ba: 0x75612000BH （0x757f5ea8） file#: 8 rdba: 0x0200009e （8/158） class: 1 ba: 0x75728000BH （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 0TEST5 TEST5 10 65536 1048576 1 2147483645 5 0TEST4 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 0T777 TEST7 10 1048576 1048576 1 2147483645 60 0T888 TEST8 10 524288 524288 1 2147483645 26 0TEST3 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 AUTOTEST7 8192 1048576 1048576 1 2147483645 2147483645 LOCAL UNIFORM AUTOTEST8 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 AUTOTEST6 8192 2097152 2097152 1 2147483645 2147483645 LOCAL UNIFORM AUTOTEST7 8192 1048576 1048576 1 2147483645 2147483645 LOCAL UNIFORM AUTOTEST8 8192 524288 524288 1 2147483645 2147483645 LOCAL UNIFORM AUTO
-- 自己看吧更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12RAC重用裸设备Windows下的Oracle数据库移植到Linux下相关资讯 Tablespace and Table 本文评论 查看全部评论 （0）
易网时代-编程资源站