Re: Re-import Question

klabu wrote:
> I need to RE-import set of tables/data from Prod schema into my
> schema....(why = get fresh data)
>
> Should I truncate or drop cascade the current (stale) set ?
>
> thanks
> --
> 10gR2

Depends. Truncate should theoretically be better, by not having to reallocate storage, but it seems to have a few obscure strangenesses that can cause some consternation. For example, there is a rule I haven't seen documented anywhere, but has been demonstrated, that segments must start on certain boundaries, so you can wind up running out of space when you think there is plenty. Creating fresh storage before or duing import avoids such problems. Also, if your normal processing is enlarging rows while updating (without causing row chaining), PCTFREE could make your storage requirements larger on recreation - unless you think about it and manipulate it carefully.

In addition, some software requires additional steps when attempting to import, so you keep things cleaner with the drop cascade. So that's my habit. I'm going through some pain right now as I have to re-add hundreds of triggers solely due to a schema name change. That used to work automagically in my configuration.

In the past, Oracle had a strong design bias towards adding (as opposed to updating and deleting) and also had issues with manipulating large amounts of segments (ie, with DMT's), so it made more of a difference. Nowadays it's more "try both and see which works better for your situation."

jg

--
@home.com is bogus.
Make money by trolling public records:
http://www.signonsandiego.com/uniontrib/20061020/news_1n20stolen.html