Oracle——操作表空间

一、简介：

      一个大的数据中分为几个表空间，创建几个用户然后指定对应的表空间并授权，这样用户就独立操作自己的资源了，每每用户登录进入后，在自己的表空间中新建表啊等等对象，互不干扰。

数据表空间 (Tablespace)：存放数据总是需要空间， Oracle把一个数据库按功能划分若干空间来保存数据。当然数据存放在磁盘最终是以文件形式，所以一盘一个数据表空间包含一个以上的物理文件数据表。

临时表空间：临时表空间主要放置一些临时数据，比如你查询一个复杂的sql语句，系统会将中间数据放在临时表空间里暂存。临时表空间会自己删除（可以选择会话结束就删除）

--1G=1024MB 
--1M=1024KB 
--1K=1024Bytes 
--1M=1048576Bytes 
--1G=1024*1048576Bytes=1073741824Bytes 

二、操作表空间

查看所有表空间：SELECT * From dba_tablespaces;

字段：
blocksize：数据块的大小，默认为：8kb
logging:将日志写入日志文件。
online,offline 联机脱机
segment space management 段空间管理方式
flashback 表空间是否可闪回
当表空间使用本地管理且段空间为字段管理时，表空间才能使用大文件表空间。（本地管理的撤销表空间和临时表空间除外。）

数据表空间：

--查看所有表空间
SELECT * From dba_tablespaces;
--查看数据表空间
select distinct  tablespace_name from  SYS.DBA_FREE_SPACE
--查看数据表空间物理文件
SELECT * From dba_data_files;
--创建数据表空间
CREATE TABLESPACE LYTP LOGGING DATAFILE 'E:\Tablespace\LYTP.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 100M EXTENT MANAGEMENT LOCAL;
--重命名表空间
ALTER TABLESPACE LYTP  RENAME TO NFTP
--删除数据表空间
Drop tablespace LYTP including contents and datafiles;  

    注： 删除表空间,删除后，如果物理文件没删除，手动删除

创建表空间：CREATE TABLESPACE LYTP LOGGING DATAFILE 'E:\LYTP.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE 100M EXTENT MANAGEMENT LOCAL;

其中：
logging 声明这个表空间上所有的用户对象的日志属性（缺省是logging）
autoextend on 表空间大小不够用时自动扩展
next 32m 自动扩展增量为32MB 
MAXSIZE 100M 表空间最大值为100M(maxsize unlimited:表空间大小最大值没有限制)
extent management local 区管理本地化

SEGMENT SPACE MANAGEMENT {AUTO|MANUAL}:段空间管理的方式，自动或者手动:(默认auto)

临时表空间：

--查看临时表空间
SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS "FREE SPACE(M)" FROM DBA_TEMP_FREE_SPACE;
--查看临时表空间物理文件
SELECT* FROM DBA_TEMP_FILES;
--查看临时表空间文件
select * from v$tempfile;


--创建临时表空间
CREATE TEMPORARY TABLESPACE TEMP_LYTP TempFILE 'E:\Tablespace\TEMP_LYTP.dbf' SIZE 32M AUTOEXTEND ON NEXT 32M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL;
--查询当前数据库默认临时表空间名
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
--修改默认表空间为刚刚建立的临时表空间
alter database default temporary tablespace TEMP_LYTP;
--查看用户所用临时表空间的情况
SELECT USERNAME,TEMPORARY_TABLESPACE FROM DBA_USERS;
--删除原来的临时表空间
DROP TABLESPACE TEMP_LYTP including contents and datafiles;
--查看临时表空间名确认临时表空间是否已删除
select * from DBA_TEMP_FREE_SPACE;  

三、操作数据文件：

为空间不足的表空间增加数据文件：ALTER TABLESPACE &tablespace_name ADD DATAFILE '&datafile_name' SIZE 50M;
如果要为临时表空间扩容，使用下面的语句：

ALTER TABLESPACE &tablespace_name ADD TEMPFILE '&datafile_name' SIZE 50M;

--为表空间增加数据文件
ALTER TABLESPACE LYTP ADD DATAFILE 'E:\Tablespace\LYTP2.dbf' SIZE 50M AUTOEXTEND ON NEXT 32M MAXSIZE 200M;
--查询表空间包含多少数据文件
select file_name, tablespace_name from dba_data_files where tablespace_name ='LYTP';
--删除数据文件
ALTER TABLESPACE LYTP DROP DATAFILE 'E:\Tablespace\LYTP2.dbf';

****如果不是Oracle11gR2版本，数据文件是无法删除的，要删除数据文件连同表空间一起删除。

语法：ALTER TABLESPACE 表空间名 DROP DATAFILE 数据文件名;

四、查看表空间大小及使用情况

SELECT a.tablespace_name "表空间名称",
       total / (1024 * 1024) "表空间大小(M)",
       free / (1024 * 1024) "表空间剩余大小(M)",
       (total - free) / (1024 * 1024) "表空间使用大小(M)",
       total / (1024 * 1024 * 1024) "表空间大小(G)",
       free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
       (total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
       round((total - free) / total, 4) * 100 "使用率 %"
  FROM (SELECT tablespace_name, SUM(bytes) free
          FROM dba_free_space
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) total
          FROM dba_data_files
         GROUP BY tablespace_name) b
 WHERE a.tablespace_name = b.tablespace_name

五、创建表空间语法详解

              原文链接1                    原文链接2

语法：

CREATE [UNDO]  TABLESPACE tablespace_name          
[DATAFILE datefile_spec1 [,datefile_spec2] ......   
 [{MININUM EXTENT integer [k|m]   
 |  BLOCKSIZE integer [k]   
 |  logging clause | FORCE LOGGING   
 |   DEFAULT {data_segment_compression} storage_clause   
 |  [online|offline]   
 |  [PERMANENT|TEMPORARY]   
 |  extent_manager_clause   
 |  segment_manager_clause}
 ]]

1、undo

当没有为系统指定回滚表空间时,系统将使用system系统回滚段来进行事务管理。

2、tablespace_name  

指出表空间的名称。

3、datafile  datefile_spec1   
指出表空间包含什么空间文件。
datefile_spec1是形如:[ 'filename' ] [SIZE integer [ K | M ]] [REUSE] [autoextend_clause]
[autoextend_clause]是形如：AUTOEXTEND { OFF | ON [ NEXT integer [ K | M ] ] [maxsize_clause] }   
其中filename是数据文件的路径名，可以是相对路径，也可以是绝对路径。   
size是文件的大小,   
REUSE表示文件是否被重用.   
AUTOEXTEND表明是否自动扩展.   
OFF | ON  表示自动扩展是否被关闭.   
NEXT 表示数据文件满了以后,扩展的大小.   
maxsize_clause表示数据文件的最大大小.形如MAXSIZE { UNLIMITED | integer [ K | M ] }.   
UNLIMITED 表示无限的表空间.   
integer是数据文件的最大大小.   
       
4、MININUM EXTENT integer [k|m]   
指出在表空间中范围的最小值。这个参数可以减小空间碎片，保证在表空间的范围是这个数值的整数倍。     
5、BLOCKSIZE integer [k]   
这个参数可以设定一个不标准的块的大小。如果要设置这个参数，必须设置db_block_size,   
至少一个db_nk_block_size,并且声明的integer的值必须等于db_nk_block_size.   
注意：在临时表空间不能设置这个参数。   
  
6、logging clause   
这个子句声明这个表空间上所有的用户对象的日志属性（缺省是logging），   
包括表，索引，分区，物化视图，物化视图上的索引，分区。   
//   
7、FORCE LOGGING   
使用这个子句指出表空间进入强制日志模式。此时，系统将记录表空间上对象的所有改变，除了临时段的改变。   
这个参数高于对象的nologging选项。   
注意：设置这个参数数据库不行open并且出于读写模式。而且，在临时表空间和回滚表空间中不能使用这个选项。   
//   
8、DEFAULT storage_clause   
声明缺省的存储子句。   
//   
9、online|offline   
改变表空间的状态。online使表空间创建后立即有效.这是缺省值.   
offline使表空间创建后无效.这个值，可以从dba_tablespace中得到。   
//   
10、PERMANENT|TEMPORARY   
指出表空间的属性，是永久表空间还是临时表空间。   
永久表空间存放的是永久对象，临时表空间存放的是session生命期中存在的临时对象。   
这个参数生成的临时表空间创建后一直都是字典管理，不能使用extent management local选项。   
如果要创建本地管理表空间，必须使用create temporary tablespace   
注意，声明了这个参数后，不能声明block size   
//   
11、extent_management_clause   
这是最重要的子句，说明了表空间如何管理范围。一旦你声明了这个子句，只能通过移植的方式改变这些参数。   
如果希望表空间本地管理的话，声明local选项。本地管理表空间是通过位图管理的。   

12、segment_management_clause   
段空间管理的方式，自动或者手动:   
SEGMENT SPACE MANAGEMENT {AUTO|MANUAL}