當前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

一个回滚段收缩的实例

發布時間：2024/8/26 编程问答 23 豆豆

生活随笔收集整理的這篇文章主要介紹了一个回滚段收缩的实例小編覺得挺不錯的,現在分享給大家,幫大家做個參考.

日前在整理數據庫表空間的是否，發現最大的數據文件來自回滾段?；貪L段文件undotbs1的數據文件已經達到23G。

希望清理這部分數據，但一時又無從下手。于是決定深入了解一下這部分內容。

法和規劃及問題的解決。?
1，檢查回滾段信息

select?t.segment_name,t.owner,t.tablespace_name,
d.file_name,
t.initial_extent,t.min_extents,t.max_extents,t.status
from?dba_rollback_segs t, dba_data_files d
where?t.file_id = d.file_id

seg_name	owner	tablespace	filename	init_extents	min_extents	max_extents	status
SYSTEM	SYS	SYSTEM	/oradata/system01.dbf	114688	1	32765	ONLINE
_SYSSMU1$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU2$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU3$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU4$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU5$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU6$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU12$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	OFFLINE
_SYSSMU7$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU8$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU9$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU10$	PUBLIC	UNDOTBS1	/oradata/undotbs01.dbf	131072	2	32765	ONLINE
_SYSSMU11$	PUBLIC	UNDOTBS1	/oradata/undotbs02.dbf	131072	2	32765	OFFLINE
_SYSSMU13$	PUBLIC	UNDOTBS1	/oradata/undotbs02.dbf	131072	2	32765	OFFLINE
_SYSSMU14$	PUBLIC	UNDOTBS1	/oradata/undotbs02.dbf	131072	2	32765	OFFLINE

這里我們看到/oradata/undotbs02.dbf中的3個公用回滾段都是offline的。而且在/oradata/undotbs01.dbf文件中也有一個回滾段屬于offline狀態。

2，檢查回滾段各段當前大小

select?ds.segment_name,ds.bytes,drs.status,ddf.file_name
from?dba_segments ds, dba_rollback_segs drs,dba_data_files ddf
where?ds.segment_name = drs.segment_name
??and?drs.file_id = ddf.file_id

segment_name	seg_size	status	file_name
_SYSSMU11$	328.1796875	OFFLINE	/oradata/undotbs02.dbf
_SYSSMU12$	208.0546875	OFFLINE	/oradata/undotbs01.dbf
_SYSSMU13$	88.1796875	OFFLINE	/oradata/undotbs02.dbf
_SYSSMU14$	95.9921875	OFFLINE	/oradata/undotbs02.dbf
SYSTEM	0.4375	ONLINE	/oradata/system01.dbf
_SYSSMU1$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU10$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU2$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU3$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU4$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU5$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU6$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU7$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU8$	2.1171875	ONLINE	/oradata/undotbs01.dbf
_SYSSMU9$	2.1171875	ONLINE	/oradata/undotbs01.dbf

結果是令人沮喪的，23G的空間只有500M左右是used，并且used空間的99％的數據還是在offline的回滾段中的。這里，我們需要做的處理應該不是對回滾段的收縮。

事實上目前online的回滾段空間是非常小的，接近2M。而大部分的空間在置入free后沒有回收。我們來檢查一下回滾段的空閑塊信息。從而推測一下freelist的組成。

select?a.segment_name,dfs.tablespace_name,dfs.block_id,dfs.bytes,dfs.blocks
from?dba_free_space dfs,
(
select?ds.segment_name,ds.header_block,drs.file_id,
lead(ds.header_block) over(partition?by?drs.file_id?order?by?ds.header_block) next_block
from?dba_segments ds, dba_rollback_segs drs
where?ds.segment_name = drs.segment_name
) a
where?dfs.block_id?between?a.header_block?and?nvl(a.next_block,100000000000)
??and?dfs.file_id = a.file_id
??and?dfs.tablespace_name =?'UNDOTBS1'

segment_name	tablespace	block_id	bytes	blocks
_SYSSMU10$	UNDOTBS1	185	65536	8
_SYSSMU10$	UNDOTBS1	201	65536	8
_SYSSMU10$	UNDOTBS1	265	65536	8
_SYSSMU10$	UNDOTBS1	281	196608	24
_SYSSMU10$	UNDOTBS1	313	327680	40
_SYSSMU10$	UNDOTBS1	361	262144	32
_SYSSMU10$	UNDOTBS1	905	3145728	384
_SYSSMU10$	UNDOTBS1	1673	1048576	128
_SYSSMU10$	UNDOTBS1	3721	33554432	4096
_SYSSMU10$	UNDOTBS1	7945	143654912	17536
_SYSSMU10$	UNDOTBS1	25865	1048576	128
_SYSSMU10$	UNDOTBS1	26377	1048576	128
_SYSSMU10$	UNDOTBS1	26889	8388608	1024
_SYSSMU10$	UNDOTBS1	28297	1048576	128
_SYSSMU10$	UNDOTBS1	28681	1048576	128
_SYSSMU10$	UNDOTBS1	28937	502267904	61312
_SYSSMU10$	UNDOTBS1	90633	1048576	128
_SYSSMU10$	UNDOTBS1	92041	13631488	1664
_SYSSMU10$	UNDOTBS1	93833	279969792	34176
_SYSSMU10$	UNDOTBS1	131081	57671680	7040
_SYSSMU10$	UNDOTBS1	139145	76546048	9344
_SYSSMU10$	UNDOTBS1	149513	84934656	10368
_SYSSMU10$	UNDOTBS1	160521	134217728	16384
_SYSSMU10$	UNDOTBS1	177161	11534336	1408
_SYSSMU10$	UNDOTBS1	179849	29360128	3584
_SYSSMU10$	UNDOTBS1	183561	33554432	4096
_SYSSMU10$	UNDOTBS1	191753	8388608	1024
_SYSSMU10$	UNDOTBS1	195977	396361728	48384
_SYSSMU10$	UNDOTBS1	245385	696254464	84992
_SYSSMU10$	UNDOTBS1	331401	1445986304	176512
_SYSSMU10$	UNDOTBS1	507913	4160749568	507904
_SYSSMU10$	UNDOTBS1	1015817	671088640	81920
_SYSSMU10$	UNDOTBS1	1105929	536870912	65536
_SYSSMU10$	UNDOTBS1	1204361	67108864	8192
_SYSSMU10$	UNDOTBS1	1217033	22020096	2688
_SYSSMU10$	UNDOTBS1	1221513	2475687936	302208
_SYSSMU10$	UNDOTBS1	1523721	4160749568	507904
_SYSSMU10$	UNDOTBS1	2031625	4160749568	507904
_SYSSMU10$	UNDOTBS1	2539529	2196701184	268152
_SYSSMU14$	UNDOTBS1	3337	33554432	4096
_SYSSMU14$	UNDOTBS1	7561	131072000	16000
_SYSSMU14$	UNDOTBS1	23817	1048576	128
_SYSSMU14$	UNDOTBS1	24969	1048576	128
_SYSSMU14$	UNDOTBS1	25353	10485760	1280
_SYSSMU14$	UNDOTBS1	27145	67108864	8192
_SYSSMU14$	UNDOTBS1	35593	729808896	89088
_SYSSMU14$	UNDOTBS1	129417	72351744	8832
_SYSSMU14$	UNDOTBS1	139273	83886080	10240
_SYSSMU14$	UNDOTBS1	152201	142606336	17408
_SYSSMU14$	UNDOTBS1	169993	179306496	21888
_SYSSMU14$	UNDOTBS1	192009	2097152	256
_SYSSMU14$	UNDOTBS1	192521	25165824	3072
_SYSSMU14$	UNDOTBS1	196105	18874368	2304
_SYSSMU14$	UNDOTBS1	199177	41943040	5120
_SYSSMU14$	UNDOTBS1	206089	38797312	4736
_SYSSMU14$	UNDOTBS1	211849	192937984	23552
_SYSSMU14$	UNDOTBS1	236425	201326592	24576
_SYSSMU14$	UNDOTBS1	262025	645922816	78848
_SYSSMU14$	UNDOTBS1	342921	1351614464	164992
_SYSSMU14$	UNDOTBS1	507913	805306368	98304
_SYSSMU14$	UNDOTBS1	606233	65536	8
_SYSSMU14$	UNDOTBS1	606249	65536	8
_SYSSMU14$	UNDOTBS1	606321	217251840	26520
_SYSSMU14$	UNDOTBS1	633865	176095232	21496

????結果顯示，大部分的free space來自10和14兩個回滾段，我們先嘗試對這兩個回滾段進行收縮。

4，收縮回滾段

SQL> alter rollback segment 10 shrink to 100M;

alter rollback segment 10 shrink to 100M

ORA-02245: invalid ROLLBACK SEGMENT name

5，修改數據文件大小

SQL> alter database datafile '/oradata/undotbs02.dbf' resize 1000M;

alter database datafile '/oradata/undotbs02.dbf' resize 1000M

ERROR at line 1:

ORA-03297: file contains used data beyond requested RESIZE value

6，重建回滾表空間，將當前回滾段執行該表空間，并刪除舊的回滾段表空間

創建新的回滾空間

SQL> create undo tablespace undotbs2 datafile '/oradata/undotbs03.dbf' size 1000M;

Tablespace created.

切換回滾空間為新的空間

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

System altered.

等待源undo表空間的所有undo segment offline

select?segment_name,tablespace_name,status?from?dba_rollback_segs
where?tablespace_name =?'UNDOTBS1'

segment_name	tablespace	status
_SYSSMU1$	UNDOTBS1	OFFLINE
_SYSSMU2$	UNDOTBS1	OFFLINE
_SYSSMU3$	UNDOTBS1	OFFLINE
_SYSSMU4$	UNDOTBS1	OFFLINE
_SYSSMU5$	UNDOTBS1	OFFLINE
_SYSSMU6$	UNDOTBS1	OFFLINE
_SYSSMU7$	UNDOTBS1	OFFLINE
_SYSSMU8$	UNDOTBS1	OFFLINE
_SYSSMU9$	UNDOTBS1	OFFLINE
_SYSSMU10$	UNDOTBS1	OFFLINE
_SYSSMU11$	UNDOTBS1	OFFLINE
_SYSSMU12$	UNDOTBS1	OFFLINE
_SYSSMU13$	UNDOTBS1	OFFLINE
_SYSSMU14$	UNDOTBS1	OFFLINE

刪除原表空間untotbs1

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

刪除原表空間包含文件

$ rm –I undotbs01.dbf

$ rm –I undotbs02.dbf

附錄1

DBA_ROLLBACK_SEGS

DBA_ROLLBACK_SEGS describes rollback segments.

Column	Datatype	NULL	Description
SEGMENT_NAME	VARCHAR2(30)	NOT NULL	Name of the rollback segment
OWNER	VARCHAR2(6)	?	Owner of the rollback segment
TABLESPACE_NAME	VARCHAR2(30)	NOT NULL	Name of the tablespace containing the rollback segment
SEGMENT_ID	NUMBER	NOT NULL	ID number of the rollback segment
FILE_ID	NUMBER	NOT NULL	File identifier number of the file containing the segment head
BLOCK_ID	NUMBER	NOT NULL	ID number of the block containing the segment header
INITIAL_EXTENT	NUMBER	?	Initial extent size in bytes
NEXT_EXTENT	NUMBER	?	Secondary extent size in bytes
MIN_EXTENTS	NUMBER	NOT NULL	Minimum number of extents
MAX_EXTENTS	NUMBER	NOT NULL	Maximum number of extent
PCT_INCREASE	NUMBER	NOT NULL	Percent increase for extent size
STATUS	VARCHAR2(16)	?	Rollback segment status
INSTANCE_NUM	VARCHAR2(40)	?	Rollback segment owning Oracle Real Application Cluster instance number
RELATIVE_FNO	NUMBER	NOT NULL	Relative file number of the segment header

附錄2?V$ROLLSTAT

This view contains rollback segment statistics.

Column	Datatype	Description
USN	NUMBER	Rollback segment number
LATCH	NUMBER	Latch for the rollback segment
EXTENTS	NUMBER	Number of extents in the rollback segment
RSSIZE	NUMBER	Size (in bytes) of the rollback segment. This value differs by the number of bytes in one database block from the value of the BYTES column of the ALL/DBA/USER_SEGMENTS views. See Also:?Oracle9i Database Administrator's Guide.
WRITES	NUMBER	Number of bytes written to the rollback segment
XACTS	NUMBER	Number of active transactions
GETS	NUMBER	Number of header gets
WAITS	NUMBER	Number of header waits
OPTSIZE	NUMBER	Optimal size of the rollback segment
HWMSIZE	NUMBER	High water mark of rollback segment size
SHRINKS	NUMBER	Number of times the size of a rollback segment decreases
WRAPS	NUMBER	Number of times rollback segment is wrapped
EXTENDS	NUMBER	Number of times rollback segment size is extended
AVESHRINK	NUMBER	Average shrink size
AVEACTIVE	NUMBER	Current size of active extents, averaged over time.
STATUS	VARCHAR2(15)	Rollback segment status: ONLINE PENDING OFFLINE OFFLINE FULL
CUREXT	NUMBER	Current extent
CURBLK	NUMBER	Current block

附錄3?V$UNDOSTAT

V$UNDOSTAT displays a histogram of statistical data to show how well the system is working. The available statistics include undo space consumption, transaction concurrency, and length of queries executed in the instance. You can use this view to estimate the amount of undo space required for the current workload. Oracle uses this view to tune undo usage in the system. The view returns null values if the system is in manual undo management mode.

Each row in the view keeps statistics collected in the instance for a 10-minute interval. The rows are in descending order by the BEGIN_TIME column value. Each row belongs to the time interval marked by (BEGIN_TIME, END_TIME). Each column represents the data collected for the particular statistic in that time interval. The first row of the view contains statistics for the (partial) current time period. The view contains a total of 1008 rows, spanning a 7 day cycle.

Column	Datatype	Description
BEGIN_TIME	DATE	Identifies the beginning of the time interval
END_TIME	DATE	Identifies the end of the time interval
UNDOTSN	NUMBER	Represents the last active undo tablespace in the duration of time. The tablespace ID of the active undo tablespace is returned in this column. If more than one undo tablespace was active in that period, the active undo tablespace that was active at the end of the period is reported.
UNDOBLKS	NUMBER	Represents the total number of undo blocks consumed. You can use this column to obtain the consumption rate of undo blocks, and thereby estimate the size of the undo tablespace needed to handle the workload on your system.
TXNCOUNT	NUMBER	Identifies the total number of transactions executed within the period
MAXQUERYLEN	NUMBER	Identifies the length of the longest query (in number of seconds) executed in the instance during the period. You can use this statistic to estimate the proper setting of the UNDO_RETENTION initialization parameter.
MAXCONCURRENCY	NUMBER	Identifies the highest number of transactions executed concurrently within the period
UNXPSTEALCNT	NUMBER	Number of attempts to obtain undo space by stealing unexpired extents from other transactions
UNXPBLKRELCNT	NUMBER	Number of unexpired blocks removed from certain undo segments so they can be used by other transactions
UNXPBLKREUCNT	NUMBER	Number of unexpired undo blocks reused by transactions
EXPSTEALCNT	NUMBER	Number of attempts to steal expired undo blocks from other undo segments
EXPBLKRELCNT	NUMBER	Number of expired undo blocks stolen from other undo segments
EXPBLKREUCNT	NUMBER	Number of expired undo blocks reused within the same undo segments
SSOLDERRCNT	NUMBER	Identifies the number of times the error ORA-01555 occurred. You can use this statistic to decide whether or not the UNDO_RETENTION initialization parameter is set properly given the size of the undo tablespace. Increasing the value of UNDO_RETENTION can reduce the occurrence of this error.
NOSPACEERRCNT	NUMBER	Identifies the number of times space was requested in the undo tablespace and there was no free space available. That is, all of the space in the undo tablespace was in use by active transactions. The corrective action is to add more space to the undo tablespace.

總結

以上是生活随笔為你收集整理的一个回滚段收缩的实例的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。