-
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks
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
;
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
Thanks
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
-
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
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
-
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
-
hi
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
-
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.
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|