|
|
|
|
| Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436589 is a reply to message #436579] |
Thu, 24 December 2009 05:57   |
chuikingman
Messages: 90
Registered: August 2009
|
Member |
|
|
I describe more detail.
I will do below for the database
===================
INSERT INTO SAS_SERVICE (C_SERV_NAME, BL_ACTIVATED, C_CLASS_NAME) values......
create or replace view RCA_CARD_VIEW_PRIVATE3 as
select /*+ INDEX(RCA_LINK_CARD_GROUP PK_RCA_LINK_CARD_GROUP) INDEX(RCA_SMART_CARD.......
update bs_component
set c_comp_args = replace (c_comp_args, 'noclassgc', '').....
===========
So, If I want to restore back to original status after I make above transaction , What do I need to do ???I do not want to use hot or cold backup as the DB is huge and need much time to backup and restore .......
Please describe in more detailed ....
|
|
|
|
|
|
|
|
| Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436601 is a reply to message #436589] |
Thu, 24 December 2009 07:00 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
chuikingman wrote on Thu, 24 December 2009 12:57So, If I want to restore back to original status after I make above transaction , What do I need to do ???I do not want to use hot or cold backup as the DB is huge and need much time to backup and restore .......
Please describe in more detailed ....
More detailed than your question?
Firstly, the code you posted contains 3 (three) transactions, as CREATE VIEW is DDL command and Oracle implicitly issues commit before and after it.
Secondly, to get into the status before running the script, do reverse actions to the ones in the script.
For INSERT, issue appropriate DELETE.
For CREATE VIEW, issue CREATE VIEW with old definition or DELETE VIEW, if it did not exist before.
For UPDATE, issue appropriate UPDATE. If it is ambiguous (as you are cleaning some column content), "save" the old content into table CREATE TABLE <new_tab> AS SELECT * FROM bs_component UPDATE bs_component u
SET c_comp_args = (SELECT c_comp_args
FROM <new_tab> n
WHERE n.<primary_key> = u.<primary_key>)
@Kamran Agayev: I am afraid, flashback feature is not available in Oracle 9 (as the title specifies).
[Edit: Added the last paragraph]
[Updated on: Thu, 24 December 2009 07:03] Report message to a moderator |
|
|
|
| Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436604 is a reply to message #436601] |
Thu, 24 December 2009 07:24 |
chuikingman
Messages: 90
Registered: August 2009
|
Member |
|
|
>>CREATE TABLE <new_tab> AS SELECT * FROM bs_component
>>before running the script. For restore, UPDATE the original >>table using "saved" values.
>>UPDATE bs_component u
>>SET c_comp_args = (SELECT c_comp_args
>> FROM <new_tab> n
>> WHERE n.<primary_key> = u.<primary_key>)
I want to clarify.
So for restore the UPDATE.
For example I want to restore back row name="A" only in the old table u that have been update.n is "saved" table.u is the updated table .
UPDATE bs_component u
SET c_comp_args = (SELECT c_comp_args
FROM <new_tab> n
WHERE u.name = "A";
Please comment .
|
|
|
|
| Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436609 is a reply to message #436604] |
Thu, 24 December 2009 07:53 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
chuikingman wrote on Thu, 24 December 2009 14:24I want to clarify.
So for restore the UPDATE.
For example I want to restore back row name="A" only in the old table u that have been update.n is "saved" table.u is the updated table .
Hm. I thought you want to restore all changes made by the UPDATE statement. As it had no where condition (at least the one you posted), it may change all rows. Maybe you know, which rows contained 'noclassgc' in C_COMP_ARGS. Maybe you know the "old" content of that column. Then you do not have to cope with this - just update to the old value.
chuikingman wrote on Thu, 24 December 2009 14:24UPDATE bs_component u
SET c_comp_args = (SELECT c_comp_args
FROM <new_tab> n
WHERE u.name = "A";
Please comment .
Syntax error - using double quotes (") instead of single ones ('), missing right parenthesis ')' before semicolon ';'.
Semantically strange - you UPDATE all rows in BS_COMPONENT from one row in <new_tab>. If there are multiple rows with NAME = 'A', this fails with exception.
|
|
|
|
|
|
| Re: backup and restore after insert&update table and create&replace VIEW in oracle 9 [message #436613 is a reply to message #436610] |
Thu, 24 December 2009 08:11 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
chuikingman wrote on Thu, 24 December 2009 15:01How can I copy back the data from "saved table to the original table ???
Please advice
With the UPDATE statement I posted in my first post.
There are only two "unknown" things:
<new_tab> - the name of table containing "saved" data
<primary_key> - name of column which is primary key (or at least unique) on BS_COMPONENT table. So it uniquely identifies a row. I do not know which one it is, as you did not post it. Maybe NAME, maybe some other one, maybe more columns.
You can optimize it by "saving" only used columns (specifying them instead of *).
You can optimize it by "saving" only used rows (by adding the WHERE condition into both CREATE TABLE AS SELECT and UPDATE statements).
But it is not necessary.
[Edit: filled ""]
[Updated on: Thu, 24 December 2009 08:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
