Create a test enviroment in Oracle 10g

What is TEMP used for in Oracle? As a scratch area for doing sorts that won't fit into main memory. So the issue is that you need to do a big sort in order to create your index, and you don't have a large enough PGA for it. So your options are: a larger PGA or a "temporary temporary" tablespace; simply create one big enough, make it your user's temporary tablespace (ALTER USER xxx TEMPORARY TABLESPACE temptemp;) build the index, then set your user back and drop it.

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A". Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table.

If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be the easiest to setup. In the more likely event that most of the records don't change in A from moment to moment, you will probably want to setup a package (or packages) on B that select from A to merge and delete as necessary on B. This will only be as up to date as the frequency in which it is run, but given your requirements it may be the best you can do.

Specifically, your package should do the following:

• Delete from B rows that do not exist in A.
• Merge A into B updating when matched and inserting when not matched.

If you want to avoid hitting the table in A multiple times you could insert the entirety of the table into a global temporary table on B and then do your Delete/Merge from there.

Concerning Minus: Minus can tell you all the rows from a query of A that are not in B. By union-ing this with B minus query of A you can get all rows that are different, but this would probably take longer to process even before adding the insert/update part. If A doesn't get updates or deletes then you could insert the results of the first minus, but an insert into B...where not exists A... would still be faster and simpler.