Undo tablespace capacity

View Tablespace Usage

SELECT a.tablespace_name,

ROUND (a.total_size) "total_size(MB)",

ROUND (a.total_size) - ROUND (b.free_size, 3) "used_size(MB)",

ROUND (b.free_size, 3) "free_size(MB)",

ROUND (b.free_size / total_size * 100, 2) || '%' free_rate

FROM ( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 total_size

FROM dba_data_files

GROUP BY tablespace_name) a,

( SELECT tablespace_name, SUM (bytes) / 1024 / 1024 free_size

FROM dba_free_space

GROUP BY tablespace_name) b

WHERE a.tablespace_name = b.tablespace_name(+);

TABLESPACE_NAME total_size(MB) used_size(MB) free_size(MB) FREE_RATE

------------------------------ -------------- ------------- ------------- -----------------------------------------

SYSAUX 900 835.687 64.313 7.15%

UNDOTBS1 24576 53.875 24522.125 99.78%

USERS 5 1.312 3.688 73.75%

SYSTEM 4170 4160.687 9.313 .22%

USER_DATA 150 105.062 44.938 29.96%

Calculate the size of the required undo table space:

1. Count the number of undo data blocks generated per second during peak traffic

SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;

MAX(UNDOBLKS/((END_TIME-BEGIN_

------------------------------

11.305

2. Get the longest time that undo data blocks can be retained in the undo table space

SQL> show parameter undo_retention;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

undo_retention integer 86400

3. Get the block size

SQL> show parameter db_blo

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_block_buffers integer 0

db_block_checking string FALSE

db_block_checksum string TYPICAL

db_block_size integer 8192

4. Multiply the above three data to get the required undo table space size

SQL> select (11.305*86400*8192)/1024/1024/1024 undoTablespace_GB from dual;

UNDOTABLESPACE_GB

-----------------

7.4520263671875

When you find that the undo table space is not enough, quickly increase the size of the undo table space and execute the following statement:

alter tablespace undotbs1 add datafile '/u01/database/instance_name/undotbs02.dbf' size 100M autoextend on next 128M maxsize 24G;

alter tablespace undotbs1 add datafile '/u01/database/instance_name/undotbs03.dbf' size 100M autoextend on next 128M maxsize 24G;

alter tablespace undotbs1 add datafile '/u01/database/instance_name/undotbs04.dbf' size 100M autoextend on next 128M maxsize 24G;

---------------------

This article is a reprinted article.