RE: Re: AW: datapump and deferred_segment_creation

I am very rusty on this. I *thought* that the datapump output side was originally intended to only generate the ddl to pre-create the segments known to be needed for the data pumped out. That would mean you would get all the dictionary operations and space allocation out of the way and then blast the data in on the datapump inbound side. Then again, that seems to be in conflict with a pipeline operation, so that notion may have been ditched before it was built.  

I believe a suggestion was made to do it as deferred for the pipeline operation and pre- built for the non-pipeline operation. Various suggestions were made circa 1995 from Oracle VLDB about the replacement for the venerable exp and imp, and I’ve forgotten a lot of it.  

I would suggest https://oracle-base.com/articles/10g/oracle-data-pump-10g and various pages to see what was actually included as built. Tim Hall’s stuff always gets the mechanics correct because he builds actually test demonstrations. He also walks through some example suggested use for purpose cases.  

Then, if you want to delve into precise details, of course Oracle’s official documentation is comprehensive. You should be able to get it all online, here is the link to 19 (but I see you’re on 11 and 12).  

https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-data-pump-overview.html#GUID-17FAE261-0972-4220-A2E4-44D479F519D4  

Now if the create versus defer IS buggy in some way, I’d probably try to create a DDL creator on the source side and defer explicitly after pre-creating the exact bits I needed with a SWAG on the sizes for each partition. My experience from long ago (think when a gig was a lot) was that coming up for air to allocate space in a tablespace tended to put a hitch in your giddyup. Oracle tends to do space allocation dynamically much better these days. That’s the way to go if you’re trying to be fully automated. But if you need something down to bare metal to fit in a tight processing window, I’d probably still do the extra work so the datapumping doesn’t have to come up for air.  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of ahmed.fikri_at_t-online.de Sent: Friday, May 14, 2021 1:17 PM
To: andy_at_klockmail.com; oracle list
Subject: AW: Re: AW: datapump and deferred_segment_creation  

source and target databases are the same. I did the test ln both 11.2.0.4 and 12.1.0.2, and observed the same.

The deferred_segment_creation is set to the default value TRUE.

Even in oracle documentation the system parameter and the datapump parameter have contradictory default values. So I'm interested to know whether this is an error from oracle side, or there is a real reason for this? Please note that changing explicitly the segment_creation solved my problem, I'm just little bit curious.

Thanks
Ahmed

Gesendet mit der Telekom Mail App <https://kommunikationsdienste.t-online.de/redirects/email_app_android_sendmail_footer>

• Original-Nachricht --- Von: Andy Klock Betreff: Re: AW: datapump and deferred_segment_creation Datum: 14. Mai 2021, 19:01 An: ahmed.fikri_at_t-online.de, list, oracle

Hi Ahmed,

"ahmed.fikri_at_t-online.de" <ahmed.fikri_at_t-online.de> writes:

> So the question, is there any reason or other use case behind
> the choice of the default value of this parameter, or maybe the
> default values are generally chosen haphazardly?
>
> And why oracle introduces this parameter in datapump, which
> overrides the system parameter deferred_segment_creation, even
> the last one can be changed at session level? (one can use
> loggon trigger for datapump outside pl/sql)

You still haven't provided any details as to how and what you are really doing. Things like database versions and details of the source and target expdp/impdp may matter.

For example:

Deferred_segment_creation Behavior When Importing in 11g a Dump Created in 10g (Doc ID 1637985.1)

or, was deferred_segment_creation = true set on the database during the expdp?

Thanks,

Andy K

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







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