from zero to one:oracle新手入门指导之一——数据库表空间知识[天源迪科论坛]

ORACLE数据库名词解释
关键字: Database   收藏    
一：首先对几个有关模式对象进行下说明

二：AUTOEXTNED_CLAUSE相关参数的说明及建议

三：STORAGE主要相关参数的说明及建议

四：建表时一些可能影响系统性能的参数的说明

一：首先对几个有关模式对象进行下说明
数据块(block)：也叫逻辑块或ORACLE块,它是数据库进行I/O的最小单位。一个数据库块对应硬盘上的一个或多个物理块,它决定于建库时所使用的初始化参数DB_BLOCK_SIZE。数据库块的大小也决定了SGA中每一个数据库缓冲区的大小。

范围(extent)：范围是数据库存储空间分配的逻辑单位，它由一组连续的数据块所组成。当数据库的对象（如表、列等）增长时，空间就被分配给范围。

段(segment)： 是由一个或多个范围组成的范围集，它包含一个表空间内特定逻辑存储结构的所有数据。一个段是一个能被建立的逻辑结构，它占有存储，并且能增长，当一个段中的所有空间被用完时，ORACLE就分配它一个新的范围，但是一个段不能跨越表空间。

表空间(tablespace)：一个数据库从逻辑上来说是由一个或多个表空间所组成，表空间是数据库中物理编组的数据仓库。每一个表空间是由段所组成，一个表空间存放一个或多个数据库的物理文件。一个数据库中的数据被逻辑地存储在表空间上。

行链锁：当把一个包含LONG类型的列数据行存储到一个数据库中时，则该行就可能被存储在两个或多个相链接的数据块中，这种现象叫行链锁。

迁移：当数据库块中的行被更新时，其行长可能增长，此时可能会出现块的自由空间被用完，而使行被移至下一数据库块，这种现象叫迁移。不论是行链锁还是迁移都将会影响系统访问数据的效率，因为ORACLE是以数据块为单位进行读取的，所以ORACLE在访问一个行链锁或迁移行时就必须扫描多个数据块，降低I/O性能也就是影响系统性能。

二：AUTOEXTNED_CLAUSE相关参数的说明及建议
AUTOEXTEND

OFF： 当AUTOEXTEND开关设置为OFF时数据文件将不能进行自动扩展。

ON： 当AUTOEXTEND开关设置为ON时数据文件在空间用完时将自动进行扩展。

NEXT：在AUTOEXTEND开关设为ON时，对指定该参数，表示数据文件每次向外扩展的字节数。MAXSIZE：指定允许数据文件向外扩展的最大空间大小。（有了此参数后面的UNLIMITED就不用）。UNLIMITED：表示不限止分配给数据文件的磁盘空间。（有了此参数那MAXSIZE就不用）。

一般建议在建立数据文件时都指定允许其扩展，对于回滚段可设置为UNLIMITED，以防止事务大多时回滚段可以有更大的扩展空间；对于将来在其空间上要建立数据量较大增长较快的表时，应该给它指定个较大的MAXSIZE值。

三：STORAGE主要相关参数的说明及建议
INITIAL：

此参数指定分配给段的第一个范围的字节量,其缺省值相当于５个数据库块，最小值为相当于2个数据库块的大小。最大值要依赖于操作系统。该参数一般设定为该对象可能增长到的最大值稍大30%或更大些到预计可能大小的2倍。

NEXT：

分配给一个段的下一个增量范围的字节量,该参数的值一般设置等于INITIAL的值。MAXEXTENTS是能分配给段的范围总数。对于我们自己建立的表在磁盘空间允许时一般把它设为UNLIMITED（无限制）。

MINEXTENTS

是段建立时分配给它的范围总数，一般为1个范围。回滚段最少应为2个范围，应尽可能再大一些

PCTINCREASE

是一个百分数，每一个增量范围都在最新分配的增量范围上增长这个百分数。缺省值是５０％，但在回滚段中此参数应设置为0。对于一般的表应把它设置为0，但对于一些修改频繁且增长很快的表应给它设置个较大的PCTINCREASE参数值，

OPTIMAL（此参数只对回滚段而言）

它指定在回滚段空闲时系统对回滚段回收的位置，当回滚段比OPTIMAL大时，ORACLE就能重新分配范围，从而维护其大小。以下几点建议可供参考：

1、于长时间运行的更新事务的回滚段，应该分配一个高的OPTIMAL参数值，以避免过多的分配和重新分配。

2、对于长时间运行查询的回滚段，为了保证在查询的同时能用于更新信息的事务，它应该有一个大的OPTIMAL参数值，以免错误快照太老。于短时间运行更新事务和短时间运行查询事务的回滚段，应该有一个较小的OPTIMAL参数值，以便增加回滚段的高速缓冲存储器。ORACLE具有范围动态重新分配的性能，其特点是：一个回滚段可以有一个最佳的尺寸，最佳尺寸是在段建立或修改时指定的。一旦一些活动事务被删除，回滚段将收缩到最佳尺寸（OPTIMAL）。（在指定存储参数时，应当遵循的的方针：即最大的使用连续的自由空间和防止数据文件碎片化。如果不控制范围的分配，则会由于过量的I/O操作和操作系统文件的碎片化而使系统性能下降。减少碎片的办法是使一个段符合INITIAL范围，并且设置PCTINCREASE适合于扩大的增量范围）以上参数在对clusters、 indexes、 rollback segments、 snapshots、 snapshot logs、 tables、tablespaces、partitions等对象进行创建或修改时进行设置。其中INITIAL、MINEXTENTS为在创建时设置，已后就不能对它进行修改；MAXEXTENTS、NEXT、PCTINCREASE、OPTIMAL都可以在对象创建后根据情况对其进行重新设置。

3、表空间（TABLESPACE）与建立在其上的对象在STORAGE参数的关系

在建立表空间（TABLESPACE）时可以设置有关存储参数（STORAGE），那么以后建立在其上的对象都将可以继承这些参数。这些参数只是一个较合理的设置，一般情况下建立在此表空间上的对象可以继承它。但有时可能有些对象较特殊，比如：对于一些修改频繁且数据量较大的对象，就应该给它个较大的NEXT参数值和较大的PCTINCREASE参数值，以避免范围的频繁删除—分配所导致的物理文件的碎片化。在这种情况下就应该在建对象时为此对象设置特有的STORAGE参数值。

四：建表时一些可能影响系统性能的参数的说明
1、自由空间的控制及相关参数的说明

对于插入、更新和删除数据库块中的行来说，可通过指定PCTFREE和PCTUSED空间参数的值来控制自由空间的使用，减少上面所讲到的“行链锁”和“迁移”：

PCTFREE：该参数用于指定在向块中插入新行时应该保留的自由空间的百分数，该保留空间用于修改已包含在该块中的行时使用。比如在建表CREATE TABLE语句中指定该参数为：PCTFREE=20，则在向该表插入新的数据行时，其每个数据块空间最多只能使用80%，一旦达到80%，就不能再向该块插入数据行。留下的20%空间留作此后修改该块中的行时使用。

PCTUSED：该参数是一个限定值，其目的是控制向一个低于PCTUSED所指定的值时，。当通过删除行或更新行（减少了列的存储使用量）而使数据库块的使用百分数ORACLE又许可向该块插入新的数据行。

所以在建表时可以根据表的特性和将来系统对表所要进行的操作对其设置一个合理的值。但两参数之和要小于或等于100（（PCTFREE+PCTUSED）<= 100）。利用PCTFREE和PCTUSED对块的自由空间进行控制：

1． 当选择较低的PCTFREE值时能允许向数据块中插入更多的数据行，使其填的更满，这样可节约数据块，使行移动少；但如果有频繁删除操作时，就需要频繁的空间再组织操作从而增加处理代价。

2．当选择较高的PCTFREE参数时，可为未来的更新操作提供更多的空间；减少空间再组织，从而降低处理代价，减少链锁行，但它需要更多的数据库块来存储数据，而且如果PCTFREE参数值太高，那么装入一个块的数据行就越少，因为ORACLE总是以块为单位读，块的数据少那每次读的数据就少，这样将影响访问数据的效率。

3． 当选择较低的PCTUSED时，可降低处理代价（因为数据库块空闲的机会少），增加未用空间，但容易造成碎片增多。

4． 当选择较高的PCTUSED时，可改善空间使用，但会增加处理代价（因为数据库块经常呈现空闲状态)

２、建表时考虑对INITRANS和MAXTRANS参数的设置

这两个参数是对数据块访问进行控制，合理调整两个参数也将尽量减少用户访问时的冲突，对所有数据的访问最终都是对数据块的访问。

INITRANS：

此参数指定在对象的每一个数据块中为指定数目的事务项预分配的空间，这样当事务进入数据块时系统就不必动态地来为事务项分配空间，其默认值为1。

MAXTRANS：

此参数指定能够并发进入一个数据块的最大事务数目，这也就限制了在任何特定的时间事务项在一个数据块所能占用的空间大小。

说明：对于用户数量较大的情况，就应将这两个参数都设得大些，对于用户较少的情况，相应事务数目也不会太多，可以设得小些，以节省空间，另外要考虑对象的大小，对于大对象，数据分散在许多数据块中，这样对同一数据块访问的可能性较小，则可考虑将这两个参数设小些，而对于较小的表，也许用不了几个数据块当用户多时，对同一块的访问可能性很大，就需要将它们设得大些。

在建表时应分析在将来开发的系统中将对表进行什么样的操作，对于在系统中要对表进行的经常性的查询的字段应为其建立索引，这样在系统中就可以利用此索引进行查询以提高查询的速度。同时在建索引时，应最好将索引存放在不同的表空间中，并且最好是存放在不同的磁盘上，这样在处理数据时就可以充分利用磁盘I/O，使数据和索引在不同的I/O上进行，以提高访问速度。

接下来是实际工作中常常使用的操作
--查看block_size大小
select * from USER_TABLESPACES;
select * from DBA_TABLESPACES;
--查看用户的某个表占据的空间大小
select sum(bytes)/1024/1024||‘ M 字节‘ from user_extents where SEGMENT_NAME = upper(‘&TABLE_NAME‘);  
--查看用户的某个表的段空间大小
select sum(bytes)/1024/1024||‘ M 字节‘ from user_segments where SEGMENT_NAME = upper(‘&TABLE_NAME‘);
--查看存储文件的空间分配详情和临时空间的大小
select tablespace_name, sum(bytes) / 1024 / 1024 / 1024||‘GB‘
  from dba_data_files
 group by tablespace_name
union all
  select tablespace_name, sum(bytes) / 1024 / 1024 / 1024||‘GB‘
    from dba_temp_files
   group by tablespace_name
   order by 2;
--这个是一个比较各个空间的使用情况的查询语句
select tablespace_name,
       sum(bytes) / 1024 / 1024 / 1024 || ‘ GB‘,
       ‘user_segments‘
  from user_segments
 group by tablespace_name
union all
  select tablespace_name,
         sum(bytes) / 1024 / 1024 / 1024 || ‘ GB‘,
         ‘dba_segments‘
    from dba_segments
   group by tablespace_name
  union all
    select tablespace_name,
           sum(bytes) / 1024 / 1024 / 1024 || ‘ GB‘,
           ‘dba_data_files‘
      from dba_data_files
     group by tablespace_name
    union all
      select tablespace_name,
             sum(bytes) / 1024 / 1024 / 1024 || ‘ GB‘,
             ‘dba_temp_files‘
        from dba_temp_files
       group by tablespace_name
      union all
        select tablespace_name,
               sum(bytes) / 1024 / 1024 / 1024 || ‘ GB‘,
               ‘dba_free_space‘
          from dba_free_space
         group by tablespace_name
         order by 1;
        

--如何建立表空间
表空间的建立有如下几种方式
--Create tablespace in Oracle

--The create tablespace statement is used to create a tablespace. --
--Permanent tablespace--
create tablespace ts_something
  logging
  datafile ‘/dbf1/ts_sth.dbf‘
  size 32m
  autoextend on
  next 32m maxsize 2048m
  extent management local;
 
create tablespace data datafile ‘/home/oracle/databases/ora10/data.dbf‘
size                                  10M
autoextend on maxsize                200M
extent management local uniform size  64K;

--Temporary tablespace--
create temporary tablespace temp_mtr
  tempfile ‘/dbf1/mtr_temp01.dbf‘
  size 32m
  autoextend on
  next 32m maxsize 2048m
  extent management local;

Note, a temporary tablespace has tempfiles, not datafiles.
--Undo tablespace--
create undo tablespace ts_undo
  datafile ‘/dbf/undo.dbf‘
  size 100M;

Misc
More than one datafile can be created with a single create tablespace command:
create tablespace ts_sth
  datafile ‘c:\xx\sth_01.dbf‘ size 4M autoextend off,
           ‘c:\xx\sth_02.dbf‘ size 4M autoextend off,
           ‘c:\xx\sth_03.dbf‘ size 4M autoextend off
  logging
  extent management local; 
 
--如何更改表空间的大小
有两种方式：1——增加数据文件 2——修改数据文件大小

alter database datafile ‘/ocsdata/ahocs/OCS_TBS_CUST_02.dbf‘ resize 2047M;
alter tablespace ocs_tbs_cust add datafile ‘/ocsdata/ahocs/OCS_TBS_CUST_02.dbf‘ size 2047M;

其他表空间问题
 用delete   方法删掉表中的数据后，发现数据所占的空间释放不了，怎么办？
 需要你对   high   water   mark (高水位)  有一个认识  
  除了truncate表和drop表外  
  要释放空间，就只能重新建立表（exp/imp等）或者： 
  Alter   table   table_name   move   tablespace   tab_space_name;
  把表移动到其他表空间，这样索引会失效的  
 oracle的数据空间是只能增大，不能减小的
 
 Oracle表空间小知识    
  Oracle表空间，底层以data_file文件形式存放，可以定义一个数据库能打开多少个数据文件，一个表空间可以有1到多个数据文件，一个数据文件只能隶属于一个表空间。
  建表时：

1、指定表空间 2、指定扩展大小

普通表只能建在一个表空间，分区表可以建在多个表空间上。

delete只能删除数据，还占着表空间，用truncate命令，但truncate没有条件。

如何将表移动表空间?
ALTER TABLE TABLE_NAME MOVE TABLESPACE_NAME;

如何将索引移动表空间?
ALTER INDEX INDEX_NAME REBUILD TABLESPACE TABLESPACE_NAME;

查询表空间信息?
SELECT * FROM DBA_DATA_FILES;

如何查看各个表空间占用磁盘情况？
SQL> col tablespace format a20
SQL> select
b.file_id 文件ID号,
b.tablespace_name 表空间名,
b.bytes 字节数,
(b.bytes-sum(nvl(a.bytes,0))) 已使用,
sum(nvl(a.bytes,0)) 剩余空间,
sum(nvl(a.bytes,0))/(b.bytes)*100 剩余百分比
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id;

事务要求的回滚段空间不够，表现为表空间用满（ORA-01560错误），回滚
段扩展到达参数MAXEXTENTS的值（ORA-01628）的解决办法.
向回滚段表空间添加文件或使已有的文件变大；增加MAXEXTENTS的值。

如何监控表空间的I/O 比例？
select B.tablespace_name name,B.file_name "file",A.phyrds pyr,
A.phyblkrd pbr,A.phywrts pyw, A.phyblkwrt pbw
from v$filestat A, dba_data_files B
where A.file# = B.file_id
order by B.tablespace_name;

如何知道表在表空间中的存储情况?
select segment_name,sum(bytes),count(*) ext_quan
from dba_extents
where tablespace_name=‘&tablespace_name‘ and segment_type=‘TABLE‘
group by tablespace_name,segment_name;

如何知道索引在表空间中的存储情况?
select segment_name,count(*)
from dba_extents where segment_type=‘INDEX‘ and owner=‘&owner‘
group by segment_name;

DBA_TABLESPACES
所有表空间的描述.

V$TABLESPACE
控制文件中表空间的信息

V$TEMP_EXTENT_MAP
显示所有临时表空间的每一个单元的状态信息.

V$TEMP_SPACE_HEADER
显示每一个临时表空间中每一个文件的聚集信息,涉及每个空间首部中当前使用多少空间和有多少自由空间.

USER_TABLESPACES
可存取的表空间的描述.

USER_TS_QUOTAS
用户的表空间限额.

DATABASE_PROPERTIES
列出当前缺省的临时表空间的名称.

DBA_FREE_SPACE
列出所有表空间中的空闲分区.

DBA_FREE_SPACE_COALESCED
包含表空间中合并空间的统计数据

DBA_TS_QUOTAS
所有用户的表空间的限额.

DBA_UNDO_EXTENTS
在撤消表空间的每个范围的提交时间.

USER_FREE_SPACE
用户可存取表空间中的空闲范围.

TS_PITR_CHECK
提供可能禁止表空间及时点恢复继续的依赖或约束信息

TS_PITR_OBJECTS_TO_BE_DROPPED
列出作为执行表空间及时点恢复的结果而丢失的所有对象.