Home » SQL & PL/SQL » SQL & PL/SQL » restore the view after update/replace the view (oracle 9 , linux)
restore the view after update/replace the view [message #436740] Sun, 27 December 2009 03:49 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
Hi,
I use below to update the view RCA_CARD_VIEW_PRIVATE3
{code}
create or replace view RCA_CARD_VIEW_PRIVATE3 as
select /*+ INDEX(RCA_LINK_CARD_GROUP PK_RCA_LINK_CARD_GROUP) INDEX(RCA_SMART_CARD PK_RCA_SMART_CARD) */
RCA_SMART_CARD.N_CARD_ID N_CARD_ID,
RCA_CARD_PROFILE.C_PRO_NAME PROFILE_NAME,
RCA_CARD_PROFILE.N_PRO_STATE PROFILE_STATE,
RCA_SMART_CARD.C_CARD_SERIAL_NUMBER CARD_SERIAL_NUMBER,
RCA_SMART_CARD.C_SIM_IMSI SIM_IMSI,
RCA_SMART_CARD.C_SIM_MSISDN SIM_MSISDN,
RCA_SMART_CARD.N_SIM_STATE SIM_STATE,
RCA_CARD_GROUP.C_GROUP_IDENTIFIER GROUP_IDENTIFIER,
RCA_SMART_CARD.C_LINKED_CARD LINKED_CARD
from RCA_SMART_CARD, RCA_CARD_PROFILE, RCA_CARD_GROUP, RCA_LINK_CARD_GROUP
where
RCA_SMART_CARD.N_PRO_IDENTIFIER = RCA_CARD_PROFILE.N_PRO_IDENTIFIER
and RCA_SMART_CARD.N_CARD_ID = RCA_LINK_CARD_GROUP.N_CARD_ID(+)
and RCA_LINK_CARD_GROUP.N_GROUP_ID = RCA_CARD_GROUP.N_GROUP_ID(+)
WITH READ ONLY;
{code}

I get the data of original view by DBMS_METADATA.GET_DDL.

The original view is like below :
{code}
CREATE OR REPLACE FORCE VIEW "RCAADMIN"."RCA_CARD_VIEW_PRIVATE3" ("N_CARD_ID",
"PROFILE_NAME", "PROFILE_STATE", "CARD_SERIAL_NUMBER", "SIM_IMSI", "SIM_MSISDN"
, "SIM_STATE", "EEPROM_SPACE_LEFT", "VOLATILE_SPACE_LEFT", "NONVOLATILE_SPACE_LE
FT", "GROUP_IDENTIFIER", "LINKED_CARD") AS
select
RCA_SMART_CARD.N_CARD_ID N_CARD_ID,
RCA_CARD_PROFILE.C_PRO_NAME PROFILE_NAME,
RCA_CARD_PROFILE.N_PRO_STATE PROFILE_STATE,
RCA_SMART_CARD.C_CARD_SERIAL_NUMBER CARD_SERIAL_NUMBER,
RCA_SMART_CARD.C_SIM_IMSI SIM_IMSI,
RCA_SMART_CARD.C_SIM_MSISDN SIM_MSISDN,
RCA_SMART_CARD.N_SIM_STATE SIM_STATE,
RCA_SMART_CARD.N_EEPROM_SPACE_LEFT EEPROM_SPACE_LEFT,
RCA_SMART_CARD.N_VOLATILE_SPACE_LEFT VOLATILE_SPACE_LEFT,
RCA_SMART_CARD.N_NONVOLATILE_SPACE_LEFT NONVOLATILE_SPACE_LEFT,
RCA_CARD_GROUP.C_GROUP_IDENTIFIER GROUP_IDENTIFIER,
RCA_SMART_CARD.C_LINKED_CARD LINKED_CARD
from
RCA_SMART_CARD,
RCA_CARD_PROFILE,
RCA_CARD_GROUP,
RCA_LINK_CARD_GROUP
where
RCA_SMART_CARD.N_PRO_IDENTIFIER = RCA_CARD_PROFILE.N_PRO_IDENTIFIER
and RCA_SMART_CARD.N_CARD_ID = RCA_LINK_CARD_GROUP.N_CARD_ID(+)
and RCA_LINK_CARD_GROUP.N_GROUP_ID = RCA_CARD_GROUP.N_GROUP_ID(+)
WITH READ ONLY
{code}
So, If I update the view and I want to change back to origianl view .
Od I need to drop the view first any re-creat it again.
Or I just need to re-create it again only and ignore "drop the view "
Then the view can update without affect others ????
Any advice ???

Re: restore the view after update/replace the view [message #436741 is a reply to message #436740] Sun, 27 December 2009 04:33 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Then the view can update without affect others ????

You cannot change the view without affecting others.
Create your own one and a synonym with the name of the view (current one not new one) if you to test something.

Regards
Michel
Re: restore the view after update/replace the view [message #436751 is a reply to message #436740] Sun, 27 December 2009 10:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
a follow on post or repeat of below URL

http://www.orafaq.com/forum/m/436578/136107/#page_top
Re: restore the view after update/replace the view [message #436753 is a reply to message #436741] Sun, 27 December 2009 19:45 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
So, If I update the view by "create " ,
The restore the view by the original "create" ,
Will the view recover without any change ?????
so, the db return normal ????

Any advice ???
Re: restore the view after update/replace the view [message #436769 is a reply to message #436753] Mon, 28 December 2009 02:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
The view will be the one you give.

Regards
Michel
Re: restore the view after update/replace the view [message #436824 is a reply to message #436740] Mon, 28 December 2009 12:46 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not really sure what you are asking. But since the post seems to be related to dependencies with views and create vs. "create or replace" I have these comments.

1) when you use CREATE VIEW, this will only work if the view does not currently exist. Thus if it does exist you must drop it first. Dropping the view means you will also loose any grants and security rules set up for the view and thus you will have to recreate grants and security settings if any etc. after you create the view.

2) when you use CREATE OR REPLACE VIEW, this will work regardless of if the view exists of not. If the view already exists, then only its view text will be replaced and recompiled. Accepting for specific scenarios, all grants and security currently set up for the view will remain unchanged and you will not have to recreate these. Thus create or replace is the preferred method to use most of the time.

3) regardless of which method you use, the view is being recompiled. Thus any objects dependent upon this view will be marked as INVALID. Additionly, dependency tracking is recursive in Oracle so any objects dependent upon these now INVALID objects will also be marked INVALID, and so on till the full dependency tree is exhausted. Not to worry though as Oracle will automatically recompile these objects when they are next referenced so there is no need for you to manually recompile these dependent objects after you create the view.

Hope this helps, Kevin
Previous Topic: How to get the table names present in Package/Procedure/Functions
Next Topic: best practices writing a query
Goto Forum:
  


Current Time: Thu Dec 12 07:06:21 CST 2024