ORA-30036: 無(wú)法按 8 擴(kuò)展段解決辦法

一、故障現(xiàn)象：

在刪除一個(gè)超大表的數(shù)據(jù)時(shí)報(bào)錯(cuò)：ORA-30036:

SQL> delete fromCRT_CURING_PRESS_TEMPHISTORY

 2  whereRECORD_TIME<to_date('2017-06-30','yyyy-mm-dd');

delete from CRT_CURING_PRESS_TEMPHISTORY

第 1 行出現(xiàn)錯(cuò)誤:

ORA-30036: 無(wú)法按 8 擴(kuò)展段 (在還原表空間'UNDOTBS1' 中)

仔細(xì)檢查發(fā)現(xiàn)是由于undo表空間所在的數(shù)據(jù)文件已經(jīng)使用達(dá)到32G所致。

二、解決辦法：

1、檢查undo表空間對(duì)應(yīng)的數(shù)據(jù)文件目錄

SQL>select tablespace_name,file_name fromdba_data_files;

2、查看undo表空間使用率：

SQL>select file_name,bytes/1024/1024from dba_data_files where tablespace_name like 'UNDOTBS%'; 

或者、

SQL>SELECT a.tablespace_name as tablespace_name,

      to_char(b.total/1024/1024,999999.99) as Total,

      to_char((b.total-a.free)/1024/1024,999999.99) as Used,

      to_char(a.free/1024/1024,999999.99) as Free,

      to_char(round((total-free)/total,4)*100,999.99) as Used_Rate

FROM (SELECT tablespace_name, sum(bytes)free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,

    (SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BYtablespace_name ) b

WHERE a.tablespace_name=b.tablespace_name

  ANDa.tablespace_name='UNDOTBS1'

ORDER BY a.tablespace_name;

3、等待原UNDO表空間所有UNDO SEGMENT OFFLINE； 

SQL>select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024,shrinks from v$rollstat order by rssize;  

SQL>selectt.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segst; 

4、新增undo數(shù)據(jù)文件！

SQL>alter tablespace undotbs1 add datafile'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS02.DBF' size 1000m AUTOEXTEND on next200m maxsize unlimited;

5、刪除原有的UNDO表空間

SQL> create pfile from spfile; 

SQL> drop tablespace undotbs1 includingcontents; 

  最后需要在重啟數(shù)據(jù)庫(kù)或者重啟計(jì)算機(jī)后到存儲(chǔ)數(shù)據(jù)文件的路徑下刪除數(shù)據(jù)文件（為什么要手動(dòng)刪除呢：以上步驟只是刪除了ORACLE中undo表空間的邏輯關(guān)系，即刪除了數(shù)據(jù)文件在數(shù)據(jù)字典中的關(guān)聯(lián)，不會(huì)自動(dòng)刪除項(xiàng)關(guān)聯(lián)的數(shù)據(jù)文件）。 

   droptablespace undotbs1 including contents and datafiles;  

新增undo數(shù)據(jù)文件！

SQL>alter tablespace undotbs1 add datafile'D:\APP\ADMINISTRATOR\ORADATA\ORCL\UNDOTBS02.DBF' size 1000m AUTOEXTEND on next200m maxsize unlimited;

3、原理通透：

UNDO 表空間用于存放UNDO數(shù)據(jù),當(dāng)執(zhí)行DML操作(INSERT,UPDATE和DELETE)時(shí),oracle會(huì)將這些操作的舊數(shù)據(jù)寫(xiě)入到UNDO段,在 oracle9i之前,管理UNDO數(shù)據(jù)時(shí)使用(Rollback Segment)完成的.從oracle9i開(kāi)始,管理UNDO數(shù)據(jù)不僅可以使用回滾段,還可以使用UNDO表空間.因?yàn)橐?guī)劃和管理回滾段比較復(fù)雜,所有oracle database 10g已經(jīng)完全丟棄用回滾段.并且使用UNDO表空間來(lái)管理UNDO數(shù)據(jù).

UNDO數(shù)據(jù)也稱(chēng)為回滾(ROLLBACK)數(shù)據(jù),它用于確保數(shù)據(jù)的一致性.當(dāng)執(zhí)行DML操作時(shí),事務(wù)操作前的數(shù)據(jù)被稱(chēng)為UNDO記錄.UNDO段用于保存事務(wù)所修改數(shù)據(jù)的舊值,其中存儲(chǔ)著被修改數(shù)據(jù)塊的位置以及修改前數(shù)據(jù),

UNDO數(shù)據(jù)的作用.

1,回退事務(wù)

當(dāng)執(zhí)行DML操作修改數(shù)據(jù)時(shí),UNDO數(shù)據(jù)被存放到UNDO段,而新數(shù)據(jù)則被存放到數(shù)據(jù)段中,如果事務(wù)操作存在問(wèn)題,就需要回退事務(wù),以取消事務(wù)變化.假定用戶(hù)A執(zhí)行了語(yǔ)句UPDATEemp SET sal=1000 WHERE empno=7788后發(fā)現(xiàn),應(yīng)該修改雇員7963的工資,而不是雇員7788的工資,那么通過(guò)執(zhí)行ROLLBACK語(yǔ)句可以取消事務(wù)變化.當(dāng)執(zhí)行ROLLBACK命令時(shí),oracle會(huì)將UNDO段的UNDO數(shù)據(jù)800（工資）寫(xiě)回到數(shù)據(jù)段中.

2,讀一致性

用戶(hù)檢索數(shù)據(jù)庫(kù)數(shù)據(jù)時(shí),oracle總是使用用戶(hù)只能看到被提交過(guò)的數(shù)據(jù)(讀取提交)或特定時(shí)間點(diǎn)的數(shù)據(jù)(SELECT語(yǔ)句時(shí)間點(diǎn)).這樣可以確保數(shù)據(jù)的一致性.例如,當(dāng)用戶(hù)A執(zhí)行語(yǔ)句 UPDATE emp SET sal=1000 WHERE empno=7788時(shí),UNDO記錄會(huì)被存放到回滾段中,而新數(shù)據(jù)則會(huì)存放到EMP段中;假定此時(shí)該數(shù)據(jù)尚未提交,并且用戶(hù)B執(zhí)行SELECT salFROM emp WHERE empno=7788,此時(shí)用戶(hù)B將取得UNDO數(shù)據(jù) 800,而該數(shù)據(jù)正是在UNDO記錄中取得的.

3,事務(wù)恢復(fù)

事務(wù)恢復(fù)是例程恢復(fù)的一部分,它是由oracleserver自動(dòng)完成的.如果在數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中出現(xiàn)例程失敗(如斷電,內(nèi)存故障,后臺(tái)進(jìn)程故障等),那么當(dāng)重啟oracle server時(shí),后臺(tái)進(jìn)程SMON會(huì)自動(dòng)執(zhí)行例程恢復(fù),執(zhí)行例程恢復(fù)時(shí),oracle會(huì)重新做所有未應(yīng)用的記錄.回退未提交事務(wù).

4,倒敘查詢(xún)(FlashBack Query)

倒敘查詢(xún)用于取得特定時(shí)間點(diǎn)的數(shù)據(jù)庫(kù)數(shù)據(jù), 它是9i新增加的特性,假定當(dāng)前時(shí)間為上午11:00,某用戶(hù)在上午10:00執(zhí)行UPDATE emp SET sal= 3500 WHERE empno=7788語(yǔ)句,修改并提交了事務(wù)(雇員原工資為3000),為了取得10:00之前的雇員工資,用戶(hù)可以使用倒敘查詢(xún)特征.

使用UNDO參數(shù)

1,UNDO_MANAGEMENT

該初始化參數(shù)用于指定UNDO數(shù)據(jù)的管理方式.如果要使用自動(dòng)管理模式,必須設(shè)置該參數(shù)為AUTO,如果使用手工管理模式,必須設(shè)置該參數(shù)為MANUAL,使用自動(dòng)管理模式時(shí), oracle會(huì)使用undo表空間管理undo管理,使用手工管理模式時(shí),oracle會(huì)使用回滾段管理undo數(shù)據(jù),

需要注意,使用自動(dòng)管理模式時(shí),如果沒(méi)有配置初始化參數(shù)UNDO_TABLESPACE,oracle會(huì)自動(dòng)選擇第一個(gè)可用的UNDO表空間存放UNDO數(shù)據(jù),如果沒(méi)有可用的UNDO表空間,oracle會(huì)使用SYSTEM回滾段存放UNDO記錄,并在ALTER文件中記載警告.

2,UNDO_TABLESPACE

該初始化參數(shù)用于指定例程所要使用的UNDO表空間,使用自動(dòng)UNDO管理模式時(shí),通過(guò)配置該參數(shù)可以指定例程所要使用的UNDO表空間.

在RAC(RealApplication Cluster)結(jié)構(gòu)中,因?yàn)橐粋€(gè)UNDO表空間不能由多個(gè)例程同時(shí)使用,所有必須為每個(gè)例程配置一個(gè)獨(dú)立的UNDO表空間.

3,UNDO_RETENTION

該初始化參數(shù)用于控制UNDO數(shù)據(jù)的最大保留時(shí)間,其默認(rèn)值為900秒,從9i開(kāi)始,通過(guò)配置該初始化參數(shù),可以指定undo數(shù)據(jù)的保留時(shí)間,從而確定倒敘查詢(xún)特征(FlashbackQuery)可以查看到的最早時(shí)間點(diǎn).

建立UNDO表空間,

UNDO表空間專(zhuān)門(mén)用于存放UNDO數(shù)據(jù),并且在UNDO表空間尚不能建立任何數(shù)據(jù)對(duì)象(表,索引,簇)

1,使用CREATE DATABASE命令建立UNDO表空間.

當(dāng)使用CREATEDATABASE命令建立數(shù)據(jù)庫(kù)時(shí),通過(guò)指定UNDO TABLESPACE選項(xiàng),可以建立UNDO表空間.示例如下:

CREATE DATABASE db01

…

UNDO TABLESPACE undotbs_01

DATAFILE ‘/u01/oracle/rbdb1/undo0101.dbf’SIZE 30M;

注意:UNDO TABLESPACE 子句不是必須的,如果使用自動(dòng)UNDO管理模式,并且沒(méi)有指定該子句,那么建立數(shù)據(jù)庫(kù)時(shí)會(huì)自動(dòng)生成名為SYS_UNDOTBS的UNDO表空間.

2,使用CREATE UNDOTABLESPACE命令建立UNDO表空間.

CREATE UNDO TABLESPACE undotbs2

DATAFILE ‘D:demoundotbs2.dbf’ SIZE 10M;

修改UNDO表空間,

使用ALTER TABLESPACE命令修改UNDO表空間.

當(dāng)事務(wù)用盡了UNDO表空間后,使用ALTER TABLESPACE … ADD DATAFILE增加數(shù)據(jù)文件

當(dāng)UNDO表空間所在的磁盤(pán)填滿(mǎn)是,使用ALTER TABLESPACE … RENAME DATAFIEL 命令移動(dòng)數(shù)據(jù)文件到其他磁盤(pán)上.

使用ALTER DATABASE … OFFLINE/ONLINE使表空間脫機(jī)/聯(lián)機(jī).

當(dāng)數(shù)據(jù)庫(kù)處于ARCHIVELOG模式時(shí),使用ALTER TABLESPACE …BEGIN BACKUP/END BACKUP命令備份UNDO表空間.

切換UNDO表空間.

啟動(dòng)例程并打開(kāi)數(shù)據(jù)庫(kù)后,同一時(shí)刻特定例程只能使用一個(gè)UNDO表空間,切換UNDO表空間是指停止例程當(dāng)前使用的UNDO表空間,并啟動(dòng)其他UNDO表空間,下面以啟用undotbs2表空間為例,說(shuō)明切換UNDO表空間的方法.

ALTER SYSTEM SET undo_tablespace=undotbs02;

在RAC(Real Application Cluster)機(jī)構(gòu)中,不同例程必須使用獨(dú)立的UNDO表空間,而不能共用同一個(gè)UNDO表空間.

刪除UNDO表空間.

當(dāng)前例程正在使用的UNDO表空間是不能被刪除的,如果確定要?jiǎng)h除當(dāng)前例程正在使用的UNDO表空間,應(yīng)首先切換UNDO表空間.然后刪除相應(yīng)的UNDO表空間.

DROP TABLESPACE undotbs1;

1,確定當(dāng)前例程正在使用的UNDO表空間.

Show parameter undo_tablespace

2,顯示數(shù)據(jù)庫(kù)的所有UNDO表空間.

SELECT tablespace_name FROMdba_tablespacesWHERE contents=’UNDO’;

3,顯示UNDO表空間統(tǒng)計(jì)信息.

使用自動(dòng)UNDO管理模式時(shí),需要合理地設(shè)置UNDO表空間的尺寸,為例合理規(guī)劃UNDO表空間尺寸,應(yīng)在數(shù)據(jù)庫(kù)運(yùn)行的高峰階段搜集UNDO表空間的統(tǒng)計(jì)信息.最終根據(jù)該統(tǒng)計(jì)信息確定UNDO表空間的尺寸.通過(guò)查詢(xún)動(dòng)態(tài)性能視圖V%UNDOSTAT,可以搜集UNDO統(tǒng)計(jì)信息.

SELECT TO_CHAR(BEGIN_TIME,’HH24:MI:SS’)BEGIN_TIME,

TO_CHAR(END_TIME,’HH24:MI:SS’) END_TIME,

UNDOBLKS

FROM V$UNDOSTAT;

BEGIN_TIME用于標(biāo)識(shí)起始統(tǒng)計(jì)時(shí)間,END_TIME用于標(biāo)識(shí)結(jié)束統(tǒng)計(jì)時(shí)間,UNDOBLKS用于標(biāo)識(shí)UNDO數(shù)據(jù)所占用的數(shù)據(jù)塊個(gè)數(shù).oracle每隔10分鐘生成一行統(tǒng)計(jì)信息.

4,顯示UNDO段統(tǒng)計(jì)信息.

使用自動(dòng)UNDO 管理模式時(shí),oracle會(huì)在UNDO表空間上自動(dòng)建立10個(gè)UNDO段,通過(guò)查詢(xún)動(dòng)態(tài)信息視圖V$ROLLNAME,可以顯示所有聯(lián)機(jī)UNDO段的名稱(chēng),通過(guò)查詢(xún)動(dòng)態(tài)性能視圖V$ROLLLISTAT,可以顯示UNDO段的統(tǒng)計(jì)信息.通過(guò)在V$ROLLNAME和V$ROLLLISTAT之間執(zhí)行連接查詢(xún),可以監(jiān)視特定UNDO段的特定信息.

SELECT a.name, b.xacts, b.writes, b.extents

FROM v$rollname a, v$rollstat b

WHERE a.usn=b.usn;

Name用于標(biāo)識(shí)UNDO段的名稱(chēng),xacts用于標(biāo)識(shí)UNDO段所包含的活動(dòng)事務(wù)個(gè)數(shù),

Writes用于標(biāo)識(shí)在undo段上所寫(xiě)入的字節(jié)數(shù),extents用于標(biāo)識(shí)UNDO段的區(qū)個(gè)數(shù).

5,顯示活動(dòng)事務(wù)信息.

當(dāng)執(zhí)行DML操作時(shí),oracle會(huì)將這些操作的舊數(shù)據(jù)放到UNDO段中,動(dòng)態(tài)性能視圖v$session用于顯示會(huì)話(huà)的詳細(xì)信息,動(dòng)態(tài)性能視圖v$transaction用于顯示事務(wù)的詳細(xì)信息,動(dòng)態(tài)性能視圖v$rollname用于顯示聯(lián)機(jī)UNDO段的名稱(chēng).通過(guò)在這3個(gè)動(dòng)態(tài)性能視圖之間執(zhí)行連接查詢(xún),可以確定正在執(zhí)行事務(wù)操作的會(huì)話(huà),事務(wù)所使用的UNDO段,以及事務(wù)所占用的UNDO塊個(gè)數(shù).

Col username format a10

Col name format a10

SELECT a.username, b.name, c.used_ublk

FROM v$session a, v$rollname b,v$transaction c

WHERE a.saddr=c.ses_addr AND b.usn=c.xidusn

AND a.username='CURE';

6,顯示UNDO區(qū)信息

數(shù)據(jù)字典視圖dba_undo_extents用于顯示UNDO表空間所有區(qū)的詳細(xì)信息.包括UNDO區(qū)尺寸和狀態(tài)等信息.

SELECT extend_id, bytes, status FROMdba_undo_extents

WHERE segment_name’_SYSSMU5$’;

其中,extent_id用于標(biāo)識(shí)區(qū)編號(hào),bytes用于標(biāo)識(shí)區(qū)尺寸,status用于標(biāo)識(shí)區(qū)狀態(tài)(ACTIVE:表示該區(qū)處于活動(dòng)狀態(tài),EXPIRED:標(biāo)識(shí)該區(qū)未用).