Tablespace and Table 的存储属性设置的实验与理解__PROD_2147483645_INITIAL_EXTENT_created_class_

当前位置: 数据库>oracle

Tablespace and Table 的存储属性设置的实验与理解

来源: 互联网发布时间：2017-06-03

本文导语: 实验目的：为了进一步搞清楚表空间、表的存储参数设定以及限制等，并观察空间分配的结果，观察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 Produc...

实验目的：为了进一步搞清楚表空间、表的存储参数设定以及限制等，并观察空间分配的结果，观察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');

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表空间，还分配了哪些块

还发现其他的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.

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.

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');

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');

-- 其实我们发现，上面所创建的一系列表空间和表，除了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');

-- 发现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');

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

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

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

-- 自己看吧

您可能感兴趣的文章:

本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。