oracle收缩数据文件

--oracle收缩数据文件
--由于 ALTER DATABASE DATAFILE 'E:\ORADATA\OLD_TABS01.DBF' RESIZE 3072M; 语句不能将文件大小改到hwmsize以下,所以要先将hwmsize缩小;

--通过将表空间的数据移动到另一个表空间来实现缩小hwmsize

--查询表空间使用情况
select /*+ ordered use_hash(a,b,c) */ a.file_id,a.file_name,a.filesize, b.freesize, 
(a.filesize-b.freesize) usedsize,  c.hwmsize,  c.hwmsize - (a.filesize-b.freesize) unsedsize_belowhwm,
  a.filesize - c.hwmsize canshrinksize 
   from  ( select file_id,file_name,round(bytes/1024/1024) filesize from dba_data_files ) a,
    ( select file_id,round(sum(dfs.bytes)/1024/1024) freesize from dba_free_space dfs group by file_id ) b,
     ( select file_id,round(max(block_id)*8/1024) HWMsize from dba_extents group by file_id) c
 where a.file_id = b.file_id   and a.file_id = c.file_id order by unsedsize_belowhwm DESC;
 ------------------------------------------------------------------------------------------------------------
select 
a.a1 表空间名称,
c.c2 类型,
c.c3 区管理,
b.b2/1024/1024 表空间大小M,
(b.b2-a.a2)/1024/1024 已使用M,
substr((b.b2-a.a2)/b.b2*100,1,5) 利用率
from 
(select  tablespace_name a1, sum(nvl(bytes,0)) a2 from dba_free_space group by tablespace_name) a,
(select tablespace_name b1,sum(bytes) b2 from dba_data_files group by tablespace_name) b,
(select tablespace_name c1,contents c2,extent_management c3  from dba_tablespaces) c 
where a.a1=b.b1 and c.c1=b.b1;
--------------------------------------------------------------------------------------------------------------
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;
------------------------------------------------以上转载--------------------------------------------------------------
--1.新建一个表空间,大小为原表空间实际大小
CREATE TABLESPACE BACKUP_TABS DATAFILE 'E:\ORADATA\BACKUP_TABS01.DBF' size 3072m;
 alter tablespace BACKUP_TABS add datafile 'E:\ORADATA\BACKUP_TABS02.DBF' size 3072m; 
--2.将原表空间的数据移动到新的表空间
--移动表
select  ds.tablespace_name,'alter table '||ds.owner||'.'||ds.segment_name||' move tablespace BACKUP_TABS;' 
from dba_segments ds  
where ds.tablespace_name IN('OLD_TABS') and ds.segment_type = 'TABLE';
--移动分区表
select  rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||' tablespace BACKUP_TABS;' cname 
from dba_segments ds  
where DS.tablespace_name IN ('OLD_TABS')and ds.segment_type = 'TABLE PARTITION';
--移动索引
select  ds.tablespace_name,'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild tablespace BACKUP_TABS;'
from dba_segments ds 
where ds.tablespace_name IN('OLD_TABS')and ds.segment_type = 'INDEX';
--移动lob字段
SELECT 'alter table '||TABLE_NAME||' move tablespace BACKUP_TABS lob ('||COLUMN_NAME||') store as(tablespace BACKUP_TABS);' 
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME IN(SELECT table_name FROM ALL_TABLES WHERE TABLESPACE_NAME ='OLD_TABS')
AND data_type IN('CLOB','BLOB');
--3.调整原表空间数据文件
 ALTER DATABASE DATAFILE 'E:\ORADATA\OLD_TABS01.DBF' RESIZE 3072M;
 alter database datafile 'E:\ORADATA\OLD_TABS01.DBF' autoextend on maxsize 3072M; 
 alter tablespace OLD_TABS add datafile 'E:\ORADATA\OLD_TABS02.DBF' size 1024M autoextend on maxsize 3072M; 
--4.将备份表中的数据移回来
select  ds.tablespace_name,'alter table '||ds.owner||'.'||ds.segment_name||' move tablespace OLD_TABS;' 
from dba_segments ds  
where ds.tablespace_name IN('BACKUP_TABS') and ds.segment_type = 'TABLE';
--移动分区表
select  rownum rm,'alter table '||ds.owner||'.'||ds.segment_name||' move partition '||ds.partition_name||' tablespace OLD_TABS;' cname 
from dba_segments ds  
where DS.tablespace_name IN ('BACKUP_TABS')and ds.segment_type = 'TABLE PARTITION';
--移动索引
select  ds.tablespace_name,'alter INDEX '||ds.owner||'.'||ds.segment_name||' rebuild tablespace OLD_TABS;'
from dba_segments ds 
where ds.tablespace_name IN('BACKUP_TABS')and ds.segment_type = 'INDEX';
--移动lob字段
SELECT 'alter table '||TABLE_NAME||' move tablespace BACKUP_TABS lob ('||COLUMN_NAME||') store as(tablespace OLD_TABS);' 
FROM ALL_TAB_COLUMNS WHERE TABLE_NAME IN(SELECT table_name FROM ALL_TABLES WHERE TABLESPACE_NAME ='BACKUP_TABS')
AND data_type IN('CLOB','BLOB');
-------------------------------------------------------------------

参考http://www.cnblogs.com/GmrBrian/archive/2013/07/04/3172670.html

参考https://blog.youkuaiyun.com/oracle_baidu/article/details/8658986