Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

LVL 14

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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
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.  …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

610 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