CODEWITHSUNDEEP
oracle-databasedbforge
Dai_N
Dai_N

Reputation: 1

Is there a way to delete data in a DB1 only that is equal to the data in DB2 with DbForge Data Compare for Oracle?

I am asking about DbForge Data Compare for Oracle program.

I have 2 databases (DB-1 and DB-2) each hosted on a different server. DB-1 is the production database and DB-2 is the old production database. In DB-2 there is a considerable amount of data that needed to be transferred to DB-1, which was done using DbForge. After some changes, it is now necessary to delete the data that is hosted in DB-1 that was moved from DB-2 only, and leave intact the data that was originally in DB-1 before the data transaction. But the last backups that were made are a bit old and for now it is not an option to do a rollback.

When I do the data comparison, some columns appear that indicate the data types for each server (as you can see in the image below).

reference image of the DbForge Data Compare for Oracle interface here

As you can see, there are some columns called: "Only in Source", "Different Records", "Identical Records", "Only in Target", of which in my case all appear to be selected with the checklist except "Identical Records". I imagine there is a way using the checklists to eliminate only the data equal to DB-2 in DB-1.

(I do not use a real image of the databases due to my company's security policies, it is mere reference so you can see what the interface that appears in the comparison is like).

Is there a way to mass delete what I need using "DbForge Data Compare"? If so, how would it be done? Or does it not contain the option and I would have to use SQL queries?

Upvotes: -1

Views: 60

Answers (1)

Devart
Devart

Reputation: 121922

If you still have the synchronization script that you used to transfer data from DB2 to DB1, you can reuse it, having manually converted your INSERTs to DELETE.

The second option is to create a new database, say, DB3. Then you need to fill it with all the new records that are present in DB1 but absent from DB2. Here is how you can do it:

  1. Open Data Compare. Set DB1 as the Source and DB2 as the Target, and then compare them.
  2. After the comparison, you'll get Only In Source records that are present in DB1. These are the records you need.
  3. The Equal and Different records are present in both DB1 and DB2, and thus should be deleted because they are no longer relevant.
  4. Exclude all the Equal and Different records via the Different records and Identical record options.
  5. Create a synchronization script that will contain only INSERTs (i.e., Only In Source records) and save it.
  6. Create a new database called DB3. Use Schema Export/Import to transfer the structure of DB2 to DB3 (but without the data).
  7. Using the synchonization script you've saved previously (Step 5), migrate data to DB3.
  8. As a result, you get DB3 with all the data you required.
  9. Back in Data Compare, set DB3 as the Source and DB1 as the Target.
  10. Compare and synchronize these databases. As a result, a script will be generated to delete all irrelevant records from DB1.

Upvotes: 0

Related Questions