constrints.sh

#!/bin/bash

PRIMARY_SCHEMA=PRE_DEV
SECONDARY_SCHEMA=ORACLEQA

############### Variables for Constraints #########
METADATA_PATH=/home/oracle/new
INDEX_DIFF_TEMP=$METADATA_PATH/temp.sql
INDEX_DIFF_TEMP1=$METADATA_PATH/temp1.sql
FILE_CONS_P=$METADATA_PATH/primary.sql
FILE_CONS_F=$METADATA_PATH/reference.sql

>$FILE_CONS_P
>$FILE_CONS_F

######### Generating Packages in PRIMARY_SCHEMA ##############
sqlplus -s <<EOF> $INDEX_DIFF_TEMP1 sys/Spice_123@192.168.2.4/payprodb as sysdba
#whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set lin 200
set linesize 200
set pagesize 0
set long 1000000
column HEAD format A2000

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);

spool '$FILE_CONS_P'
select dbms_metadata.get_ddl( 'CONSTRAINT', constraint_name,'$PRIMARY_SCHEMA') AS HEAD from
(
        select constraint_name from
        (
                SELECT constraint_name, constraint_type, table_name, status FROM all_constraints where owner='$PRIMARY_SCHEMA'
                and constraint_type = 'P' AND table_name like 'T_%'
                MINUS
                SELECT constraint_name, constraint_type, table_name, status FROM all_constraints where owner='$SECONDARY_SCHEMA'
                and constraint_type = 'P' AND table_name like 'T_%'
                        )
);
spool off;

spool '$FILE_CONS_F'

SELECT  dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name,'$PRIMARY_SCHEMA') AS HEAD
FROM
        (
                SELECT DISTINCT table_name
                FROM dba_constraints t
                WHERE table_name IN (select TABLE_NAME from DBA_TABLES WHERE OWNER = '$PRIMARY_SCHEMA')
                AND OWNER = '$PRIMARY_SCHEMA'
                AND EXISTS (SELECT 1 FROM dba_constraints WHERE table_name = t.table_name AND OWNER = '$PRIMARY_SCHEMA' AND constraint_type = 'R')
MINUS
                SELECT DISTINCT table_name FROM dba_constraints t
                WHERE table_name IN (select TABLE_NAME from DBA_TABLES WHERE OWNER = '$SECONDARY_SCHEMA')
                AND OWNER = '$SECONDARY_SCHEMA'
                AND EXISTS (SELECT 1 FROM dba_constraints WHERE table_name = t.table_name AND OWNER = '$SECONDARY_SCHEMA' AND constraint_type = 'R')
    );
spool off;

exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);

exit 1;
EOF

cat $FILE_CONS_P | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_CONS_P
cat $FILE_CONS_F | grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/"'$$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >$INDEX_DIFF_TEMP
mv $INDEX_DIFF_TEMP $FILE_CONS_F