oracle 表空间的回收脚本

/* 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。