`
itspace
  • 浏览: 978798 次
  • 性别: Icon_minigender_1
  • 来自: 杭州
社区版块
存档分类
最新评论

Oracle表空间用统一区大小注意点

OracleSQLCache 
阅读更多
今天在做测试用EXTENT MANAGEMENT LOCAL UNIFORM管理时，遇到看起来比较奇怪的问题，于是研究了一下。
首先创建3个表空间，注意其初始大小和UNIFORM SIZE。
SQL> create tablespace testblock datafile '/oradata/mcstar/testblock01.dbf' SIZE 200M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 2m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> create tablespace testblock2 datafile '/oradata/mcstar/testblock02.dbf' SIZE 10M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.

SQL> create tablespace testblock3 datafile '/oradata/mcstar/testblock03.dbf' SIZE 10M
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.
需要注意的是Oracle创建表空间时，自动扩展为关闭
SQL> select file_id,file_name,blocks-user_blocks,AUTOEXTENSIBLE from  dba_data_files order by 1;

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS AUT
------- ---------------------------------------- ------------------ ---
      1 /oradata/mcstar/system01.dbf                              8 YES
      2 /oradata/mcstar/undotbs01.dbf                             8 NO
      3 /oradata/mcstar/sysaux01.dbf                              8 YES
      4 /oradata/mcstar/users01.dbf                               8 YES
      5 /oradata/mcstar/mcstar01.dbf                              8 NO
      6 /oradata/mcstar/xu01.dbf                                  8 NO
      7 /oradata/mcstar/zhoul01.dbf                               8 YES
      8 /oradata/mcstar/zhoul201.dbf                              8 YES
      9 /oradata/mcstar/testblock01.dbf                         256 NO
     10 /oradata/mcstar/undotbs02.dbf                             8 YES
     11 /oradata/mcstar/testblock02.dbf                         128 NO

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS AUT
------- ---------------------------------------- ------------------ ---
     12 /oradata/mcstar/testblock03.dbf                         256 NO

12 rows selected.
问题来了，可以看到数据文件block和用户可用的block想减，值竟然不一样。
9号文件差值为256*8k/1024/1024=2M,11号文件差值为128*8k/1024/1024=1M,12号文件差值为256*8k/1024/1024=2M。
由前面知道9号文件的uniform size为2M,11号文件的uniform_size为1M，似乎和差值对的上，但12号文件的差值和uniform_size完全对不上
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
  2  order by 1;

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
      1 /oradata/mcstar/system01.dbf                              8
      2 /oradata/mcstar/undotbs01.dbf                             8
      3 /oradata/mcstar/sysaux01.dbf                              8
      4 /oradata/mcstar/users01.dbf                               8
      5 /oradata/mcstar/mcstar01.dbf                              8
      6 /oradata/mcstar/xu01.dbf                                  8
      7 /oradata/mcstar/zhoul01.dbf                               8
      8 /oradata/mcstar/zhoul201.dbf                              8
      9 /oradata/mcstar/testblock01.dbf                         256
     10 /oradata/mcstar/undotbs02.dbf                             8
     11 /oradata/mcstar/testblock02.dbf                         128

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
     12 /oradata/mcstar/testblock03.dbf                         256

12 rows selected.

继续研究，在表空间testblock3上创建表格，并插入一定的数据量。
SQL> create table testblock3  tablespace testblock3 as select * from obj$;

Table created.

SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;

EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9       1024
        
SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>user,tabname=>'TESTBLOCK3',estimate_percent=>100);

PL/SQL procedure successfully completed.

SQL>  select AVG_ROW_LEN,NUM_ROWS,SAMPLE_SIZE from dba_tables where lower(table_name)='testblock3';

AVG_ROW_LEN   NUM_ROWS SAMPLE_SIZE
----------- ---------- -----------
         78      60759       60759

SQL> alter database datafile '/oradata/mcstar/testblock03.dbf' resize 16m;

Database altered.

SQL> begin
  2  for i in (select * from testblock3) loop
  3  insert into testblock3 select * from testblock3 where rownum=1;
  4  commit;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TESTBLOCK3 by 1024 in tablespace
TESTBLOCK3
ORA-06512: at line 3

根据平均行长度为78，数据文件存放业务数据行数可近似计算为((16*1024/8-8)*(8192-819-60))/78=2040*7313/78=191263，但现在目前只存有88252。
两者相差较大。
SQL> select count(*) from testblock3;

  COUNT(*)
----------
     88252

由于目前数据文件只有16M，减去1个数据文件头，7个extent map block，插入数据时并足以进行第二个extent扩展，所以目前表格中只有一个extent，
业务表格实际值，远小于理论值，也就解释的通了。
SQL> select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;

EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9       1024

通过bbed查看，可以看到业务数据好像写到了1033个块，但理论上Oracle业务数据只要写到1032个block即可。
        
BBED> dump block 1032
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1032             Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
06a20000 07040003 7a259c0e 000a0106 83bc0000 01000000 22c00400 4e0e9c0e

BBED> dump block 1033
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1033             Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
06a20000 08040003 7a259c0e 000a0106 c4420000 01b60000 22c00400 4e0e9c0e


BBED> dump block 1034
File: /oradata/mcstar/testblock03.dbf (0)
Block: 1034             Offsets:    0 to  511           Dba:0x00000000
------------------------------------------------------------------------
00a20000 09040000 00000000 00000105 09a30000 00000000 00000000 00000000

于是采用10046进行跟踪，可以看到业务数据存储到block 11032个为止。
SQL> alter system flush buffer_cache;

System altered.


SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';

Session altered.

SQL> select count(*) from testblock3;

  COUNT(*)
----------
     88252

SQL>  ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

WAIT #19: nam='db file sequential read' ela= 8 file#=12 block#=1030 blocks=1 obj#=311330 tim=1273413313089160
WAIT #19: nam='db file sequential read' ela= 8 file#=12 block#=1031 blocks=1 obj#=311330 tim=1273413313089194
WAIT #19: nam='db file sequential read' ela= 7 file#=12 block#=1032 blocks=1 obj#=311330 tim=1273413313089226
通过以上分析。12号文件不能被用户使用的256个块，似乎和extent不能扩展有关。
对dba_extent视图查询可知，Oracle开始extent扩展是从第9号block开始（1号block为datafile_head，2-7号block为extent map）。
由于extent扩展至少需要向数据文件一次性申请1个extent大小（本例12号文件一开始为10M，第二次extent扩展时，由于不能获得8M，所以将浪费2M空间，需要注意的是这2M空间包含了datafile_head和extent map）
为验证上述猜想，继续测试，将数据文件resize至刚好能满足2个extent大小的大小。8M*2*1024*1024+8*8K*1024=16777216+65536=16842752
将数据文件扩展至16842752大小
SQL> alter database datafile '/oradata/mcstar/testblock03.dbf' resize 16842752;

Database altered.
继续插入业务数据
SQL> begin
  2  for i in (select * from testblock3) loop
  3  insert into testblock3 select * from testblock3 where rownum=1;
  4  commit;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> begin
  2  for i in (select * from testblock3) loop
  3  insert into testblock3 select * from testblock3 where rownum=1;
  4  commit;
  5  end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table SYS.TESTBLOCK3 by 1024 in tablespace
TESTBLOCK3
ORA-06512: at line 3

可以看到表格testblock3扩展出第二个分区
SQL>  select EXTENT_ID,BLOCK_ID,BLOCKS from dba_extents where file_id=12;


EXTENT_ID   BLOCK_ID     BLOCKS
---------- ---------- ----------
         0          9       1024
         1       1033       1024
        
SQL>  select count(*) from testblock3;

  COUNT(*)
----------
    204172
采用10046跟踪，可以看到业务数据已经存放至2056为止，该数据文件得到了充分利用。
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 1';

Session altered.

SQL> alter session set db_file_multiblock_read_count=1;

Session altered.

SQL>  alter system flush buffer_cache;

System altered.

SQL>  select count(*) from testblock3;

  COUNT(*)
----------
    204172

SQL>  ALTER SESSION SET EVENTS '10046 trace name context off';

Session altered.

WAIT #1: nam='db file sequential read' ela= 11 file#=12 block#=2054 blocks=1 obj#=311330 tim=1273414177770633
WAIT #1: nam='db file sequential read' ela= 10 file#=12 block#=2055 blocks=1 obj#=311330 tim=1273414177770673
WAIT #1: nam='db file sequential read' ela= 10 file#=12 block#=2056 blocks=1 obj#=311330 tim=1273414177770715
12号数据文件不可用空间也降至8个数据块。
SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
order by 1;
  2 
FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
      1 /oradata/mcstar/system01.dbf                              8
      2 /oradata/mcstar/undotbs01.dbf                             8
      3 /oradata/mcstar/sysaux01.dbf                              8
      4 /oradata/mcstar/users01.dbf                               8
      5 /oradata/mcstar/mcstar01.dbf                              8
      6 /oradata/mcstar/xu01.dbf                                  8
      7 /oradata/mcstar/zhoul01.dbf                               8
      8 /oradata/mcstar/zhoul201.dbf                              8
      9 /oradata/mcstar/testblock01.dbf                         256
     10 /oradata/mcstar/undotbs02.dbf                             8
     11 /oradata/mcstar/testblock02.dbf                         128

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
     12 /oradata/mcstar/testblock03.dbf                           8
    
12 rows selected.

通过以上测试表明，我们创建数据文件设置的大小只要能满足extent扩展的需要，就能最大程度的节省空间。如果不加考虑就设置大小，在数据文件自动扩展关闭的情况下，那最大程度将浪费(uniform size-8*block_size)的空间大小。
SQL> create tablespace testblock4 datafile '/oradata/mcstar/testblock04.dbf' SIZE 16842752
  2  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8m
  3   SEGMENT SPACE MANAGEMENT AUTO;

Tablespace created.


SQL> select file_id,file_name,blocks-user_blocks from dba_data_files
order by 1;
  2 
FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
      1 /oradata/mcstar/system01.dbf                              8
      2 /oradata/mcstar/undotbs01.dbf                             8
      3 /oradata/mcstar/sysaux01.dbf                              8
      4 /oradata/mcstar/users01.dbf                               8
      5 /oradata/mcstar/mcstar01.dbf                              8
      6 /oradata/mcstar/xu01.dbf                                  8
      7 /oradata/mcstar/zhoul01.dbf                               8
      8 /oradata/mcstar/zhoul201.dbf                              8
      9 /oradata/mcstar/testblock01.dbf                         256
     10 /oradata/mcstar/undotbs02.dbf                             8
     11 /oradata/mcstar/testblock02.dbf                         128

FILE_ID FILE_NAME                                BLOCKS-USER_BLOCKS
------- ---------------------------------------- ------------------
     12 /oradata/mcstar/testblock03.dbf                           8
     13 /oradata/mcstar/testblock04.dbf                           8

13 rows selected.
分享到：
| 谈谈Oracle dba_free_space
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    oracle表空间详解

    在 Oracle8i 之后，创建表空间都推荐使用“本地管理表空间”，这种表空间中的分区是一个固定大小的值。语法结构如下： ``` CREATE TABLESPACE 空间名称 DATAFILE '文件名 1' SIZE 数字 M [,'文件名2' SIZE 数字….] ...

    Oracle本地管理表空间

    ### Oracle本地管理表空间知识点详解 #### 一、概述 **本地管理表空间**是Oracle 8i版本引入的一种新的表空间管理模式。相比于之前的**字典管理表空间**，它采用了更加高效的空间管理机制，主要通过在每个数据文件...

    六分钟学会创建Oracle表空间的步骤[转]

    - 创建sales表空间，初始大小800MB，自动扩展，每次增加50MB，最大1GB，采用本地统一管理方式分配空间。 4. `create tablespace sales datafile '/home/app/oracle/oradata/oracle8i/sales01.dbf' size 800M ...

    oracle 表空间管理

    【Oracle 表空间管理】 在Oracle数据库管理系统中，表空间是存储数据的主要逻辑结构，它由一个或多个数据文件组成。传统的表空间管理方式被称为字典管理表空间，这种管理方式依赖于数据库的数据字典来追踪空间的...

    Win764位安装Oracle11g及新建表空间和用户

    在Win7 64位操作系统上安装Oracle Database 11g Release 2（11.2）企业版并新建表空间和用户，涉及以下几个主要步骤： 1. 安装准备： - 首先需要从Oracle官方网站下载适合Win7 64位的Oracle Database 11g Release ...

    IT+ORACLE+各类表空间创建方法

    在这个例子中，我们指定了数据文件的路径和初始大小（500MB），并且设置了区（Extent）的统一大小为128KB。如果不指定“UNIFORM SIZE”，Oracle会使用默认的区大小，通常是64KB。 二、创建临时表空间 临时表空间是...

    查询Oracle数据库表空间和数据文件方法

    火龙果软件工程技术中心 表空间是oracle数据库中最大的逻辑单位与...一、查看Oracle数据库中表空间信息的方法1、查看Oracle数据库中表空间信息的工具方法：使用oracleenterprisemanagerconsole工具，这是oracle的客户

    oracle教程10管理表空间和数据文件.ppt

    本地管理表空间的一个重要特性是统一大小的扩展（Uniform Size），这使得所有段在分配空间时使用相同大小的扩展，简化了管理。创建本地管理表空间的示例： ```sql CREATE TABLESPACE tbs DATAFILE '/u01/oradata/tbs...

    Oracle表空间设置和管理浅析

    1. **EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M**：设置临时表空间的区管理为本地化，且区大小统一为1MB，这样可以提高临时数据的处理效率。 2. **TEMPFILE**和**SIZE**用于定义临时数据文件及其初始大小，`...

    一种Oracle数据库表空间监控方法.pdf

    创建的表通常包含字段，如IP地址、实例名、表空间名、数据文件数量、大小、已使用空间、剩余空间等，以便全面掌握表空间状态。 接下来，需要在每个被监控的数据库上创建一个专门的DBLINK访问用户，并授予相应的权限...

    Oracle数据库空间使用和管理若干方法

    1. 统一区片尺寸：为每个表空间上的段设置相同的区片尺寸，使用INITIAL = NEXT，PCTINCREASE = 0，确保分配的区片是特定参数的倍数。 2. 只在表空间级别指定INITIAL和NEXT参数，避免在创建数据段时指定。 3. 区片...

    六分钟学会创建Oracle表空间的实现步骤

    - `UNIFORM SIZE`指定了所有区的统一大小。 - `AUTOALLOCATE`允许Oracle根据表的大小动态分配区的大小。 示例创建的表空间中，`sales`表空间使用了不同的管理方式和扩展选项。 6. 创建临时表空间： 临时表空间用于...

    oracle lmt_tablespace 存储

    LMT 是 Oracle 提供的一种高级表空间管理方式，它在数据文件级别管理存储空间，通过位图跟踪数据块的使用状态，从而提供更高效的空间管理和更低的系统开销。 ### 1. 什么是 LMT？ LMT 是一种由表空间自身管理其...

    Oracle数据库基础及应用管理表空间和文件PPT学习教案.pptx

    Oracle数据库的基础知识主要涵盖表空间和文件的管理，这些概念对于理解Oracle数据库的逻辑和物理存储结构至关重要。首先，表空间（Tablespace）是Oracle数据库的最高级别的逻辑存储单元，可以理解为数据库在磁盘上的...

    MAPGIS与Oracle数据库空间数据格式的转换

    现代GIS系统更倾向于使用数据库管理系统（DBMS）来统一管理空间数据和属性数据，其中Oracle Spatial因其强大的空间数据处理能力而受到广泛青睐。 #### MAPGIS明码格式详解 - **工作区**：MapGIS数据管理的核心单元...

    oracle 11g图形创建实例、表表空间、dmp导入命令

    Oracle 11g 图形创建实例、表表空间、dmp 导入命令详解 ...本文详细讲解了如何使用 Oracle 11g 图形界面创建实例、表表空间和导入 dmp 文件的步骤，希望能够帮助读者更好地理解和掌握 Oracle 11g 的使用。

    表空间--清华大学计算中心ORACLE培训资料.pptx

    - 使用`CREATE TABLESPACE`语句创建表空间，并指定数据文件的大小和位置。 - `ALTER TABLESPACE`语句可以用于修改表空间的属性，如增加数据文件、更改默认存储参数或使表空间在线或离线。 8. **数据字典管理表...

    Oracle数据库基础及应用第10章管理表空间和文件.ppt

    Oracle数据库的基础知识主要涵盖如何管理和应用表空间以及与之关联的文件。表空间在Oracle数据库中扮演着核心角色，它是数据库逻辑结构的最大单位，物理上对应于磁盘上的数据文件。这一章主要讨论了以下几个方面： ...

    Oracle数据库基础及应用第10章管理表空间和文件.pptx

    在本地管理方式下，Oracle使用位图记录表空间的分配情况，提供了UNIFORM（统一大小的区）和AUTOALLOCATE（自动调整大小的区）两种分配策略。段的管理则分为MANUAL（手动）和AUTO（自动）两种方式，自动方式使用位图...

Magicbox
Global site tag (gtag.js) - Google Analytics