当前位置:Gxlcms > mysql > TablespaceandTable的存储属性设置的实验与理解

TablespaceandTable的存储属性设置的实验与理解

时间:2021-07-01 10:21:17 帮助过:14人阅读

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

人气教程排行