Rollback segments - database logging

Advertisement
November 30

Got a problem...
We are trying to move data from one database to another using: INSERT INTO table1 (SELECT a,b,c FROM [email protected]). The problem we are having is that the amount of data being moved with this in sert command is too great - we are getting an error indicating that our rollback segment is not large enough - ORA-01562: failed to extend rollback segment number 4.
Unfortunately, we are not going to be able to increase the size of the rollback segment any further than we already have. Is there a way to disable the database logging that occurs?
I tried using INSERT INTO table1 dbms_disable_logging (SELECT a,b,c FROM [email protected]), but noticed no difference - same error.
Any ideas?
Thank you,
Dan

Advertisement

Replay

We use select..into all the time in an unlogged mode for speed and rollback space reasons.
We replicate fifty million row tables with a minimal amount of rollback segment (<50MB).
In unlogged mode we can replicate 50 million rows in under forty minutes - that's smokin!
Oracle version 8 or greater introduced this concept. I am running 8.1.6.
You need to alter the table to disable logging and then perform the select into.
The table alter only takes seconds.
ALTER TABLE IDMDBO.DIM_PLANS NOLOGGING
Then do the select into and then turn logging back on (if so desired).
You can also disable logging at the table space level.
Keep in mind there are only a few database operations that can be performed unlogged and select..into is one of them.
There is currently no way to carte blanche disable transaction logging - hopefully this will be coming soon!
Cheers.
- Brendan Sullivan
null

View 4 Replies

Tags:

  1. Bbm channels how to add on 9300
  2. file: C:UsersHPDesktopmemeo.txt
  3. lumia 640 video wide playback problem
  4. lenovo a6000 upgrade ing problem
  5. how to fix error recovery my27
  6. screen exist used in ml81n
  7. 3D8Y
  8. 3QVY
  9. 7PBZ
  10. droppedwm7
Copyrights 2019 Fcffair BigData Resource, All rights reserved