Oracle 11g表空间和数据文件管理(《Oracle从入门到精通》读书笔记3)

需要弄清的几个关系和概念：

1. 表空间和数据文件的关系：

通过第二章（Oracle的结构体系）可以做出如下对应关系图：

除了日志文件，和表空间相关的所有系统和用户数据信息都记录在这个数据文件（DBF）中；查询表空间和其DBF文件对应关系的语句如下：

col tablespace_name for a10
col file_name for a50
col bytes for 999,999,999
select tablespace_name,file_name,bytes/1024/1024 as MB from dba_data_files order by tablespace_name;

2. 默认表空间：

一般有6个，在第一篇文章里写过其中的4个：

点击打开链接

此外还有Example（安装时如果选择“实例方案”则会出现）和TEMP（隐藏存在，一般不会显示出来）表空间。

表空间管理

一、创建表空间：

在创建表空间前需要考虑一下4点：

·是要创建小文件表空间，还是大文件表空间（默认小）；

·是使用局部盘区管理方式，还是传统的 目录盘区管理（默认局部）；

·是手动管理段空间，还是自动管理段空间（默认自动）；

·创建的表空间是否是用于临时段或撤销段的特殊表空间。

创建表空间的语法格式：

create [smallfile|bigfile] tablespace tablespace_name

datafile '\filepath' size num K|M reuse

[,\filepath size num K|M reuse]

[,...]

[autoextend [on|off] next numK|M

[maxsize numK|M |unlimited]

[mininum extent numK|M]

]

[default storage storagename]

[online|offline]

[logging|nologging]

[permanent|temorary]

[extent management dictionary | local [autoallocate|uniform size numK|M]]

;

参数及关键字解释：

tablespace_name：要创建的表空间名称

datafile '\filepath' size num K|M ：表空间数据文件的路径与名字、大小

[smallfile|bigfile] ：是要创建小文件表空间，还是大文件表空间（默认小）

reuse：若对应路径的dbf文件出现重名，则清除后再创建；否则直接创建；

autoextend [on|off] next numK|M：dbf文件是自动扩展还是非自动扩展，非自动的话需要通过next设置扩展值；

[maxsize numK|M |unlimited]：如果autoextend为on，表示dbf文件每次自动扩展的最大字节数，或无限扩展；

[mininum extent numK|M]：如果autoextend为on，表示dbf文件每次自动扩展的最小字节数，也就是OS格式化磁盘时的“分配单元大小”字段值；

[default storage storage]：指定以后要创建的表、索引、簇的存储参数值，这些参数将影响以后这些对象的存储参数值；

[online|offline]：指定创建的表空间在线|离线

[logging|nologging]：表空间在加载数据时是否产生日志，默认logging

[permanent|temorary]：创建永久表空间|临时表空间

[extent management dictionary | local [autoallocate|uniform size numK|M]]：表空间的扩展方式是本地化管理还是数据字典管理，默认local 

1. 通过本地化管理方式创建一个10MB、扩展大小为256KB的表空间：

create tablespace test_2 datafile 'c:\tsdbf\ts1.dbf'

size 10m

extent management local uniform size 256K;

2. 通过段空间管理方式创建表空间：这是建立在本地化空间管理方式基础之上的，是为了往后兼容而保留的

（1）手动段空间管理方式

create tablespace tb_3 datafile 'c:\tsdbf\ts3.dbf'

size 10M

extent management local autoallocate

segment space management manual;

（2）自动段空间管理方式

create tablespace tb_4 datafile 'c:\tsdbf\ts4.dbf'

size 10M

extent management local autoallocate

segment space management auto;

*注：自动段空间管理方式不能用于创建临时表空间和系统表空间；而oracle是推荐使用自动断空间管理方式去管理表空间的（但是默认参数确实manual，因此在创建表空间时要明确指定为auto）

3. 创建非标准块表空间：默认的数据块大小为8192B=8KB，但可以通过下列语句创建非标准块（需要是时8KB的整数倍）表空间

alter system set db_16k_cache_size=16m scope=both;

create tablespace tb_5 datafile 'c:\tsdbf\ts5.dbf'

size 10M reuse

autoextend on next 4m maxsize unlimited

blocksize 16k

extent management local autoallocate

segment space management auto;

*注意这里要提前修改 db_16k_cache_size参数，否则创建时会报错。

*修改数据文件大小的语句：

alter database datafile 'c:\tsdbf\ts5.dbf' resize 11m;

4. 创建大文件表空间（为超大型数据库设计）

create bigfile tablespace bigtb_1 datafile 'c:\tsdbf\bigts1.dbf' size 1g;

由于大文件表空间只有一个数据文件，可以很方便的修改其大小：

alter tablespace bigtb_1 resize 2g;

二、维护表空间和数据文件

1. 设置默认表空间：在oracle中创建用户时，如果不指定表空间，则默认使用临时表空间temp和永久表空间system，这样会影响系统的执行效率。好的办法是先计划并创建好表空间，然后在创建用户时指定：

CREATE USER username IDENTIFIED BY password
DEFAULT TABLESPACE TEST_DATA
TEMPORARY TABLESPACE TEST_TEMP;

设置默认临时表空间的语句：

create temporary tablespace temp_test tempfile 'c:\tsdbf\temp1.dbf' size 10m;
alter database default temporary tablespace temp_test;

*被设置的临时表空间属性必须为临时表空间。

设置默认永久表空间：

alter database default tablespace tb_5;

2. 更改表空间状态：首先需要该表空间满足

·online状态；

·不包含任何回滚段；

·表空间不是归档模式；

更改某表空间为只读状态：

alter tablespace tablespacename read only;

更改表空间为可读写状态：

alter tablespace tablespacename read write;

3. 重命名表空间

*注：不能对system和sysaux表空间重命名，也不能对处于offline状态的表空间重命名。重命名后，原表空间中存放的数据对象（表、索引、簇等）会被存放到新表空间名下。

alter tablespace tablespacename rename to new_tablespacename;

4. 删除表空间

drop tablespace tablespacename [including contens] [cascade constraints];

[including contens]：表示删除表空间的同时删除其中存放的数据；如果不加、而表空间中又存在数据的话，则会报错；

[cascade constraints]：删除的同时删除相关的完整性限制（包括主键及唯一索引等），如果不加、而表空间中又存在完整性限制，则删除时会报错。

5. 维护表空间中的数据文件

（1）向表空间添加新DBF文件：

alter tablespace tablespacename add datafile 'filepath\new.dbf' size 10m autoextend on next 5m maxsize unlimited;

（2）删除数据文件：

alter tablespace tablespacename drop datafile 'filepath\new.dbf' ;

（3）对数据文件的自动扩展设置

·可以在创建表空间的时候设置（如（1）所示）

·可以在创建数据库时设置：

create database {ORACLE_NAME}　　 //数据库名，一般与ORACLE_SID相同
user sys identified by {密码}       //不设置，则默认为“change_on_install”
user system identified by {密码}    // 不设置，则默认为“manager”
maxlogfiles 5       //最大日志组数
maxlogmembers 5     //日志组中最多成员数
maxloghistory 1    //（RAC环境下有效，暂不深入）
maxdatafiles 100   // 最大数据文件数
logfile group 1 ('/u01/app/oracle/oradata/orcl/redo01a.log',
                 '/u01/app/oracle/oradata/orcl/redo01b.log',
                 '/u01/app/oracle/oradata/orcl/redo01c.log') size 20M,
        group 2 ('/u01/app/oracle/oradata/orcl/redo02a.log',
                 '/u01/app/oracle/oradata/orcl/redo02b.log',
                 '/u01/app/oracle/oradata/orcl/redo02c.log') size 20M,
        group 3 ('/u01/app/oracle/oradata/orcl/redo03a.log',
                 '/u01/app/oracle/oradata/orcl/redo03b.log',
                 '/u01/app/oracle/oradata/orcl/redo03c.log') size 20M
                              // 创建日志组，及组中成员
character set WE8ISO8859P1     //数据库字符集
national character set UTF8    // 国家字符集
extent management local

//指定system表空间中，扩展段的管理方式 [ local | dictionary ]

// 使用“本地管理(local)”，即位图管理方式。（见后面的“Oracle体系结构”部分）
// 设置为“本地管理”时，必须同时指定扩展块的大小（两种方式）：
         1、UNIFORM SIZE（统一大小）{单位：“K” 或 “M”}；
         2、AUTOALLOCATE（自动分配）
// 默认为“字典管理(dictionary)”方式。

·可以在alter tablespace中设置

*查询当前表空间DBF文件是否为自动扩展：

col file_name for a50;

select file_name,autoextensible from dba_data_files where tablespace_name='TEST_2';

如果查询结果为NO，则用以下语句改为自动扩展：

alter database datafile 'C:\TSDBF\TS1.DBF' autoextend on next 10m maxsize unlimited;

关闭自动扩展：

alter database datafile 'C:\TSDBF\TS1.DBF' autoextend off;

三、管理撤销表空间

1. 也就是UNDO表空间，他的作用：

（1）使不同的用户进程检索数据时、保持数据的一致性；

（2）可以通过rollback语句回滚事物段；

（3）恢复事物：这是由Oracle的SMON进程自动执行的，在断电、内存故障时会触发。

（4）闪回操作（后面会写）

2. Undo表空间的初始化参数：

（1）show parameter undo_tablespace;

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------
undo_tablespace                      string      UNDOTBS1

查看当前实例使用的undo表空间名（为undotbs1）

（2）show parameter undo_management;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
undo_management                      string      AUTO

当前undo数据的管理模式；auto-自动撤销管理模式，manual-回滚段管理模式

*注：使用auto模式时，如果没有指定undo_tablespace，则会选择第一个可用的 undo表空间存放undo数据；如果没有可用的undo表空间，则直接使用sustem回滚段存放undo记录、并在alter文件中记录警告。

（3）show parameter undo_retention;

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
undo_retention                       integer     900

控制undo数据的最大保留时间，默认900秒；这也决定了闪回操作可以闪回的最早时间点。

*注：修改这些参数的语句：alter system set parameter_name=XXX;

3. undo表空间的基本操作：

（1）创建undo表空间：

create undo tablespace undo_tbs_2

datafile 'C:\TSDBF\undo_tbs_2.DBF'

size 10m autoextend on next 10M;

（2）给undo表空间添加DBF文件：和普通表空间操作语句一样；

还可以用如下办法对undo数据文件进行热备份：

SQL> alter tablespacetablespacenamebegin backup;

表空间已更改。

SQL> host copy C:\TSDBF\UNDO_TBS_2.DBF C:\TSDBF\UNDO_TBS_2backup.dbf
已复制         1 个文件。//这里是window的复制命令；linux系统的复制命令为cp

SQL>  alter tablespace undo_tbs_2 end backup;
表空间已更改。

*注：进行undo数据的热备份操作需要数据库开启归档模式，详情请参见（点击打开链接）；如果不开，会报ora-01123错误。

（3）切换undo表空间：

alter system set undo_tablespace = undo_tablespace_name;

（4）删除undo表空间：

drop tablespace tablespace_name;

*注：删除正在使用的表空间需要进行切换后才能删除。

（5）查询Undo表空间信息：

--查询回退块的生成信息：

select to_char(begin_time,'hh24:mi:ss') as starttime,

to_char(end_time,'hh24:mi:ss') as endtime,

undoblks as undo_block_num

from v$undostat order by begin_time;

--显示undo段统计信息：

select n.name,s.xacts,s.writes,s.extents from v$rollname n,v$rollstat s where n.usn=s.usn;

--显示undo区信息：

select segment_name,extent_id,bytes,status from dba_undo_extents where segment_name = '_SYSSMU1_1518548437$';

SEGMENT_NAME                    EXTENT_ID      BYTES STATUS
------------------------------ ---------- ---------- ---------
_SYSSMU1_1518548437$                    0      65536 EXPIRED
_SYSSMU1_1518548437$                    1      65536 EXPIRED
_SYSSMU1_1518548437$                    2    1048576 EXPIRED
_SYSSMU1_1518548437$                    3    1048576 UNEXPIRED

*注：其中EXPIRED表示该区未使用，active表示处于活动状态

--显示活动事务信息：

select name,status from v$transaction;

四、管理临时表空间：

1. 临时表空间有数据库更具需要创建、管理和删除，供临时段使用；下面的操作会经常用到临时表空间：

·select distinct

·union联合查询

·minus

·analyze分析

·链接两个没有索引的表

2. 临时表空间相关操作：

（1）创建：

create temporary tablespace temp_01 tempfile 'C:\temp_01.tpf' size 10m;

（2）改名：

alter tablespace temp_01 rename to new_temp_01;

（3）添加临时数据文件（.TPF文件）：

alter tablespace new_temp_01 add tempfile 'C:\temp_02.tpf' size 10M;

（4）删除临时表空间：

drop tablespace new_temp_01;

（5）设置某临时表空间为当前临时表空间：

其他操作和一般表空间类似，请参考前文。

3. 临时表空间信息全部存在dba_temp_files字典中，可以通过v$tempfiles视图查询：

select * from dba_temp_files;

4. 临时表空间组：

（1）顾名思义，就是把多个临时表空间组成一个组，以解决：

·因大量排序数据而导致单一临时表空间不足；

·当一个用户同时有多个会话时，可以使用族中的不同临时表空间；

·使并行的服务器在单节点上能够使用多个临时表空间。

（2）创建临时表空间的时候就将其添加到某个临时表空间组中：

create temporary tablespace temp_01 tempfile 'C:\temp_01.tpf' size 10m tablespace group group1;

create temporary tablespace temp_02 tempfile 'C:\temp_02.tpf' size 10m tablespace group group1;

create temporary tablespace temp_03 tempfile 'C:\temp_03.tpf' size 10m tablespace group group2;

create temporary tablespace temp_04 tempfile 'C:\temp_04.tpf' size 10m tablespace group group2;

（3）将某个临时表空间从1组转移到2组：

alter tablespace temp_03 tablespace group group1;

（4）查询当前数据库的临时表空间组及其下包含的临时表空间：

select * from dba_tablespace_groups;

（5）把某个组分配给指定用户使用：

alter user sys temporary tablespace group1;

（6）设置默认临时表空间组：

先查当前的实例名： select * from v$instance;

alter database orcl default temporary tablespace group1;

（7）删除临时表空间组，需要删除改组下的所有临时表空间，以上述的group2为例：

drop tablespace temp_04 including contents and datafiles;

这是再查询select * from dba_tablespace_groups;会发现group2已经不存在了。

（第八章完）