FAQ
Is it correct that upgrading from 9i to 10g will change the MV refresh behaviour. I checked in one forum that it will use delete command for complte refresh instead of Truncate. Has anybody seen this issue. If this is true then Is there any parameter in init.ora that can change the default behaiour of 9i like using Truncate. I checked the forum and they are referring Atomic Refresh attribute in DBMS_mview can revert the process but it is not good to modify several MV.

Is it only true when we move from 9i to 10g or also true if MV are created in 10g itself.

TIA

Sanjay

Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs

Search Discussions

  • Bradd Piontek at Mar 5, 2008 at 7:08 pm
    Sanjay,
    Yes, this is the default behavior in 10g and to modify it you need to use
    the DBMS_MVIEW package (easy enough to script or write an anonymous PL/SQL
    for). I personally like the change in behavior as it makes the data more
    accessible (always had those users hitting the table while the refresh was
    occuring and seeing empty rows).

    I'm not sure what happens when you upgrade. I would guess the views would
    work as they did in 9i and only work as the default if you changed them or
    re-created them, but I could be wrong.

    Bradd
    On Wed, Mar 5, 2008 at 12:49 PM, Sanjay Mishra wrote:

    Is it correct that upgrading from 9i to 10g will change the MV refresh
    behaviour. I checked in one forum that it will use delete command for
    complte refresh instead of Truncate. Has anybody seen this issue. If this is
    true then Is there any parameter in init.ora that can change the default
    behaiour of 9i like using Truncate. I checked the forum and they are
    referring Atomic Refresh attribute in DBMS_mview can revert the process but
    it is not good to modify several MV.

    Is it only true when we move from 9i to 10g or also true if MV are created
    in 10g itself.

    TIA
    Sanjay

    ------------------------------
    Looking for last minute shopping deals? Find them fast with Yahoo! Search.<http://us.rd.yahoo.com/evt=51734/*http://tools.search.yahoo.com/newsearch/category.php?category=shopping>
    --
    http://www.freelists.org/webpage/oracle-l
  • Johnson, William L (TEIS) at Mar 5, 2008 at 7:36 pm
    When you upgrade to Oracle 10g, the behavior changes even if the
    Materialized views are not dropped and recreated. This is the kind of
    change in behavior that makes our lives interesting. Changing the
    default behavior for a MV with 19 million rows doesn't work so well when
    you move from truncate to delete...



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Bradd Piontek
    Sent: Wednesday, March 05, 2008 2:09 PM
    To: smishra_97_at_yahoo.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Oracle 9i vs 10g MV refresh behaviour



    Sanjay,
    Yes, this is the default behavior in 10g and to modify it you need to
    use the DBMS_MVIEW package (easy enough to script or write an anonymous
    PL/SQL for). I personally like the change in behavior as it makes the
    data more accessible (always had those users hitting the table while the
    refresh was occuring and seeing empty rows).

    I'm not sure what happens when you upgrade. I would guess the views
    would work as they did in 9i and only work as the default if you changed
    them or re-created them, but I could be wrong.

    Bradd

    On Wed, Mar 5, 2008 at 12:49 PM, Sanjay Mishra
    wrote:

    Is it correct that upgrading from 9i to 10g will change the MV refresh
    behaviour. I checked in one forum that it will use delete command for
    complte refresh instead of Truncate. Has anybody seen this issue. If
    this is true then Is there any parameter in init.ora that can change the
    default behaiour of 9i like using Truncate. I checked the forum and they
    are referring Atomic Refresh attribute in DBMS_mview can revert the
    process but it is not good to modify several MV.



    Is it only true when we move from 9i to 10g or also true if MV are
    created in 10g itself.



    TIA

    Sanjay



    Looking for last minute shopping deals? Find them fast with Yahoo!
    Search.
    <http://us.rd.yahoo.com/evt=51734/*http:/tools.search.yahoo.com/newsearc
    h/category.php?category=shopping>
  • Freeman, Donald at Mar 5, 2008 at 7:53 pm
    To get them to act like they did in 9i you have to use "Atomic Refresh"
    in your refresh options.


    Donald Freeman
    Database Administrator II
    Commonwealth of Pennsylvania
    Department of Health
    Bureau of Information Technology
    2150 Herr Street
    Harrisburg, PA 17103
    dofreeman_at_state.pa.us



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Bradd Piontek
    Sent: Wednesday, March 05, 2008 2:09 PM
    To: smishra_97_at_yahoo.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Oracle 9i vs 10g MV refresh behaviour

    Sanjay,
    Yes, this is the default behavior in 10g and to modify it you need to
    use the DBMS_MVIEW package (easy enough to script or write an anonymous
    PL/SQL for). I personally like the change in behavior as it makes the
    data more accessible (always had those users hitting the table while the
    refresh was occuring and seeing empty rows).

    I'm not sure what happens when you upgrade. I would guess the views
    would work as they did in 9i and only work as the default if you changed
    them or re-created them, but I could be wrong.

    Bradd

    On Wed, Mar 5, 2008 at 12:49 PM, Sanjay Mishra
    wrote:

    Is it correct that upgrading from 9i to 10g will change the MV
    refresh behaviour. I checked in one forum that it will use delete
    command for complte refresh instead of Truncate. Has anybody seen this
    issue. If this is true then Is there any parameter in init.ora that can
    change the default behaiour of 9i like using Truncate. I checked the
    forum and they are referring Atomic Refresh attribute in DBMS_mview can
    revert the process but it is not good to modify several MV.


    Is it only true when we move from 9i to 10g or also true if MV
    are created in 10g itself.


    TIA
    Sanjay

    Looking for last minute shopping deals? Find them fast with
    Yahoo! Search.
    <http://us.rd.yahoo.com/evt=51734/*http://tools.search.yahoo.com/newsear
    ch/category.php?category=shopping>

    --
    http://www.freelists.org/webpage/oracle-l
  • Harvinder Singh at Mar 5, 2008 at 7:09 pm
    Hi,



    We are not able to connect to RAC instance unless we stop the listener
    using svrctl command and restart it again using lsnrctl command,

    Do I need to add anything in tnsnames.ora files since out listener name
    is listener_dev-rhas2 and not listener?



    $ srvctl status nodeapps -n dev-rhas2
    VIP is running on node: dev-rhas2
    GSD is running on node: dev-rhas2
    Listener is running on node: dev-rhas2
    ONS daemon is running on node: dev-rhas2
    -bash-3.00$ lsnrctl status

    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 05-MAR-2008
    13:43:57

    Copyright (c) 1991, 2006, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTO =tcp)(HOST=)(PORT=1521))

    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener

    Linux Error: 111: Connection refused

    -bash-3.00$ lsnrctl status listener_dev-rhas2

    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 05-MAR-2008
    13:44:11

    Copyright (c) 1991, 2006, Oracle. All rights reserved.
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER

    Alias LISTENER_DEV-RHAS2
    Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    Start Date 05-MAR-2008 13:33:09
    Uptime 0 days 0 hr. 11 min. 1 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF

    Listener Parameter File
    /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File
    /opt/oracle/oracle/product/10.2.0/db_1/network/log/listener_dev-rhas2.lo
    g
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.137)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
    service...
    Service "netmeter" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter1" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter2" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter3" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeterXDB" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter_XPT" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    The command completed successfully

    Following is entry in tnsnames.ora:
    linux2 =
    (DESCRIPTION =

    (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.137)(PORT = 1521))

    )
    (CONNECT_DATA =

    (SERVICE_NAME = netmeter)
    (instance_name = netmeter2)
    )
    )

    C:\orasupport>tnsping linux2



    TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on
    05-MAR-2

    008 13:57:33



    Copyright (c) 1997, 2007, Oracle. All rights reserved.



    Used parameter files:

    C:\app\hsingh\product\11.1.0\client_1\network\admin\sqlnet.ora





    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS =

    (PROTOCOL = TCP)

    (HOST = 192.168.1.137)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =

    netmeter)

    (instance_name = netmeter2)))

    OK (0 msec)

    C:\orasupport>sqlplus dblair/dblair_at_linux2

    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 5 13:46:47 2008

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:

    ORA-12516: TNS:listener could not find available handler with matching
    protocol
    stack

    This only works If i stop the listener using svrctl then start the
    listener with lsnrctl start.



    srvctl stop listener -n dev-rhas2

    lsnrctl start



    C:\orasupport>sqlplus dblair/dblair_at_linux2



    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 5 14:01:26 2008



    Copyright (c) 1982, 2007, Oracle. All rights reserved.





    Connected to:

    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

    With the Partitioning, Real Application Clusters, OLAP and Data Mining
    options



    SQL>



    Thanks

    --Harvinder
  • Andrew Kerber at Mar 5, 2008 at 7:47 pm
    Is your local_listener parameter set in your init.ora or spfile? If so,
    what does it say?

    On Wed, Mar 5, 2008 at 1:09 PM, Harvinder Singh <
    Harvinder.Singh_at_metratech.com> wrote:
    Hi,



    We are not able to connect to RAC instance unless we stop the listener
    using svrctl command and restart it again using lsnrctl command,

    Do I need to add anything in tnsnames.ora files since out listener name is
    listener_dev-rhas2 and not listener?



    $ srvctl status nodeapps -n dev-rhas2
    VIP is running on node: dev-rhas2
    GSD is running on node: dev-rhas2
    Listener is running on node: dev-rhas2
    ONS daemon is running on node: dev-rhas2
    -bash-3.00$ lsnrctl status

    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 05-MAR-2008 13:43:57

    Copyright (c) 1991, 2006, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTO =tcp)(HOST=)(PORT=1521))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    Linux Error: 111: Connection refused

    -bash-3.00$ lsnrctl status listener_dev-rhas2

    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 05-MAR-2008 13:44:11

    Copyright (c) 1991, 2006, Oracle. All rights reserved.
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_DEV-RHAS2
    Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    Start Date 05-MAR-2008 13:33:09
    Uptime 0 days 0 hr. 11 min. 1 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File
    /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File
    /opt/oracle/oracle/product/10.2.0/db_1/network/log/listener_dev-rhas2.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.137)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
    service...
    Service "netmeter" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter1" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter2" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter3" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeterXDB" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    Service "netmeter_XPT" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this service...
    The command completed successfully

    Following is entry in tnsnames.ora:
    linux2 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.137)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = netmeter)
    (instance_name = netmeter2)
    )
    )

    C:\orasupport>tnsping linux2



    TNS Ping Utility for 32-bit Windows: Version 11.1.0.6.0 - Production on
    05-MAR-2
    008 13:57:33



    Copyright (c) 1997, 2007, Oracle. All rights reserved.



    Used parameter files:

    C:\app\hsingh\product\11.1.0\client_1\network\admin\sqlnet.ora





    Used TNSNAMES adapter to resolve the alias

    Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL
    = TCP)
    (HOST = 192.168.1.137)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME =
    netmeter)

    (instance_name = netmeter2)))

    OK (0 msec)


    C:\orasupport>sqlplus dblair/dblair_at_linux2

    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 5 13:46:47 2008

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12516: TNS:listener could not find available handler with matching
    protocol
    stack

    This only works If i stop the listener using svrctl then start the
    listener with lsnrctl start.



    srvctl stop listener -n dev-rhas2

    lsnrctl start



    C:\orasupport>sqlplus dblair/dblair_at_linux2



    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 5 14:01:26 2008



    Copyright (c) 1982, 2007, Oracle. All rights reserved.





    Connected to:

    Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

    With the Partitioning, Real Application Clusters, OLAP and Data Mining
    options



    SQL>


    Thanks

    --Harvinder

    --
    Andrew W. Kerber

    'If at first you dont succeed, dont take up skydiving.'

    --
    http://www.freelists.org/webpage/oracle-l
  • Jason Heinrich at Mar 5, 2008 at 8:20 pm
    It looks like you have one instance auto-registering under several
    different service names. Can you connect to a different service, or do
    they all give the same error?

    Have a look at Metalink note 240710.1. Is your database particularly
    busy? It seems there was a problem with the listener and
    automatically registered instances, where under heavy load the
    listener could think that the instance could not handle anymore
    connections, and returned the error you are seeing. The solution is
    to increase your PROCESSES parameter in the database.

    Google "ORA-12516": it has a few good results.
    On 3/5/08, Harvinder Singh wrote:




    Hi,



    We are not able to connect to RAC instance unless we stop the listener using
    svrctl command and restart it again using lsnrctl command,

    Do I need to add anything in tnsnames.ora files since out listener name is
    listener_dev-rhas2 and not listener?
    --
    Jason Heinrich
    --
    http://www.freelists.org/webpage/oracle-l
  • Dan Norris at Mar 6, 2008 at 4:02 am
    <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">

    I'll bet US$1 that your TNS entry is using the base IP address of the
    host (which I assume to be 192.168.1.137) instead of the VIP address
    which is where the listener is listening.
    I'd like to see the lsnrctl status of the listener after you start it
    manually. I'm going to guess/expect that it has some difference from
    the status output you've shown below. While you're at it, throw in the
    contents of the /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
    too and we should be able to figure out what's up.
    Having said all that, I don't see any reason why the connection below
    should fail. So far, you've only included details about the non-working
    configuration. Since you've got an alternative that works, include the
    same details about that and it should help us debug with you.
    Dan

    Harvinder Singh wrote:

    <!--
    /* Style Definitions */
    p.MsoNormal, li.MsoNormal, div.MsoNormal
    {margin:0in;
    margin-bottom:.0001pt;
    font-size:12.0pt;
    font-family:"Times New Roman";}
    a:link, span.MsoHyperlink
    {color:blue;
    text-decoration:underline;}
    a:visited, span.MsoHyperlinkFollowed
    {color:blue;
    text-decoration:underline;}
    span.EmailStyle17
    {mso-style-type:personal-reply;
    font-family:Arial;
    color:navy;}
    @page Section1
    {size:8.5in 11.0in;
    margin:1.0in 1.25in 1.0in 1.25in;}
    div.Section1
    {page:Section1;}
    -->

    Hi,

    We are not able to connect to RAC instance
    unless we stop the listener
    using svrctl command and restart it again using lsnrctl command,
    Do I need to add anything in tnsnames.ora
    files since out listener name
    is listener_dev-rhas2 and not listener?

    $ srvctl status nodeapps -n dev-rhas2
    VIP is running on node: dev-rhas2
    GSD is running on node: dev-rhas2
    Listener is running on node: dev-rhas2
    ONS daemon is running on node: dev-rhas2
    -bash-3.00$ lsnrctl status

    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 05-MAR-2008
    13:43:57

    Copyright (c) 1991, 2006, Oracle. All rights reserved.

    Connecting to (ADDRESS=(PROTO =tcp)(HOST=)(PORT=1521))
    TNS-12541: TNS:no listener
    TNS-12560: TNS:protocol adapter error
    TNS-00511: No listener
    Linux Error: 111: Connection refused

    -bash-3.00$ lsnrctl status listener_dev-rhas2

    LSNRCTL for Linux: Version 10.2.0.3.0 - Production on 05-MAR-2008
    13:44:11

    Copyright (c) 1991, 2006, Oracle. All rights reserved.
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
    STATUS of the LISTENER
    ------------------------
    Alias LISTENER_DEV-RHAS2
    Version TNSLSNR for Linux: Version 10.2.0.3.0 - Production
    Start Date 05-MAR-2008 13:33:09
    Uptime 0 days 0 hr. 11 min. 1 sec
    Trace Level off
    Security ON: Local OS Authentication
    SNMP OFF
    Listener Parameter File
    /opt/oracle/oracle/product/10.2.0/db_1/network/admin/listener.ora
    Listener Log File
    /opt/oracle/oracle/product/10.2.0/db_1/network/log/listener_dev-rhas2.log
    Listening Endpoints Summary...
    (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1)))
    (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.1.137)(PORT=1521)))
    Services Summary...
    Service "PLSExtProc" has 1 instance(s).
    Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this
    service...
    Service "netmeter" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this
    service...
    Service "netmeter1" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this
    service...
    Service "netmeter2" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this
    service...
    Service "netmeter3" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this
    service...
    Service "netmeterXDB" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this
    service...
    Service "netmeter_XPT" has 1 instance(s).
    Instance "netmeter2", status READY, has 1 handler(s) for this
    service...
    The command completed successfully

    Following is entry in tnsnames.ora:
    linux2 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.137)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = netmeter)
    (instance_name = netmeter2)
    )
    )

    C:\orasupport>tnsping linux2

    TNS Ping Utility for 32-bit Windows: Version
    11.1.0.6.0 - Production on
    05-MAR-2
    008 13:57:33

    Copyright (c) 1997, 2007, Oracle.  All rights
    reserved.

    Used parameter files:
    C:\app\hsingh\product\11.1.0\client_1\network\admin\sqlnet.ora


    Used TNSNAMES adapter to resolve the alias
    Attempting to contact (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS =
    (PROTOCOL = TCP)
    (HOST = 192.168.1.137)(PORT = 1521)))
    (CONNECT_DATA = (SERVICE_NAME =
    netmeter)
    (instance_name = netmeter2)))
    OK (0 msec)

    C:\orasupport>sqlplus dblair/dblair@linux2

    SQL*Plus: Release 11.1.0.6.0 - Production on Wed Mar 5 13:46:47 2008

    Copyright (c) 1982, 2007, Oracle. All rights reserved.

    ERROR:
    ORA-12516: TNS:listener could not find available handler with matching
    protocol
    stack

    This only works If i stop the listener using svrctl then start the
    listener
    with lsnrctl start.

    srvctl stop listener -n dev-rhas2
    lsnrctl start

    C:\orasupport>sqlplus dblair/dblair@linux2

    SQL*Plus: Release 11.1.0.6.0 - Production on
    Wed Mar 5 14:01:26 2008

    Copyright (c) 1982, 2007, Oracle.  All rights
    reserved.


    Connected to:
    Oracle Database 10g Enterprise
    Edition Release 10.2.0.3.0 - Production
    With the Partitioning, Real Application
    Clusters, OLAP and Data Mining
    options

    SQL>

    Thanks
    --Harvinder
  • Sanjay Mishra at Mar 5, 2008 at 8:11 pm
    Thanks for the update. It means the only way is to modify all MV to refresh with Atomic Refresh. I have a DW where we have several big 200+G MV refreshed qurterly. Here Delete can be big issue and so trying to test it before planning the upgrade in Test env.

    Sanjay

    Original Message ----
    From: "Freeman, Donald"
    To: piontekdd_at_gmail.com; smishra_97_at_yahoo.com
    Cc: oracle-l_at_freelists.org
    Sent: Wednesday, March 5, 2008 2:53:14 PM
    Subject: RE: Oracle 9i vs 10g MV refresh behaviour

    To get them to act like they did in 9i you have to use "Atomic Refresh" in your refresh options.


    Donald Freeman
    Database Administrator II
    Commonwealth of Pennsylvania
    Department of Health
    Bureau of Information Technology
    2150 Herr Street
    Harrisburg, PA 17103
    dofreeman_at_state.pa.us



    From: oracle-l-bounce_at_freelists.org On Behalf Of Bradd Piontek
    Sent: Wednesday, March 05, 2008 2:09 PM
    To: smishra_97_at_yahoo.com
    Cc: oracle-l_at_freelists.org
    Subject: Re: Oracle 9i vs 10g MV refresh behaviour

    Sanjay,
    Yes, this is the default behavior in 10g and to modify it you need to use the DBMS_MVIEW package (easy enough to script or write an anonymous PL/SQL for). I personally like the change in behavior as it makes the data more accessible (always had those users hitting the table while the refresh was occuring and seeing empty rows).

    I'm not sure what happens when you upgrade. I would guess the views would work as they did in 9i and only work as the default if you changed them or re-created them, but I could be wrong.

    Bradd

    On Wed, Mar 5, 2008 at 12:49 PM, Sanjay Mishra wrote:

    Is it correct that upgrading from 9i to 10g will change the MV refresh behaviour. I checked in one forum that it will use delete command for complte refresh instead of Truncate. Has anybody seen this issue. If this is true then Is there any parameter in init.ora that can change the default behaiour of 9i like using Truncate. I checked the forum and they are referring Atomic Refresh attribute in DBMS_mview can revert the process but it is not good to modify several MV.


    Is it only true when we move from 9i to 10g or also true if MV are created in 10g itself.


    TIA

    Sanjay

    Looking for last minute shopping deals? Find them fast with Yahoo! Search.

    Never miss a thing. Make Yahoo your home page.
    http://www.yahoo.com/r/hs
  • Gints Plivna at Mar 5, 2008 at 9:03 pm
    This was already discussed in this list here
    http://www.freelists.org/archives/oracle-l/01-2007/msg00666.html
    and asktom here
    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15695764787749

    Gints Plivna
    http://www.gplivna.eu

    2008/3/5, Sanjay Mishra :
    Is it correct that upgrading from 9i to 10g will change the MV refresh
    behaviour. I checked in one forum that it will use delete command for
    complte refresh instead of Truncate. Has anybody seen this issue. If this is
    true then Is there any parameter in init.ora that can change the default
    behaiour of 9i like using Truncate. I checked the forum and they are
    referring Atomic Refresh attribute in DBMS_mview can revert the process but
    it is not good to modify several MV.

    Is it only true when we move from 9i to 10g or also true if MV are created
    in 10g itself.
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedMar 5, '08 at 6:49p
activeMar 6, '08 at 4:02a
posts10
users9
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase