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

A library maintained recently found that the utilization of undo tablespaces has reached 95%.

Automatic extension of undo tablespaces, or new undo tablespaces for switching, can solve this problem.

Automatic expansion of undo tablespaces may lead to no release of undo, increase of undo tablespaces, and waste of storage space.

Human intervention is also required to create a new undo tablespace for switching.

Can you let Oracle automatically shrink undo tablespaces? Checked the MOS and found a way.

Set the undo tablespace to auto-expand, and set the maximum value MAXSIZE for data file auto-extension.

In this way, there is no need to adjust the undo_retention, while maximizing flashback

It can also avoid the problem that the utilization of undo table space is too high and does not release.

SQL > select * from v$version BANNER----Oracle Database 11g Enterprise Edition Release 11.2.0.3.0-64bit ProductionPL/SQL Release 11.2.0.3.0-ProductionCORE 11.2.0.3 .0 ProductionTNS for Linux: Version 11.2.0.3.0-ProductionNLSRTL Version 11.2.0.3.0-ProductionSQL > show parameter undoNAME TYPE VALUE-- undo_management string AUTOundo_ Retention integer 1800undo_tablespace string UNDOTBS1SQL > SELECT autoextensible FROM dba_data_files WHERE tablespace_name = 'UNDOTBS1' AUTOEXTENSIBLE-YESSQL > SELECT (sum (blocks) * 8) / 1024, status2 FROM dba_undo_extents group by status; (SUM (BLOCKS) * 8) / 1024 STATUS--6648.375 UNEXPIRED504.625 EXPIRED

Set the undo tablespace to auto-expand, and set the maximum value MAXSIZE for data file auto-extension.

SQL > ALTER DATABASE DATAFILE'/ opt/ora11g/datafile/LiangWei/undotbs01.dbf' AUTOEXTEND ON NEXT 1m MAXSIZE 8192 M

After about 5 to 10 minutes, check the undo tablespace usage

SQL > SELECT ((SELECT (NVL (SUM (bytes), 0)) FROM dba_undo_extentsWHERE tablespace_name = 'UNDOTBS1'AND status IN (' ACTIVE', 'UNEXPIRED')) * 100) / (SELECT SUM (bytes) FROM dba_data_filesWHERE tablespace_name =' UNDOTBS1') PCT_INUSEFROM dual;PCT_INUSE-5.50333658

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.