【干货攻略】OUT OF SPACE报错处理

[dmdba@dm ~]$ cd home/dmdba/dmdbms/bin
[dmdba@dm bin]$ ./disql test
SQL> create table t1(id int);

SELECT F.TABLESPACE_NAME,
       ROUND((T.TOTAL_SPACE - F.FREE_SPACE)  1024,2) "USED (GB)",
       ROUND(F.FREE_SPACE  1024,2) "FREE (GB)",
       ROUND(T.TOTAL_SPACE  1024,2)  "TOTAL (GB)",
       (ROUND((F.FREE_SPACE  T.TOTAL_SPACE),4)* 100) ||  '% ' PER_FREE,
       (ROUND((T.TOTAL_SPACE - F.FREE_SPACE)  T.TOTAL_SPACE, 4) * 100)||'%' PER_USED,
   T.AUTOEXTENSIBLE
  FROM (
         SELECT TABLESPACE_NAME,ROUND(SUM(BLOCKS * page/1024/ 1024)) FREE_SPACE
 FROM DBA_FREE_SPACE
 GROUP BY TABLESPACE_NAME
   ) F,
       ( SELECT TABLESPACE_NAME,AUTOEXTENSIBLE, ROUND(SUM(BYTES  1048576)) TOTAL_SPACE
     FROM DBA_DATA_FILES
         GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE
   ) T
 WHERE F.TABLESPACE_NAME = T.TABLESPACE_NAME;

select t2.name,
         path,
         t1.total_size*page/1024/1024 as total_size,
         t1.free_size*page/1024/1024 as free_size,
         t1.real_free_size*page/1024/1024 as real_free_size,
         t1.max_size,
         t1.next_size,
         t1.auto_extend
    from v$datafile t1,
         v$tablespace t2
   where t1.group_id=t2.id
order by name;

SQL> select  cast(dbms_metadata.get_ddl('USER','TEST') as VARCHAR) from dual; --看创建用户的DDL，有没有DISKSPACE LIMIT，大小是页的个数*page

SQL> SELECT QUOTAS.TABLESPACE_NAME, QUOTAS.USERNAME, MAX_BYTES FROM DBA_TS_QUOTAS QUOTAS WHERE QUOTAS.USERNAME = 'TEST';

经过以上3步检查后，发现是给用户设置了磁盘限额，才导致OUT OF SPACE。最后取消磁盘限额或放大磁盘限额解决这个问题。

放大磁盘限额：

SQL> alter user "TEST" diskspace limit 500; --单位是M

取消磁盘限额：

SQL> alter user "TEST" diskspace UNLIMITED;