Oracle Database Question

[raj_523]

Hi,

I have two databases DB1 which has bunch of tables.  A job runs daily on that database and updates multiple tables.  I am asked to generate a report of what changes have been occurred after the job is completed.

Approach 1 :

Create a copy of the tables prior to running the job and compare that with the table after running the job.  This cannot be done in DB1 as its prod database. 

I want to create a replica of tables in DB1 in another space, say TestDB.  Is this possible.  Can i create a replica table in TestDB?

Is there any other easier way for the above problem. 

Kindly help.

[Cheddi Chinnodu]

sorry2 mama...no idea.. Somebody wil help u  sHa_fr1ends

[raj_523]

thankyou
LTT

[maaditenali]

"Oracle Flashback Technology" ani edo vundi baaa try cheyyi... I dont know much abt that.. but nee scenario ki work avutundanukunta.. google it..

[CheGuevara]

[quote author=raj_523 link=topic=168412.msg2036079#msg2036079 date=1300279017]
Hi,

I have two databases DB1 which has bunch of tables.  A job runs daily on that database and updates multiple tables.  I am asked to generate a report of what changes have been occurred after the job is completed.

Approach 1 :

Create a copy of the tables prior to running the job and compare that with the table after running the job.  This cannot be done in DB1 as its prod database. 

I want to create a replica of tables in DB1 in another space, say TestDB.  Is this possible.  Can i create a replica table in TestDB?

Is there any other easier way for the above problem. 

Kindly help.
[/quote]

use the database links concepts to create a  connection between testdb and prod db.using that link always populate the tables in testdb with the prod before running the job.....

example:  run this statement on testdb by connecting to any user CREATE PUBLIC DATABASE LINK remote USING 'remote';

[satire]

[quote author=raj_523 link=topic=168412.msg2036079#msg2036079 date=1300279017]
Hi,

I have two databases DB1 which has bunch of tables.  A job runs daily on that database and updates multiple tables.  I am asked to generate a report of what changes have been occurred after the job is completed.

Approach 1 :

Create a copy of the tables prior to running the job and compare that with the table after running the job.  This cannot be done in DB1 as its prod database. 

I want to create a replica of tables in DB1 in another space, say TestDB.  Is this possible.  Can i create a replica table in TestDB?

Is there any other easier way for the above problem. 

Kindly help.
[/quote]


enable the audit log so thAT YOU CAN GET ALL THE DETAILS

[harshatati]

[quote author=raj_523 link=topic=168412.msg2036079#msg2036079 date=1300279017]
Hi,

I have two databases DB1 which has bunch of tables.  A job runs daily on that database and updates multiple tables.  I am asked to generate a report of what changes have been occurred after the job is completed.

Approach 1 :

Create a copy of the tables prior to running the job and compare that with the table after running the job.  This cannot be done in DB1 as its prod database. 

I want to create a replica of tables in DB1 in another space, say TestDB.  Is this possible.  Can i create a replica table in TestDB?

Is there any other easier way for the above problem. 

Kindly help.
[/quote]
Baa neeku SAS vaste dantho cheyocchhu job ki mundhi emi columns kavalo daniki chinna code rayi and after job do the same thing. we can schedule a job in SAS todo that automatically as well

2nd way: spread sheet ki DB2 connect chesi kuda cheyochu anukunta!!

inka emanna easy ways unte matuku sorry adho try chesa!!

[at19261]

[quote author=raj_523 link=topic=168412.msg2036079#msg2036079 date=1300279017]
Hi,

I have two databases DB1 which has bunch of tables.  A job runs daily on that database and updates multiple tables.  I am asked to generate a report of what changes have been occurred after the job is completed.

Approach 1 :

Create a copy of the tables prior to running the job and compare that with the table after running the job.  This cannot be done in DB1 as its prod database. 

I want to create a replica of tables in DB1 in another space, say TestDB.  Is this possible.  Can i create a replica table in TestDB?

Is there any other easier way for the above problem. 

Kindly help.
[/quote]

If you want to compare number of rows before and after job then you have to generate the stats before the job and take the NUM_ROWS from DBA_TABLES for set of tables which are updating in your job and make note into temp table. And after completion of your job again generate the stats after completion of job and then compare NUM_ROWS column in DBA_TABLES for set of tables with the temp table so that you can get the difference.

If you want to compare the data then make sure that you create replica of prod database. And create one more extra set of schemas with the names schemas_afterjob in the same database.

Before running the job, take export of all tables which is going to be update through the job and import into replica test database. And once job finishes, take another export of all tables which is updated through the job in PROD and again import into schemas which you created with the names schemas_afterjob in the same replica test database.

Note:- The above method is not feasible if it is large database.

I am not oracle dba , there may be easier way