Re: Why increase in UNDO after truncate?

The problem is likely the import not the truncate. What is the COMMIT setting for the import? What is your UNDO_RETENTION setting? If you commit more frequently and have a low undo_retention setting for the database while importing, you will minimize the amount of undo used because undo blocks will be reused more often instead of allocating new blocks and extending the tablespace. UNDO_RETENTION is dynamic so you can lower it, run the import, then raise it back up without bouncing the database.

However, the downside is that the chance of an ORA-01555 increases for user queries for the duration of the import.

Regards,
Daniel Fink

J. Dex wrote:
> This is probably a stupid question but I have a 9207 database. In
> order to do a refresh, I truncate all the tables, disable sequences,
> foreign keys, etc. Since I truncate all the tables, etc., I don't
> understand why when I run an import it dramatically increases the size
> of the UNDO tablespace? What is it increasing UNDO? Is there any
> way around it?
>
> _________________________________________________________________
> Try Search Survival Kits: Fix up your home and better handle your cash
> with Live Search!
> http://imagine-windowslive.com/search/kits/default.aspx?kit=improve&locale=en-US&source=hmtagline
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l