朋友生产数据库在向特定的一张表插入数据时报超出表空间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插入数据时恢复正常
