• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

How can i get trace what are the tables have been inserted?

 
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Good day,

Appreciated some expert here shed me some lights on how can I keep track on oracle database change, for instance, when the application runs a certain function, which may trigger db change on either delete or insertion to certain tables. What is the easy way to trace that?

I'm using oracle 11g and SQL developer 3.

Thanks in advance!
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
There probably isn't an easy way. It would be better if you told us why you need to do this, because the best way might depend on your goal.

I can think of several possibilities:

1) Use queries to find out what changed. That is, you'd run some queries at regular intervals which would fetch changed records. There are several ways to do it:
- you might simply read the entire table and compare it to the copy you got the last time (easiest to avoid various caveats);
- you might try to put some timestamps on your data to be able to query for things that changed since last query. Very tricky to get right, because in Oracle you don't get to see the changes until the transaction committed. And you can't easily get the commit time of a row. I can elaborate on this if you're be interested;
- you might use flashback queries to see what changed since last query. I haven't ever used this to implement the functionality you require, so I don't know what caveats to expect.

2) Use Oracle's Database Change Notification - this is a built in mechanism in Oracle that can call a method in your application when some data you're registered the notification for changes. This is only usable if the data actually change very rarely, otherwise the load on the database can get rather high. I know JDBC client has a support for this, other clients might have as well.

3) You might put triggers on your tables. If you can contain the required logic in the database, this would probably be the best option, but I'd suggest to avoid overusing triggers.

4) It is possible to extract the information off-line from redo logs. The tool to use would be LogMiner (I haven't used it myself, so the link is the only thing I can offer).

On one project, I've implemented a combination of 3) and 1) - triggers collected the change information and put it into some sort of a journal table, which was regularly queried for changes from Java. The goal was to avoid checking 100+ tables I needed to monitor, only the journal table needed processing. I had to put substantial amount of work to get it right, though, and some concurrency issues were pretty thorny.
 
Nakataa Kokuyo
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks Martin, We have a plan to move the configuration data from one environment to another, due to limited time and no one can tell the table relationship - due to senior people left, I have to find a way to figure out when a function triggering from application, what are the tables get impacted and changed - especially the insertion part and then I can query those data and move to another environment.

could you please suggest me what is the best way for my case?

p/s: I not sure which table related to function that going to trigger from application, for instance: create user, what the table will get data inserted.
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
That's really unpleasant situation. We still need to answer some questions, though. How often do you need to move the data? Can the application be shut down while you move the data? Can you completely replace the target data with the contents of the source database - that is, is the target environment guaranteed not to change the data in question?

I'm afraid that you won't be able to avoid the need to understand your data model at least a little bit. You should also consider that you might spend more time trying to look for solution along the lines you've shown than you'd spent doing it right (ie. acquiring the understanding of what the application does).
 
Nakataa Kokuyo
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Here my answer

How often do you need to move the data? - is in ad hoc basis
Can the application be shut down while you move the data? - no, we will use web service to send json over to target environement. once the data goes to target env, user have to click import to trigger the data insertion
Can you completely replace the target data with the contents of the source database - that is, is the target environment guaranteed not to change the data in question? - this is still finding on my end, we expect to archive if there are existing data and remove it and then do insertion.

my purpose is to know what are the tables changed and capture those data and then move over ..
 
Martin Vashko
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If the data in the target database could change independently of the source, you'd be in trouble. Changes from source might be incompatible with other changes in the target - for example, a row with identical key could be created in both environments, and trying to reconcile that would be pretty difficult.

First of all, you really should invest some time into determining whether a ready-made solution isn't available, to avoid reinventing the wheel. I'd suggest starting with google:
oracle database mirror changes
oracle use logminer to mirror data
and so on.

Conceptually, moving the data is quite straightforward:

Initial setup:
1) Identify the set of tables that need to be managed.
2) Delete all data from these tables in target database.
3) In source database, create a copy of each table in the set, with the same structure, but empty.
4) Create a view that will show you the difference between the original table and its copy (see later).

Moving the data:
1) Create a snapshot of data to move using the differential views
2) Move the snapshot over to the target database and apply it on the tables (in one transaction)
3) If the application at the target database succeeded, apply it (in one transaction) to the copy of the managed tables in the source database.

You must do it in transactions as described above, otherwise the tables might get out of sync and you wouldn't have tools to remedy that. Also, if set up this way, all of the data will be moved around when you use the process for the first time. If the amount of data is too big for this, you might need to copy the data from source to target database using other means initially. Important thing is to get into the state where the contents of the target tables is the same as the contents of the copied tables in the source.

A view to find differences might be:This will give a list of records which were deleted since the last time (marked as 'DELETE' in the operation column) together with list of record which were inserted ('INSERT'). Updated record will produce one DELETE record and one INSERT record.

It's obviously important to apply all deletes first, then all inserts. If there is referential integrity between the tables, you need to start deleting from child tables, and start inserting from parent tables. If tables cannot be arranged in this way (from parents to children), you might need to disable referential integrity constraints while applying the updates. If there are referential integrity constraints leading out of your set of tables, you need to figure out what to do about it.

I'd say that this is a lot of work . Think twice whether you really couldn't find a tool that would do this for you.
 
Nakataa Kokuyo
Ranch Hand
Posts: 191
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
wow ...thanks a lot Martin, it really helpful...agreed with you, really a lot of work to do.

some of the record key, might be a running number and different in each environment, and other table might reference those, do you experience this challenge before and any good approach to cover it?
reply
    Bookmark Topic Watch Topic
  • New Topic