0

I used minus operator to compare two tables. Example:

select colmn1, ..., column10 from table
minus
select colmn1, ..., column10 from remote_table@db_link;

but in case if tables are too big, I got the following error

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP.

Is there any other way?

Viktor Predybaylo
  • 57
  • 1
  • 8

3 Answers3

2

You can split data into pieces and compare it piece by piece. But it will not be one query. Something like this:

declare
  diff number := 0;
  subdiff number;
  piece_size number := 1000;
  table_size number;
begin
  select count(*) 
    into table_size
    from table;

  for i in (select rownum r from dual connect by level <= ceil(table_size/piece_size)) loop
     select count(*)
       into subdiff
       from (select colmn1, ..., column10 from table
              where id between (i.r - 1) * piece_size and i.r * piece_size
              minus
             select colmn1, ..., column10 from remote_table@db_link
              where id between (i.r - 1) * piece_size and i.r * piece_size);
     diff := diff + subdiff;
  end loop;
  dbms_output.put_line('Total lines: ' + diff);
end;

Here you calculate count of rows in the local table, then split it into pieces with 1000 rows (variable piece_size), and compare table piece by piece, collecting total amount of different lines in the diff variable. Then you will see total amount of lines after the loop.
It could take a lot of time, so first you need to find maximal size of piece, which doesn't raise the error. It depends on your systems and could be a 100 000 rows, 1 000 000 rows or any other size.
If you need to see rows themselves, not only the amount, you can copy them into a temporary table in the same manner.

Dmitriy
  • 5,525
  • 12
  • 25
  • 38
  • 1
    Remember to check for IDs which are in the remote database but not in the local one. – APC Sep 21 '17 at 09:54
  • @APC, thank you for your advice! You mean full outer join ? – Viktor Predybaylo Sep 22 '17 at 05:52
  • Not necessarily. You can just extend this solution for `remote ID minus local ID` – APC Sep 22 '17 at 06:00
  • @Dmitry, could you please advice, how to modify query, I need to see value of column1? – Viktor Predybaylo Sep 27 '17 at 05:20
  • @ViktorPredybaylo It quite hard to give certain advise because in a case of a big tables you will get really lot of data, and it will be hard to view it. For example, you can output them using `dbms_output`, or manually filter with hardcoded limits as `select from table where id between 1 and 10000 minus select from table@dblink where id between 1 and 10000`. In this case, you will see only part of desired data, but you can query them part by part. – Dmitriy Sep 27 '17 at 15:31
2

"if tables are too big, I got the following error"

You are executing a minus operation, which requires Oracle to sort the two tables. When the tables are large Oracle uses disk to hold the intermediate sort results. This means writing to the Temporary tablespace; if the sort exceeds the capacity of your Temporary tablespace you get ORA-01652.

First of all, talk to your DBA. Maybe there were a lot of sorts occurring and you might have better luck at a quieter time. Perhaps they can extend the TEMP tablespace. Or they may be able to provision a dedicated Temporary tablespace just for your user (possibly just standing it up for the purposes of this exercise - it depends on what you're doing and why).

"Is there any other way?"

That also depends on what you're doing and why. For a one off exercise you could split the tasks into multiple steps:

  1. Use the keys to find records in local database not in remote database
  2. Use the keys to find records in remote database not in local database
  3. Eliminate the records from the previous two steps in the set of candidate records

If the set of common records remains too large you could create a checksum of each record by concatenating all the columns and hashing them with ora_hash() or one of Oracle's crypto functions. This will give you two much smaller chunks of data to compare.

If this is a repeating exercise you need to re-think your data management strategy. Maybe one of those tables ought to be a materialized view over the other.

Finally, remember that you have to use MINUS twice: for A minus B and B minus A. One of the attractions of the checksum is that it makes this operation easier

select t1.id as local_id
       , t2.id as remote_id
       , case
           when  t1.id is null then 'not in local'
           when  t2.id is null then 'not in remote'
           else  'existing changed'
         end as state
from your_table as t1
     full outer join your_table@remote_db t2
     on t1.id = t2.id
where t1.id is null
or t2.id is null
or t1.check_sum != t2.check_sum
APC
  • 144,005
  • 19
  • 170
  • 281
0

Try to use "NOT IN" instead.

select * 
  from employees
 where (department_id, manager_id) not in 
           (select department_id, manager_id 
              from departments ) ;

"Minus" operation requires sorting, and this leads to taking much memory from oracle server's PGA memory, and if not enough, from temp segments. "Not in" sometimes also use sorting, but usually not.

Moreover, If you need only data from table A without data from B, and do not need exclude duplicates from result, then "NOT IN" is the right choice.

Eduard Okhvat
  • 206
  • 1
  • 7