Unable to extend segment by 8 1

[aswolff]

Hello,

I am using statements of the form

'insert into table_a select * from table_b'

to copy data from a prod db to a test db.

Some of my tables are so huge that the statement will fail with a 'ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1''.

What can I do to avoid this error? Is there a way to force a commit say after 10000 records?

TIA-

Alex-

 

[SantaMufasa]

Alex,

Is this a one-time need, or is this for a "regularly scheduled program"?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[taupirho]

You might try to use direct path insert with no logging set on your (target) table, this will disable (most) redo and undo and also should be very fast.

You indicate direct path insert by using the append hint, table logging can be set by the alter table command.

 

[aswolff]

It's a weekly job.

Taupirho where do I find more info using "direct path inset with no logging set on target tables". Is this something I can do within my SQL or do I need to get the DBA involved to turn logging off?

Thanks.

 

[taupirho]

Well, if you can do an alter table you can set/unset logging for the table. If you can type in /*+APPEND */ after the INSERT keyword of your insert statement you can do direct paths inserts. I don't think you need any more info than that. Its not really that complicated. If in doubt create a little test case and run with it

 

[SantaMufasa]

Alex,

Hold on ! NOLOGGING does not affect the creation of undo (rollback). NOLOGGING affects only the creation of REDO entries, which are an entirely separate database structure from UNDO/rollback structures.

REDO entries do not occupy any database tablespace. REDO takes up a small amount of space in memory, then, based upon a variety of triggers, goes directly and serially out to on-line redo log files, which do not occupy any database tablespace. As on-line redo log files fill, Oracle writes the filled log file to archived redo log files (if the database is in ARCHIVELOG mode), and, whether in ARCHIVELOG mode or NOARCHIVELOG mode, then overwrites the oldest on-line redo log file with new redo entries. REDO entries exist for the purpose of recovering the database or (more recently) for "log mining" -- to manually resurrect data that may have been inappropriately lost.

But notice that none of the above behaviour deals with UNDO/Rollback entries, which exist for completely different reasons: read consistency and for cancelling a transaction (i.e., rollback).

Alex has a problem with running out of "undo tablespace 'UNDOTBS1'".

First, let's ask "How much UNDO/rollback space does Oracle use for INSERTs?" If you decide to cancel (i.e., UNDO) a massive INSERT, Oracle must revert to what was in the database prior to the INSERT. So,

Q. How much space did INSERTed rows occupy prior to the INSERT?

A. None!!!

Therefore, Oracle must store no row data in the UNDO/Rollback tablespace for INSERTed rows...only the INSERTed rows' ROWIDs.

Q. How much space do ROWIDs occupy?
A. Not much.

If your massive INSERTs occupy only a small amount of UNDO/Rollback space, then what accounts for the hyper-consumption that causes the "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'" error? The answer is (most likely) "your indexes on the target table".

If indexes exist on a table receiving massive INSERTs, then mostly likely massive fragmentation/chaining is happening to the index, as well. Massive INDEX-block activity/changes appear in the UNDO/Rollback segments.

Massive fragmentation/chaining that occurs to a table's INDEX(es) causes significant performance degradation as an ongoing problem until you re-build the index.

Therefore, I recommend that you:

1) Do a "DROP INDEX..." command on all of the indexes that exist on the target table prior to the massive INSERTs,
2) Conduct the massive INSERT,
3) Re-"CREATE INDEX..." on all of the indexes for the target table.

I anticipate these benefits:

1) Your "ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'" error will disappear,
2) Your INSERT will be significantly faster (since Oracle will not be wasting time by creating, fragmenting and/or chaining index blocks to accommodate newly INSERTed rows,
3) You will have no fragmentation/chaining as a result of the index rebuilds.

Let us know your findings after following this strategy.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Excellent post Santa. I must however disagree with your references to chaining. Why would a massive insert cause chaining on the indexes? what do we currently know about the sizes of the indexes and the sizes of the blocks? Not a heck of a lot. Since chaining will only occur if the size of the index entry is greater than the size of the block (storage parameters taken into account of course) I think it's a bit of a leap to assume any chaining here, and since migration only happens during updates, you can't be talking about that either (not that I thought you were). Other than that minor point, great post. I reckon it deserves a thank you

Just my 2c

 

[taupirho]

Dave, I kind of assumed the OP's test db table wouldn't have indexes on it - could be wrong but they don't say either way.

If that is the case then my answer still stands. If not then your method is the way forward, although I would still do a direct path insert in your step 2 rather than a standard insert - every little helps.

 

[SantaMufasa]

Jim, I, perhaps ill-advisedly, used the term "chaining" to represent the actions of

1) attempting to INSERT a new index entry into its "proper" index block, but then
2) encountering no available space for the new index entry, thus
3) causing an index-block split,
4) resulting in "linking/chaining/redirecting" the index flow to a new leaf block level in the index.

Regardless of the "proper" terminology that Oracle may use, (which I don't recall at this moment, but welcome you or someone else to enlighten us), the INSERTing of new rows causes an inefficient "detour" in an otherwise-efficient index, out to an index's leaf block where the newly INSERTed entry resides.

So, I used the word "chaining" (which applies to data-block "forwarding-address" activity in a table), but, due to the behaviour that I described above (and my forgetting the "technical term" that Oracle uses), thought that I could take poetic license with the word, "chaining". (That's what I get for "thinking". <grin>)

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]