Oracle表空间设计基本原则

默默的成长

发布于 2022-11-02 14:35:17

5550

发布于 2022-11-02 14:35:17

文章被收录于专栏：前端记录笔记

携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第15天，点击查看活动详情 >>

1. Oracle表空间设计基本原则

1、系统数据与应用数据必须存储于不同的表空间。 2、按照应用划分数据，不同应用的数据应存储于不同的表空间。 3、表和索引分离，需存储在不同的表空间，以便分布到不同的数据文件和硬盘上，并分别进行不同的物理存储参数优化，减少磁盘I/O的竞争。 4、相对静态的表和频繁变动的表分开存放在不同的表空间，以便分别进行不同的物理参数优化。 5、为中间表单独设计表空间，可以不考虑备份。 6、采用临时表空间组技术，提高大批量数据处理效率。

\

2. 表空间设计标准

2.1 隔离系统数据与应用数据

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

Oracle数据库安装初始化之后，会自带一些系统表空间，包括system，sysaux，undotbs1，users，temp等，

如果是RAC环境数据库，还会有一个undotbs2作为实例2的默认undo表空间，这是约定俗成的标准规范。

\

所以，创建用户之前就应该规划好用来存放该用户的数据的默认表空间，

如不指定，新建用户会使用数据库的默认表空间

查看数据库的默认表空间：

SYS@wghis1>``set linesize 512``SYS@wghis1>col PROPERTY_VALUE ``for a50``SYS@wghis1>col DESCRIPTION ``for a50``SYS@wghis1>``select * from database_properties where property_name like ``'DEFAULT%'``; PROPERTY_NAME PROPERTY_VALUE DESCRIPTION``------------------------------ -------------------------------------------------- --------------------------------------------------``DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace``DEFAULT_PERMANENT_TABLESPACE USERS Name of default permanent tablespace``DEFAULT_EDITION ORA$BASE Name of the database default edition``DEFAULT_TBS_TYPE SMALLFILE Default tablespace ``type

可以看到数据库的默认表空间为users，个别数据库的users表空间巨大，就是因为存放了业务用户的数据导致。

2.2 按照应用划分数据

按照应用划分数据，不同应用的数据应存储于不同的表空间

不同的业务，也可以称为不同的schema，在oracle的逻辑体系中，就是不同的业务用户。

\

例如：

某数据库中需要新建业务用户A，就要相应的提前创建A用户用来存放数据的表空间A，并在创建用户时指定A表空间为业务用户A的默认表空间。

同理用户B也要用响应的表空间B。

表空间创建语句：

create tablespace A datafile ``'+DATA01' size 10G AUTOEXTEND on MAXSIZE 30G ;

创建用户时指定

create user A iedntified by ``passwd default tablespace A;

也可以在用户创建之后更改

alter user A default tablespace A;

\

2.3 表和索引分离

表和索引分离，需存储在不同的表空间；

把表和索引的表空间存储在不同在磁盘上，把两类不同IO性质的数据分开放，这样可以提高磁盘的IO总体性能；

如果索引的数据文件损坏，只要创建索引即可，不会引起数据丢失的问题。

创建索引时，需要指定表空间名：

create index ind_name on tb_name(col_name) tablespace tbs_name;

\

2.4 物理IO隔离

为提高IO总体性能，可以考虑将不同表空间存放于不同的ASM磁盘VG中。

为每一个业务表空间、索引表空间创建独立的ASM磁盘组

查询磁盘组信息：

SYS@wghis1>``select group_number,name,total_mb,free_mb from ``v``$asm_diskgroup; GROUP_NUMBER NAME TOTAL_MB FREE_MB``------------ ------------------------------ ---------- ----------`` ``1 CRS 5120 4724`` ``2 DATA 204800 132893`` ``4 DATA01 1024000 131262`` ``5 ARCH 102400 74254

对应关系为A磁盘组用于存放A表空间，A表空间用于存放A用户业务数据；

但考虑实际生产环境中，单独为业务用户规划磁盘组，管理成本和维护成本较高，此选项可以作为IO性能提成的一种备选方案。

2.5 为lob字段单独创建表空间

由于lob字段的特殊性，可以考虑将带有lob字段的表存放于独立表空间

lob类型的数据全部存储在表空间中，表中只存放指针，即使在建表时没指定表空间，数据也全部存入该数据库默认表空间中。

例如用alter table tb_name move tablespace tbs_name 来对表做空间迁移时只能移动非lob字段以外的数据。

创建lob字段表空间

\

create tablespace lob_data datafile ``'+DATA01' size 10G AUTOEXTEND on MAXSIZE 30G ;

\

创建含有lob字段的表时指定lob字段存放独立表空间

create table customer( id_customer varchar2(32),name varchar2(100),photo blob) tablespace lob_data;

\

3. 表空间管理

在Oracle体系中，一个数据库实例可以有多个表空间；

默认的smallfile表空间可以有多个数据文件，单个数据文件最大为32G；

数据文件可以设置初始化大小，也可以设置自动扩展，扩展最大即为32G。

\

3.1 表空间创建

create tablespace tbs_name datafile ``'+DATA01' size 10G AUTOEXTEND on MAXSIZE 30G ;

由于oracle数据库中pctfree参数的存在，故单个数据文件大小设置为最大30G

3.2 添加数据文件

alter tablespace tbs_name add datafile ``'+DATA01' size 30G AUTOEXTEND off ;

为统计表空间大小和磁盘使用率后续添加数据文件均设置为最大值30G并关闭自动扩展

如使用文件系统 datafile 后面 ‘ ’中要写明数据文件的完整路径和名称

而ASM磁盘管理只需要指定磁盘组名称即可数据文件同理

3.3 删除数据文件

alter tablespace tas_name drop datafile file_name(or file_id);

Oracle 10G R2 之后版本中可以删除某个空的数据文件此操作慎用

3.4 删除表空间

drop tablespace tbs_name including contents and datafiles cascade constraints;

逻辑和物理上完全删除表空间和数据文件

\

4. 临时表空间

\

通过创建临时表空间，oracle能够使带有排序操作的SQL语句获得更快的执行速率

如： CREATE INDEX、 ANALYZE、SELECT DISTINCT、ORDER BY、GROUP BY、 UNION ALL、 INTERSECT、MINUS、SORT-MERGE JOINS、HASH JOIN 这些操作。当操作完成系统会自动释放临时段。

一个临时表空间可以被多个用户所使用，在临时表空间中创建的段叫做"临时段"，oracle只会为一个实例创建一个临时段，这个临时段被实例中的所有排序操作共享使用，但是临时段每个区只能由一个事务使用。

如果数据库运行过程中有大量并发操作，为了提高排序性能，可以建立多个临时表空间。

\

4.1 临时表空间管理

临时表空间管理与永久表空间管理唯一区别：datafile为tempfile

创建临时表空间

create temporary tablespace temp_tbs_name tempfile ``'+DATA01' size 10M AUTOEXTEND on MAXSIZE 30G ;

为临时表空间添加数据文件

alter tablespace temp_tbs_name add tempfile ``'+DATA01' size 30G AUTOEXTEND off ;

删除临时表空间

drop tablespace temp_tbs_name including contents and datafiles;

更改数据库默认临时表空间

alter database default temporary tablespace temp_tbs_name ;

更改用户临时表空间

alter user username default temporary tablespace temp_tbs_name;

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

原始发表：2022-08-12，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库

sql

存储

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

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

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

数据库

sql

存储

登录后参与评论

0 条评论

热度