OraFAQ Forum: SQL & PL/SQL » To find all the tables and their columns where the data has changed in a table

Home » SQL & PL/SQL » SQL & PL/SQL » To find all the tables and their columns where the data has changed in a table (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

To find all the tables and their columns where the data has changed in a table [message #600889]

Wed, 13 November 2013 09:46

novice1
Messages: 15
Registered: September 2007

Junior Member

Hello,

I am having a requirement to send a daily updated customer details or newly created customer details to a third party as batch file.

1. if the customer is already sent then we should be sending only the data in the columns that has been changed rather then the complete customer details.
2.If the customer is a new customer then i need to send his complete details.

I am on oracle 10g database and i need to do this daily as a overnight batch.

I am thinking of using couple of options

1. use DBMS_CDC_UTILITY
but this is needs a staging area where we need to create the stage tables.

I need to get the information on all the updated and inserts happening in the environment.
Can you please help me what can be the best possible option to satisfy this requirement.

Please advice .

Report message to a moderator

Re: To find all the tables and their columns where the data has changed in a table [message #600892 is a reply to message #600889]

Wed, 13 November 2013 11:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>Can you please help me what can be the best possible option to satisfy this requirement.
which metric measures best?
what is unit of measure for best?
change application such that it writes to batch file whenever DML is done against affected tables.

Report message to a moderator

Re: To find all the tables and their columns where the data has changed in a table [message #600895 is a reply to message #600892]

Wed, 13 November 2013 11:50

novice1
Messages: 15
Registered: September 2007

Junior Member

Hello,

We cannot have any specific metrics to define best.
My concern was to have the best Possible solution for my problem.
As you know we can have more than 1 solution for a problem.
I cannot change the application for every change to write to batch file.
As you might know the application can become heavy for every such transactions and such designs.

Thanks

Report message to a moderator

Re: To find all the tables and their columns where the data has changed in a table [message #600898 is a reply to message #600895]

Wed, 13 November 2013 12:04

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I cannot change the application for every change to write to batch file.
what else is in the list of things you can not do; so we can avoid mentioning them, too?
what things are you allowed to do to accomplish this task?
all DML is recorded in REDO log files & DBMS_LOGMNR can be used to extract those details.

Report message to a moderator

Re: To find all the tables and their columns where the data has changed in a table [message #600902 is a reply to message #600889]

Wed, 13 November 2013 12:15

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

Is it possible for you to create an audit or history table. Per the industry development standards, you must have all the columns in an audit table, alongwith an aud_id column as a primary key and it should be used as a sequence. If it a history table, you could figure out what all columns you need.

By this method, you can track the details for your report.

Report message to a moderator

Re: To find all the tables and their columns where the data has changed in a table [message #600944 is a reply to message #600902]

Thu, 14 November 2013 02:51

novice1
Messages: 15
Registered: September 2007

Junior Member

Thanks Lalith & blackswan.

Report message to a moderator

Previous Topic:	Query to find users logged in lasttime
Next Topic:	ORA-01031: insufficient privileges error in Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 17:20:08 CDT 2024