Error while try to Refresh Materialized view
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Error while try to Refresh Materialized view

  1. #1
    Join Date
    Apr 2008
    Posts
    4

    Unhappy 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

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  3. #3
    Join Date
    Apr 2008
    Posts
    4

    Question 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

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.

  5. #5
    Join Date
    Apr 2008
    Posts
    4

    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



  6. #6
    Join Date
    Sep 2000
    Location
    Sao Paulo,SP,Brazil, Earth, Milky Way
    Posts
    350
    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

  7. #7
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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
    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.

  8. #8
    Join Date
    Apr 2008
    Posts
    4

    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

  9. #9
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    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
  •  



Click Here to Expand Forum to Full Width