一个回滚段收缩的实例

来源：互联网发布：garageband是什么软件编辑：程序博客网时间：2024/04/28 17:06

日前在整理数据库表空间的是否，发现最大的数据文件来自回滚段。回滚段文件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

32765

ONLINE

_SYSSMU1$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU2$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU3$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU4$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU5$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU6$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU12$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

OFFLINE

_SYSSMU7$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU8$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU9$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU10$

PUBLIC

UNDOTBS1

/oradata/undotbs01.dbf

131072

32765

ONLINE

_SYSSMU11$

PUBLIC

UNDOTBS1

/oradata/undotbs02.dbf

131072

32765

OFFLINE

_SYSSMU13$

PUBLIC

UNDOTBS1

/oradata/undotbs02.dbf

131072

32765

OFFLINE

_SYSSMU14$

PUBLIC

UNDOTBS1

/oradata/undotbs02.dbf

131072

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

_SYSSMU10$

UNDOTBS1

201

65536

_SYSSMU10$

UNDOTBS1

265

65536

_SYSSMU10$

UNDOTBS1

281

196608

_SYSSMU10$

UNDOTBS1

313

327680

_SYSSMU10$

UNDOTBS1

361

262144

_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

_SYSSMU14$

UNDOTBS1

606249

65536

_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.