Home » Developer & Programmer » Data Integration » Disable Rollback and Warehouse Loading
Disable Rollback and Warehouse Loading [message #92912] Mon, 04 March 2002 03:18 Go to next message
tom Sikora
Messages: 1
Registered: March 2002
Junior Member
I am loading many tables from an operational store and have had (many!) occasions to cancel the load mid-stream. The rollback of the inserts from a select takes a long time.

Is there any way to diable the rollback (transaction logging?) feature of Oracle, especially when loading a warehouse table? There is but one commit, at the end, and errors or restarts waste lots of rollback time.

Note: If an error occurs, the expectation is to start all over again and no "recovery" is really needed. We are running 8.1.7 of Oracle.
Re: Disable Rollback and Warehouse Loading [message #92913 is a reply to message #92912] Mon, 04 March 2002 07:35 Go to previous messageGo to next message
Wei Lang
Messages: 23
Registered: March 2002
Junior Member
There are two problems related to rollback segment for long "insert .. select":
1. If the operational store is doing a lot of DML, the rollback segement of your opreational database is very large because Oracle try to maintain the read consistancy.
2. If you want to cancel the "insert ...select", the uncommited data in data warehouse need to rollback. As you said, it could take long time.

You can disable the redo logging (for roll forward) in data warehouse but it is not the rollback. My suggestion for your problem is:
1. Use PL/SQL to do the insert to a new table in data warehouse and commit often. Do not open a cursor to long.
2. Do the extraction of data while the load of operational database is light.
3. If you want to cancel the load, kill the pl/sql and drop the new table.
4. After extraction of data, use pl/sql to insert data from new table to your data warehouse table and commit often.

Hope it helps.

Wei
Re: Disable Rollback and Warehouse Loading [message #93113 is a reply to message #92912] Sun, 02 February 2003 07:16 Go to previous message
Shiraz Akhtar
Messages: 1
Registered: February 2003
Junior Member
Data Consistancy
Previous Topic: Re: Pro*c Books
Next Topic: Re: Data Warehouse Jobs - Richmond, VA
Goto Forum:
  


Current Time: Fri Mar 29 04:40:24 CDT 2024