Tablespace and Table 的存储属性设置的实验与理解
为了进一步搞清楚表空间、表的存储参数设定以及限制等，并观察空间分配的结果，观察DATA BLOCKS的数量和类型
实验目的：为了进一步搞清楚表空间、表的存储参数设定以及限制等，并观察空间分配的结果，观察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