oracle表空间

tablespace 表空间【sysaux 系统辅助表空间（自动优化）】

临时表空间

存放排序的中间结果或临时表
--创建临时表空间
CREATE SMALLFILE TEMPORARY TABLESPACE "TEMPTEST" TEMPFILE '/u01/app/oracle/oradata/orcl/temptest.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
--修改数据库默认临时表空间
alter database default temporary tablespace temp;
--修改用户临时表空间
alter user test01 temporary tablespace temptest;
--查询数据库默认配置
select * from database_properties where property_name like 'DEFAULT_%_TABLESPACE';

--创建用户指定默认表空间和临时表空间
create user test01 identified by test01 
default tablespace test   --10g
temporary tablespace temp  --9i
quota 10M on users;
-- 查询用户表空间
select u.username,u.default_tablespace,u.temporary_tablespace  from dba_users u where username='SCOTT'

永久表空间

创建表空间（使用EM生成sql语句）

-- 数据库文件后缀没有要求，最好加上.dbf smallfile 最大32G 
CREATE SMALLFILE TABLESPACE "TEST" DATAFILE '/u01/app/oracle/oradata/orcl/test01.dbf' 
SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO 
-- for online【使用大文件表空间】 最好在有底层强大存储支持（如ASM）的情况下使用Bigfile，达到最优的性能平衡点。很少使用,不利于IO分散,容易产生争用
CREATE BIGFILE TABLESPACE "LIULI.DBF" DATAFILE '/u01/app/oracle/oradata/orcl/liuli.dbf'
SIZE 100M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
--修改表空间无限制
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/orcl/test01.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;
--添加数据文件  ；[测试证明如果有过个数据文件 存储时 是平均分配的] 
ALTER TABLESPACE "TEST" ADD DATAFILE '/u01/app/oracle/oradata/orcl/test02.dbf' SIZE 100M AUTOEXTEND ON NEXT 25M MAXSIZE UNLIMITED

UNDO表空间

撤销表空间规划方案
1.业务开始表空间自动扩展
2.业务稳定，关闭自动扩展（设置为最大值的4倍），设置 undo_retention 的值

show parameter undo;

undo_management=AUTO 回滚表空间段的段管理模式，管理员只需要备足够的表空间容量，oracle会自动管理扩展回滚段的数量。只能使用一个UNDO表空间。
undo_tablespace:只有在自动管理模式下才可以使用。指明使用哪个UNDO表空间
undo_retention＝900 ：
提交之后旧的镜像保持在回滚段中的时间。
非强制的回退保持时间.(回滚空间不足老的镜像就会被覆盖)

作用:

1.交易的回滚:没有提交的交易可以rollback
2.交易的恢复:数据库崩溃时,将磁盘的不正确数据恢复到交易前
3.读一致性 :被查询的记录有事务占用,转向回滚段找改前镜像
4.闪回数据 :从回滚段中构造历史数据
–创建撤销表空间 CREATE SMALLFILE UNDO TABLESPACE “UNDOTBS1” DATAFILE ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’ SIZE 90M REUSE
AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M

Oracle 的Undo有两种方式： 一是使用undo 表空间，二是使用回滚段.
我们通过 undo_management 参数来控制使用哪种方式，如果设为auto，就使用UNDO 表空间，这时必须要指定一个UNDO 表空间。 如果设为man l，系统启动后使用rollback segment方式存储undo信息。如果系统没有指定undo_management，那么系统默认以man l方式启动,即使设置了auto方式的参数，这些参数将被忽略。

UNDO_RETENTION，该参数用来指定undo 记录保存的最长时间，以秒为单位，是个动态参数，完全可以在实例运行时随时修改，通常默认是900 秒，也就是15 分钟。undo_retention 只是指定undo 数据的过期时间，并不是说，undo 中的数据一定会在undo表空间中保存15 分钟，比如说刚一个新事务开始的时候，如果undo 表空间已经被写满，则新事务的数据会自动覆盖已提交事务的数据，而不管这些数据是否已过期，因此呢，这就又关联回了第一点，当你创建一个自动管理的undo 表空间时，还要注意其空间大小，要尽可能保证undo 表空间有足够的存储空间。

管理表空间

-- 只读表空间
alter tablespace tbs01 read only;
alter tablespace tbs01 read write;
--删除表空间：
drop tablespace tbs01 including contents and datafile;
--离线状态DBA可以移动数据文件路径 
alter tablespace tbs01 offline;
alter tablespace mytbs online;

alter table table_name move 表相关的索引会失效，
所以之后还要执行 alter index index_name rebuild online; 最后重新编译数据库所有失效的对象
alter table table_name shrink space cascade 时，
他相当于 alter table table_name move 和 alter index index_name rebuild online

1. Move会移动高水位，但不会释放申请的空间，是在高水位以下(below HWM)的操作。
2. shrink space 同样会移动高水位，但也会释放申请的空间。

–查看表空间使用情况

select 
b.file_name,b.tablespace_name，b.bytes/1024/1024 大小M,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024  已使用M,
substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5)  利用率 
from dba_free_space a,dba_data_files b 
where a.file_id=b.file_id 
group by b.tablespace_name,b.file_name,b.bytes 
order by b.tablespace_name;