Oracle收缩表空间主要命令

本脚本主要用于回收表空间，收缩数据文件 —By Firsouler

set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading "Smallest|Size|Poss."
column currsize format 999,990 heading "Current|Size"
column savings  format 999,990 heading "Poss.|Savings"
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = 'db_block_size';
/
select file_name,
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       ceil( blocks*&&blksize/1024/1024) currsize,
       ceil( blocks*&&blksize/1024/1024) -
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents
       where tablespace_name='USERS' group by file_id ) b
where a.file_id = b.file_id(+) and a.tablespace_name='USERS' order by savings desc;
--输出修改语句
column cmd format a75 word_wrapped
select 'alter database datafile '''||file_name||''' resize ' ||
       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 )  || 'm;' cmd
from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
         from dba_extents where tablespace_name='TEST'
        group by file_id ) b
where a.file_id = b.file_id(+)
  and ceil( blocks*&&blksize/1024/1024) -
      ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0 and tablespace_name='TEST';

查找占用数据文件高水位的对象

-- FINDEXT.SQL 显示详细的块号 位置
-- This script lists all the extents contained in that datafile,
-- the block_id where the extent starts,
-- and how many blocks the extent contains.
-- It also shows the owner, segment name, and segment type.
-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE
SET ECHO OFF
SET PAGESIZ 25
column file_name format a50
select file_name, file_id from dba_data_files order by 2;
ttitle -
center 'Segment Extent Summary' skip 2
col ownr format a8 heading 'Owner' justify c
col type format a8 heading 'Type' justify c trunc
col name format a30 heading 'Segment Name' justify c
col exid format 990 heading 'Extent#' justify c
col fiid format 9990 heading 'File#' justify c
col blid format 99990 heading 'Block#' justify c
col blks format 999,990 heading 'Blocks' justify c
-- lisst top 30 high datafile block_id
select * from (select owner ownr, segment_name name,PARTITION_NAME, segment_type type, extent_id exid, file_id fiid, block_id blid, blocks blks
from dba_extents
where tablespace_name ='TEST'
order by block_id desc) where rownum<=30;
-- SHRINK_DATAFILE.SQL  输入收缩大小，如果无法收缩，列出需要移动的对象
-- This script lists the object names and types that must be moved in order to resize a datafile to a specified smaller size
-- Input: FILE_ID from DBA_DATA_FILES or FILE# from V$DATAFILE
-- Size in bytes that the datafile will be resized to
SET SERVEROUTPUT ON
DECLARE 
     V_FILE_ID NUMBER; 
     V_BLOCK_SIZE NUMBER; 
     V_RESIZE_SIZE NUMBER; 
BEGIN 
     V_FILE_ID := &FILE_ID; 
     V_RESIZE_SIZE := &RESIZE_FILE_TO; 
     SELECT BLOCK_SIZE INTO V_BLOCK_SIZE FROM V$DATAFILE WHERE FILE# = V_FILE_ID; 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('.'); 
     DBMS_OUTPUT.PUT_LINE('OBJECTS IN FILE '||V_FILE_ID||' THAT MUST MOVE IN ORDER TO RESIZE THE FILE TO '||V_RESIZE_SIZE||' BYTES'); 
     DBMS_OUTPUT.PUT_LINE('==================================================================='); 
     DBMS_OUTPUT.PUT_LINE('NON-PARTITIONED OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('==================================================================='); 
     for my_record in ( 
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME 
          FROM DBA_EXTENTS 
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID 
          AND SEGMENT_TYPE NOT LIKE '%PARTITION%' 
          ORDER BY 1) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME); 
     END LOOP; 
     DBMS_OUTPUT.PUT_LINE('==================================================================='); 
     DBMS_OUTPUT.PUT_LINE('PARTITIONED OBJECTS'); 
     DBMS_OUTPUT.PUT_LINE('==================================================================='); 
     for my_record in ( 
          SELECT DISTINCT(OWNER||'.'||SEGMENT_NAME||' - PARTITION = '||PARTITION_NAME||' - OBJECT TYPE = '||SEGMENT_TYPE) ONAME 
          FROM DBA_EXTENTS 
          WHERE (block_id + blocks-1)*V_BLOCK_SIZE > V_RESIZE_SIZE 
          AND FILE_ID = V_FILE_ID 
          AND SEGMENT_TYPE LIKE '%PARTITION%' 
          ORDER BY 1) LOOP 
               DBMS_OUTPUT.PUT_LINE(my_record.ONAME); 
     END LOOP; 
END; 
/

移动相关对象命令参考

注意，使用move 时，索引会失效，也会锁表，一定要提前规划和沟通，切勿随意操作。

--LOB字段 move
alter table edc_exchange.edc move tablespace TS_EDC2015 lob(XML_CLOB) store as edclob(tablespace TS_EDC2015);
--分区表move(举例说明)
alter table scott.obj_part move  partition p201212 tablespace test;
alter table scott.obj_part move  subpartition p20121231 tablespace test;
--重建相关索引
alter index  EDC_EXCHANGE.SYS_C00117331 rebuild tablespace TS_EDC2015;

来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29487349/viewspace-2952005/，如需转载，请注明出处，否则将追究法律责任。