home

REM sql to generate truncate table sql
spool /data/scripts/GENERATE/tst_truncate_table.sql
select 'truncate table ' ||table_name||' ;'
from user_tables
where table_name in (select table_name from user_tables@test)
and substr(table_name, 1, 3) not in ('TMP', 'BKU');
spool off

REM sql to generate table list to be exported from test instance

spool /data/scripts/GENERATE/test_table_list
select 'tables= '
from dual;
select table_name||' ,'
from user_tables
where table_name in (select table_name from user_tables@test)
and substr(table_name, 1, 3) not in ('TMP', 'BKU')
union
select 'mk9999999'
from dual;
spool off

REM sql to generate constraints disable sql
spool /data/scripts/GENERATE/tst_constraints_disable.sql
select 'alter table '||table_name||' disable constraint '||constraint_name||' ;'
from user_constraints
where constraint_type = 'R';
spool off

REM sql to generate triggers disable sql
spool /data/scripts/GENERATE/tst_triggers_disable.sql
select 'alter trigger '||trigger_name||' disable;'
from user_triggers;
spool off

REM sql to generate constraints enable sql
spool /data/scripts/GENERATE/tst_constraints_enable.sql
select 'alter table '||table_name||' enable constraint '||constraint_name||' ;'
from user_constraints
where constraint_type = 'R';
spool off

REM sql to generate triggers enable sql
spool /data/scripts/GENERATE/tst_triggers_enable.sql
select 'alter trigger '||trigger_name||' enable;'
from user_triggers;
spool off

REM sql to generate drop sequence sql
spool /data/scripts/GENERATE/tst_seq_drop.sql
select 'drop sequence ' ||sequence_name|| ' ;'
from user_sequences
where sequence_name in (select sequence_name from user_sequences@test);
spool off

REM sql to generate TEST sequences in TST
spool /data/scripts/GENERATE/tst_seq_create.sql
select 'create sequence ' ||sequence_name||' Increment By '||to_char(increment_by)||' start with '||to_char(last_number+1)||' maxvalue '||to_char(max_value)||' ;'
from user_sequences@test;
spool off