Any way to track the changes to the DB from one point of time?

Posted on 2013-11-26
Medium Priority
Last Modified: 2013-12-04

I have done so many changes to my dev. DB. Now I have to move those changes to another DB.

I want to know whatever changes I did from a specific point of time, any way to get this?
Question by:sakthikumar
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 23

Expert Comment

ID: 39681524
A broad question, let's try to focus a bit.  Assuming a current version of the product -- a database has several features to track changes:  auditing access, or who's made content changes, can be tracked -- there are simple ways, and complex ways.

In like manner, the actual content change (DML) is easily tracked if the database logs such changes (in archivelog mode).  Those logs are commonly copied to other hosts, and applied to update the database copy on those backup databases.  Oracle DataGuard is the utility used.

Oracle Flashback is also available, by which one can report the changes made to the data over time, for as long as the log copies are available.  Again, very high level mention.

But, there's no convenient way to report what, when, or by whom a data definition (DDL) was changed -- say a person with admin rights changed one column in one table in production from num(5) to num(6).  Comparison can be made, but that's not a feature commonly done in a production environment.

So, reword your question with specifics please.
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39682255
If you are running in archive log mode, Oracle's Logminer should be able to show you what was executed and when.

If you are licensed for it the Change Management Pack for Enterprise Manager can compare two different databases and show you what is different.  Never used it but remember reading it can do it.

You could also generate the DDL for the schema with dbms_metadata for development and production then do a compare, preferably side by side, to locate the differences.

Guess this is a learning moment for you?  You should ALWAYS track changes in development...

I take it to the extreme but it has saved me soooo many times:  I have a master SQL script that can rebuild ALL my database objects any time I want.

Whenever I'm doing development, I create a new SQL script for that patch/release/whatever.  If a change needs to be made, I modify the dev script.  Then when it comes time to move it into production it is as simple as running a script.

Then I merge the dev script into the master production script.

If you are using a software versioning/tracking tool, it is even better.  You can check in the database script with the application code for any specific release.
LVL 38

Accepted Solution

Geert Gruwez earned 1000 total points
ID: 39682999
whatever point in time ... that's a rather big problem
toad has a database/schema comparison which can list all the differences between the 2 and provide a script to sync them both

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39683003
You may want to give this one a try:


It's free (open source) and looked quite suitable for me (seen on DOAG 2013 in Germany)...
LVL 29

Assisted Solution

MikeOM_DBA earned 1000 total points
ID: 39693534
1) You can start by querying the LAST_DDL_TIME column from DBA_OBJECTS table to get recent changes on objects of the affected schema.

2) Using SQL Developer, you can then compare these changed objects between the source and target database.

3) Not sure if SQL Developer will generate the code to apply the changes (I use TOAD).

4) Apply changes to the target schema

5) Then you can worry about the data (if at all).

Good luck!
LVL 23

Expert Comment

ID: 39693703
Mike, I follow your logic about DBA_OBJECTS, but w/o the asker getting back on we're all shooting in the dark.  I don't believe that this particular view would help me know if a table column was added, for example.  And as you can appreciate, his/her system could have hundreds or thousands of transactions per minute -- manual comparison would become labor intensive and error-prone.

LVL 29

Expert Comment

ID: 39693944
Hi dvz,

My take on the requirement is that the OP is asking only about differences in schema objects which he/she needs to migrate (apply) to another db and not "transactions" (data).

Therefore the missing column would be supplied from the comparison of objects between the source and target db's.
DBA_OBJECTS just provides the list of "suspect" objects that were either created or modified after the required date.

But you are right, without more clear requirements, we're all shooting in the dark.


Featured Post

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question