Shulou(Shulou.com)06/01 Report--

How to understand the rapid expansion of oracle undo tablespaces, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Recently, it was found that the utilization rate of undo tablespaces in two databases has been increasing. Once in two days, it has been increased to nearly 40G. Later, it felt that this phenomenon was a little abnormal, because database transactions were not so frequent that they did not always reach 100%.

So I looked it up on the Internet, and sure enough, it was caused by a bug of oracle. This bug should be available on all platforms in the oracle10.2 version. The bug oracle says it has been patched in version 10.2.0.4, but I did find it on AIX.

BUG number: 5387030, 420525.1

Environment:

OS\ DB: AIX 5.3\ oracle 10.2.0.2 64-bit

OS\ DB: HP-Unix\ oracle 10.2.0.2 64-bit

Phenomenon:

View the current undo tablespace usage:

SQL > select t.tablespace_name, total, free, round (100 * (1-(free/total)), 3) | |'% 'percentage of space used

From (select tablespace_name, sum (bytes) / 1024 total from dba_data_files

Where tablespace_name='UNDOTBS1'

Group by tablespace_name) t

(select tablespace_name, sum (bytes) / 1024 plus 1024 free from dba_free_space

Group by tablespace_name) f where t.tablespace_name=f.tablespace_name (+)

Order by tablespace_name

The proportion of space used by TABLESPACE_NAME TOTAL FREE

UNDOTBS1 39000 1442.0625 95.027%

Undo tablespaces are not automatically extended:

SQL > Select file_name,Autoextensible From Dba_Data_Files

2 Where tablespace_name='UNDOTBS1'

3 Order By File_id

FILE_NAME AUTOEXTENSIBLE

-

/ gsfs02/oradata/CICGSFSP/undotbs01.dbf NO

/ gsfs03/oradata/CICGSFSP/undotbs02.dbf NO

.

SQL > show parameter undo_retention

NAME TYPE VALUE

-

Undo_retention integer 1800

Resolve:

Setting the tablespace to automatically expand and the maximum tablespace to the current size of the tablespace has the same effect as the non-automatic expansion of table emptiness, so as to avoid the generation of this bug.

-generate a script to modify the data file

Select 'alter database datafile''| | file_name | |''Autoextend on' | | 'Maxsize' | | to_char (bytes/1024/1024) | | 'mashing' From Dba_Data_Files

Where tablespace_name='UNDOTBS1'

Order By File_id

Alter database datafile'/ gsfs02/oradata/CICGSFSP/undotbs01.dbf' Autoextend on Maxsize 2000m

Alter database datafile'/ gsfs03/oradata/CICGSFSP/undotbs02.dbf' Autoextend on Maxsize 2000m

Alter database datafile'/ gsfs04/oradata/CICGSFSP/undotbs03.dbf' Autoextend on Maxsize 2000m

Alter database datafile'/ gsfs05/oradata/CICGSFSP/undotbs04.dbf' Autoextend on Maxsize 2000m

.

SQL > select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') from dual

TO_CHAR (SYSDATE,'YYYY-MM-DDHH2

-

2010-10-29 10:40:03

Then execute its script to modify the data file to automatically extend the property to on

Observe the undo tablespace usage again every few minutes, and the usage rate is plummeting:

The proportion of space used by TABLESPACE_NAME TOTAL FREE

UNDOTBS1 29000 3790.0625 86.931%

The proportion of space used by TABLESPACE_NAME TOTAL FREE

UNDOTBS1 29000 10837.0625 62.631%

The proportion of space used by TABLESPACE_NAME TOTAL FREE

UNDOTBS1 38000 27496.9375 27.64%

Then look at the suggested value of the tuned_undoretention field of an important view, v$undostat:

-- 2010-10-29 10:42:18 is exactly the time after I have modified the properties of the data file

SQL > select to_char (begin_time,'yyyy-mm-dd hh34:mi:ss'), to_char (end_time,'yyyy-mm-dd hh34:mi:ss'), undoblks,tuned_undoretention,maxquerylen,maxqueryid from v$undostat WHERE rownum < 20

TO_CHAR (BEGIN_TIME,'YYYY-MM-DD TO_CHAR (END_TIME,'YYYY-MM-DDHH UNDOBLKS TUNED_UNDORETENTION MAXQUERYLEN MAXQUERYID)

-

2010-10-29 10:52:18 2010-10-29 10:56:39 1128 1800 88 5kma9ywsgcwhm

2010-10-29 10:42:18 2010-10-29 10:52:18 6848 1800 88 5kma9ywsgcwhm

2010-10-29 10:32:18 2010-10-29 10:42:18 5847 345600 200 cykr0t05sxa73

2010-10-29 10:22:18 2010-10-29 10:32:18 3770 345600 199 70r1113n3vwyu

2010-10-29 10:12:18 2010-10-29 10:22:18 5014 345600 105 4cp98gmy1q8qk

2010-10-29 10:02:18 2010-10-29 10:12:18 5292 345600 229 3156qc5zh6pwn

2010-10-29 09:52:18 2010-10-29 10:02:18 4113 345600 1amcb16ytfpbx

2010-10-29 09:42:18 2010-10-29 09:52:18 6184 345600 79 7qbdzfacr8srx

2010-10-29 09:32:18 2010-10-29 09:42:18 3939 345600 78 bunvx480ynf57

2010-10-29 09:22:18 2010-10-29 09:32:18 5575 345600 56 3s4zvp29gjsz0

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.