Home » SQL & PL/SQL » SQL & PL/SQL » backup and restore the view (oracle 9.2.0.7.0)
backup and restore the view [message #436513] Wed, 23 December 2009 22:19 Go to next message
chuikingman
Messages: 90
Registered: August 2009
Member
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 ?? I do not backup the whole DB by hot backup and cold backup

Please describe in detailed
Re: backup and restore the view [message #436515 is a reply to message #436513] Wed, 23 December 2009 22:42 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
DBMS_METADATA.GET_DDL

run procedure above or query view below

SQL> DESC USER_VIEWS
 Name					   Null?    Type
 ----------------------------------------- -------- ----------------------------
 VIEW_NAME				   NOT NULL VARCHAR2(30)
 TEXT_LENGTH					    NUMBER
 TEXT						    LONG
 TYPE_TEXT_LENGTH				    NUMBER
 TYPE_TEXT					    VARCHAR2(4000)
 OID_TEXT_LENGTH				    NUMBER
 OID_TEXT					    VARCHAR2(4000)
 VIEW_TYPE_OWNER				    VARCHAR2(30)
 VIEW_TYPE					    VARCHAR2(30)
 SUPERVIEW_NAME 				    VARCHAR2(30)
Re: backup and restore the view [message #436521 is a reply to message #436515] Wed, 23 December 2009 23:31 Go to previous messageGo to next message
chuikingman
Messages: 90
Registered: August 2009
Member
HI,
So, In my case ,
I need to type
desc RCA_CARD_VIEW_PRIVATE3 ;
OR
select * from USER_VIEWS where VIEW_NAME='RCA_CARD_VIEW_PRIVATE3';

Which one ???

How can I delete and re-create the view ???
Any detailed description ???

Where can I get DBMS_METADATA.GET_DDL ??
It is not the SQL command ......
Re: backup and restore the view [message #436524 is a reply to message #436521] Wed, 23 December 2009 23:37 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
>Where can I get DBMS_METADATA.GET_DDL ??

I am sorry to see that GOOGLE & SEARCH are broken for you.
Please be patient while repairs are completed.
Additional information will be posted when it becomes available.
Re: backup and restore the view [message #436542 is a reply to message #436521] Thu, 24 December 2009 01:53 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
How can I delete and re-create the view ???
Any detailed description ???


You drop a view with DROP VIEW....

Quote:
Where can I get DBMS_METADATA.GET_DDL ??
It is not the SQL command ......


No - it's a pl/sql command.
Try DESC DBMS_METADATA, or even reading the documentation.

At the end of the day, the best way to be able to recreate the view is to ave a copy of the DDL statement that you use to create it stored in a source control repository.
Previous Topic: multibyte characters to a csv file
Next Topic: update query required
Goto Forum:
  


Current Time: Wed Dec 07 04:39:30 CST 2016

Total time taken to generate the page: 0.10580 seconds