扩展撤销表空间容量
[sql] 查看表空间使用情况
- 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
- 来自dba_data_files
- GROUP BY tablespace_name)a,
- (SELECT tablespace_name,SUM(bytes)/ 1024/1024 free_size
- 来自dba_free_space
- GROUP BY tablespace_name)b
- WHERE.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%
- 用户5 1.312 3.688 73.75%
- SYSTEM 4170 4160.687 9.313 .22%
- USER_DATA 150 105.062 44.938 29.96%
- 计算所需撤销表空间的大小:
- 1.计算业务高峰期每秒产生撤消数据块的个数
- SQL>从v $ undostat中选择max(undoblks /((end_time - begin_time)* 24 * 3600));
- MAX(UNDOBLKS /((END_TIME-BEGIN_
- ------------------------------
- 11.305
- 2.得到撤消数据块在撤销表空间中可以保留的最长时间
- SQL> show参数undo_retention;
- 名称类型值
- ------------------------------------ ----------- --- ---------------------------
- undo_retention integer 86400
- 3.得到数据块大小
- SQL> show parameter db_blo
- 名称类型值
- ------------------------------------ ----------- --- ---------------------------
- db_block_buffers整数0
- db_block_checking string FALSE
- db_block_checksum string TYPICAL
- db_block_size整数8192
- 4.将以上三者的数据相乘就是所需撤销表空间的大小数
- SQL> select(11.305 * 86400 * 8192)/ 1024/1024/1024 undoTablespace_GB from dual;
- UNDOTABLESPACE_GB
- -----------------
- 7.4520263671875
- 发现撤销表空间不够的时候,赶紧增加撤消表空间的大小,执行语句如下:
- alter tablespace undotbs1在下一个128M maxsize 24G上添加数据文件'/u01/database/instance_name/undotbs02.dbf'大小为100M autoextend;
数据文件大小为100M