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

    Unanswered: backup and restore the view by SQL command

    Hi,
    I need to update the view by below
    ===
    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;
    ===
    But I want to backup and prepare to restore it by SQL command.
    How can I backup this by SQL command before I execute the above sql command .And How can I fall back the original view if I execute the above SQL command ?? What I need to collect first before I execute it ??
    Please describe in detailed

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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;
    just use your software version control system
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2009
    Posts
    55
    I do not understand .
    No sw control system in my case .
    How can I backup and re-create/restore this view by SQL command only...

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by chuikingman View Post
    No sw control system in my case
    Ouch!
    If you are running a production system you should have a version control system to store your database model's DDL (and migration script).
    I strongly recommend to setup one as soon as possible.

    How can I backup and re-create/restore this view by SQL command only...
    Check out the dbms_metadata package. It provides functions to retrieve the complete source from the system.
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_metada.htm#i1015856

    As an alternative you can retrieve the current source using
    Code:
    SELECT text 
    FROM user_views
    WHERE view_name = 'MY_VIEW_NAME'
    More details here:
    http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14237/statviews_4489.htm#i1635848

  5. #5
    Join Date
    Dec 2009
    Posts
    1
    i accept with information:
    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;

Posting Permissions

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