朋友生产数据库在向特定的一张表插入数据时报超出表空间data的空间限额如是执行下查看用户所用的表空间配额信息，查看用户所使用表空间的配额发现没有限制,因为max_bytes为 -1
SQL> select * from dba_ts_quotas where username='data'; TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DROPPED ------------------------------ ------------------------------ ---------- ---------- ---------- ---------- ------- data data 8825732464 -1 107735992 -1 NO 1 rows selected SQL> SQL> select 2 fs.tablespace_name "Tablespace", 3 (df.totalspace-fs.freespace) "Used MB", 4 fs.freespace "Free MB", 5 df.totalspace "Total MB", 6 round(100*(fs.freespace/df.totalspace)) "Pct. Free" 7 from 8 (select tablespace_name,round(sum(bytes)/1024/1024) TotalSpace 9 from dba_data_files group by tablespace_name) df, 10 (select tablespace_name,round(sum(bytes)/1024/1024) FreeSpace 11 from dba_free_space group by tablespace_name) fs 12 where df.tablespace_name=fs.tablespace_name; Tablespace Used MB Free MB Total MB Pct. Free ------------------------------ ---------- ---------- ---------- ---------- SYSTEM 7207 3033 10240 30 TEST 6790 34170 40960 83 USERS 173 25427 25600 99 UNDOTBS2 227 24013 24240 99 DATA 990119 176281 1166400 15 SYSAUX 3925 1195 5120 23 UNDOTBS1 12898 28062 40960 69 7 rows selected
查看表lv_data的依赖对象
SQL> select NAME,TYPE from dba_dependencies where REFERENCED_NAME='LV_DATA'; NAME TYPE ------------------------------ ------------------ LV_DATA VIEW FC_UPDATE_CORPFUND PROCEDURE FC_UPDATE_MY PROCEDURE FC_UPDATE_KY PROCEDURE FC_UPDATE_FACTPAY PROCEDURE FC_UPDATE_CALCPAY PROCEDURE FC_UPDATE_KY PROCEDURE ...... LV_DATA SYNONYM LV_DATA VIEW LV_DATA SYNONYM LV_DATA SYNONYM 139 rows selected
查看所有依赖对象的所有者
SQL> select distinct owner from dba_objects where OBJECT_NAME in(select NAME from dba_dependencies where REFERENCED_NAME='LV_DATA'); OWNER ------------------------------ SY SY_BK WEB CX DATA OLD TEST XC CZ OSY BACKUP TJ 12 rows selected
对所有依赖对象所有者授权可以无限制使用表空间
SQL> grant unlimited tablespace to OSY; Grant succeeded SQL> grant unlimited tablespace to SBK; Grant succeeded SQL> grant unlimited tablespace to WEB; Grant succeeded SQL> grant unlimited tablespace to CX; Grant succeeded SQL> grant unlimited tablespace to DATA; Grant succeeded SQL> grant unlimited tablespace to OLD; Grant succeeded SQL> grant unlimited tablespace to TEST; Grant succeeded SQL> grant unlimited tablespace to XC; Grant succeeded SQL> grant unlimited tablespace to CZ; Grant succeeded SQL> grant unlimited tablespace to SY; Grant succeeded SQL> grant unlimited tablespace to BACKUP; Grant succeeded SQL> grant unlimited tablespace to TJ; Grant succeeded
再向表lv_data插入数据时恢复正常