Export ed Import solo dati

Eseguire la copia dei soli dati dallo schema JLA@PROD01 (Produzione) ad JLA@PROT (Test).

Su PROD01
# vim exp_JLA.sh

!#/bin/bash
time expdp \"sys/xxxxx as sysdba\" SCHEMAS=JLA dumpfile=exp_JLA_20220323.dmp logfile=exp_JLA_20220323.log FLASHBACK_SCN=7964209154 CONTENT=DATA_ONLY
exit

DATA_ONLY unloads only table row data; no database object definitions are unloaded.


Su PROT
a) Disabilitare i trigger
Salvare in locale il file contenente i trigger da disabilitare e abilitare estratte dalla seguente query:

select owner as trigger_schema_name,
trigger_name,
trigger_type,
triggering_event,
table_owner as schema_name,
table_name as object_name,
base_object_type as object_type,
status,
trigger_body as script
--select 'alter trigger ' || owner || '.'|| trigger_name ||' disable;'
--select 'alter trigger ' || owner || '.'|| trigger_name ||' enable;'
from sys.dba_triggers
where owner in ('JLA') ;

b) Disabilitare fk.
Salvare in locale il file contenente le fk da disabilitare e abilitare estratte dalla seguente query:

select b.owner, b.table_name child_table,
c.column_name FK_column, b.constraint_name , a.status, b.status
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner
and b.table_name=c.table_name
and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name
and b.constraint_type='R'
and a.owner='JLA'
-- and a.table_name=' '
and a.CONSTRAINT_TYPE='P';

select 'alter table ' || b.owner ||'.'|| b.table_name || ' disable constraint ' || b.constraint_name ||';'
--select 'alter table ' || b.owner ||'.'|| b.table_name || ' enable constraint ' || b.constraint_name ||';'
from dba_constraints a, dba_constraints b, dba_cons_columns c
where a.owner=b.r_owner
and b.owner=c.owner
and b.table_name=c.table_name
and b.constraint_name=c.constraint_name
and a.constraint_name=b.r_constraint_name
and b.constraint_type='R'
and a.owner='JLA'
--and a.table_name=' ... '
and a.CONSTRAINT_TYPE='P';


c) Eseguire l'import

# vim imp_JLA.sh

!#/bin/bash
time impdp  \"sys/xxxxx as sysdba\" SCHEMAS=JLA dumpfile=exp_JLA_20220323.dmp logfile=imp_JLA_20220323.log CONTENT=DATA_ONLY TABLE_EXISTS_ACTION=TRUNCATE
exit

d) Abilitare FK constraint e Trigger

Nel caso sia richiesto di esportate solo alcune tabelle (e a maggior ragione se hanno il nome in maiuscolo e minucolo) conviene usare il parfile 

exp_table_TEDP_tab.sh

#!/bin/bash

export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P15

export ORACLE_SID=TEDP

time expdp  \"sys/ as sysdba\"  parfile=exp_table_FIS.dat

exit

exp_table_FIS.dat

DUMPFILE=exp_TEDP_tabelle_20231016.dmp 

LOGFILE=exp_TEDP_20231016.log

TABLES = FIS."DCRvote", FIS."DTDPernti",FIS."DUDModi"

FLASHBACK_SCN=249347996 

CONTENT=DATA_ONLY

Anche in fase di import usare il parfile

imp_FIS_tab.sh

export ORACLE_SID=TEDT

time impdp  \"sys/ as sysdba\"    parfile=imp_tab_TEDP.dat

exit

imp_tab_TEDP.dat

dumpfile=exp_TEDP_tabelle_20231016.dmp

logfile=imp_TEDP_tabelle_20231016.log

CONTENT=DATA_ONLY

TABLES = FIS."DCRvote", FIS."DTDPernti",FIS."DUDModi"

TABLE_EXISTS_ACTION=TRUNCATE

Esposrte tutte le tabelle tranne una

• Export consistente di solo i dati delle tabelle di uno schema tranne una tabella

expdp SCHEMAS=EMEN

dumpfile=exp_EMEN_20231228.dmp 

logfile=exp_EMEN_20231228.log 

FLASHBACK_SCN=8850160797 

CONTENT=DATA_ONLY

 EXCLUDE=TABLE:\"LIKE \'<nometabella>%\'\"