Best Practices while doing expdp and impdp activity
Best Practices while doing expdp and impdp activity in Oracle.
1) Check the size in Source database and Take the export of the schema/table.
2) If the request is to drop the Schema / objects in target database ,after dropping the schema, try to resize the Tablespace/ datafile to reclaim the space as per the schema size in Source database.
3) Start the Import into target database by Excluding the Indexes, as Indexes’ creation is taking longer time always and import will complete very late.
4)By using dump file, Extract the Indexes in sqlfile using import command as shown below.
| $nohup impdp userid=”‘/as sysdba'” directory=DIRECTORY_NAME DUMPFILE=Source dumpfile logfile=logfile_name SCHEMAS=Schema_name Include=Index sqlfile=filename.sql PARALLEL=16 & |
Note: Parallel=16 is for example . This setting is for DB /schemas size in TB
5) Now modify SQLFILE which contains Indexes statements with below settings:
Starting of the script:
alter session enable parallel DDL;
From parallel 1 to Parallel 16;
Logging to Nologging;
Alter session disable parallel DDL;
So that Import complete first.
6) Execute modified (index)sql script in Target database.
7) Match the number of objects between source and Target and make sure they are matching.
8) If Import fails with some known Errors they are Ignorable.
9) During the Import , make sure that we have enough space in TEMP, Tablespaces, FRA and also monitor the Alert logfile when you do not see progress for longer time.
10) If you feel Import is not progress for more time, then count number of objects or size of the schema. So that we can see it originally it is progressing or not.
see also