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. 

Gather Table Stats with CASCADE TRUE option

Gather table stats with CASCADE TRUE option.
VIJ.VIJ_ITEMS_D

Connect Using system user

->Verify last_analyzed
select * from dba_tables where table_name='VIJ_ITEMS_D'
From TOAD-->

BEGIN
 dbms_stats.gather_table_stats(ownname=> 'VIJ', tabname=> 'VIJ_ITEMS_D', cascade=> TRUE);
END;

>Verify last_analyzed
select * from dba_tables where table_name='VIJ_ITEMS_D'

Flash query to restore the deleted data AS OF TIMESTAMP

 Lets say by mistake data from the table oe_order_holds_all got deleted by any means, you can create a temp table from flashback and get only the data which you want from this table and do criss cross.

create table ont.oe_order_holds_all_vij  as select * from  ont.oe_order_holds_all  as OF TIMESTAMP TO_TIMESTAMP('27-jan-15 00:03:00', 'dd-mon-yy hh24:mi:ss');

create table ont.oe_hold_sources_all_vij  as select * from  ont.oe_hold_sources_all  as OF TIMESTAMP TO_TIMESTAMP('27-jan-15 00:03:00', 'dd-mon-yy hh24:mi:ss');

and gave select privilege user which wanted to see the changes.

Finding sid for the concurrent request

SELECT ses.sid, 
            ses.serial# 
       FROM v$session ses, 
            v$process pro 
           WHERE ses.paddr = pro.addr 
                AND pro.spid IN (SELECT oracle_process_id 
                                           FROM apps.fnd_concurrent_requests 
                                        WHERE request_id = &request_id);

Oracle Applications Finding patches applied between dates

SELECT *
FROM (
select ap.patch_name|| decode(ab.baseline_name,null,'','.') || ab.baseline_name, MERGED_DRIVER_FLAG, max(at.name),
l.language, ap.applied_patch_id, pr.appl_top_id, count(*) DRIVERS_APPLIED, max(end_date) COMPLETION_DATE,
max(end_date), pd.patch_driver_id PATCH_DRV_ID, pd.patch_abstract PATCH_DESCRIPTION
from ad_appl_tops at, ad_patch_driver_langs l, ad_patch_runs pr, ad_patch_drivers pd, ad_applied_patches ap, ad_bugs ab
where pr.appl_top_id = at.appl_top_id and
pr.patch_driver_id = pd.patch_driver_id and pd.applied_patch_id = ap.applied_patch_id and pd.patch_driver_id = l.patch_driver_id
and ab.bug_number (+) = ap.patch_name AND ( trunc( pr.start_date ) >= to_date('10/05/2016','MM/DD/YYYY') AND trunc( pr.end_date) <= to_date('12/31/2016','MM/DD/YYYY'))
group by ap.applied_patch_id, ap.patch_name|| decode(ab.baseline_name,null,'','.') || ab.baseline_name, pd.patch_abstract,
pr.appl_top_id, MERGED_DRIVER_FLAG, pd.patch_driver_id, l.language order by 9 desc, 1 desc, 2 desc, 3 desc, 4 desc )
WHERE rownum < 201