Schema refresh - Oracle Applications 1213 like AP,AR DATA_ONLY

1) On source DB

create or replace directory ap_dump as '/tmp';
--enter / as sysdba as user when prompted
expdp DUMPFILE=ap.dmp DIRECTORY=ap_dump LOGFILE=ap_dump.log SCHEMAS=ap,ar parallel=4 CONTENT=DATA_ONLY

drop directory ap_dump;

scp ap.dmp to /tmp on target server
 
2) On target DB

-->Disable triggers and constraints

declare
cursor c1 is select owner, table_name, constraint_name from dba_constraints where owner = 'HR' and constraint_type = 'R' and status = 'ENABLED';
begin
for r in c1 loop
   execute immediate 'alter table '||r.owner||'.'||r.table_name||' disable constraint ' ||r.constraint_name;
end loop;
end;
/
declare
cursor c1 is select owner, trigger_name from dba_triggers where table_owner = 'HR' and status = 'ENABLED';
begin
for r in c1 loop
execute immediate 'alter trigger '||r.owner||'.'||r.trigger_name||' disable';
end loop;
end;
/

create or replace directory ap_load as '/tmp';

 --enter / as sysdba when prompted for user name
impdp DUMPFILE=ap.dmp CONTENT=DATA_ONLY SCHEMAS=ap,ar DIRECTORY=ap_load LOGFILE=ap_load.log PARALLEL=4 TABLE_EXISTS_ACTION=TRUNCATE

look for errors in in import log.

drop directory ap_load;

--renable triggers and contraints


declare

cursor c1 is select owner, table_name, constraint_name from dba_constraints where owner = 'HR' and constraint_type = 'R' and status <> 'ENABLED';

begin

for r in c1 loop
   execute immediate 'alter table '||r.owner||'.'||r.table_name||' enable constraint ' ||r.constraint_name;
end loop;
end;
/

declare
cursor c1 is select owner, trigger_name from dba_triggers where table_owner = 'HR' and status <> 'ENABLED';

begin

for r in c1 loop
   execute immediate 'alter trigger '||r.owner||'.'||r.trigger_name||' enable';
end loop;
end;
/

---------------------------------------------------------------
------------------------------------------------------------
Another option - TABLE_EXISTS_ACTION=REPLACE

Disabled constraints and triggers of hr,flkhr schema
1)    Export all data
expdp DUMPFILE=hrall.dmp DIRECTORY=hr_dump LOGFILE=hr_dumpall.log SCHEMAS=hr,flkhr parallel=4

2)    Import with REPLACE option
impdp DUMPFILE=hrall.dmp  SCHEMAS=hr,flkhr DIRECTORY=hr_load LOGFILE=hr_load_merit.log PARALLEL=4 TABLE_EXISTS_ACTION=REPLACE

This did not gave the ORA-06502: PL/SQL: numeric or value error

ORA-39034: Table TABLE_DATA

After import triggers were missing as they are owned by APPS schema,

Create the spool for trigger creation

SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON

BEGIN
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
   DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
Spool somefile.sql
SELECT DBMS_METADATA.get_ddl ('TRIGGER', trigger_name, owner)
FROM   dba_triggers
WHERE  table_owner        ='HR';

And ran the somefile.sql as apps user and it created all the HR triggers.
Did the same trigger script for FLKHR shema.

Verified the count of the objects of HR,FLKHR and all was good using dba_objects (not using user_ or all_ )

This generated lots on Invalid and need to compile all the apps schema ot use utlrp.sql
The REPLACE option won't work with CONTENT=DATA_ONLY. You would need to remove that parameter. The replace will drop the tables so will probably generate a lot of invalid objects.