收縮Oracle資料檔案

us_yunleiwang發表於2013-12-05
原文網址 : http://blog.itpub.net/23490154/viewspace-1062422/
Oracle

   最近有網友提到收縮Oracle資料檔案的問題，這是DBA經常碰到的一個常見問題。通常我們需要收縮相應的資料檔案以減少來自磁碟空間的壓力以及提高資料庫的整體效能。但這並非對於所有情形都是適用的，尤其是生產環境。因為生產環境資料清洗相當較少，因此空間浪費也比較小，而且一旦收縮之後又要重新自動擴充套件資料檔案，浪費系統資源。對於UAT，DEV環境，多DB，磁碟空間壓力大的情形，收縮一下非常有必要。勒緊褲帶過日子也是常有的事情，哈哈。總之收縮資料檔案會使得磁碟空間得以釋放以及加快資料遷移，RMAN備份等。本文分享了Tom大師的收縮指令碼以及給出了undo，臨時表空間，表段收縮的連結。

     幾種收縮的情形：
          收縮表段(shrink space) 
          收縮臨時表空間 
          收縮undo表空間

 

1、演示收縮資料檔案

[sql] view plaincopyprint?
  1. robin@ORADB:~/dba_scripts/custom/sql> sql  
  2.   
  3. SQL*Plus: Release 10.2.0.3.0 - Production on Wed Oct 30 15:05:18 2013  
  4.   
  5. Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.  
  6.   
  7. Connected to:  
  8. Oracle Database 10g Release 10.2.0.3.0 - 64bit Production  
  9.   
  10. goex_admin@USBOTST> @shrink_data_files;   
  11.   
  12. VALUE  
  13. --------------------  
  14. 8192  
  15.   
  16.                                                    Smallest  
  17.                                                        Size  Current    Poss.  
  18. FILE_NAME                                             Poss.     Size  Savings  
  19. -------------------------------------------------- -------- -------- --------  
  20. /u02/database/USBOTST/oradata/sysUSBOTST.dbf            605      650       45  
  21. /u02/database/USBOTST/oradata/USBOTST_archive_idx.      725    1,871    1,146  
  22. dbf  
  23.   
  24. /u02/database/USBOTST/oradata/USBOTST_his_idx.dbf         1       32       31  
  25. /u02/database/USBOTST/oradata/USBOTST_ipo_idx.dbf         7       10        3  
  26. /u02/database/USBOTST/oradata/USBOTST_account_tbl.    6,293    6,293        0  
  27. dbf  
  28.   
  29. /u02/database/USBOTST/oradata/USBOTST_rpt_tbl.dbf        21      373      352  
  30. /u02/database/USBOTST/oradata/USBOTST_audit_tbl.db      938      966       28  
  31. f  
  32.   
  33. /u02/database/USBOTST/oradata/tbs_rman01.dbf             13       50       37  
  34. /u02/database/USBOTST/undo/undotbsUSBOTST.dbf           358    7,350    6,992  
  35. /u02/database/USBOTST/oradata/USBOTST_archive_tbl.      760    1,950    1,190  
  36. dbf  
  37.   
  38. /u02/database/USBOTST/oradata/USBOTST_rpt_idx.dbf        10      359      349  
  39. /u02/database/USBOTST/oradata/USBOTST_vou_tbl.dbf         4      145      141  
  40. /u02/database/USBOTST/oradata/USBOTST_stock_l_tbl.        4       20       16  
  41. dbf  
  42.   
  43. /u02/database/USBOTST/oradata/USBOTST_ca_idx.dbf          1       22       21  
  44. /u02/database/USBOTST/oradata/USBOTST_his_tbl.dbf         1      959      958  
  45. /u02/database/USBOTST/oradata/USBOTST_vou_idx.dbf         2       90       88  
  46. /u02/database/USBOTST/oradata/sysauxUSBOTST.dbf         697      800      103  
  47. /u02/database/USBOTST/oradata/spot_data.dbf              81       95       14  
  48. /u02/database/USBOTST/oradata/USBOTST_tx_tbl.dbf         16      103       87  
  49. /u02/database/USBOTST/oradata/USBOTST_tx_his_tbl.d       88      878      790  
  50. bf  
  51.   
  52. /u02/database/USBOTST/oradata/USBOTST_ca_tbl.dbf          1       60       59  
  53. /u02/database/USBOTST/oradata/USBOTST_imp_exp_tbl.       60      108       48  
  54. dbf  
  55.   
  56.     .........................................................................  
  57.   
  58.                                                                      --------  
  59. sum                                                                    29,686  --&gt可被釋放的總空間  
  60.   
  61. 44 rows selected.  
  62.   
  63. Database altered.  
  64.   
  65. Database altered.  
  66.   
  67. Database altered.  
  68.   
  69. Database altered.  
  70.   
  71. alter database datafile '/u02/database/USBOTST/oradata/USBOTST_ipo_idx.dbf'  
  72. *  
  73. ERROR at line 1:  
  74. ORA-03297: file contains used data beyond requested RESIZE value  
  75. ---&gt Author : Leshami        ---&gtBlog : http://blog.csdn.net/leshami  
  76. ...........................................  
  77. --可能存在個別檔案出現無法收縮的情形，提示超出最小的size。  

2、收縮指令碼

[sql] view plaincopyprint?
  1. --此指令碼可用於Oracle 10g,11g  
  2. robin@ORADB:~/dba_scripts/custom/sql> more shrink_data_files.sql   
  3. set verify off  
  4. col value format a20  
  5. column file_name format a50 word_wrapped  
  6. column smallest format 999,990 heading "Smallest|Size|Poss."  
  7. column currsize format 999,990 heading "Current|Size"  
  8. column savings format 999,990 heading "Poss.|Savings"  
  9. break on report  
  10. compute sum of savings on report  
  11.   
  12. column value new_val blksize  
  13. select value from v$parameter where name = 'db_block_size'  
  14. /  
  15.    
  16. select file_name,  
  17.        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,  
  18.        ceil( blocks*&&blksize/1024/1024) currsize,  
  19.        ceil( blocks*&&blksize/1024/1024) -  
  20.        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings  
  21. from dba_data_files a,  
  22.      ( select file_id, max(block_id+blocks-1) hwm  
  23.          from dba_extents  
  24.         group by file_id ) b  
  25. where a.file_id = b.file_id(+)  
  26. /  
  27.    
  28. column cmd format a75 word_wrapped  
  29.   
  30. set heading off feedback off termout off  
  31. spool /tmp/tmp_shrink_data_files.sql   
  32. select 'alter database datafile '''||file_name||''' resize ' ||  
  33.        ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || 'm;' cmd  
  34. from dba_data_files a,  
  35.      ( select file_id, max(block_id+blocks-1) hwm  
  36.          from dba_extents  
  37.         group by file_id ) b  
  38. where a.file_id = b.file_id(+)  
  39.   and ceil( blocks*&&blksize/1024/1024) -  
  40.       ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0  
  41. /  
  42. spool off;  
  43. set heading on feedback on termout on  
  44. @/tmp/tmp_shrink_data_files.sql    

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23490154/viewspace-1062422/，如需轉載，請註明出處，否則將追究法律責任。

相關文章