Home » SQL & PL/SQL » SQL & PL/SQL » How can we rollback DDL changes (3 topics merged)
How can we rollback DDL changes (3 topics merged) [message #265477] Thu, 06 September 2007 07:15 Go to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi All,

How can i Rollback the DDL Commands (Database table design changes) in Oralce9i?


I have one database schema it has number of tables ,view,sequence, etc. if anybody changes the table , sequence design. i want "Rollback" the DDL changes.

Regards,
Siva.P
Bangalore
Re: How can i Rollback the DDL Commands (Database table design changes) in Oralce9i? [message #265480 is a reply to message #265477] Thu, 06 September 2007 07:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
How can i Rollback the DDL Commands (Database table design changes) in Oralce9i?

You can't.

Regards
Michel

[Updated on: Thu, 06 September 2007 08:34]

Report message to a moderator

Re: How can i Rollback the DDL Commands (Database table design changes) in Oralce9i? [message #265494 is a reply to message #265480] Thu, 06 September 2007 07:34 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi michel,

I want "Rollback" all the changes (DDL changes) in my Database object(table,view,,sequence,etc...)

i can view the recent DDL changes from all_objects system table.

is it possible to rollback the changes in oracle 9i? Please let me know if you have any options.

i know one option (flashback) is there in Oracle 10g.

Thanks,
Siva.P
Re: How can i Rollback the DDL Commands (Database table design changes) in Oralce9i? [message #265507 is a reply to message #265494] Thu, 06 September 2007 08:28 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Flashback won't work after DDL. You want a point in time recovery.

MHE
How can Rollback the DDL Changes in Database (Database Design Changes)...? [message #266791 is a reply to message #265477] Tue, 11 September 2007 11:51 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi,

I have one issue which i have faced in my database (oracle9i) My Onsite people are done the changes in Database design (inserted a column and altered the table column size, etc...), onsite people are not much aware of Database,

So my onsite team lead asking me to write a script (procedure) for Rollback the all DDL changes in my database.

i have build one query for retrieve the table list and last DDL issue date and time and table created date and time

Select object_name,created,last_ddl_time,timestamp from all_objects where object_type='TABLE' order by last_ddl_time desc;

See the query output as an attachment

So, Can you help how Rollback the DDL Changes in my database?


Thanks in Advance.

Thanks,
Siva.P
Bangalore
India,

[Updated on: Tue, 11 September 2007 11:54]

Report message to a moderator

Re: How can Rollback the DDL Changes in Database (Database Design Changes)...? [message #266794 is a reply to message #266791] Tue, 11 September 2007 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't repost your question.
Continue in the previous topic.

Regards
Michel
xxx [message #266795 is a reply to message #265477] Tue, 11 September 2007 12:01 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi ThomasG,
i have one more question i think which is related to Oracle DBA.

How can we Rollback the DDL Changes in my database...?

I will send you more details if you want.

go to the below link

http://www.orafaq.com/forum/m/266791/111314/#msg_266791


Thanks,
Siva.P
Bangalore
Re: How can Rollback the DDL Changes in Database (merged) [message #266799 is a reply to message #265477] Tue, 11 September 2007 12:11 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
The best you'll be able to do is take the files containing the DDL & manually reverse engineer them to back out the changes.
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266800 is a reply to message #266795] Tue, 11 September 2007 12:13 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't roll back DDL statements.


Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #266802 is a reply to message #266795] Tue, 11 September 2007 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Don't use a topic to post another question.
You pollute the topic.

This is a very bad attitude. Don't do this another time.

I will remove these posts.

Regards
Michel

[Updated on: Tue, 11 September 2007 12:22]

Report message to a moderator

Re: How can Rollback the DDL Changes in Database (merged) [message #266810 is a reply to message #265477] Tue, 11 September 2007 12:51 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Another option is to restore the database from a point in time before the DDL changes were applied
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #267003 is a reply to message #266802] Wed, 12 September 2007 04:22 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
I am very sorry Michal...


Thanks,
Siva.P
bangalore
Re: ORA - 01000 "maximum open cursors exceeded " error in Oracle9i...? [message #267005 is a reply to message #266802] Wed, 12 September 2007 04:24 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
I am very sorry Michal...
i'll never post new qustion in this section.

Regards,
Siva.P
Bangalore
Re: How can Rollback the DDL Changes in Database (merged) [message #267007 is a reply to message #266810] Wed, 12 September 2007 04:28 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi Marc,

How can we restore the database changes from a point in time before done the DDL Changes in the database?

Can you tell me with an example...?

Thanks,
Siva.P
Bangalore

Re: How can Rollback the DDL Changes in Database (merged) [message #267009 is a reply to message #267007] Wed, 12 September 2007 04:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
What sort of backups do you have?
Re: How can Rollback the DDL Changes in Database (merged) [message #267012 is a reply to message #267009] Wed, 12 September 2007 04:38 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi Rowbottom,

Basically i am using the Remote server, so all the backups and recovery has been handled by Onshore DBA Team, So i don't have idea about the backups.

Thanks,
Siva.P
Bangalore


Re: How can we rollback DDL changes (3 topics merged) [message #267014 is a reply to message #265477] Wed, 12 September 2007 04:43 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Quote:
How can i Rollback the DDL Commands (Database table design changes) in Oralce9i?


Actually can't be rolled back. But yet someone can say it can be then it will not wonder me.

If you use

create schema authorization test
create table test1
create table test2


If the problem of creating any table after test1(may be itis in test2 )then test1/all tables will be rolled back.

Hai don't take it otherwise. I just wanted to show one way of how a table can be rolled back. Cool

Actually it works as an entire statement.
Re: How can Rollback the DDL Changes in Database (merged) [message #267021 is a reply to message #267012] Wed, 12 September 2007 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your best bet is probably to talk to your DBA team then.
Work out what time you need to do the recovery to (Ie just before you made these changes) and ask them to do a point in time recovery of the database to that point.
Re: How can we rollback DDL changes (3 topics merged) [message #267024 is a reply to message #267014] Wed, 12 September 2007 05:06 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi Arju,

That is ok...But my problem is, don't want to overwrite the tables..i want to find out the what are the tables got changes and how to rollback the DDL changes in those tables.

i wrote a query for list out the what are the tables gets DDL changes with date, But i don't have to rollback those changes.

This is the query for list out the changed tables with timestamp.


Select object_name,created,last_ddl_time,timestamp from all_objects where object_type='TABLE' order by last_ddl_time desc;



Thanks,
Siva.P
Bangalore
India.
Re: How can we rollback DDL changes (3 topics merged) [message #267026 is a reply to message #267024] Wed, 12 September 2007 05:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In that case, you'll need them to create a new Db, do the recovery into that Db, and then you do a comparison by hand.
Re: How can Rollback the DDL Changes in Database (merged) [message #267039 is a reply to message #267021] Wed, 12 September 2007 05:55 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi Rowbattom,

I want to rollback my database one month before (25th day of the month) why because we are releasing our developed code to PROD server at middle of 3rd week. so we need to rollback the DDL changes in case any problem in release code.

Thanks,
Siva.P
Bangalore
India.
Re: How can Rollback the DDL Changes in Database (merged) [message #267059 is a reply to message #267039] Wed, 12 September 2007 06:46 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're getting the terminology wrong. This is not rolling back the DDL changes - that can't be done.
What we are talking about it restoring an older version of the database, from before the DDL changes were made. This database will contain nothing that was done after that point.

If this is what you want, then go for it. If you want to be able to pick and choose which changes you get, then you'll need to do the recovery to a different Db, and manually transfer any changes.
Re: How can we rollback DDL changes (3 topics merged) [message #267076 is a reply to message #265477] Wed, 12 September 2007 07:53 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
Something tells me I'm glad I'm not the one that is going to be receiving this code.
Re: How can we rollback DDL changes (3 topics merged) [message #267095 is a reply to message #267076] Wed, 12 September 2007 08:18 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Yeah - they do seem to have strayed a little from best practice.

The fact that they've seemingly got no idea what changes they've made is a bit unnerving.
Re: How can we rollback DDL changes (3 topics merged) [message #267098 is a reply to message #267095] Wed, 12 September 2007 08:25 Go to previous messageGo to next message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Hi JRowbottom,

you are not fully correct, i know what are changes have to be rollback in the database.

Let me take on example:

we have number of table in particular schema in that some table got changed (insert one column in table1, alter the column value size in table2, etc),
So now i need to find what are the tables got altered and changed and how to rollback the altered and changed tables?

Let me know if you are not clear on my question.


Thanks,
Siva.P
Bangalore
India.

Re: How can we rollback DDL changes (3 topics merged) [message #267104 is a reply to message #267098] Wed, 12 September 2007 08:44 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
How many times do you need to be told?

YOU CANNOT ROLLBACK DDL CHANGES

Got it? It cannot be done.



You quite clearly don't know what changes have been made to your schema. If you did know all the changes, all you'd have to do to undo the last set of changes would be:

1) Look at the structure of one of the tables in PROD
2) Work out the effect of applying all the changes except the ones in question
3) Alter the tables in your ev database to match this new derived structure.


Re: How can we rollback DDL changes (3 topics merged) [message #267106 is a reply to message #267104] Wed, 12 September 2007 08:48 Go to previous message
psiva_oracle
Messages: 25
Registered: August 2007
Location: india
Junior Member
Thanks Rowbottom, for your kindly response

Regards,
Siva.P
Bangalore
India.
Previous Topic: Multiset union error
Next Topic: Query Please
Goto Forum:
  


Current Time: Fri Dec 02 12:10:45 CST 2016

Total time taken to generate the page: 0.09213 seconds