Oracle表空间(Tablespace) (转fw0124)

转自 http://blog.csdn.net/fw0124/article/details/6652305

tablespace是Oracle空间管理上的逻辑单位，实体上存放数据的是tablespace里面的Data File。
所以tablespace可以看成是Data File的群组。
tablespace可进一步分为段segments、区段extents和块blocks。
一个datafile只属于一个数据库的一个tablespace。

当数据库刚建立起来，系统会建立一个叫做SYSTEM的系统tablespace，存放SYS、SYSTEM等User重要的系统数据。
创建数据库后，需要创建一个表空间，例如USERS，然后
alter database default tablespace users;
给数据库指定一个默认表空间。（否则会使用SYSTEM作为默认表空间，这很不好）
如果我们建立Oracle User时，不指定默认的tablespace，
则此User则会以USERS作为默认表空间。
创建表格时，如果不指定所用的表空间，就会使用用户的默认表空间。

表空间类型
1)Permanent
一般我们创建的表空间都是Permanent表空间。

2)Undo
Undo tablespace是系统用的特殊的tablespace，
主要的功用是提供用户修改数据未commit之前的read consistency的功能以及rollback。

3)Temporary
Temporary tablespace也是系统用的特殊的tablespace。
当使用者需要做排序时，有时就会使用Temporary tablespace,
因此里面的Segement的生命周期都很短，会话结束就会消失。
每个系统都必须要有一个预设的Temporary tablespace（Default Temporary tablespace），
CREATE DATABASE语句的DEFAULT TEMPORARY tablespace子句创建一个默认的临时表空间。
Temporary tablespace是NOLOGGING模式，因此若数据库损毁，做Recovery不需要恢复Temporary tablespace。

查看Database的Default Temporary tablespace的方法：
SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE
PROPERTY_NAME='DEFAULT_TEMP_tablespace';
修改数据库的Default Temporary tablespace的方法：
alter database default temporary tablespace temp;

Extent管理
有两种方式：本地管理Local Managed与字典管理Dictionary Managed。
Local Managed与Dictionary Managed最主要的分别在于空间管理方式的不同。
Local managed的管理方式是让每个tablespace自己利用bitmaps去管理他自己的空间，
而Dictionary Managed则是利用SYSTEM tablespace的数据字典来做空间管理。
这两者最大的不同在于Local managed大大的改善了Oracle做空间管理时，抢夺SYSTEM tablespace资源的问题。
所以Oracle从8i以后已经朝Local managed的方向去走了，所以我们应该尽量使用Local managed的方式。

Local managed使用bitmaps做空间管理。
bitmaps中每个bit代表一个data block或者一堆相邻的data block(extent)。
从10g开始，SYSTEM tablespace预设使用local managed。
假如SYSTEM tablespace是local managed，那么其他tablespace必须是local managed。
若没指定使用local managed或者dictionary managed，则默认使用local managed。 
使用local managed则不需要做空间缝合(loalescing)，
因为相邻的不同大小的extent，辨识extent使用状态的bits也在一起，
Oracle可以直接使用这些相邻的extent。不需要先进行缝合才可以使用。这也可以增进部份效能。

另外还有1个参数可以指定extent的大小：AUTOALLOCATE或者UNIFORM
AUTOALLOCATE是让Oracle自己来决定extent的大小；
而UNIFORM则是强制规定tablespace中extent的为固定的大小。

通常若你明确的知道extent必须多大，才会使用UNIFORM，
使用UNIFORM的好处是每个extent的大小都相同，不会产生空间破碎的问题。
但是如果无法预知extent必须多大，使用AUTOALLOCATE会比较好，让Oracle自己决定使用extent的大小。
使用AUTOEXTENT,Oracle会使用的extent大小为64k、1M、8M、64M。
可以使用下面的语句查询tablespace所使用的Extent有几种：
select bytes,count(*) from dba_extents where tablespace_name='USERS' group by bytes;

Segment管理
本地管理的permanent表空间可以指定segment管理参数：AUTO与MANUAL。
MANUAL是使用我们熟悉的PCTUSED、FREELISTS、FREELIST GROUPS的方式来管理Segment中的使用和未使用的data block；
而AUTO则是使用bitmaps来管理使用和未使用的data block。上面这些参数都不使用了。
(PCTUSED、FREELISTS等参数的使用：http://blog.csdn.net/fw0124/article/details/6901819)

如果没有特别的需求话，使用AUTO会比使用MANUAL有更好的空间利用率与效能上的提升。
默认是采用自动段空间管理Automatic segment-space management and is the default.

创建permanent表空间
创建permanent表空间的完整命令:
Create [undo] tablespace <ts_name>
datafile <file_spec1> [,<file_spec2>]
mininum extent <m> k|m
blocksize <n> [k]
logging clause
force logging
default storage_clause
online | offline
extent_manager_clause
segment_manager_clause

1)undo指定系统将创建一个回滚表空间

2)tablespace指定表空间名称

3)datafile指定数据文件的路径、名称、大小及自增长状况.
具体形如
'E:\oracle\product\10.2.0\oradata\orcl\TEST.DBF' size 50M autoextend on next 10M maxsize 500M
可以指定autoextend off，就没有后面的递增和最大尺寸了.
可以在maxsize后面指定最大尺寸unlimited说明表空间无限大。

4)mininum extent <m> k|m指出在表空间的extent的最小值，这个参数仅用于字典管理的表空间。

5)blocksize <n> [k]设置块的大小，如果要设置这个参数，必须设置成db_block_size的整数倍；

6)logging cluse指示这个表空间上所有用户对象的日志属性，缺省是logging；
force logging指示表空间进入强制日志模式。
此时系统将记录表空间上对象的所有改变，除了临时段的改变。
这个参数高于logging参数中的nologging选项；

7）default storage_clause声明缺省的存储子句；

8)online|offline指定表空间状态；

9)extent_management_clause说明表空间如何管理extent。
Oracle创建的tablespace默认均是本地管理的。
明确的表述应该是EXTENT MANAGEMENT子句中指定 LOCAL。

可以用 AUTOALLOCATE 选项(默认)来使Oracle自动管理盘区：
CREATE tablespace users DATAFILE '/u02/oracle/data/users.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

也可以指定UNIFORM：
CREATE tablespace users DATAFILE '/u02/oracle/data/users.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
如果未指定盘区大小，则默认为1M，说明数据文件至少要大于1MB

10)segment_management_clause
setgment管理参数可以指定为AUTO或者MANUAL。例如：
CREATE tablespace users DATAFILE '/u02/oracle/data/users.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO;

表空间的状态
tablespace的状态有3种：ONLINE、OFFLINE、READ ONLY。
ONLINE是正常工作的状态，OFFLINE状态下，是不允许访问数据的，
SYSTEM tablespace和DEFAULT temp tablespace是不能被OFFLINE的，且带有active undo segments的tablespace也不能被OFFLINE。

切换ONLINE和OFFLINE状态的命令是
alter tablespace <name> offline/online。

当状态变成READ-ONLY时，会产生一个checkpoint，此时数据只能读不能写，但是可以drop对象，
相关命令是alter tablespace <name> read only和alter tablespace <name> read write。

表空间的存储设置
1、查看表空间的名称及大小

[sql] view plaincopyprint?

1. select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size  
2. from dba_tablespaces t, dba_data_files d  
3. where t.tablespace_name = d.tablespace_name  
4. group by t.tablespace_name;  

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name;

2、查看表空间物理文件的名称及大小

[sql] view plaincopyprint?

1. select tablespace_name, file_id, file_name,  
2. round(bytes/(1024*1024),0) total_space  
3. from dba_data_files  
4. order by tablespace_name;  
5.   
6. select tablespace_name, file_id, file_name,  
7. round(bytes/(1024*1024),0) total_space  
8. from dba_temp_files  
9. order by tablespace_name;  

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_data_filesorder by tablespace_name;select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_spacefrom dba_temp_filesorder by tablespace_name;

3、查看表空间的使用情况

[sql] view plaincopyprint?

1. select sum(bytes)/(1024*1024) as free_space,tablespace_name   
2. from dba_free_space  
3. group by tablespace_name;   
4. SELECT * from DBA_TEMP_FREE_SPACE;  

select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_spacegroup by tablespace_name; SELECT * from DBA_TEMP_FREE_SPACE;

4、检查数据文件的空间占用百分比：

[sql] view plaincopyprint?

1. SELECT total.tablespace_name, total_size/1024/1024, nvl(free_size, 0)/1024/1024,  
2. ROUND((total_size - nvl(free_size, 0)) / total_size, 4)*100 usage  
3. FROM  
4. (SELECT tablespace_name, SUM(bytes) total_size FROM DBA_DATA_FILES GROUP BY tablespace_name) total  
5. left outer join  
6. (SELECT tablespace_name, SUM(bytes) free_size FROM DBA_FREE_SPACE GROUP BY tablespace_name) free  
7. on total.tablespace_name = free.tablespace_name  
8. ORDER BY usage DESC;  

SELECT total.tablespace_name, total_size/1024/1024, nvl(free_size, 0)/1024/1024,ROUND((total_size - nvl(free_size, 0)) / total_size, 4)*100 usageFROM(SELECT tablespace_name, SUM(bytes) total_size FROM DBA_DATA_FILES GROUP BY tablespace_name) totalleft outer join(SELECT tablespace_name, SUM(bytes) free_size FROM DBA_FREE_SPACE GROUP BY tablespace_name) freeon total.tablespace_name = free.tablespace_nameORDER BY usage DESC;

5、查询每个用户占用数据文件的大小
select owner, sum(bytes)/1024/1024 from dba_extents group by owner;

6、修改tablespace的大小
主要是通过datafile的大小来实现的，修改datafile的大小又有3种方法：
1)使数据文件自增长；
表DBA_DATA_FILES中有一个字段AUTOEXTENSIBLE与这个方法对应，它指示数据文件是否自增长。
create tablespace test datafile 'E:\oracle\product\10.2.0\oradata\orcl\TEST.DBF' size 50M;
alter database datafile 'E:\oracle\product\10.2.0\oradata\orcl\TEST.DBF' autoextend on next 10M maxsize 100M;
（临时表空间用alter database tempfile...，下同）

2)改变数据文件大小；
alter database datafile 'E:\oracle\product\10.2.0\oradata\orcl\TEST.DBF' resize 100M;

3)添加数据文件。
这应该是最好的一种方式，便于管理。
alter tablespace test add datafile 'E:\oracle\product\10.2.0\oradata\orcl\TEST01.DBF'
size 50M autoextend on next 10M maxsize 100M;

7、移动数据文件
移动数据文件有2种方法，一种是使用alter tablespace命令，一种是使用alter database命令。

使用alter tablespace移动数据文件前，需要先将表空间OFFLINE，然后将需要移动的数据文件复制到目的地。
先执行命令：alter tablespace test offline;
然后将TEST01.DBF复制到目的目录，再执行命令：
alter tablespace test rename datafile 'E:\oracle\product\10.2.0\oradata\orcl\TEST01.DBF'
  to 'E:\oracle\product\10.2.0\oradata\TEST01.DBF';
然后再将表空间ONLINE就可以了：alter tablespace test online;

使用alter database移动数据文件时，且数据库需要处于MOUNTED状态。
先关闭数据库：shutdown immediate;
然后启动数据库，启动选项为mount：startup mount；
然后将TEST01.DBF复制到目的目录，再执行命令：
alter database rename file 'E:\oracle\product\10.2.0\oradata\TEST01.DBF'
  to 'E:\oracle\product\10.2.0\oradata\orcl\TEST01.DBF';
再打开数据库:alter database open。
(如果这个时候发生错误
ORA-01110:数据文件7：’E:ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST01.DBF’，
执行命令：
recover datafile ’E:ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\TEST01.DBF’。
提示完成介质恢复，再打开数据库：alter database open)

8、修改表格所在的表空间
alter table t move tablespace example;

删除表空间
删除表空间，使用命令drop tablespace <name>。
有3个选项：
INCLUDING CONTENTS:指示删除表空间中的segments；
INCLUDING CONTENTS AND DATAFILES:指示删除segments和datafiles；
CASCADE CONSTRAINTS:删除所有与该空间相关的完整性约束条件。
例如：
drop tablespace test INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

临时表空间
1、创建本地管理的临时表空间：
CREATE TEMPORARY tablespace temp TEMPFILE '/u02/oracle/data/temp01.dbf' SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

2、添加临时文件
ALTER tablespace temp ADD TEMPFILE '/u02/oracle/data/temp02.dbf' SIZE 20M REUSE;

3、改变临时文件状态
ALTER DATABASE TEMPFILE '/u02/oracle/data/temp02.dbf' ONLINE|OFFLINE;

4、更改临时文件大小
ALTER DATABASE TEMPFILE '/u02/oracle/data/temp02.dbf' RESIZE 4M;

5、取消临时文件并删除相应操作系统文件
ALTER DATABASE TEMPFILE '/u02/oracle/data/temp02.dbf' DROP
INCLUDING DATAFILES;

 

删除数据文件

使用offline数据文件的方法
非归档模式使用：alter database datafile '...' offline drop;
归档模式使用： alter database datafile '...' offline;

说明：
1) 以上命令只是将数据文件的状态更改为recover，而不是在数据库中删除数据文件。
offline drop命令相当于把一个数据文件至于离线状态，并且需要恢复，并非删除数据文件
该数据文件的信息在控制文件种仍存在。查询v$datafile，仍显示该文件。
2) offline后，存在此datafile上的对象将不能访问
3) 归档模式下offline和offline drop效果是一样的, offline 之后多需要进行recover 才可以online。
4) 如果是非归档模式，只能是offline drop. 因为非归档模式没有归档日志文件，无法进行recover操作。
当然，只要online redo日志没有被重写，可以对这个文件recover后进行online操作。

Oracle 10G R2开始，可以采用
Alter tablespace <name> drop datafile file_name;
来删除一个空数据文件，并且相应的数据字典信息也会清除.