I've been dealing with this error off and on for the last 3 years and I just wanted to verify that my understanding of its cause is accurate.Cause 1) There is only one user logged into the database.Therefore, commiting every so often would fix cause 1, but can do nothing about cause 2.Are there any other scenarios where ORA-01555 can occurr?Oracle clears out any unneeded transactions in the rollback segment but eventually it fills up and the error occurs.Oracle cannot clear out any of user B's transaction since they are needed to maintain user A's consistent view of the database, which is before user B logged in.The MERGE statement is designed to combine multiple operations to reduce the complexity of mixed insert and update operations.MERGE allows you to avoid multiple INSERT, UPDATE, and DELETE DML statements by combining the operations into a single statement.
file,10) full (contracts_file) */ * from contracts_file ))) f on d.contract_id = f.contract_id when matched then update set desc = f.desc, init_val_loc_curr = f.init_val_loc_curr, init_val_adj_amt = f.init_val_adj_amt when not matched then insert values ( f.contract_id, f.desc, f.init_val_loc_curr, f.init_val_adj_amt); In Oracle Database 10g, the MERGE statement has been extended to cover a larger variety of complex and conditional data transformations, allowing faster loading of large volumes of data.
You should use the MERGE statement to select rows from one or more sources for insert or update of one or more tables.
The decision to perform an insert or update is based on conditions specified by you.
That user is updating\inserting\deleting a lot of database records without committing.
Oracle clears out any unneeded transactions in the rollback segment but eventually it fills up and the error occurs. User B logs into the data base and is updating\inserting\deleting a lot of database records, but is committing every 100 records.
Thanks Mark cause 1) no, that will not cause a 1555.