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  |
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 #436824 is a reply to message #436740] |
Mon, 28 December 2009 12:46  |
 |
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
|
|
|
|
Goto Forum:
Current Time: Thu Dec 12 07:06:21 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
