查看表空间使用情况
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%
计算所需undo表空间的大小:
1.计算业务高峰期每秒产生undo数据块的个数
SQL> select max(undoblks / ((end_time – begin_time)*24*3600)) from v$undostat;
MAX(UNDOBLKS/((END_TIME-BEGIN_
——————————
11.305
2.得到undo数据块在undo表空间中可以保留的最长时间
SQL> show parameter undo_retention;
NAME TYPE VALUE
———————————— ———– ——————————
undo_retention integer 86400
3.得到数据块大小
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.将以上三者的数据相乘就是所需undo表空间的大小数
SQL> select (11.305*86400*8192)/1024/1024/1024 undoTablespace_GB from dual;
UNDOTABLESPACE_GB
—————–
7.4520263671875
发现undo表空间不够的时候，赶紧增加undo表空间的大小，执行语句如下：
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;
———————
本文为转载文章
