/*
一、概念
表空间：是一个或多个数据文件的逻辑集合
表空间逻辑存储对象：
Ø 永久段-->如表与索引
Ø 临时段-->如临时表数据与排序段
Ø 回滚段-->用于事物回滚或闪回内存的撤销数据
表空间分类：系统表空间(system、sysaux)，非系统表空间
一个表空间至少包含一个数据文件，一个数据文件只能属于一个表空间。
不可或缺的几个表空间：
SYSTEM --->字典表空间，不能被损坏
UNDO --->dml把数据快照到此，数据提交即消失（用于恢复），只能有一个undo表空间起作用
SYSAUX --->10g 高并发系统繁忙时，会造成system争用，将工具放到SYSAUX,减轻system的压力，SYSAUX不影响系统（影响性能），存储awr，和system一样不可或缺，否则数据库不能正常运行
TEMP --->临时数据相关的内容，比如hashjoin，排序，pga容量不足是占用，只能有一个临时表空间起作用
USERS --->10g用户数据从system拨离出来，如果创建用户没有使用表空间所指定的默认表空间
Undo、temp表空间最好是分组，这样减少不同session出现争用。
从逻辑的角度来看，一个数据库（database）下面可以分多个表空间（tablespace）；一个表空间下面又可以分多个段（segment）；一个数据表要占一个段（segment），一个索引也要占一个段（segment）。一个段（segment）由多个区间（extent）组成，那么一个区间又由一组连续的数据块（data block）组成。这连续的数据块是在逻辑上是连续的，有可能在物理磁盘上是分散。
二、Oracle的存储结构
1.Schema: 用户--->创建相关对象、表、视图、序列、函数、存储过程、包等，其实你创建了用户，也就对应创建了这个用户的schema
2.逻辑结构：database数据库--->tablespace表空间---> segment段--->extent区间----> block块
逻辑结构是Oracle内部管理数据库中对象的方式
3.物理结构：OS block --->datafile物理结构通常是一系列数据文件
--查看scott用户的默认表空间及数据文件
SQL> select username,default_tablespace,temporary_tablespace from dba_users where username='XXX';
SQL> colname format a50
SQL>select t1.name,t2.name--查看表空间及数据文件
2 from v$tablespace t1,v$datafile t2
3 where t1.ts#= t2.ts#;
NAMENAME
/*
XXX--> table-->数据定义(位于)-->system表空间
数据(位于) -->user表空间(逻辑存储)-->表段-->区间-->内存块
-->索引段等-->区间-->内存块
user表空间(物理存储)-->user01.dbf
-->采用本地管理，包含头部信息，可用、已用等位图信息
当databuffer缓冲区满则调用dbwr进程将数据写入到物理文件当中 */
/*
三、创建表空间
--创建表空间的条件
1.具有create tablespace的权限，DBA角色具有该权限，sysdba,sysoper
2.创建的是bigfiel，还是smallifle超过T级别应考虑bigfile
3.新建的表空间的I/O，是否会导致磁盘I/O不够用
4.datafile后跟的路径应该具备写的权限 */
--查看缺省是BIGFILE还是SMALLFILE,当缺省为SMALLFILE,创建表空间不指定表空间类型则为SMALLFILE
SQL>select PROPERTY_NAME,PROPERTY_VALUEfrom database_propertieswhere PROPERTY_NAME like'%TBS%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TBS_TYPE SMALLFILE
--修改创建表空间为大或小表空间的默认值
SQL>alterdatabaseset default bigfile tablespace;
Database altered.
SQL> select PROPERTY_NAME,PROPERTY_VALUE from database_properties where PROPERTY_NAMElike'%TBS%';
PROPERTY_NAME PROPERTY_VALUE
------------------------------ --------------------------------------------------
DEFAULT_TBS_TYPE BIGFILE
--改回为缺省值
SQL>alterdatabaseset default smallfile tablespace;
Database altered.
--建议创建表空间时最好带上[BIGFILE | SMALLFILE]参数，以免参数设置导致创建了不合理的表空间
/*
注意：
大表文件（bigfile)最大可以存放个T的容量。头文件的大小达到了G－－＞block,普通的头文件大小为M---->block。
create bigfile tablespace <> datafile <>........;
好处：减少了数据文件的个数，管理方便，大的对象的存放得到了优化。减少了control文件的信息，控制文件定义了datafile的个数。
bigfile只能存在一个数据文件，所以要保证分配的的磁盘具有足够的空间。 */
创建表空间最好是统一区大小，另外使用自动段管理，减少碎片
--演示创建表空间
SQL>create tablespace TBS1 datafile'/u01/app/oracle/oradata/orcl/tbs_1.dbf'size 100m;
SQL>select t1.name,t2.namefrom v$tablespace t1,v$datafile t2
where t1.ts#= t2.ts#;
/*
创建临时表空间：
创建临时表空间，不能使用非标准数据块，另临时表空间不能存放永久对象。*/
CREATE TMEPORARY TABLESPACE tablespace_name
TEMPFILE '...'SIZE n
CREATE TEMPORARY TABLESPACE T2 TEMPFILE'/opt/oracle/oradata/orcl/t2.dbf'
size 100m;
/*
临时表空间具有以下特征：
临时数据文件不能置为只读
临时数据文件不能重命名
监时数据文件的日志方式总是NOLOGGING
临时表空间扩容：*/
--(1)重置临时文件大小
alterdatabase tempfile'/opt/oracle/oradata/orcl/t2.dbf' resize 150m;
--(2)让临时文件能自动扩展
alterdatabase tempfile'/opt/oracle/oradata/orcl/t2.dbf'
autoextend on next 10m maxsize 50m;
--(3)增加临时文件
alter tablespace t2add tempfile'/disk8/oracle/t2b.dbf'size 50m;
--查看临时文件
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE(MB)"
from dba_temp_files;
--默认临时表空间：
select*from database_properties;
--修改默认临时表空间：
ALTERDATABASEDEFAULT TEMPORARY TABLESPACE T2;
/*
默认临时表空间最好要指定一下，如果没有指定默认的临时表空间，那么将使用system表空间作为排序区----这个在10g之后已经用temp临时表空间取代，因为创建数据库的时候自动创建了一个
默认临时表空间有一定的限制：
默认临时表空间不能删除
默认临时表空间不能脱机
获得创建表空间的语句：*/
SELECT dbms_metadata.get_ddl('TABLESPACE','SYSTEM')FROM dual;
/*
临时表空间组：
10G引入的，是一组临时表空间，只能由临时表空间组成。组名不能与临时表空间同名
临时表空间不能显示的创建和删除，当把第一个临时表空间分配给某个临时表空间组的时候，自动创建
临时表空间组，将最后一个临时表空间，删除时，组也将自动删除。
创建临时表空间组：
1、创建临时表空间的时候 */
CREATE TEMPORARY TABLESPACE tablespace_name TEMPFILE'...'SIZE n
TABLESPACE GROUP group_name;
ALTER TABLESPACE tablespace_name TABLESPACEGROUP group_name;
ALTER TABLESPACE T3 TABLESPACE GROUP TEMP_GRP;
--查看临时表空间组的信息:
dba_tablespace_groups
SELECT*FROM DBA_TABLESPACE_GROUPS;
--将临时表空间组成员移除：
ALTER TABLESPACE tablespace_name TABLESPACEGROUP'';
--删除临时表空间：
--将所有成员全移出去，便自动删除
--创建UNDO表空间（还原表空间）：
/*
SQL> show parameter undo;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter tablespace undotbs1 retention guarantee;
Tablespace altered
创建语法：*/
CREATE UNDO TABLESPACE tablespace_name DATAFILE'...'SIZE n
SQL>CREATE UNDO TABLESPACE undo2
DATAFILE '/opt/oracle/oradata/orcl/undo2_01.dbf' SIZE 200M,
'/opt/oracle/oradata/orcl/undo2_02.dbf'size 100m;
--修改当前系统的UNDO表空间:
ALTER SYSTEMSET UNDO_TABLESPACE=tablespace_name
/*
UNDO表空间扩容：
（）重置DATAFILE大小
（）让DATAFILE能AUTOEXTEND
（）添加数据文件
删除UNDO表空间 */
DROP TABLESPACE tablespace_name
/*
注：当前的UNDO表空间不能删除
系统只能使用一个UNDO表空间 */
--创建表空间的详细语法：
/*
四、表空间的管理方式：
字典管理：oracle 8i(不包括i），只存在一种表空间的管理模式，即字典管理表空间（DMT）
DMT是指oracle的空间分配或回收是通过数据库中的数据字典表来记录和管理的
用于管理的两个数据字典表分别是：UET$（used extents)和FET$(freeextents)
其工作方式是：当建立一个新的段或者段在表空间时，oracle通过一系列的SQL语句来完成这个工作且和前面的两个字典表有关，在繁忙的系统中会造成竞争和等待（另一个DMT会带来的问题是空间碎片）
本地管理（LMT）： 在i的R2版本后成了默认的选项，可以使用extent management local uniform size 1M选项制定
LMT在表空间的数据文件头部加入了一个位图区域，在其中记录每个extent的使用状况当extent被使用或者被释放，oracle会更新头部的记录来反映这个变化，不产生回滚信息因为仅仅操作数据文件头部的几个数据块，不用操作数据字典，LMT比DMT要快，尤其是在繁忙的时候更明显
--查看表空间使用的管理方式： */
另外段的管理方式默认会使用自动，也可以制定
SEGMENT SPACE MANAGEMENT AUTO;这样就使用位图数组，而非之前的freelist链接列表管理了。避免了大并发的性能瓶颈
SQL>select TABLESPACE_NAME,EXTENT_MANAGEMENT,BLOCK_SIZE,STATUS,CONTENTS,FORCE_LOGGING,BIGFILEfrom dba_tablespaces;
--DMT 和LMT 的相互转换
--将字典管理的表空间转换为本地管理
exec dbms_space_admin.tablespace_migrate_to_local('表空间名')--表空间名用大写
--将本地管理的表空间转换为字典管理
exec dbms_space_admin.tablespace_migrate_from_local('表空间名')
--表空间改名：
--必须在open状态下，system , sysaux不能改名
ALTER TABLESPACE XXX RENAMETO YYY
--表空间的扩容：
/*
表空间的大小由组成它的所有数据文件的大小之和来决定
扩容的方法：*/
--(1)重置数据文件大小
ALTERDATABASE DATAFILE'...'| FileNo RESIZE XX
--(2)设置数据文件能自动增长
ALTER DATABASE DATAFILE '... '| FileNo AUTOEXTEND ON | OFF NEXT 20MMAXSIZE 300M;
--查看哪些表空间为自动增长
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLEFROM dba_data_files;
/* (3)添加数据文件
增加到表空间中的数据文件不能直接从表空间中删除，除非删掉整个表空间
增加数据文件将有助于均衡I/O
一个表空间文件最多为个，文件越多，执行一次检查点的代价越高 */
ALTER TABLESPACE tablespace_name ADD DATAFILE'...'SIZE XX;
ALTER TABLESPACE tbs2ADD DATAFILE'/u01/app/oracle/oradata/orcl/tbs2.dbf'size 200m autoextendon;
--表空间的四种状态：
online
offline
read only
read write
/*
offline：
一个表空间的正常状态是联机(ONLINE)，有时需要将某一个表空间进行脱机，以进行数据库维护
如：
在数据库打开的状态下移动数据文件
在数据库打开的情况下恢复一个表空间或数据文件
执行表空间的脱机备份（虽然可以进行进行联机备份）
使数据库某一部分不可访问，让其它部分能正常访问。
表空间脱机的时候，将产生检查点事件，该表空间上的数据是不能访问的
命令：*/
ALTER TABLESPACE tablespace_name OFFLINE;
--联机命令：
ALTER TABLESPACE tablespace_name ONLINE;
--在脱机后，有可能无法再联机，这时要做数据的介质恢复
ALTERDATABASE RECOVER TABLESPACE tablespace_name
ALTER TABLESPACE tablespace_name ONLINE;
/*
read only:
不能执行DML语句，可以使用的为DDL，DQL语句 */
ALTER TABLESPACE tablespace_nameREAD ONLY;
read write：
ALTER TABLESPACE tablespace_nameREAD WRITE;
--一些表空间的特殊状态
tablespacename onlie read only
system 必须online 必须read write
sysaux 可以offline 不能read only
undo 不能offline 不能read only
--查看表空间的状态
SQL>select tablespace_name,file#,v.status,v.enabledfrom dba_data_files d,
2 v$datafile v
3 where d.file_id= v.file#;
TABLESPACE_NAME FILE# STATUS ENABLED
------------------------------ ---------- ------- ----------
SYSTEM 1 SYSTEM READ WRITE
UNDOTBS1 2 ONLINE READ WRITE
SYSAUX 3 ONLINE READ WRITE
USERS 4 ONLINE READ WRITE
EXAMPLE 5 ONLINE READ WRITE
TBS1 6 ONLINE READ WRITE
--几种状态的相互转换的演示
--将表空间置为只读
SQL>alter tablespace usersread only;
Tablespace altered.
SQL>update scott.empset sal= sal + 50where ename='SCOTT';--置为只读后不可更新
update scott.empset sal= sal+ 50 where ename = 'SCOTT'
*
ERROR at line 1:
ORA-00372:file 4 cannot be modified at thistime
ORA-01110: datafile 4:'/u01/app/oracle/oradata/orcl/users01.dbf'
SQL>select tablespace_name,file#,v.status,v.enabledfrom dba_data_files d,
2 v$datafile v
3 where d.file_id= v.file#and tablespace_name ='USERS';
TABLESPACE_NAME FILE# STATUS ENABLED
------------------------------ ---------- ------- ----------
USERS 4 ONLINE READ ONLY
--将表空间置为脱机
SQL>alter tablespace users offline;
Tablespace altered.
SQL>update scott.empset sal= sal + 100where ename= 'SCOTT';/*置为脱机后不可更新*/
update scott.empset sal= sal+ 100 where ename= 'SCOTT'
*
ERROR at line 1:
ORA-00376:file 4 cannot beread at thistime
ORA-01110: datafile 4:'/u01/app/oracle/oradata/orcl/users01.dbf'
--将表空间置为联机
SQL>alter tablespace users online;
Tablespace altered.
SQL>select tablespace_name,file#,v.status,v.enabledfrom dba_data_files d,
2 v$datafile v
3 where d.file_id= v.file#and tablespace_name ='USERS';
TABLESPACE_NAME FILE# STATUS ENABLED
------------------------------ ---------- ------- ----------
USERS 4 ONLINE READ ONLY
--注意，readonly状态下的表空间可以删除数据，仅仅是不能插入或更新数据，如下
SQL>deletefrom scott.empwhere ename='SCOTT';
SQL>rollback;
Rollback complete.
--将表空间置为读写状态
SQL>alter tablespace usersread write;
--表空间数据文件的重命名或重定位
--方法一，在open状态下且处于archive模式:
--1、查出数据文件位置和所属的表空间
select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "SIZE(MB)"
from dba_data_files;
--2、将数据文件所在的表空间离线
alter tablespace uu offline;
--3、在操作系统下将文件移动到另外一个位置，或重命名(建议使用cp)
host mv /opt/oracle/oradata/orcl/tt.dbf/disk9/uu1.dbf
--4、修改控制文件，将数据文件的指针重新指向到另一个位置(执行rename)
alter tablespace uu rename datafile '/opt/oracle/oradata/orcl/tt.dbf'
to '/disk9/uu1.dbf';
--5、表空间在线
alter tablespace uu online
例如：
-- tbs
alter tablespace TBS offline;
host mv
alter tablespace tbs rename datafile 'C:\ORADATA\TBS_DATA03.DBF' to 'E:\oradata\TBS_DATA03.DBF';
alter tablespace TBS online;
-- datafile&tempfile
alter database tempfile 'C:\ORADATA\TEMP01.DBF' offline;
host mv
alter database rename file 'C:\ORADATA\TEMP02.DBF' to 'D:\ORADATA\ORCL\TEMP02.DBF';
alter database tempfile 'D:\ORADATA\TEMP02.DBF' offline;
--方法二，不能脱机的表空间 如果非归档模式，只能脱机表空间，脱机单独数据文件时候将出错 alter database datafile xxx offline;
--1、关闭数据库，再启动到mount状态
startup mount
--2、在操作系统下，移动或重命名文件
host mv /opt/oracle/oradata/orcl/tt.dbf/disk9/uu1.dbf
--3、修改控制文件
alter database rename file '/disk8/uu.dbf' to '/disk8/uu2.dbf';
--4、打开数据库
alter database open;
/*
删除表空间：
意味着表空间和数据文件的信息从数据库中删除，同时也应该将相应的操作系统文件删除
不能删除的表空间
系统表空间
有活动回滚段的表空间
默认临时/永久表空间
命令：*/
DROP TABLESPACE tablespace_name [INCLUDING CONTENTS [AND DATAFILES]]
INCLUDING CONTENTS -- 删除表空间及所有段
INCLUDING CONTENTS [AND DATAFILES] CASCADE CONSTRAINT--删除表空间、所有段、数据文件、同时删除系统中的物理文件
删除损坏的临时表空间
如果临时表空间中有一个文件坏块或者文件误删，可以新增一个文件在删除错误的临时表空间文件即可，比如temp表空间下的文件'D:/oracle/oradata/temp01.dbf'损坏可以执行如下步骤替换
alter tablespace temp add tempfile 'E:/oracle/oradata/temp02.dbf' size 50m;
alter tablespace temp drop tempfile 'D:/oracle/oradata/temp01.dbf';
--限制某个用户对表空间的使用：
ALTERUSERuser_name QUOTA nON tablespace_name;
ALTERUSER scott QUOTA 10mon UU;
--取消限制
ALTERUSERuser_name QUOTA UNLIMITEDON tablespace_name;
GRANT UNLIMITED TABLESPACETOuser_name;
--单个数据文件离线：
--1、查看有哪些数据文件
SELECT name FROM v$datafile;
--2、离线
ALTER DATABASE DATAFILE '...' OFFLINE;
--3、在线
ALTER DATABASE DATAFILE '... ' ONLINE
-- 有可能提示要进行介质恢复：
RECOVER DATAFILE '...' ；
ALTER DATABASE RECOVER TABLESPACE tablespace_name;
-- 之后再将文件再线
--重命名数据文件（同前面移动数据文件一样）
--获取表空间和数据文件信息
--表空间信息
dba_tablespaces
v$tablespace
--数据文件信息
dba_data_files
v$datafile
--临时数据文件信息
dba_temp_files
v$tempfile
--查看系统默认的表空间及临时表空间：
SQL>select PROPERTY_NAME,PROPERTY_VALUEfrom database_properties
2 where PROPERTY_NAMElike'DEFAULT%';
--修改改系统的默认表空间：
SQL>ALTERDATABASEDEFAULT TABLESPACE tablespace_name
--修改系统的默认临时表空间：
SQL>ALTERDATABASEDEFAULT TEMPORARY TABLESPACE tablespace_name
--查看用户默认表空间：
SQL>select USERNAME,USER_ID,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACEfrom dba_users;
--修改用户默认表空间：
SQL>ALTERUSERuser_nameDEFAULT TABLESPACE tablespace_name
--查看表空间的使用情况：
dba_data_files;
dba_free_space;
--例：查每个表空间的剩余空间
SQL>select tablespace_name,sum(bytes/1024/1024)
2 from dba_free_space group by tablespace_name;
/*
五、数据文件
数据文件是数据库中最重要的一个要求，是所有的数据库内容存放的地方
datafile是按照表空间为组织单位，表空间的构成是按照段区块为层次－－－数据文件的逻辑结构
datafile就是表空间的物理文件。
datafile中的内容是通过dbwr把data buffer cache中的dirty buffer data 写入的
dbwr触发：
ckpt触发，dirty buffer到一定量的时候，data buffer caceh的内存空间快用完了
查询空闲内存空间超时，表空间offline/read only,表的drop, truncate开始执备份表空间的时候 */
--查看数据文件
SQL>select file#,name,status,enabled,bytes/1024/1024 M from v$datafile;
SQL>select tablespace_name,file_name,user_bytes/1024/1024 Mfrom dba_data_files;
--数据文件的增加与删除
--为TBS1增加一个数据文件
SQL>alter tablespace tbs1add datafile'/u01/app/oracle/oradata/orcl/tbs1_3.dbf'size 10m autoextend on;
--还有一种创建表空间时不指定大小，而是重用一个存在的dbf要用resue参数，reuse是把原有的结构拿来用，里面的数据是用不了的，因为早都没有了。
--删表空间中的数据文件，g以后的版本才用以下功能，在之前版本的就只能把表空间给干掉。
SQL>alter tablespace tbs1drop datafile'/u01/app/oracle/oradata/orcl/tbs1_3.dbf';
--有数据的数据文件不能删。第一个不能删，如果要删就把表空间干掉，数据文件中有数据写入的文件也不能删除，如下
SQL>alter tablespace tbs1drop datafile 7;--7是v$datafile中的file#.
alter tablespace tbs1drop datafile 7
*
ERROR at line 1:
ORA-03262: thefileis non-empty
/*
数据文件的日志记录
只有temp表空间是nologing别的都是logging的
日志记录的几种模式
nologin 并不是不记录日志而是记录的很少
logging正常记录日志信息
force logging 记录的日志比logging还详细记录信息非常多用于DG data guard容灾的情况 */
SQL>select tablespace_name,logging,force_loggingfrom dba_tablespaces;
TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS01 LOGGING NO
EXAMPLE NOLOGGING NO
TBS1 LOGGING NO
--修改表空间中日志文件记录的方式
SQL>alter tablespace tablespace_name nologging| logging| force logging;
SQL>alter tablespace example logging;
Tablespace altered.
SQL>alter tablespace tbs1 force logging;
Tablespace altered.
SQL>select tablespace_name,logging,force_loggingfrom dba_tablespaces;
TABLESPACE_NAME LOGGING FOR
------------------------------ --------- ---
SYSTEM LOGGING NO
UNDOTBS1 LOGGING NO
SYSAUX LOGGING NO
TEMP NOLOGGING NO
USERS01 LOGGING NO
EXAMPLE LOGGING NO
TBS1 LOGGING YES
本文参考了http://blog.csdn.net/leshami/article/details/5611738，也添加修改了一些的内容。
扫一扫
261
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
