oracel逻辑结构—表空间

Tablespace：表空间是一个逻辑的概念，是数据文件（ datafile ）的容器，是数据文件在数据库中逻辑的组织形式。而数据文件真实的存在于操作系统之上 , 也可以是裸设备（ ASM ）。

 

常见的表空间有：

1、 system系统表空间

数据库创建时就创建，必须包含，存放数据库基本组件信息 比如基表。主要存储的是基表和数据字典，其实大家在对表、索引等数据库对象修改结构时都是在操作system 表空间里的表。如 alter 语句；

 

2、 sysaux辅助表空间

10G引入  system 太大了 . 就将部分功能组件（如 sqlplus 的工具）取出放到 sysaux 里， 属于SYSTEM 表空间的辅助表空间，独立出来的好处， SYSTEM 表空间的负荷得以减轻 . ，反复创建一些相关对象及组件引起 SYSTEM 表空间的碎片问题得以避免。

查看sysaux 表空间内的组件信息和大小

Select OCCUPANT_NAME,SPACE_USAGE_KBYTES from V$SYSAUX_OCCUPANTS;

 

3、 undotbs回退表空间

用于事务回退, 保存改前镜像块

 

4、 temp临时表空间

存放临时数据 磁盘排序或临时表

 

5、 users和 example

oracle提供的样本数据表空间

 

6、 用户数据表空间

用户自建

 

一、 表空间的类型

查看表空间的类型

idle> select TABLESPACE_NAME,CONTENTS from dba_tablespaces;

有：永久表空间

临时表空间

回退表空间

1、永久表空间管理

又分为:

小文件表空间( 一个表空间可以存放 1023 个文件 每个文件 4M*block_size)

基本应用都是这类 默认创建也是它

大文件表空间( 一个表空间可以存放 1 个文件 文件管理 4G*block_size)

很少使用, 不利于 IO 分散 , 容易产生争用 , 容易被 FS 限制

1）、查看：

select tablespace_name,file_id,file_name,ceil(bytes/1048567)||'M' MB from dba_data_files;

 

2）、创建小表空间

文件系统:create tablespace mytbs datafile '/PATH/file.dbf' size 10m;

裸设备:create tablespace rawtbs datafile '/dev/raw/raw1' size 4095M

 

3）、创建大文件表空间

 create bigfile tablespace bigtbs datafile '/u01/oracle/oradata/beijing/bigtbs01.dbf' size 5M;

 select tablespace_name,bigfile from dba_tablespaces;

 

二、临时表空间

    存放排序的中间结果或临时表

查看：

select tablespace_name,contents from dba_tablespaces where CONTENTS='TEMPORARY';

创建临时表空间：

create temporary tablespace NAME tempfile '' size 10M;

 

修改用户的临时表空间：

select temporary_tablespace from dba_users where username='SCOTT';

 

alter user scott temporary tablespace temp2;

 

修改数据库默认临时表空间：

alter database default temporary tablespace temp;

 

三、 回退表空间

UNDO表空间 存储的是  rollback segment 。

1、 system 回退段 系统创建 为 system 表空间的对象提供回退

2、非系统回退段 用户创建 为用户表空间的对象提供回退

3、延迟回退段   系统创建 表空间非正常离线时自动创建 我们不能管理

回退表空间的作用：

1.交易的回退 : 没有提交的交易可以 rollback

2.交易的恢复 : 数据库崩溃时 , 将磁盘的不正确数据恢复到交易前

3.读一致性   : 被查询的记录有事务占用 , 转向回滚段找改前镜像

4.闪回数据   : 从回滚段中构造历史数据

回滚段创建的时候是空闲的，只有事务产生时候才会产生数据。

 

创建UNDO 表空间：

 create undo tablespace undo2 datafile '/u01/oracle/oradata/ora10g/undo02.dbf' size 10M;

 

管理风格：自动或手动

 

SQL> show parameter undo

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

undo_management                      string      AUTO

undo_retention                       integer     900

undo_tablespace                      string      UNDOTBS1

 

undo_management=AUTO 回滚表空间段的段管理模式，

自动管理：

管理员只需要备足够的表空间容量，oracle 会自动管理扩展回滚段的数量。

只能使用一个UNDO 表空间

undo_tablespace:只有在自动管理模式下才可以使用。指明使用哪个 UNDO 表空间。

查看回顾段：select segment_name,tablespace_name,status from dba_rollback_segs;

 

回滚段的名字系统自动取

system是系统回退段 ,DBA 不能管理 .

 

切换回滚表空间的时候不会影响回滚数据:

SQL> alter system set undo_tablespace=undo2;

 

undo_retention＝ 900  ：提交之后旧的镜像保持在回滚段中的时间。非强制的回退保持时间 .( 回滚空间不足老的镜像就会被覆盖 )

 

强制保持：但是对空间要求较大，要慎用。(10g 开始支持 )

alter tablespace UNDOTABS1 RETENTION GUARANTEE;

select  tablespace_name,RETENTION from dba_tablespaces;

 

手工管理UNDO:

修改方法

SYS@beijing> show parameter rollback;

NAME      TYPE  VALUE

------------------------------------ ----------- ------------------------------

fast_start_parallel_rollback      string  LOW

rollback_segments      string

transactions_per_rollback_segment    integer  5

 

默认一个回滚段可以维持5 个事务  不要放太多事务 会产生回滚段头争用 

因为段头中记录段中的块的位置   找段里的数据就要访问段头

 

改成手动

SQL> alter system set undo_management=manual scope=spfile;

SQL> startup force

由于undo_management 是非自动管理，所以要重启才可以生效。

查看undo 段：

select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs;

手动创建回滚段 段会自动拓展大小 所以不需要指明

create rollback segment rbs1 tablespace undotbs1;

修改状态：alter rollback segment rbs1 online;

 

关于回滚段的知识我会在segment 中详细讲解。

 

 

五、表空间的状态

查看表空间的状态：

select tablespace_name,status from dba_tablespaces;

修改状态：

alter tablespace mytbs read write/read only/offline;

 

1、 Online(读写 ) 状态

 

2、 Readonly（只读）状态

只读表空间 只能select 查询和 drop 删除对象 不能修改 (INTSERT update delete truncate)system undotbs temp  包含活动事务的表空间都不能只读，只读表空间不可以修改表数据 但可以删除表结构 因为表结果存储在数据字典中 数据字典在 system 表空间。

 

3、 offline(离线 ) 状态

只可以删除对象(drop)  不可以 select dml truncate, 系统表空间 +default 临时 + 包含活动事务的表空间是不能离线的 , 离线状态 DBA 可以移动数据文件路径 , 离线就是冻结 将 SCN 号锁定在某一时刻

select name,checkpoint_change# open_scn,last_change# stop_scn from v$datafile;

结果：NAME      OPEN_SCN STOP_SCN

-------------------------------------------------- ---------- ----------

/u01/oracle/oradata/ora10g/system01.dbf        475662

/u01/oracle/oradata/ora10g/undotbs01.dbf         475662

/u01/oracle/oradata/ora10g/sysaux01.dbf         475662

/u01/oracle/oradata/ora10g/users01.dbf         476689   476689

/u01/oracle/oradata/ora10g/mytbs01.dbf         476652

 

 

六、表空间扩容

查看表空间大小：

select tablespace_name,sum(bytes/1048576) curr_MB,sum(MAXBYTES/1048576) MAX_MB from dba_data_files group by TABLESPACE_NAME;

结果：

TABLESPACE_NAME    CURR_MB     MAX_MB

--------------- ---------- ----------

SYSAUX 240 32767.9844

UNDOTBS1 25 32767.9844

USERS 25 32767.9844

SYSTEM     480 32767.9844

MYTBS 10    10

如果最大大小为空 说明不能增长。

 

 

查询当前分配大小中的剩余尺寸：

SQL> select TABLESPACE_NAME,sum(bytes/1048576) free_mb from dba_free_space group by TABLESPACE_NAME;

结果：

TABLESPACE_NAME    FREE_MB

--------------- ----------

UNDOTBS1     4.0625

SYSAUX       .875

USERS    24.5625

SYSTEM      6.875

MYTBS     10.875

 

扩容的方法:

1、使文件自动增长

alter database datafile '' autoextend on next 10M maxsize 4G;

alter database datafile '' autoextend off;

2、 增加新文件

alter tablespace uses add datafile '' size 5M;

alter tablespace uses drop datafile ''; /*10G才能删 有数据不能删 第一个文件不能删 */

3、 修改现有文件的大小

alter database datafile '' resize 15M;

alter database datafile '' resize 10M;

 

删除表空间和数据文件

1、 删除表空间及其内容一起删除

drop tablespace mytbs INCLUDING CONTENTS;

2、 删除表空间及其文件一起删除

drop tablespace mytbs INCLUDING CONTENTS and datafiles;

 

注意：

创建索引或主键和唯一约束时将索引指定位置存放 生产库一定要把表和索引分开存放

创建索引和带索引的约束时, 指定索引的存储位置

SQL> create index t1_ind on t1(empno) tablespace mytbs;

SQL> ALTER TABLE T1 MODIFY(ENAME constraint T1_pk primary key using index tablespace mytbs);