解决undo 表空间过大导致磁盘空间不足的问题
undo表空间不断扩大问题的原因:1有较大的事务量让oracle undo 自动扩展,产生过度占有磁盘空间的情况。2有较大事务没有收缩或者没有提交所导致。
<wbr><br> 1.查看还原表空间所在磁盘是否使用率过高,及linux 系统哪个磁盘处于比较空闲的状态<br><br> :$df -lh<br><br> 2.在oracle 数据库中查看所有表空间的占用率:<br><br> SQL> SELECT Total.name "Tablespace Name",<br><wbr> 2<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> nvl(Free_space, 0) Free_space,<br><wbr> 3<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> nvl(total_space-Free_space, 0) Used_space,<br><wbr> 4<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> total_space<br><wbr> 5<wbr> FROM<br><wbr> 6<wbr><wbr><wbr> (select tablespace_name, sum(bytes/1024/1024) Free_Space<br><wbr> 7<wbr><wbr><wbr><wbr><wbr><wbr> from sys.dba_free_space<br><wbr> 8<wbr><wbr><wbr><wbr><wbr> group by tablespace_name<br><wbr> 9<wbr><wbr><wbr> ) Free,<br><wbr>10<wbr><wbr><wbr> (select b.name,<wbr> sum(bytes/1024/1024) TOTAL_SPACE<br><wbr>11<wbr><wbr><wbr><wbr><wbr><wbr> from sys.v_$datafile a, sys.v_$tablespace B<br><wbr>12<wbr><wbr><wbr><wbr><wbr> where a.ts# = b.ts#<br><wbr>13<wbr><wbr><wbr><wbr><wbr> group by b.name<br><wbr>14<wbr><wbr><wbr> ) Total<br><wbr>15<wbr> WHERE Free.Tablespace_name(+) = Total.name<br><wbr>16<wbr> ORDER BY Total.name<br><wbr>17<wbr> /<br><br> 星期二 5月<wbr> 29<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 第<wbr><wbr><wbr> 1<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> Space Usage for Database in Meg<br><br> EXAMPLE<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 21.25<wbr><wbr><wbr><wbr><wbr> 78.75<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 100<br> STAGING<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 1.9375<wbr><wbr><wbr><wbr> 1.0625<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
undo表空间不断扩大问题的原因:1有较大的事务量让oracle undo 自动扩展,产生过度占有磁盘空间的情况。2有较大事务没有收缩或者没有提交所导致。
<wbr><br> 1.查看还原表空间所在磁盘是否使用率过高,及linux 系统哪个磁盘处于比较空闲的状态<br><br> :$df -lh<br><br> 2.在oracle 数据库中查看所有表空间的占用率:<br><br> SQL> SELECT Total.name "Tablespace Name",<br><wbr> 2<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> nvl(Free_space, 0) Free_space,<br><wbr> 3<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> nvl(total_space-Free_space, 0) Used_space,<br><wbr> 4<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> total_space<br><wbr> 5<wbr> FROM<br><wbr> 6<wbr><wbr><wbr> (select tablespace_name, sum(bytes/1024/1024) Free_Space<br><wbr> 7<wbr><wbr><wbr><wbr><wbr><wbr> from sys.dba_free_space<br><wbr> 8<wbr><wbr><wbr><wbr><wbr> group by tablespace_name<br><wbr> 9<wbr><wbr><wbr> ) Free,<br><wbr>10<wbr><wbr><wbr> (select b.name,<wbr> sum(bytes/1024/1024) TOTAL_SPACE<br><wbr>11<wbr><wbr><wbr><wbr><wbr><wbr> from sys.v_$datafile a, sys.v_$tablespace B<br><wbr>12<wbr><wbr><wbr><wbr><wbr> where a.ts# = b.ts#<br><wbr>13<wbr><wbr><wbr><wbr><wbr> group by b.name<br><wbr>14<wbr><wbr><wbr> ) Total<br><wbr>15<wbr> WHERE Free.Tablespace_name(+) = Total.name<br><wbr>16<wbr> ORDER BY Total.name<br><wbr>17<wbr> /<br><br> 星期二 5月<wbr> 29<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 第<wbr><wbr><wbr> 1<br><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> Space Usage for Database in Meg<br><br> EXAMPLE<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 21.25<wbr><wbr><wbr><wbr><wbr> 78.75<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 100<br> STAGING<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr> 1.9375<wbr><wbr><wbr><wbr> 1.0625<wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr><wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>