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