Home » SQL & PL/SQL » SQL & PL/SQL » Gererate Undo Query Logs corresponding to DDL/DML logs (oracle 9i)
Gererate Undo Query Logs corresponding to DDL/DML logs [message #380102] Fri, 09 January 2009 03:09 Go to next message
Mayank Saini
Messages: 6
Registered: January 2009
Location: India/Delhi/Noida
Junior Member
HI ALL ,
My requirement is to generate Undo Queries logs, by giving DDL/DML logs as a input.Suppose i have create / insert and update queries log, and i want the DDL/DML logs to revert these logs.
Please show me direction.
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380105 is a reply to message #380102] Fri, 09 January 2009 03:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I really don't understand what you're asking.

What are Query Logs?

Try either explaining in more detail, or providing an example, or preferably both.
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380107 is a reply to message #380102] Fri, 09 January 2009 03:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It all depends on what DML logs you have.
And what if you have triggers?
And what about children if you have foreign keys?

Regards
Michel
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380114 is a reply to message #380102] Fri, 09 January 2009 03:41 Go to previous messageGo to next message
Mayank Saini
Messages: 6
Registered: January 2009
Location: India/Delhi/Noida
Junior Member
Hi Michel
I m working on a application development tool ,by which you desing Forms/Screens , say create Form/ create Field. There can be foreign key relationship. We dont have triggers.
All these designing create DDL/DML logs in database.

My requirement is to build a feature by which we can revert these logs.
for example
CREATE TABLE info( VERSIONNO int)
ALTER TABLE info ADD version VARCHAR2(20)
ALTER TABLE info ADD CONSTRAINT pk_A55 PRIMARY KEY(instantAppsDBVersion )
ALTER TABLE info ADD releasedumppath VARCHAR2(20)
ALTER TABLE info ADD clearmodelassessorcacheforapp NUMBER(1,0


if this we assume log for development

i need logs like .
alter table info drop column clearmodelassessorcacheforapp ;
alter table info drop column releasedumppath ;
//drop constrain
then drop table..

to revert the development.

like this
Regards
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380115 is a reply to message #380114] Fri, 09 January 2009 03:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice exercise. Take care of side effect of some statements.
Good luck.

Regards
Michel
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380117 is a reply to message #380115] Fri, 09 January 2009 03:46 Go to previous messageGo to next message
Mayank Saini
Messages: 6
Registered: January 2009
Location: India/Delhi/Noida
Junior Member
Gud Luck Please suggest me somethink Smile
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380214 is a reply to message #380117] Fri, 09 January 2009 09:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Suggestion: Think of a way to UNDO a DROP TABLE statement, or TRUNCATE, or DELETE/UPDATE of a row.
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380219 is a reply to message #380214] Fri, 09 January 2009 09:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How were you planning to recover the data after you've un-done a DROP COLUMN (for example)
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380492 is a reply to message #380102] Mon, 12 January 2009 01:23 Go to previous messageGo to next message
Mayank Saini
Messages: 6
Registered: January 2009
Location: India/Delhi/Noida
Junior Member
if we generate undo query for the drop table , we have to take the data & structure of Table and store it some whr..
Like undo log of delete from <table> where ?;
is : insert into <table > values(?,?,)
like this i think.
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380516 is a reply to message #380492] Mon, 12 January 2009 03:10 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
How about relying on backups?
Is it really that bad if someone erroneously updates something that it is worth creating a complete new (and error-prone) application for it?
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #380525 is a reply to message #380516] Mon, 12 January 2009 03:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Frank wrote on Mon, 12 January 2009 10:10
How about relying on backups?

...Or flashback features (only for DML in his version)...

Regards
Michel

[Updated on: Mon, 12 January 2009 03:31]

Report message to a moderator

Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #385758 is a reply to message #380525] Tue, 10 February 2009 23:27 Go to previous messageGo to next message
Mayank Saini
Messages: 6
Registered: January 2009
Location: India/Delhi/Noida
Junior Member
We can rely on backup only for DML,not for DDL
Initailly i am planing to develop it like this.

execute all DDL first thereby generating drop table against create table alter against alter like this..
This will get revert if last DDL query is not executed.There by maintaining the application in initial stage...
we can use backup for DML i guess..???
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #385763 is a reply to message #385758] Tue, 10 February 2009 23:43 Go to previous messageGo to next message
srinivasreddy777
Messages: 11
Registered: October 2007
Location: Hyderabad
Junior Member

@Mayank

By following the thread i found, you want to take a backup copy of old data for each DML fired on a table (X). And you want to rollback to a specific stage by applying previous values.

My pointer on the same is to create a trigger (or in case of procedure please call it every time you fire DML) which forms a merge / delete statement for u.

In case of delete on table : Form the Merge with old values (for each row) and store merge statements in a table (Y).

In case of update on table : Again use the merge statement with the respective old values of table (X) and store the merge statement in (Y).

In case of insert on table : write a delete statement and store the statement in the table (Y).

You can apply the statements by order to rollback.

Hope this would solve your prob. Revert in case of any issues

[Updated on: Tue, 10 February 2009 23:45]

Report message to a moderator

Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #385812 is a reply to message #385763] Wed, 11 February 2009 03:16 Go to previous messageGo to next message
Mayank Saini
Messages: 6
Registered: January 2009
Location: India/Delhi/Noida
Junior Member
Thanks Srinivas,
You are right we can use flashback feature/ or merge statement to rollback DML to previous state .
But what will help in case of DDL Statement ,
say i have created two tables A and B having relationship between them. Now i want undo logs of these DDL ?? how can i get that..

Thanks in advance
Re: Gererate Undo Query Logs corresponding to DDL/DML logs [message #386088 is a reply to message #385812] Thu, 12 February 2009 10:14 Go to previous message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Hi

I think you are looking auditing kind of feature. If it is so try for some AUDIT feature in Oracle.

http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96521/audit.htm#13630

Thanks
Trivendra

[Updated on: Thu, 12 February 2009 10:15]

Report message to a moderator

Previous Topic: need solution
Next Topic: Query on PL/SQL table using CAST
Goto Forum:
  


Current Time: Fri Dec 02 17:01:58 CST 2016

Total time taken to generate the page: 0.31508 seconds