/* SYS登陆操作 需要先降低可用空间然后RESIZE
1、打开TABLE的 ROW MOVEMENT
2、SHRINK SPACE回收TABLE空间
3、关闭ROW MOVEMENT
3、RESIZE TABLESPACE
*/
/* 查看较大的TABLE/INDEX占用的空间
SQL=SELECT * FROM DBA_SEGMENT ORDER BY BYTES/1024/1024 DESC
针对结果SHRINK单TABLE/INDEX
*/
----执行SELECT之后的结果语句----
SELECT'ALTERTABLE用户名.'||TABLE_NAME||' ENABLE ROW MOVEMENT;'
FROM DBA_TABLES
WHERE OWNER='用户名'
SELECT'ALTERTABLE用户名.'||TABLE_NAME||' SHRINK SPACE;'
FROM DBA_TABLES
WHERE OWNER='用户名'
UNION
SELECT'ALTERINDEX用户名.'||INDEX_NAME||' SHRINK SPACE;'
FROM DBA_INDEXES
WHERE OWNER='用户名'
SELECT'ALTERTABLE用户名.'||TABLE_NAME||' DISABLE ROW MOVEMENT;'
FROM DBA_TABLES
WHERE OWNER='用户名'
----tempfile可以resize的空间.on apply that have onlyone tempfile----
select'alterdatabase tempfile '''||a.name||''' reize '||b.siz||'M;'from v$tempfile a,
(select tmsize.maxblk*bk.value/1024/1024 siz from
(select nvl(max(segblk#),128) maxblk from v$sort_usage) tmsize,
(selectvalueFrom v$parameterwherename ='db_block_size') bk) b
----计算datafile可以resize收缩的空间 ----
select a.file#,
a.name,
a.bytes/1024/1024CurrentMB,
ceil(HWM * a.block_size)/1024/1024ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024ReleaseMB,
'alter database datafile'''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) ||'M;' ResizeCMD
from v$datafile a,
(select file_id,
max(block_id+blocks-1) HWM
from dba_extents
groupbyfile_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
----对一个TABLESPACE的datafile resize----
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
ceil(HWM * a.block_size)/1024/1024ResizeTo,
(a.bytes - HWM * a.block_size)/1024/1024ReleaseMB,
'alter database datafile'''||a.name||''' resize '||
ceil(HWM * a.block_size/1024/1024) ||'M;' ResizeCMD
from v$datafile a,
(select file_id,max(block_id+blocks-1) HWM
from dba_extentswherefile_idin
(selectb.file# Fromv$tablespace a ,v$datafile b
wherea.ts#=b.ts#and a.name='MIS_INDEX')
groupbyfile_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
--ORA-03297: 文件包含在请求的 RESIZE值以外使用的数据
--创建 test_space表空间
CREATETABLESPACE test_spacedatafile'/home/oracle/app/oracle/oradata/dwtest/test_space.dbf'SIZE200MautoextendON;
set headingoff
set echooff
set feedbackoff
set termouton
spool d:/aaa.sql
--移动表
selectDISTINCT'alter table '||owner||'.'|| segment_name || ' move tablespace test_space;'fromdba_extentswhere segment_type='TABLE'and file_id=18;
--移动索引
selectDISTINCT'alter index '||owner||'.'|| segment_name || ' rebuild tablespace test_space;'fromdba_extentswhere segment_type='INDEX'and file_id=18;
--移动分区表
selectDISTINCT'alter table '||owner||'.'|| segment_name || ' move partition '|| partition_name ||' tablespace test_space;'fromdba_extentswhere segment_type='TABLE PARTITION'and file_id=18;
--移动分区索引
selectDISTINCT'alter index '||owner||'.'|| segment_name || ' rebuild partition '|| partition_name ||' tablespace test_space;'fromdba_extentswhere segment_type='INDEX PARTITION'and file_id=18;
spool off
ALTERDATABASEDATAFILE'D:/ORACLE/ORADATA/ICAPP/IC_DATA6.ORA'RESIZE300M
--把原来表空间ic_data中的数据再移动回来,修改aaa.sql中的表空间名为ic_data再执行,然后drop tablespace test_space includingcontents and datafiles。