Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2009
    Posts
    55

    Unanswered: restore the view after update/replace the view

    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 ???

  2. #2
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by chuikingman View Post
    So, If I update the view and I want to change back to origianl view
    To "restore" the view, simply run your original CREATE statement (ideally stored in a version control system)

    Od I need to drop the view first any re-creat it again.
    If you drop it, you lose any possibly defined privileges on the view.
    Otherwise there is no difference between CREATE OR REPLACE and DROP/CREATE

    Btw: it's usually not such a good idea to force an index usage, especially with any current Oracle version.
    Did you really make sure the index usage is faster than whatever the optimizer choses?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •