前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >Oracle架构设计

Oracle架构设计

作者头像
Alfred Zhao
发布2019-05-24 20:34:12
6170
发布2019-05-24 20:34:12
举报

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

那么接下来,问题来了.. Q1:当我们接手一个新项目,有哪些规范可以参考?

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

Q2:当我们维护的是一个历史项目,有哪些需要去检查记录?

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

Q1:当我们接手一个新项目,有哪些规范可以参考?

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

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

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

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

比如DBS_D_JINGYU存放数据,那么同时定义DBS_I_JINGYU存放对应表的索引。

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

情景1:如果一个库中有多个业务,分别对应不同的业务用户,那么可以根据用户划分

例如:现在有2个业务用户jingyu, ludan; 那么设计为表空间DBS_D_JINGYU存放jingyu业务用户的数据,表空间DBS_D_LUDAN存放luduan业务用户的数据。

代码语言:javascript
复制
create tablespace DBS_D_JINGYU;
create tablespace DBS_D_LUDAN;

create user jingyu identified by jingyu default tablespace DBS_D_JINGYU;
create user ludan identified by ludan default tablespace DBS_D_LUDAN;

grant resource, connect to jingyu, ludan;

情景2:如果只有一个业务用户,但业务很复杂,有不同种类的数据,则可以根据数据的种类不同划分对应的表空间。

比如jingyu用户,对应默认的表空间是DBS_D_JINGYU。 另外建立几个表空间存放其他明确划分的数据。 DBS_D_CONFIG 存jingyu用户下的配置数据。 DBS_D_FACT 存jingyu用户下实际产生的业务数据。

小结:

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

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

代码语言:javascript
复制
#假设ASM磁盘组为DATA;
#创建新的表空间DBS_D_JINGYU的语句:
create tablespace DBS_D_JINGYU datafile '+DATA' size 30G autoextend off;
#为表空间DBS_D_JINGYU增加一个新的30G数据文件的语句:
alter tablespace DBS_D_JINGYU 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. 查询表空间使用率

【转载】Oracle 查看表空间使用率SQL脚本

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

代码语言:javascript
复制
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. 查询数据表空间中是否错误的存放了索引

代码语言:javascript
复制
select owner, segment_name, segment_type, sum(bytes)/1024/1024
   from dba_segments
 where tablespace_name = 'DBS_D_JINGYU'
 group by owner, segment_name, segment_type
 having segment_type in ('INDEX PARTITION','INDEX')
 order by 4 desc

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

代码语言:javascript
复制
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_JINGYU'
 order by c.table_name, c.partition_position;

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

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

代码语言:javascript
复制
select owner, segment_name, segment_type, sum(bytes)/1024/1024
  from dba_segments
 where tablespace_name = 'DBS_D_JINGYU'
 group by owner, segment_name, segment_type
 order by 4 desc

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

代码语言:javascript
复制
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;
本文参与 腾讯云自媒体同步曝光计划,分享自作者个人站点/博客。
原始发表:2015-07-29 ,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 作者个人站点/博客 前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • Q1:当我们接手一个新项目,有哪些规范可以参考?
    • 1. 不要试图使用任何系统自带的表空间作为你的业务表空间。
      • 2. 数据表空间和索引表空间分开管理。
        • 3. 不同业务用户的表空间区分开,相同业务用户不同类数据的表空间区分开。
          • 情景1:如果一个库中有多个业务,分别对应不同的业务用户,那么可以根据用户划分
          • 情景2:如果只有一个业务用户,但业务很复杂,有不同种类的数据,则可以根据数据的种类不同划分对应的表空间。
          • 小结:
      • Q2:当我们维护的是一个历史项目,有哪些需要去检查记录?
        • 1. 查询表空间使用率
          • 2. 查询当前连接用户用到了哪些表空间
            • 3. 查询数据表空间中是否错误的存放了索引
              • 4. 查询拼接出删除历史分区表的执行SQL
                • 5. 查询某表空间中的段所属用户、段名、段类型
                  • 6. 查询表空间中数据文件的当前大小(MB),最大大小(GB),是否自动扩展
                  相关产品与服务
                  数据库
                  云数据库为企业提供了完善的关系型数据库、非关系型数据库、分析型数据库和数据库生态工具。您可以通过产品选择和组合搭建,轻松实现高可靠、高可用性、高性能等数据库需求。云数据库服务也可大幅减少您的运维工作量,更专注于业务发展,让企业一站式享受数据上云及分布式架构的技术红利!
                  领券
                  问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档