tag:blogger.com,1999:blog-245514474549983999.post4026194913337258383..comments2023-06-22T04:25:43.194-05:00Comments on OraJourn: Data Pump Rocks!Charles Schultzhttp://www.blogger.com/profile/07973399674184183130noreply@blogger.comBlogger5125tag:blogger.com,1999:blog-245514474549983999.post-39628868080133295862007-12-25T13:28:00.000-06:002007-12-25T13:28:00.000-06:00Charles,I wasn't 'clear' enough in regards to cons...Charles,<BR/><BR/>I wasn't 'clear' enough in regards to constraints. I wouldn't invalidate any constraints, especially not in source, production db.<BR/>This is what I'd do :<BR/>- you don't imp constraints using DataPump<BR/>- after your data are imported into target db, you run script(s) to create/enable constraints.<BR/>- if you run 'regular' sql like 'alter table X add constraint ...enable', Oracle will validate those constraints (going through all data checking if they satisfy constraint conditions) and that can take a while especially for huge tables (this is from my experience ...) <BR/>- what I suggest is to run something like 'alter table X add constraint ... enable NOVALIDATE'. This way you tell Oracle ' my existing data are fine since they are from production, don't check it, enable constraints from now on'<BR/>- you can run 'validate' scripts later just in case if you want to make sure that everything is fine<BR/>-I would do above for all kind of constraints, just take care of the order in which you create them : PK first, then unique,check,FKs,etc, ... I mean FKs will fail if there is no PK they refer to...<BR/><BR/>One more thing , for primary keys on huge tables, I would create (PK) indexes during 'create indexes' phase. If you don't, Oracle will create these indexes when create primary keys and it won't run in parallel. If you create index on PK column before create PK, Oracle will recognize that index when try to create PK and will use it ...<BR/><BR/>Also, all above depends on what you want to achieve ... If you can 'survive' with refreshing process taking +-30 minutes (or so) then check if this is worth of your testing time at all. But if every minute counts - I would go for it.<BR/>Hope this helped a little more than my last comment...Unknownhttps://www.blogger.com/profile/03268474766206020063noreply@blogger.comtag:blogger.com,1999:blog-245514474549983999.post-48422656156131087282007-12-25T06:23:00.000-06:002007-12-25T06:23:00.000-06:00Thanks Milan. We are trying your 2nd suggestion ri...Thanks Milan. We are trying your 2nd suggestion right now. I am curious, how does invalidating the constraints help in this situation? And I assume you mean in the source database, since I do not see how that would help in the target.<BR/><BR/>Thanks again,Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.comtag:blogger.com,1999:blog-245514474549983999.post-83268693066103618522007-12-24T11:01:00.000-06:002007-12-24T11:01:00.000-06:00Charles, nice posting.I would just suggest enablin...Charles, nice posting.<BR/>I would just suggest enabling constraints with 'no validate' clause assuming that they were valid in your source database. That's how I saved some (significant) time during similar 'db refresh' process in my environment.<BR/>I would also try to create indexes by running several scripts in parallel (each of them creates its indexes with parallel option as well). This will require some testing in order to find the most efficient number of scripts and value for 'PARALLEL' parameter (depending on number of CPUs and IO layout in your environment).<BR/>Regards ...Unknownhttps://www.blogger.com/profile/03268474766206020063noreply@blogger.comtag:blogger.com,1999:blog-245514474549983999.post-3863455696213317102007-12-21T16:03:00.000-06:002007-12-21T16:03:00.000-06:00Since we are excluding the indexes, the stats for ...Since we are excluding the indexes, the stats for those indexes are excluded as well. The metadata file has all those stats, which get created when I run paresh.<BR/><BR/>But thanks for the note. If you spot anything else that can help us out, be sure to let me know. =)<BR/><BR/>Roy Swonger says that some good things are coming up with 11g. And I hope we can get some of these Enhancement Requests in soon.Charles Schultzhttps://www.blogger.com/profile/07973399674184183130noreply@blogger.comtag:blogger.com,1999:blog-245514474549983999.post-71145810803134395902007-12-21T15:38:00.000-06:002007-12-21T15:38:00.000-06:00I found that running impdp with exclude=statistics...I found that running impdp with exclude=statistics<BR/>And then collecting statistics on the imported objects is significantly faster than waiting for impdp to import the statistics (at least on 10.2)Anonymousnoreply@blogger.com