Shulou(Shulou.com)06/01 Report--

friend production database exceeds the space limit of the data tablespace when inserting data into a particular table If so, check the quota information of the table space used by the user, check the quota of the table space used by the user, and find that there is no limit, because max_bytes is-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 selectedSQL>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 30TEST 6790 34170 40960 83USERS 173 25427 25600 99UNDOTBS2 227 24013 24240 99DATA 990119 176281 1166400 15SYSAUX 3925 1195 5120 23UNDOTBS1 12898 28062 40960 697 rows selected

View dependencies of table lv_data

SQL> select NAME,TYPE from dba_dependencies where REFERENCED_NAME='LV_DATA';NAME TYPE------------------------------ ------------------LV_DATA VIEWFC_UPDATE_CORPFUND PROCEDUREFC_UPDATE_MY PROCEDUREFC_UPDATE_KY PROCEDUREFC_UPDATE_FACTPAY PROCEDUREFC_UPDATE_CALCPAY PROCEDUREFC_UPDATE_KY PROCEDURE...... LV_DATA SYNONYMLV_DATA VIEWLV_DATA SYNONYMLV_DATA SYNONYM139 rows selected

View all dependent object owners

SQL> select distinct owner from dba_objects where OBJECT_NAME in(select NAME from dba_dependencies where REFERENCED_NAME='LV_DATA');OWNER------------------------------SYSY_BKWEBCXDATAOLDTESTXCCZOSYBACKUPTJ12 rows selected

Grant all dependent object owners unlimited access to tablespaces

SQL> grant unlimited tablespace to OSY;Grant succeededSQL> grant unlimited tablespace to SBK;Grant succeededSQL> grant unlimited tablespace to WEB;Grant succeededSQL> grant unlimited tablespace to CX;Grant succeededSQL> grant unlimited tablespace to DATA;Grant succeededSQL> grant unlimited tablespace to OLD;Grant succeededSQL> grant unlimited tablespace to TEST;Grant succeededSQL> grant unlimited tablespace to XC;Grant succeededSQL> grant unlimited tablespace to CZ;Grant succeededSQL> grant unlimited tablespace to SY;Grant succeededSQL> grant unlimited tablespace to BACKUP;Grant succeededSQL> grant unlimited tablespace to TJ;Grant succeeded

Restore normal when inserting data into table lv_data again

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.