表空间实战

﹏ヽ暗。殇╰゛Y 2022-09-27 14:26 220阅读 0赞

表空间的管理  
   表空间是ORACLE数据库中最大的逻辑结构，表空间是组织数据和分配空间的逻辑结构。ORALCE数据库中的所有对象和方案对象都被逻辑的存储在表空间中。  
   控制文件、重做日志文件、归档日志文件等其他文件不属于任何表空间。  
         
 表空间的特性        
   一个数据库包含多个表空间，一个表空间包含多个数据文件。表空间只能属于一个数据库。表空间的大小 等于其内部数据文件大小之和。  
   数据库的大小 等去其所包含表空间之和。表空间可以脱机或联机，但是SYSTEM表空间不能脱机。  
   表空间存在 只读和读写状态可以互相切换。可以向表空间中增加或删除数据文件。任何一个用户使用表空间的数量是有一定限额的。  
   一个用户默认属于一个表空间，但是该用户的不同方案对象的数据可以存放到不同表空间中。  
   可以将一个表中的数据和该表的索引数据存放到不同的表空间中。以提高I/O性能。  
   表空间分为： 永久表空间（permanent）、临时表空间(temporary)、撤销表空间(undo)、大表空间等  
    
    
  表空间类型  
    系统表空间 （SYSTEM 、SYSAUX）,其他都是非系统表空间.  
    临时表空间(temporary)：当执行排序、分组汇总、索引等功能的SQL语句时，服务器进程首先将临时数据存放搭配PGA区的排序区中，当排序区不够用时，  
         服务器进程就会在临时表空间中建立临时段，并将这些临时数据存放到临时段中。  
         在临时表空间中，同一个例程的所有SQL语句的排序操作将共享使用一个排序段(sort segment)。排序段在执行第一条排序操作  
         时被创建，在例程关闭时被释放。通过V$SORT\_SEGMENT来查询排序段的使用情况，通过V$SORT\_USAGE来查询使用排序段的会话和用户信息。  
          
    撤销表空间(undo)： 即将回退段(rollback segment)放到了撤销表空间中。由系统自动管理回退段。通过DBA\_ROLLBACK\_SEGS 查询得到撤销表空间中的回退段信息。  
         在ORACLE中可以创建多个撤销表空间，但是同一时刻只允许激活一个撤销表空间。当回退段不足时，一个事务可以使用多个回退段。  
        撤销表空间中的撤销段的主要目的是：  
        1、用一条ROLLBACK语句明确地回退一个事事务；  
        2、隐含地回退一个事务；  
        3、重构数据的一个读一致性图像；  
        4、从逻辑错误中恢复；  
          
    大文件表空间(bigfile):大文件表空间只能放置一个数据文件或临时文件。其数据文件可以包括4G个数据库。

表空间的区、段管理方式  
          表空间是按照区和段空间进行管理的。  
          区管理又分为： 本地管理方式 和字典管理方式；    
          本地管理方式按区进行分配；区的分配方式有 自动分配 和 统一分配 两种；  
          紧接着 区分配是 按段空间管理；段空间管理分为  自动管理和手动管理 ；  
           
          本地管理方式：表空间中区分配和区回收的管理信息都被存储在表空间的数据文件中。表空间会在每个数据文件中维护一个 "位图(bitmao)"结构，用户记录表空间中  
      所有区的分配情况。这样当在表空间中分配新的区或回收已分配的区时，ORACLE将对数据文件中的 “位图”进行更新。而这种更新不会产生回退信息和  
      重做信息。  
        
    表空间的区的分配方式：  
             统一(UNIFORM):指定表空间中所有区的大小都相同。区的大小默认值是1MB。  
             自动(AUTOALLOCATE或SYSTEM)：指定由ORACLE系统来自动管理区的大小，这是默认设置。区的大小随表的大小自动动态改变。  
               
              表大小和区大小的对应关系为：  
                表大小           区大小  
                 
                 64KB(或前16个区)          64KB  
                 1MB(接下来的36个区)         1MB  
                 64MB(接下来的120个区)        8MB  
                 1000MB(以后的区)          64MB     
        
   表空间的段空间管理方式：  
             段空间的管理方式主要是指ORACLE用来管理段中已用数据块和空间数据库的机制。  
              
       手动(MANUAL)：ORACLE将使用空闲列表(free list)来管理端的空间数据块。  
       自动(AUTO)：  ORACLE将使用位图(bitmap)来管理端的已用数据块和空闲数据块。  
    
   表空间的几种状态：  
             读写状态(Read Write)、只读状态(Read Only)；  
             联机状态(Online)、脱机状态(Offline)；  
              
             脱机模式的4种情况：  
                 正常(Normal)：是ORACLE默认的脱机模式。必须保证所有数据文件都是联机的、可用的。ORACLE会执行一次检查点，  
          以便将SGA区中与该表空间相关的脏缓存块都写入数据文件中。  
          然后再关闭表空间中的所有数据文件。下一次将表空间恢复为联机状态无需进行数据库恢复。  
        临时(Temporary)：不必保证该表空间的所有数据文件为联机的、可用的，ORACLE会执行一次检查点。下次将表空间恢复为  
          联机可能需要数据库恢复。  
        立即(Immediate)：不必保证该表空间的所有数据文件为联机的、可用的，ORACLE也不会执行一次检查点。而是直接将该表空间  
          中的所有数据文件都设置为脱机状态。下次将表空间设置为联机必须进行数据库恢复。  
        用于恢复(For Recover)：如果要对表空间进行基于时间的恢复，可以使用该种脱机模式将表空间切换到脱机状态。然后DBA就  
            可以使用备份的数据文件覆盖原有的数据文件，而后再在这些数据文件上，利用归档重做日志，就可以  
            将表空间恢复为某个时间点的状态。  
              
    表空间的管理准则  
             如果表空间是本地管理方式，则在创建表空间时不能指定区分配的默认存储参数；只能将它设置为自动(AUTOALLOCATE)或统一(UNIFORM)方式；  
             并且在表空间创建之后不能再更改。   
             如果表空间是字典管理方式，则不仅要在创建时设置默认存储参数，而且在创建之后还可以通过修改  
             存储参数对表空间的存储管理方式进行更改。  
              
    确定表空间的大小  
             表空间是用来存储表、索引中的数据的。表空间的大小取决于表的大小和索引数据量的大小。  
              
             确定一个表的大小的主要因素：  
     1、每行大约有多少个字节；  
     2、该表最初或至少包含多少行；  
     3、每个时间段(每天、每周、每月、每季度、每年等)该表中的行的增长情况；  
     4、在该表上进行的操作类型(插入、删除、更新)；  
     5、哪种操作类型比较多；  
       
     表的大小 = 最大行长 \* 行数 \* (1+PCTFREE/100) \*预留的百分比；  
        
       最大行长 ：   表中一条记录的长度即所有字段长度之和；  
        
       PCTFREE:    即空闲百分比。是用于设置块中现有行的可能的update 而需要保留的百分比。另一个与块管理相关的参数是PCTUSED(已用百分比).  
           当一个块被insert数据时，要留下PCTFREE指定的百分比空间以便进行更新数据时使用，如果剩余的空间小雨PCTFREE指定的百分比，  
           该块就要被从空闲列表中取出来，在空闲空间中没有因 delete 数据而降低到PCTUSED指定的百分比空间之前，不考虑在该块中插入  
           新的数据。如果该块中的数据少于PCTUSED指定的百分比时，该块就被认为空闲得足够供插入操作使用，并被放到空闲列表中。  
           根据插入、更新操作比较多，删除较少的情况 。设置PCTFREE =50 , PCTUSED =30.  
            
       预留的百分比：例如 表行月增长1%，按1年计算 则预留百分比 = (1 + 1%)的12次幂。  
        
           撤销表空间大小 = (UNDO\_RETENTION参数的值(默认900秒) \* 每秒需要的撤销块 \* DB\_BLOCK\_SIZE) +DB\_BLOCK\_SIZE  
            
  撤销管理： 回退段撤销管理(Rollback Segments Undo ) 自动撤销管理(System Managed Undo) 两种

选择回退段撤销管理,需要将 参数设置为 undo\_management=NULL，还需要设置参数 rollback\_segments 即系统中所使用回退段的名称；  
  transaction 系统中事务总数；transaction\_per\_rollback\_segment 即 指定回退段可以服务的的事务个数；  
  max\_rollback\_segment 即 系统中最大回退段的个数

创建撤销表空间时只能指定本地化管理表空间类型；盘区管理方式只能是自动分配管理即 autoallocate;段的管理方式也只能是手动管理方式 即MANUAL；  
   撤销表空间中的数据文件的状态只有 ONLINE 和 OFFLINE 两种；只能想撤销表空间中添加数据文件；移动撤销表空间的数据文件；撤销表空间中的撤销  
   记录默认保留900 秒，之后将自动清除，避免撤销表空间迅速膨胀。可以通过修改参数 undo\_retention 来修改撤销记录保留的时间.

ALTER TABLESPACE testspace OFFLINE normal;  
ALTER TABLESPACE TESTSPACE ONLINE ;  
alter tablespace testspace READ ONLY ;  
ALTER TABLESPACE TESTSPACE READ WRITE;  
SELECT TABLESPACE\_NAME,STATUS FROM DBA\_TABLESPACES;  
CREATE TABLE T(ID INTEGER,NAME VARCHAR(12) ) TABLESPACE testspace;  
insert into t values (2,'dDd');  
select \* from t;  
\---表空间的四种状态 online offline  read only  read write  
\---增加表空间  
ALTER TABLESPACE testspace  
add datafile  'E:/OracleDataBase/app/Administrator/oradata/newwen/testSPACE01\_2.dbf' size 1M AUTOEXTEND ON NEXT 1M MAXSIZE 5M;---通过添加新的数据文件来增加表空间

ALTER DATABASE DATAFILE 'E:/OracleDataBase/app/Administrator/oradata/newwen/testSPACE01\_1.dbf' RESIZE 2M;  ----通过增加数据文件的大小 增加表空间

\---修改表空间  
ALTER DATABASE DATAFILE 'E:/OracleDataBase/app/Administrator/oradata/newwen/testSPACE01\_1.dbf'  AUTOEXTEND ON NEXT 2M  MAXSIZE 6M;  
ALTER DATABASE TEMPFILE 'E:/OracleDataBase/app/Administrator/oradata/newwen/TEMPFILESPACE01\_1.dbf' AUTOEXTEND ON NEXT 2M MAXSIZE 10M;  
\--设置默认表空间  
SELECT \* FROM USER\_USERS;---查询当前用户默认表空间信息。  
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE   tmpspace;  
ALTER DATABASE DEFAULT TABLESPACE commonspace;  
\---数据文件的三种状态 online offline  offline drop(非归档模式下)  
\--表空间的状态会影响数据文件的状态，而数据文件的状态不会影响表空间的状态  
\--移动表空间中的数据文件的方法和步骤 （第二步是将数据文件放到新位置）  
alter tablespace testspace offline;  
alter tablespace testspace  
rename datafile 'E:/OracleDataBase/app/Administrator/oradata/newwen/testSPACE01\_2.dbf' to 'F:/Oracle\_Tools/testSPACE01\_3.dbf';  
/\*介质恢复数据文件的方法或步骤  
shutdown immediate;  
startup mount;  
recover datafile 'F:/Oracle\_Tools/testSPACE01\_3.dbf';---如果在没有offline的情况下将数据文件复制到了新位置，则在将表空间恢复为ONLINE时，需要介质恢复新路径下的数据文件，否则就不需要介质恢复。  
startup open;  
\*/  
alter tablespace testspace online;

\---表空间重命名(必须是ONLINe状态)

alter tablespace testspace rename to newtestspace;  
\----删除表空间  
\--语法格式：  
drop tablespace space\_name  
\[including contents \[and datafiles\]\];

drop tablespace testspace  
including contents;----只是从数据库字典和控制文件中将该表空间的相关信息删除了。而不会实际删除对应的系统中与其对应的数据文件  
\---INCLUDING CONTENTS表示删除表空间的同时，删除表空间中的所有数据库对象。如果表空间中有数据库对象必须这样执行。否则  
\---直接执行DROP TABLESPACE TESTSPACE即可  
\--或者  
drop tablespace testspace  
including contents and datafiles;  
\----including contents and datafiles 表示删除表空间的同时，删除表空间中所有的对应的数据文件。

\--创建和修改临时表空间组  
create temporary tablespace tmpspace1  
tempfile 'F:/Oracle\_Tools/tmpfiles01\_1.dbf' size 2m  
tablespace group tmpgroup;

create temporary tablespace tmpspace2  
tempfile 'F:/Oracle\_Tools/tmpfiles01\_2.dbf' size 2m  
tablespace group tmpgroup1;

alter tablespace tmpspace1 tablespace group tmpgroup1;---改变临时表空间所属的临时表空间组  
select \* from dba\_tablespace\_groups;

\---大文件表空间  
\--大文件表空间只能对应唯一一个数据文件或临时文件，而普通表空间可以最大支持1021个数据文件或临时文件  
\--当大于1021个时，开始循环复用原来的文件号。及前1021个的数据文件绝对号和相对号是一样的。  
\--大文件其对应的文件可达4G个数据块大小，而普通表空间其对应的文件为4M个数据块大小。  
select \* from database\_properties;---当前数据库默认的表空间类型  
\--非标准数据块表空间 也称非标准表空间。  
\--即其数据块的大小与标准数据块大小不一样。使用BLOCKSIZE子句指定表空间块的大小。但是必须要有数据缓存区  
\--参数db\_nk\_cache\_size的值与BLOCKSIZE参数的值相匹配。对应关系如下：  
BLOCKSIZE                                   db\_nk\_cache\_size  
2KB                                                 db\_2k\_cache\_size  
4KB                                                 db\_4k\_cache\_size  
8KB                                                 db\_8k\_cache\_size  
16KB                                                db\_16k\_cache\_size  
32KB                                                db\_32k\_cache\_size

\--标准数据块大小是在创建数据库时指定的。在初始化参数文件中db\_block\_size指定。  
alter system set db\_16k\_cache\_size=16M;  
create tablespace nonstandardBlockspace  
datafile 'F:/Oracle\_Tools/blocksizefiles01\_1.dbf' size 2m autoextend  on next 2M maxsize 5M  
blocksize 16K;

\---撤销表空间(为了实现对数据回退、恢复、事务回滚以及撤销等操作，ORACLE数据库提供了一部分存储空间，  
\--                      专门保存撤销记录，将修改前的数据保存到空间中，故称为撤销表空间。)  
\--一个数据库可以有多个撤销表空间，但是数据库一次只能使用一个撤销表空间。  
\---创建撤销表空间  
CREATE UNDO TABLESPACE undospace  
DATAFILE 'F:/Oracle\_Tools/undofiles01\_1.dbf' SIZE 2M  
AUTOEXTEND ON  NEXT 2M MAXSIZE 10M;

\--将数据库使用的撤销表空间切换到其他表空间：使用 alter system 语句修改参数undo\_tablespace的值即可。  
\--切换撤销表空间后，数据库中新事务的撤销数据将保存在新的撤销表空间中。  
ALTER SYSTEM SET UNDO\_TABLESPACE=UNDOTBS\_NEWNAME;

\--修改撤销记录保留的时间  
\--在主动撤销管理方式中，可以指定撤销信息在提交后需要保留的时间，防止在长时间的查询过程中出现snapshot too old错误  
\--使用动态参数undo\_retention参数指定保留的时间。保留的时间单位是以秒为计算的。  
ALTER SYSTEM SET UNDO\_RETENTION=600;  
\--删除撤销表空间  
DROP TABLESPACE UNDOTBSNAME INCLUDING CONTENTS AND DATAFILES;