欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2022-06-05 18:07:40
...

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