需要弄清的几个关系和概念:
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已经不存在了。
(第八章完)