查看表空间使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
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数据块的个数

1
2
3
4
5
SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;
 
MAX(UNDOBLKS/((END_TIME-BEGIN_
------------------------------
                        11.305

2.得到undo数据块在undo表空间中可以保留的最长时间

1
2
3
4
5
SQL> show parameter undo_retention;
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_retention                       integer     86400

3.得到数据块大小

1
2
3
4
5
6
7
8
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表空间的大小数

1
2
3
4
5
SQL> select (11.305*86400*8192)/1024/1024/1024 undoTablespace_GB from dual;
 
UNDOTABLESPACE_GB
-----------------
  7.4520263671875

发现undo表空间不够的时候，赶紧增加undo表空间的大小，执行语句如下：

1
2
3
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;