Error while try to Refresh Materialized view

i have oracle 9.2.0.1 on windows server 2003

i have make schedule to referesh materialized views using oracle

but when it going to refresh ,it changes state of refresh in unusable

and when i try to do it manually it gives error
-----------------------------------
Ora-12008 error in materialized view refresh path
ora-01114 io error writing block to file 201(block #2621321)
ora-27069 skgfdisp:attmempt to do i/o beyond the range of file
osd-04026 invalid parameter passed(OS 2621327)
ora-01114 io error writing block to file 201(block#2621321)
ora-27069 skgfdisp:attempt do i/o beyond the range of the file
osd-04026 invalid parameter passed (OS 2621327)
ora 06512 at "SYS.DBMS_SNAPSHOt",line 794
ora 06512 at "SYS.DBMS_SNAPSHOt", line 851
ora 06512 at "SYS.DBMS_SNAPSHOt", line 832
ora 06512 at line 2

-----------------------------------

another thing i want to say when i restart my server and try to refresh manually it can refresh
but after day
when turn comes to refersh automatically gives same error
please help me

To which tablespace does file #201 belongs?

Is it your temp tablespace or is it the tablespace where your Mview is sitting on?

Hi
my all materialized view stays in tablespace called distdata07
i can not understand where i got file #201
how to check for it

Please execute and post...

Code:

---

select  file_id,
        file_name,
        tablespace_name
from    dba_data_files
where  file_id = 201
;

---

Thanks for favourable reply
when i try out above query it shows zero rows(no such file id) ,so i have excute without where clause and got




FILE_ID FILE_NAME TABLESPACE_NAME
-------------------------------------------------------------------------------------
1 E:\ORACLE\ORADATA\DISUPE\DISUPE\SYSTEM01.DBF SYSTEM
2 E:\ORACLE\ORADATA\DISUPE\DISUPE\UNDOTBS01.DBF UNDOTBS1
3 E:\ORACLE\ORADATA\DISUPE\DISUPE\CWMLITE01.DBF CWMLITE
4 E:\ORACLE\ORADATA\DISUPE\DISUPE\DRSYS01.DBF DRSYS
5 E:\ORACLE\ORADATA\DISUPE\DISUPE\EXAMPLE01.DBF EXAMPLE
6 E:\ORACLE\ORADATA\DISUPE\DISUPE\INDX01.DBF INDX
7 E:\ORACLE\ORADATA\DISUPE\DISUPE\ODM01.DBF ODM
8 E:\ORACLE\ORADATA\DISUPE\DISUPE\TOOLS01.DBF TOOLS
9 E:\ORACLE\ORADATA\DISUPE\DISUPE\USERS01.DBF USERS
10 E:\ORACLE\ORADATA\DISUPE\DISUPE\XDB01.DBF XDB
11 E:\WINDIS\DISUPE\DATA\DISUPEMST.ORA DISUPEMST
12 E:\WINDIS\DISUPE\DATA\UPEDISTDATA07.ORA DISTDATA07

hardik_982, surely if the file #201 is not in the dba_data_files list, it is a temp file of some kind.... I don´t have here a Win2003 to check, but maybe you have in it a HARD LIMIT to file sizes, the tempfiles are AUTOEXTENSIBLE and UNLIMITED in size, the query refreshing the MV is complex and it is consuming temp space, the tempfiles are automatically growing and passing the SO/hardware max size limit ?? The message "attempt to do i/o beyond the range of file" seams to confirm this hypothesis... Check this, if so simply recreate the temp tablespace with tempfiles having a (for your platform) valid MAXSIZE size, or even don´t use AUTOEXTEND at all and create the temp with the required size...

Regards,

Chiappa
Junior Member

Quote:

---

Originally Posted by hardik_982

when i try out above query it shows zero rows(no such file id)

---

That's very good news. Your offending datafile is part of your temporary tablespace, you can double-check that by running query below...

Code:

---

select  file_id,
        file_name,
        tablespace_name
from    dba_temp_files
where  file_id = 201
;

---

Here is the fix.
1- Drop the offending temporary tablespace; this is the one you got from the above query.
2- Re-Create your temporary tablespace; be sure no datafile is larger than 4 Gig and as Chiappa said make them of a fixed size -autoextend=no

hi JChiappa

i have so complex materialized view with lots of views and inside view so many table field with different schema
currently on server temp file size is 20482mb and it is 100% full with autoextent
so i have to recreate temp tablespace again?


Thanks

Quote:

---

Originally Posted by hardik_982

... temp file size is 20482mb and it is 100% full

---

What you see is temp tablespace HWM, it doesn't mean it is actually full.