oracle 如何增加表空间

软件环境：
1、Windows NT4.0+ORACLE 8.0.4
2、ORACLE安装路径为：C:\ORANT

实现方法：

可以用以下两种方法：

1、为这个表空间增加一个数据文件
SQL> alter tablespace 表空间名 add datafile '/u1/oradata/userdata_002.ora' size 50m;　　--Unix中
SQL> alter tablespace 表空间名 add datafile 'c:\oradata\userdata_002.ora' size 50m;　　 --Windows NT中

2、重新调整数据文件的大小
SQL> alter database datafile '/u1/oradata/userdata_001.ora' resize 50M; 　--Unix中
SQL> alter database datafile 'c:\oradata\userdata_002.ora' resize 50M;　　--Windows NT中

Oracle数据库的空间管理技巧

---- 在Oracle数据库中，DBA可以通过观测一定的表或视图来了解当前空间的使用状况，进而作出可能的调整决定。

---- 一.表空间的自由空间

---- 通过对表空间的自由空间的观察，可用来判断分配给某个表空间的空间是太多还是不够。请看下列的语句

SQL >   select a.file_id "FileNo",a.tablespace_name
"Tablespace_name",
  2  a.bytes "Bytes",a.bytes-sum(nvl(b.bytes,0)) "Used",
  3  sum(nvl(b.bytes,0)) "Free",
  4 sum(nvl(b.bytes,0))/a.bytes*100  "%free"
  5 from dba_data_files a, dba_free_space b
  6 where a.file_id=b.file_id(+)  
  7 group by a.tablespace_name ,
  8 a.file_id,a.bytes order by a.tablespace_name;

File  Tablespace
No _nameBytes  Used  Free %free
------ ---------  -------- --------- --------- ---------
11IDX_JF  .146E+09 849305600 1.297E+09 60.431806
9 JFSJTS 2.146E+09 1.803E+09 343793664 16.016961
10JFSJTS 2.146E+09 1.359E+09 787431424 36.685546
2 RBS523239424 359800832 163438592 31.235909
12RBS1.610E+09 1.606E+09   3104768 .19289495
8 RBSJF  3.220E+09 2.716E+09 504356864 15.662396
7 SFGLTS 2.146E+09 1.228E+09 918159360 42.776014
6 SFSJTS 2.146E+09 1.526E+09 620093440 28.889457
1 SYSTEM 523239424  59924480 463314944 88.547407
3 TEMP   523239424294912 522944512 99.943637
4 TOOLS  15728640   12582912   314572820
5 USERS  7340032  81927331840  99.888393

12 rows selected.

---- 可以看出，在FileNo为12的表空间RBS中，只有0.19%的分配空间未被使用，这个比例太小了，而在SYSTEM及TEMP等表空间中，高达80%以上的空间未被利用，对于生产型数据库，这个表空间的设置有些偏高。

---- 关于自由空间的管理，有下面的一些建议：

利用Export及Import命令卸出和装入表空间可以释放大量的空间，从而缓解增加另外的数据文件的要求。

如果包含具有高插入(insert)和更新(update)活动的表的表空间中自由空间的比重下降到了15%以下，要为此表空间增加更多的空间。

对于一个基本是静态表数据的表空间，如果有多于20%的自由空间，则可以考虑减少分配给它的文件空间量。

减少SYSTEM表空间的空间量比较困难，因为那要重建数据库。
---- 二 表及索引的扩展
---- A.为了防止表或索引被过分扩展，及时实现对数据库的调整，用户应当经常对有关对象进行观察。

---- 我们可以认为，扩展区域大于5个的表或索引为过分扩展(overextended)。请看下面的语句：

SQL > select substr(segment_name,1,15)
Segment_name,segment_type,
  2 substr(tablespace_name,1,10)
Tablepace_name,extents,Max_extents
  3from dba_segments
  4where extents >5 and owner='JFCL'
  5order by segment_name;

SEGMENT_NAMESEGMENT  TABLEPACE_  
EXTENTS  MAX_EXTENTS
_TYPE
-------------- --------- ----------
CHHDFYB TABLE  JFSJTS   11121
CHHDFYB_DHHMINDEX  JFSJTS9121
DJHZFYB_BF  TABLE  JFSJTS   17500
DJHZFYB_DJHMINDEX  IDX_JF6500
DJHZFYB_JZHMINDEX  IDX_JF7500
GSMFYB  TABLE  JFSJTS   11121
JFDHTABLE  JFSJTS   14500
JFDH_DHHM   INDEX  IDX_JF   61500
JFDH_JZHM   INDEX  IDX_JF   64500
XYKFYB  TABLE  JFSJTS7121
YHDATABLE  JFSJTS6500
YHDA_BAKTABLE  JFSJTS6500
YHHZFYB_12  TABLE  JFSJTS   10500

13 rows selected.

---- 通过观察， DBA可以及时发现问题并进行相应的处理。
---- 我们可以利用export卸出表，然后删除表，再利用import命令将表装入，这样，可以将不连续的区域合并成一个连续的空间。

---- B.如果用户希望对表的空间设置进行优化，例如，需要改变表EMP的initial参数，可以采用下面的方法：

---- 1.在将EMP表卸出并删除后执行imp命令时使用indexfile参数：

---- imp userid=scott/tiger file=emp.dmp indexfile=emp.sql Oracle把表和索引的创建信息写到指定的文件，而不是把数据写回。

---- 2.打开emp.sql文件：

REM  CREATE TABLE "SCOTT"."EMP" ("EMPNO"
NUMBER(4, 0), "ENAME"
REM  VARCHAR2(10), "JOB" VARCHAR2(9),
"MGR" NUMBER(4, 0), "HIREDATE" DATE,
REM  "SAL" NUMBER(7, 2), "COMM" NUMBER
(7, 2), "DEPTNO" NUMBER(2, 0))
REM  PCTFREE 10 PCTUSED 40 INITRANS 1
MAXTRANS 255 LOGGING STORAGE(INITIAL
REM  10240 NEXT 10240 MINEXTENTS 1 MAXEXTENTS
121 PCTINCREASE 50 FREELISTS
REM  1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USER_DATA" ;
REM  ... 14 rows


---- 对它进行编辑，去除"REM"等信息，找到Initial参数，根据需要改变它。
---- 3.在SQL*plus中执行emp.sql。

---- 4.装入数据：

---- imp userid=scott/tiger ignore=y file=emp.dmp

---- 需要注意的是，ignore参数必须设为Y.

---- C.可以用下面的语句来观察表或索引距离达到最大扩展的状况，“UNUSE”为距离达到最大扩展的值，在User_extents表中，extent_id是从0开始记述数的。

SQL >select a.table_name "TABLE_NAME",max
(a.max_extents) "MAXEXTENTS" ,
  2   max(b.extent_id)+1 "IN USE", MAX
(a.max_extents)-(max(b.extent_id)+1) "UNUSE"
  3   from user_tables a, user_extents b
  4where a.table_name=b.segment_name
  5  group by a.table_name  ORDER BY 4;

TABLE_NAME  MAXEXTENTS  IN USEUNUSE
---------- ----------- -------- ---------
YZPHB  98  1   97
SHJYB  121 1  120
SHFYB  121 1  120
RCHDB  121 1  120
SJTXDZB121 1  120
SJTXDAB121 1  120
CHYHB  121 1  120
JFDH   50014  486
8 rows selected.

---- 如果“UNUSE"小到一定的程度，我们就应该加以关注，进行适当的调整处理。
---- 三 关于连续空间

---- 可以用下面的语句来查看数据库中的自由空间：

SQL >  select * from dba_free_space
where tablespace_name='SFSJTS'
  2  order by block_id;

TABLESPACE   FILE_ID BLOCK_ID  BYTESBLOCKS
_NAME
----------- --------- --------------
SFSJTS  6 133455  1064960 130
SFSJTS  6 133719  1032192 126
SFSJTS  6 133845  1064960 130
SFSJTS  6 135275  1064960 130
SFSJTS  6 135721   606208  74
SFSJTS  6 139877   901120 110
SFSJTS  6 143497   737280  90
SFSJTS  6 220248   737280  90
SFSJTS  6 246228   491520  60
SFSJTS  6 261804  1064960 130

10 rows selected.

---- 我们可以通过命令的结果来估计相邻自由空间的真正数量。对每一行，用起始快的id(BLOCK_ID)加上自由块(BLOCKS)的数量，如果其和与下一行的块id(BLOCK_ID)相等，则此两行是连续的。如上例第二行和第三行，133719+126=133845，而1338456+130!=135275,所以从block_id为133719开始，有126+130=256个block的连续空间。
---- 在Oracle数据库的后台，系统监视器(SMON)周期性地合并自由空间相邻的块，以得到更大的连续块。而DBA可以用SQL命令来完成这个工作：

---- alter tablespace tablespace_name coalesce;

---- Oracle空间管理对数据库的工作性能有重要影响，其管理方法值得我们认真摸索研究

本文转载自：http://www.itpub.net/thread-10191-1-1.html

Oracle数据库碎片整理

---- 我们知道，Oracle作为一种大型数据库，广泛应用于金融、邮电、电力、民航等数据吞吐量巨大，计算机网络广泛普及的重要部门。对于系统管理员来讲，如何保证网络稳定运行，如何提高数据库性能，使其更加安全高效，就显得尤为重要。作为影响数据库性能的一大因素--数据库碎片，应当引起DBA的足够重视，及时发现并整理碎片乃是DBA一项基本维护内容。

---- 1、碎片是如何产生的

---- 当生成一个数据库时，它会分成称为表空间（Tablespace）的多个逻辑段（Segment），如系统（System）表空间,临时（Temporary）表空间等。一个表空间可以包含多个数据范围（Extent）和一个或多个自由范围块，即自由空间（Free Space）。

---- 表空间、段、范围、自由空间的逻辑关系如下：



---- 当表空间中生成一个段时，将从表空间有效自由空间中为这个段的初始范围分配空间。在这些初始范围充满数据时，段会请求增加另一个范围。这样的扩展过程会一直继续下去，直到达到最大的范围值，或者在表空间中已经没有自由空间用于下一个范围。最理想的状态就是一个段的数据可被存在单一的一个范围中。这样，所有的数据存储时靠近段内其它数据，并且寻找数据可少用一些指针。但是一个段包含多个范围的情况是大量存在的，没有任何措施可以保证这些范围是相邻存储的，如图〈1〉。当要满足一个空间要求时，数据库不再合并相邻的自由范围（除非别无选择）， 而是寻找表空间中最大的自由范围来使用。这样将逐渐形成越来越多的离散的、分隔的、较小的自由空间，即碎片。例如：



---- 2、碎片对系统的影响

---- 随着时间推移，基于数据库的应用系统的广泛使用，产生的碎片会越来越多，将对数据库有以下两点主要影响：

---- （1）导致系统性能减弱

---- 如上所述，当要满足一个空间要求时，数据库将首先查找当前最大的自由范围，而"最大"自由范围逐渐变小，要找到一个足够大的自由范围已变得越来越困难，从而导致表空间中的速度障碍，使数据库的空间分配愈发远离理想状态；

---- （2）浪费大量的表空间

---- 尽管有一部分自由范围（如表空间的pctincrease为非0）将会被SMON（系统监控）后台进程周期性地合并，但始终有一部分自由范围无法得以自动合并，浪费了大量的表空间。

---- 3、自由范围的碎片计算

---- 由于自由空间碎片是由几部分组成，如范围数量、最大范围尺寸等，我们可用FSFI--Free Space Fragmentation Index（自由空间碎片索引）值来直观体现：

FSFI=100*SQRT(max(extent)/sum(extents))*1/SQRT(SQRT(count(extents)))

---- 可以看出，FSFI的最大可能值为100（一个理想的单文件表空间）。随着范围的增加，FSFI值缓慢下降，而随着最大范围尺寸的减少，FSFI值会迅速下降。
---- 下面的脚本可以用来计算FSFI值：

    rem    FSFI Value Compute
    rem    fsfi.sql
    column FSFI format 999,99
    select tablespace_name,sqrt(max(blocks)/sum(blocks))*
           (100/sqrt(sqrt(count(blocks)))) FSFI
    from dba_free_space
    group by tablespace_name order by 1;
    spool fsfi.rep;
    /
    spool off;

---- 比如，在某数据库运行脚本fsfi.sql,得到以下FSFI值：
    TABLESPACE_NAME                   FSFI
    ------------------------------ -------
    RBS                              74.06
    SYSTEM                          100.00
    TEMP                             22.82
    TOOLS                            75.79
    USERS                           100.00
    USER_TOOLS                      100.00
    YDCX_DATA                        47.34
    YDCX_IDX                         57.19
    YDJF_DATA                        33.80
    YDJF_IDX                         75.55

---- 统计出了数据库的FSFI值，就可以把它作为一个可比参数。在一个有着足够有效自由空间，且FSFI值超过30的表空间中，很少会遇见有效自由空间的问题。当一个空间将要接近可比参数时，就需要做碎片整理了。
---- 4、自由范围的碎片整理

---- （1）表空间的pctincrease值为非0

---- 可以将表空间的缺省存储参数pctincrease改为非0。一般将其设为1，如：

       alter tablespace temp
       default storage(pctincrease 1);

---- 这样SMON便会将自由范围自动合并。也可以手工合并自由范围：
       alter tablespace temp coalesce;

---- 5、段的碎片整理
---- 我们知道，段由范围组成。在有些情况下，有必要对段的碎片进行整理。要查看段的有关信息，可查看数据字典dba_segments，范围的信息可查看数据字典dba_extents。如果段的碎片过多， 将其数据压缩到一个范围的最简单方法便是用正确的存储参数将这个段重建，然后将旧表中的数据插入到新表，同时删除旧表。这个过程可以用Import/Export（输入/输出）工具来完成。

---- Export（）命令有一个（压缩）标志，这个标志在读表时会引发Export确定该表所分配的物理空间量，它会向输出转储文件写入一个新的初始化存储参数--等于全部所分配空间。若这个表关闭， 则使用Import（）工具重新生成。这样，它的数据会放入一个新的、较大的初始段中。例如：

exp user/password file=exp.dmp compress=Y grants=Y indexes=Y
        tables=(table1,table2);
               
---- 若输出成功，则从库中删除已输出的表，然后从输出转储文件中输入表：
imp user/password file=exp.dmp commit=Y buffer=64000 full=Y

---- 这种方法可用于整个数据库。
---- 以上简单分析了Oracle数据库碎片的产生、计算方法及整理，仅供参考。数据库的性能优化是一项技术含量高，同时又需要有足够耐心、认真细致的工作。 对数据库碎片的一点探讨，