当前位置:  数据库>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');
 


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

 


-- 自己看吧


    
 
 

您可能感兴趣的文章:

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












  • 相关文章推荐
  • Oracle tablespace表空间调整


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,