EXPDP & IMPDP

 How to find failed tables after export/import job has done on Oracle Database ?

cat expdp.log | grep -i "error" | grep -o '\"[[:graph:]]\{0,255\}\".\"[[:graph:]]\{0,255\}\"' > failed_tables.txt


it'll show failed tables having formatted in the expdp.log file as below :-
"schema"."table_name"

Hi Team, Please use these steps for schema refresh when export is perform by normal exp/imp utlity. Step 1:- Take a export using expdp of user that needs to refresh. Example :- Expdp system/xyz directory=DPMUP dumpfile=dmp_file_name_%U.DMP LOGFILE=dmp_file_name_IMP_EXP.LOG SCHEMAS=user1,user2 Step 2:- drop the user.( make sure app is down ). Step 3:- import the metadata only from dumpfile ( created in step1 ). Example :- impdp system/xyz directory=DPMUP dumpfile=dmp_file_name_%U.DMP LOGFILE=dmp_file_name_IMP_EXP.LOG REMAP_SCHEMA=source_schema:target_schema,source_schema:target_schema CONTENT=METADATA_ONLY EXCLUDE=TABLE Note:- exclude table else you will get error like this during data import. ORA-02291: integrity constraint (owner.FK79B62C533C17239) violated - parent key not found Or you need to disable all constraints manually before data import. Step 4:- crosscheck for any missing role from source schema. SELECT * FROM dba_role_privs where GRANTEE in(‘owner'); --- on source. GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- owner APPLICATION_DEVELOPER NO YES SQL >SELECT * FROM dba_role_privs where GRANTEE='owner_schema'; --- on target. GRANTEE GRANTED_ROLE ADM DEF ------------------------------ ------------------------------ --- --- owner1 APPLICATION_CREATOR NO YES owner1 NEWT_SYSPRIV NO YES this is the missing role ( APPLICATION_DEVELOPER ) on target. SQL >grant APPLICATION_DEVELOPER to schema; Step 5:- Check for the tablespace used by source schema. SQL >select distinct tablespace_name from dba_segments where owner='schema'; ---- On target. TABLESPACE_NAME ------------------------------ ts1

SQL>select distinct tablespace_name from dba_segments where owner='schema'; ----- On source. TABLESPACE_NAME ------------------------------ t1

t2 Target schema don’t have quota on users in this case. SQL >alter user schema quota unlimited on users; User altered. Step 6:- This is the last step ,now we are good to start import. imp system/cowboy01 file=dmp_file_%U.dmp fromuser=source_user touser=target_user log=dmp_file_name.log ignore=y STATISTICS=NONE

USERID=system/your_password

DIRECTORY=DATA_PUMP_DIR

DUMPFILE=exp_objects.dmp

LOGFILE=exp_objects.log

SCHEMAS=HR

INCLUDE=TABLE,INDEX,VIEW,SEQUENCE,CONSTRAINT,SYNONYM,ROLE_GRANT,SYSTEM_GRANT,

OBJECT_GRANT