熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

Oracle回滾段空間回收步驟

2013-11-13 22:24:10  來源: Oracle 

  是誰偷偷的用了那麼多空間呢(本來有幾十個G的Free磁盤空間的)?

  檢查數據庫表空間占用空間情況:

  SQL> select tablespace_namesum(bytes)/// GB

   from dba_data_files group by tablespace_name

   union all

   select tablespace_namesum(bytes)/// GB

   from dba_temp_files group by tablespace_name order by GB;

  TABLESPACE_NAME                        GB

  

  USERS                         

  UNDOTBS                       

  SYSTEM                        

  SYSAUX                        

  WAPCM_TS_VISIT_DETAIL           

  HY_DS_DEFAULT                          

  MINT_TS_DEFAULT                        

  MMS_TS_DATA                       

  MMS_IDX_SJH                            

  MMS_TS_DEFAULT                         

  IVRCN_TS_DATA                          

  TABLESPACE_NAME                        GB

  

  MMS_TS_DATA                           

  CM_TS_DEFAULT                          

  TEMP                          

  UNDOTBS                      

   rows selected

  不幸的發現UNDO表空間已經擴展至G而TEMP表空間也擴展至G個表空間加起來占用了G的磁盤空間導致了空間不足

  顯然曾經有大事務占用了大量的UNDO表空間和Temp表空間Oracle的AUM(Auto Undo Management)從出生以來就經常出現只擴展不收縮(shrink)的情況(通常我們可以設置足夠的UNDO表空間大小然後取消其自動擴展屬性)

  現在我們可以采用如下步驟回收UNDO空間:

  確認文件

  SQL> select file_namebytes// from dba_data_files

   where tablespace_name like UNDOTBS;

  FILE_NAME

  

  BYTES//

  

  +ORADG/danaly/datafile/undotbs

  

  檢查UNDO Segment狀態

  SQL> select usnxactsrssize///hwmsize///shrinks

   from v$rollstat order by rssize;

  USN      XACTS RSSIZE/// HWMSIZE///    SHRINKS

  

                                              

                                              

                                                

                                              

                                              

                                              

                                              

                                              

                                              

                                              

                                                

   rows selected

  創建新的UNDO表空間

  SQL> create undo tablespace undotbs;

  Tablespace created

  切換UNDO表空間為新的UNDO表空間

  SQL> alter system set undo_tablespace=undotbs scope=both;

  System altered

  此處使用spfile需要注意以前曾經記錄過這樣一個案例:Oracle診斷案例Spfile案例一則

  等待原UNDO表空間所有UNDO SEGMENT OFFLINE

  SQL> select usnxactsstatusrssize///hwmsize///shrinks

   from v$rollstat order by rssize;

  USN      XACTS STATUS          RSSIZE/// HWMSIZE///    SHRINKS

  

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

  USN      XACTS STATUS          RSSIZE/// HWMSIZE///    SHRINKS

  

            PENDING OFFLINE                                    

   rows selected

  再看:

  :: SQL> /

  USN      XACTS STATUS          RSSIZE/// HWMSIZE///    SHRINKS

  

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

            ONLINE                                           

   rows selected

  Elapsed: ::

  刪除原UNDO表空間

  :: SQL> drop tablespace undotbs including contents;

  Tablespace dropped

  Elapsed: ::

  檢查空間情況

  由於我使用的ASM管理可以使用gR提供的信工具asmcmd來察看空間占用情況

  [oracle@danaly ~]$ export ORACLE_SID=+ASM

  [oracle@danaly ~]$ asmcmd

  ASMCMD> du

  Used_MB      Mirror_used_MB

                

  ASMCMD> exit

  空間已經釋放


From:http://tw.wingwit.com/Article/program/Oracle/201311/18992.html
  • 上一篇文章:

  • 下一篇文章:
  • Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.