> 本指令碼主要用於回收表空間，收縮資料檔案 --By Firsouler

``` sql
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';   
```

#### 查詢佔用資料檔案高水位的物件
``` sql
-- 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 時，索引會失效，也會鎖表，一定要提前規劃和溝通，切勿隨意操作。

``` sql
--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;
```

【RESIZE】Oracle收縮表空間主要命令

相關文章