space quota exceeded for tablespace（表空间不足）

space quota exceeded for tablespace（表空间不足）：解决之道

 

方法一（测试成功）：SQL> alter user "用户"quota unlimited on "表空间名";
　　 
　　User altered.

　　方法二：SQL> alter user "用户" default tablespace "表空间名";　　 

        2  quota 0 on system
　　  3  quota 500M on testtablespace
　　  4  /
　　
User altered.
　　
方法三：SQL> GRANT UNLIMITED TABLESPACE TO test;
　　 
Grant succeeded.

 

　　

Oracle 查看表空间

查看各表空间的使用率 select df.tablespace_name "表空间名",totalspace "总空间M",freespace "剩余空间M",round((1-freespace/totalspace)*100,2) "使用率%" from (select tablespace_name,round(sum(bytes)/1024/1024) totalspace from dba_data_files group by tablespace_name) df, (select tablespace_name,round(sum(bytes)/1024/1024) freespace from dba_free_space group by tablespace_name) fs where df.tablespace_name=fs.tablespace_name; 查看回滚段命中率情况 select rn.name,rs.gets "被访问次数",rs.waits "等待回退段块的次数",(rs.waits/rs.gets)*100 "命中率" from v$rollstat rs,v$rollname rn; 查看当前用户每个表占用空间的大小：     Select Segment_Name,Sum(bytes)/1024/1024 From User_Extents Group By Segment_Name order by Sum(bytes); 查看每个表空间占用空间的大小：     Select Tablespace_Name,Sum(bytes)/1024/1024 From Dba_Segments Group By Tablespace_Name;

 

/u01/oradata/orcl/users01.dbf' resize 50m;