1. I create every MV based on the proper tables and specify the initial extend size properly .
2. There are still 1GB data in the tablespace which allocate to the MVs
3. The Master Tables changed very few .
4. The Refresh is 'Fast' mode
5. Sometime , the refresh action will cause the oracle error ,
the tablespace [My MV Tablespace] can not extent XXXX
How to investigate in this case ?
Check the NEXT extent size is adequate, must be > than the amount stated in the 'cannot extend xxK' error.
;)
Quote:
Originally posted by LKBrwn_DBA
Check the NEXT extent size is adequate, must be > than the amount stated in the 'cannot extend xxK' error.
;)
The Error Message is ORA-01536.
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT
1 AR_COLLECTION 104857600 524288
2 AR_CONSIGNMENT 268435456 524288
3 AR_CON_OPTION_DTL 10485760 524288
4 AR_CON_PIC_STAT_HIST 41943040 524288
5 AR_CON_PIECE 62914560 524288
6 AR_CON_STAT_HIST 41943040 524288
7 AR_INV_CON_DTL 41943040 524288
8 AR_INV_HDR 41943040 524288
9 AR_INV_STAT_HIST 41943040 524288
..................................
NExt extenT IS VERY SMALL ACTUALLY
I found below thread in Metalink :
( It looks very interesting :)
The information in this article applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.4
This problem can occur on any platform.
Errors
ORA 1536 space quota exceeded for tablespace ""
Symptoms
Insert on a table reports,ORA-01536.
But enough space is available in the tablespace.
grant unlimited tablespace to username.
But still faces the same error.
Cause
There may be a dependency object on this table.
Insert on this object may need to update the dependant object, which really exhausted the quota.
But the error will be reported generally.
Fix
1.Find for any dependant object over that table.
select NAME,TYPE from dba_dependencies where REFERENCED_NAME='table name';
2.If found, find the owner of that object.
select OWNER,OBJECT_NAME from dba_objects where OBJECT_NAME='dependant object name';
3.Grant unlimited tablespace to that user.
grant unlimited tablespace to;
4.Now you can be able to do the insertion over that table.
Well did the Metalink note solve your problem..?
:)
It suppose work :)