Oracle架构设计01：表空间的管理维护规范

转载自：http://www.cnblogs.com/jyzhao/p/4668199.html#1.1

 

Oracle数据库的表空间管理可以说是非常简单和基础的一项维护工作，但是越简单的事情就越要制定统一的规范，这样数据库的各项管理工作才会愈加的简单高效。

那么接下来，问题来了..
Q1：当我们接手一个新项目，有哪些规范可以参考(概述)？

　　1. 不要试图使用任何系统自带的表空间作为你的业务表空间
　　2. 数据表空间和索引表空间分开管理
　　3. 不同业务用户的表空间区分开，相同业务用户不同类数据的表空间区分开

 

Q2：当我们维护的是一个历史项目，有哪些需要去检查记录（概述）？

　　1. 查询表空间使用率
　　2. 查询当前连接用户用到了哪些表空间
　　3. 查询数据表空间中是否错误的存放了索引
　　4. 查询拼接出删除历史分区表的执行SQL
　　5. 查询某表空间中的段所属用户、段名、段类型
　　6. 查询表空间中数据文件的当前大小(MB)，最大大小(GB)，是否自动扩展

 

 

Q1：当我们接手一个新项目，有哪些规范可以参考（详解）？

 

可以从以下几方面来考虑：

1. 不要试图使用任何系统自带的表空间作为你的业务表空间。

包括system,sysaux,undo,users等，这是约定俗成的标准规范。

 

2. 数据表空间和索引表空间分开管理。

比如DBS_D_ERIC存放数据，那么同时定义DBS_I_ERIC存放对应表的索引。

 

3. 不同业务用户的表空间区分开，相同业务用户不同类数据的表空间区分开。

##情景1：如果一个库中有多个业务，分别对应不同的业务用户，那么可以根据用户划分
例如：现在有2个业务用户eric, sea;
那么设计为表空间DBS_D_ERIC存放eric业务用户的数据，表空间DBS_D_SEA存放sea业务用户的数据。

create tablespace DBS_D_ERIC;
create tablespace DBS_D_SEA;

create user eric identified by eric default tablespace DBS_D_ERIC;
create user sea identified by sea default tablespace DBS_D_SEA;

grant resource, connect to eric, sea;

##情景2：如果只有一个业务用户，但业务很复杂，有不同种类的数据，则可以根据数据的种类不同划分对应的表空间。
比如eric用户，对应默认的表空间是DBS_D_ERIC。
另外建立几个表空间存放其他明确划分的数据。
DBS_D_CONFIG 存eric用户下的配置数据。
DBS_D_FACT 存eric用户下实际产生的业务数据。

##小结：
这里的情景举例只是提供了一个思路或者说形成规范的意识。
其实实际生产中本身并没有一个通用的规范，例如：
如果实际业务量很小，数据量很小，可能就没必要分这么细致；
如果数据量很大，可能存放业务数据的部分还要根据实际业务划分的更细致；
如果业务用户非常多，但各自的数据量都很少，也没必要为每个用户都分配独立的表空间。

顺便提一句，底层存储方面现在建议直接使用Oracle ASM技术，条带化减少I/O争用和热块问题。
同时使用OMF可以使创建表空间变的非常简单，如上面的例子中创建表空间不再需要手工指定datafile和数据文件大小。
但实际项目中为了避免自动扩展造成的空间管理过分配问题，一般不设置自动扩展，即指定固定大小并关闭自动扩展。如下例：

#假设ASM磁盘组为DATA；
#创建新的表空间DBS_D_ERIC的语句：
create tablespace DBS_D_ERIC datafile '+DATA' size 30G autoextend off;

#为表空间DBS_D_ERIC增加一个新的30G数据文件的语句：
alter tablespace DBS_D_ERIC add datafile '+DATA' size 30G autoextend off;

#查询DATA的空间使用情况：
select name, total_mb, free_mb, USABLE_FILE_MB from v$asm_diskgroup where name='DATA';

 

Q2：当我们维护的是一个历史项目，有哪些需要去检查记录（详解）？

1. 查询表空间使用率

set pagesize 500
set lin 120
col TABLESPACE_NAME for a30
col SUM_SPACE(M) for a15
col SUM_BLOCKS for a35
col USED_SPACE(M) for a15
col USED_RATE(%) for a15
col FREE_SPACE(M) for a15
SELECT D.TABLESPACE_NAME, 
       SPACE || 'M' "SUM_SPACE(M)", 
       SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)", 
       ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) || '%' 
          "USED_RATE(%)", 
       FREE_SPACE || 'M' "FREE_SPACE(M)" 
  FROM (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                 SUM (BLOCKS) BLOCKS 
            FROM DBA_DATA_FILES 
        GROUP BY TABLESPACE_NAME) D, 
       (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) FREE_SPACE 
            FROM DBA_FREE_SPACE 
        GROUP BY TABLESPACE_NAME) F 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
UNION ALL                                                         
SELECT D.TABLESPACE_NAME, 
       SPACE || 'M' "SUM_SPACE(M)", 
       USED_SPACE || 'M' "USED_SPACE(M)", 
       ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)", 
       NVL (FREE_SPACE, 0) || 'M' "FREE_SPACE(M)" 
  FROM (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE, 
                 SUM (BLOCKS) BLOCKS 
            FROM DBA_TEMP_FILES 
        GROUP BY TABLESPACE_NAME) D, 
       (  SELECT TABLESPACE_NAME, 
                 ROUND (SUM (BYTES_USED) / (1024 * 1024), 2) USED_SPACE, 
                 ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE 
            FROM V$TEMP_SPACE_HEADER 
        GROUP BY TABLESPACE_NAME) F 
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+) 
ORDER BY 1; 

 

2. 查询当前连接用户用到了哪些表空间

select tablespace_name from user_tables union
select tablespace_name from user_tab_partitions union
select tablespace_name from user_indexes union
select tablespace_name from user_ind_partitions;

 

3. 查询数据表空间中是否错误的存放了索引

select owner, segment_name, segment_type, sum(bytes)/1024/1024
from dba_segments
where tablespace_name = 'DBS_D_ERIC'
group by owner, segment_name, segment_type
having segment_type in ('INDEX PARTITION','INDEX')
order by 4 desc;

 

4. 查询拼接出删除历史分区表的执行SQL

select c.table_name,
c.tablespace_name,
c.partition_name,
'alter table ' || c.table_name || ' drop partition ' ||
c.partition_name || ';'
from user_tab_partitions c
where c.partition_name like '%'
and c.tablespace_name = 'DBS_D_ERIC'
order by c.table_name, c.partition_position;

##注： partition_name 的过滤条件自己加上。

 

5. 查询某表空间中的段所属用户、段名、段类型

select owner, segment_name, segment_type, sum(bytes)/1024/1024
from dba_segments
where tablespace_name = 'DBS_D_ERIC'
group by owner, segment_name, segment_type
order by 4 desc;

 

6. 查询表空间中数据文件的当前大小(MB)，最大大小(GB)，是否自动扩展

set linesize 200
col file_name for a66
select FILE_ID, FILE_NAME, TABLESPACE_NAME, BYTES/1024/1024 "MB", MAXBYTES/1024/1024/1024 "GB", AUTOEXTENSIBLE, STATUS, ONLINE_STATUS from dba_data_files;