时间：2021-07-01 10:21:17 帮助过：11人阅读
oracle删除(释放)数据文件/表空间流程 生产环境：数据库里空间不足，niptest表空间251G，只使用了17G 再alter database datafile '...../niptest1' resize 10G;的时候说超出了范围要求 由于表变动比较频繁，高水位比较大 ( 高水位HWM “High Water Mark “：
oracle删除(释放)数据文件/表空间流程
生产环境：数据库里空间不足，niptest 表空间251G，只使用了17G
再alter database datafile '...../niptest1' resize 10G; 的时候说超出了范围要求
由于表变动比较频繁，高水位值比较大
(高水位 HWM “High Water Mark “：oracle中block有没有使用的分界线，它会随着数据的insert而上升，但它并不会随数据的delete而下降，因此全表扫描的时间并不因数据的delete而减少，相反可能由于块清除反而全表扫描时间增加）
删除表空间步骤：
1) 批量将niptest表空间中的表move 到USERS表空间，再删除表空间niptest
首先看下此表空间内的表 move到其他表空间 防止数据丢失
select * from dba_tables where tablespace_name='NIPTEST';
select * from dba_extents where tablespace_name='NIPTEST';
select * from dba_segments where tablespace_name='NIPTEST';
SELECT 'alter table '||owner||'.'||table_name||' move tablespace USERS;' FROM DBA_tables WHERE TABLESPACE_NAME='NIPTEST'; 批量把表移动到其他表空间
****** move （降低高水位）
优点：可以移动表到其他表空间，在执行命令时不需要执行alter table table_name enable row movement
缺点：表move 会导致表中的索引失效，要rebuild；同时表会产生行级锁......；在此如果表中有LOB字段 时要用一下命令来实现表空间移动：alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name;也可以单独move lob，index要rebuild
****** shrink space
优点：降低高水位时索引不会失效
缺点：不能将表移动到其他表空间；高水位降低效果没有move明显；同时在执行命令前要先执行(alter table table_name enable row movement允许行移动)也会表会产生行级锁.......，shrink比move更耗费cpu,产生很多current block这样生成巨大的redo与undo 如果表中索引很少可以建议使用move降低高水位
2) 移动完表发现 主键和索引还是在源表空间
SELECT * FROM DBA_extents WHERE TABLESPACE_NAME='USERS'; -->查看原表空间的主键和索引
alter index XX rebuild tablespace ;
---> 批量执行索引重建
SELECT 'alter index '||owner||'.'||segment_name||' rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME='NIPTEST'; 批量将主键索引重建到其他表空间
select * from dba_segments where tablespace_name='NIPTEST';弄完上面的操作，这里还有数据，不要在意，那是回收站的，无需修改直接删表空间即清空
(3) 表都移动完了 之后 先把数据文件offline drop再删除数据文件
alter database datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' offline drop;
---> 查看下数据文件状态
select status from dba_tablespaces v$datafile where tablespace_name='NIPTEST'; -----offline
--> 再删除表空间
drop tablespace niptest; --> 删除表空间，但不删除其文件
drop tablespace niptest including contents; -->删除表空间同时删除表空间的数据对象
drop tablespace niptest including contents and datafiles;
--> 删除表空间时删除数据对象及其OS系统文件一起删除，，以便释放空间,前提表空间不能是数据库默认表空间
否则会 报错：ORA-12919: Can not drop the default permanent tablespace
(4) 怎么查看下数据库的默认表空间是什么：
select * from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
如果您删除的表空间是数据库默认表空间要用一下命令来
更换数据库默认表空间：alter database default tablespace users;
再执行： drop tablespace niptest including contents and datafiles;
[root@kfdb49 kfdb]# df -hl --> 看下OS系统空间情况 --- 表空间释放
------创建表空间的语句：
create tablespace niptest datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' size 10G autoextend on next XXM maxsize XXm extent management local;
有的人会想着再创建一个niptest 为10G的表空间，避免后期imp时源表的表空间是niptest
： 其实不需要，如果库中没有niptest表空间，就算imp源表的表空间是niptest，也会导入到用户的默认表空间，有niptest表空间的话则会导入到niptest表空间(如果后续不想拥有niptest表空间 就要斩草除根的将niptest删除之后不要创建)
回收unlimited tablespace 给予额外权限niptest表空间权限会正常导入，否则报错
额外权限和用户默认表空间是一个的话可以正常导入，否则知道表结构到用户默认表空间
具体实验参考： http://space.itpub.net/28602568/viewspace-760169#xspace-tracks
oracle删除(释放)数据文件/表空间流程
生产环境：数据库里空间不足，niptest 表空间251G，只使用了17G
再alter database datafile '...../niptest1' resize 10G; 的时候说超出了范围要求
由于表变动比较频繁，高水位值比较大
(高水位 HWM “High Water Mark “：oracle中block有没有使用的分界线，它会随着数据的insert而上升，但它并不会随数据的delete而下降，因此全表扫描的时间并不因数据的delete而减少，相反可能由于块清除反而全表扫描时间增加）
删除表空间步骤：
1) 批量将niptest表空间中的表move 到USERS表空间，再删除表空间niptest
首先看下此表空间内的表 move到其他表空间 防止数据丢失
select * from dba_tables where tablespace_name='NIPTEST';
select * from dba_extents where tablespace_name='NIPTEST';
select * from dba_segments where tablespace_name='NIPTEST';
SELECT 'alter table '||owner||'.'||table_name||' move tablespace USERS;' FROM DBA_tables WHERE TABLESPACE_NAME='NIPTEST'; 批量把表移动到其他表空间
****** move （降低高水位）
优点：可以移动表到其他表空间，在执行命令时不需要执行alter table table_name enable row movement
缺点：表move 会导致表中的索引失效，要rebuild；同时表会产生行级锁......；在此如果表中有LOB字段 时要用一下命令来实现表空间移动：alter table owner.table_name move tablespace tablespace_name lob (lob_column) store as lob segment tablespace tablespace_name;也可以单独move lob，index要rebuild
****** shrink space
优点：降低高水位时索引不会失效
缺点：不能将表移动到其他表空间；高水位降低效果没有move明显；同时在执行命令前要先执行(alter table table_name enable row movement允许行移动)也会表会产生行级锁.......，shrink比move更耗费cpu,产生很多current block这样生成巨大的redo与undo 如果表中索引很少可以建议使用move降低高水位
2) 移动完表发现 主键和索引还是在源表空间
SELECT * FROM DBA_extents WHERE TABLESPACE_NAME='USERS'; -->查看原表空间的主键和索引
alter index XX rebuild tablespace ;
---> 批量执行索引重建
SELECT 'alter index '||owner||'.'||segment_name||' rebuild tablespace USERS;' FROM DBA_extents WHERE TABLESPACE_NAME='NIPTEST'; 批量将主键索引重建到其他表空间
select * from dba_segments where tablespace_name='NIPTEST';弄完上面的操作，这里还有数据，不要在意，那是回收站的，无需修改直接删表空间即清空
(3) 表都移动完了 之后 先把数据文件offline drop再删除数据文件
alter database datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' offline drop;
---> 查看下数据文件状态
select status from dba_tablespaces v$datafile where tablespace_name='NIPTEST'; -----offline
--> 再删除表空间
drop tablespace niptest; --> 删除表空间，但不删除其文件
drop tablespace niptest including contents; -->删除表空间同时删除表空间的数据对象
drop tablespace niptest including contents and datafiles;
--> 删除表空间时删除数据对象及其OS系统文件一起删除，，以便释放空间,前提表空间不能是数据库默认表空间
否则会 报错：ORA-12919: Can not drop the default permanent tablespace
(4) 怎么查看下数据库的默认表空间是什么：
select * from database_properties where property_name = 'DEFAULT_PERMANENT_TABLESPACE';
如果您删除的表空间是数据库默认表空间要用一下命令来
更换数据库默认表空间：alter database default tablespace users;
再执行： drop tablespace niptest including contents and datafiles;
[root@kfdb49 kfdb]# df -hl --> 看下OS系统空间情况 --- 表空间释放
------创建表空间的语句：
create tablespace niptest datafile '/home/oracle/app/oracle/oradata/kfdb/niptest1' size 10G autoextend on next XXM maxsize XXm extent management local;
有的人会想着再创建一个niptest 为10G的表空间，避免后期imp时源表的表空间是niptest
： 其实不需要，如果库中没有niptest表空间，就算imp源表的表空间是niptest，也会导入到用户的默认表空间，有niptest表空间的话则会导入到niptest表空间(如果后续不想拥有niptest表空间 就要斩草除根的将niptest删除之后不要创建)
回收unlimited tablespace 给予额外权限niptest表空间权限会正常导入，否则报错
额外权限和用户默认表空间是一个的话可以正常导入，否则知道表结构到用户默认表空间
具体实验参考： http://space.itpub.net/28602568/viewspace-760169#xspace-tracks