1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

error when trying to refresh materialized view

Discussion in 'SQL PL/SQL' started by ecivgamer, Nov 18, 2014.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Hi all,

    Got error when trying to refresh materialized view.

    exec dbms_refresh.refresh('scheme.SNAP_1');

    Error report -
    ORA-01882: timezone region not found
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2254
    ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2460
    ORA-06512: at "SYS.DBMS_IREFRESH", line 683
    ORA-06512: at "SYS.DBMS_REFRESH", line 195
    ORA-06512: at line 1
    01882. 00000 - "timezone region not found"
    *Cause: The specified region name was not found.
    *Action: Contact Oracle Support Services.


    What is it and how to solve?


    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
    PL/SQL Release 10.2.0.3.0 - Production
    CORE 10.2.0.3.0 Production
    TNS for Solaris: Version 10.2.0.3.0 - Production
    NLSRTL Version 10.2.0.3.0 - Production
     
  2. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    The source of materialized view:

    SELECT
    column1,
    column2
    FROM scheme1.table1@dblink1



    ORA-01882: timezone region not found
    ORA-02063: preceding line from PRODFIN
    01882. 00000 - "timezone region not found"
    *Cause: The specified region name was not found.
    *Action: Contact Oracle Support Services.
    Error at Line: 33 Column: 25

    How to solve it?
     
  3. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi.
    For a start :

    Code (SQL):

     
    SELECT * FROM v$timezone_file ;

    SELECT * FROM v$timezone_names;

     SELECT dbtimezone FROM dual;

    SELECT dbtimezone FROM dual@dblink1;

     
     
  4. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    Ubuntu, oracle sql developer



    I edited the config file in /opt/sqldeveloper/sqldeveloper/bin/sqldeveloper.conf adding



    AddVMOption -Duser.timezone="+02:00" (My time zone)



    It worked for me.