-
Notifications
You must be signed in to change notification settings - Fork 0
Expand file tree
/
Copy pathscript.sh
More file actions
165 lines (132 loc) · 6.32 KB
/
script.sh
File metadata and controls
165 lines (132 loc) · 6.32 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
#!/bin/bash
PRIMARY_SCHEMA=DEV
SECONDARY_SCHEMA=ORACLEQA
METADATA_PATH=/home/oracle/new
TABLE_DIFF_SUMMARY=$METADATA_PATH/table_diff_summary.txt
TABLE_DIFF_TABLE=$METADATA_PATH/table_diff_tables.txt
TABLE_DIFF_PROC=$METADATA_PATH/table_diff_procs.txt
TABLE_DIFF_TEMP=$METADATA_PATH/temp.txt
TABLE_DIFF_TEMP1=$METADATA_PATH/temp1.txt
TEMP_SQL_FILE=$METADATA_PATH/temp.sql
FINAL_SQL_TABLE=$METADATA_PATH/final_sql_table.sql
MODIFIED_TABLE=$METADATA_PATH/modified.sql
ADDED_TABLE=$METADATA_PATH/added.sql
DROPPED_TABLE=$METADATA_PATH/dropped.sql
######### START - TO SHOW TABLE CHANGE SUMMARY ##############
sqlplus -s <<EOF> $TABLE_DIFF_TEMP sys/Spice_123 as sysdba
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set linesize 2000
set lin 2000
set pagesize 0
set long 100000
column HEAD format A2000
spool '$ADDED_TABLE'
select TABLE_NAME from dba_tables where owner='$PRIMARY_SCHEMA'
minus
select TABLE_NAME from dba_tables where owner='$SECONDARY_SCHEMA';
spool off;
spool '$DROPPED_TABLE'
select TABLE_NAME from dba_tables where owner='$SECONDARY_SCHEMA'
minus
select TABLE_NAME from dba_tables where owner='$PRIMARY_SCHEMA';
spool off;
spool '$MODIFIED_TABLE'
col table_name format a30
col column_name format a30
col data_type format a30
(SELECT table_name,column_name,data_type FROM dba_tab_columns WHERE owner='$SECONDARY_SCHEMA' AND table_name IN (select object_name from dba_objects where object_type in 'TABLE' )
MINUS
SELECT table_name,column_name,data_type FROM dba_tab_columns WHERE owner='$PRIMARY_SCHEMA' AND table_name IN (select object_name from dba_objects where object_type in 'TABLE'))
UNION
(SELECT table_name,column_name,data_type FROM dba_tab_columns WHERE owner='$PRIMARY_SCHEMA' AND table_name IN (select object_name from dba_objects where object_type in 'TABLE')
MINUS
SELECT table_name,column_name,data_type FROM dba_tab_columns WHERE owner='$SECONDARY_SCHEMA' AND table_name IN (select object_name from dba_objects where object_type in 'TABLE' ));
spool off;
exit;
EOF
cat $ADDED_TABLE |grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/$/\t\t\t\t\t\t\tADDED/g' >$TABLE_DIFF_TABLE
cat $DROPPED_TABLE |grep -v "^$" | tr -s " " " " | grep -v "rows selected" | grep -v "^ $" | sed 's/$/\t\t\t\t\t\t\tDROPPED/g' >>$TABLE_DIFF_TABLE
cat $MODIFIED_TABLE | grep -v "^$" |tr -s " " " " | grep -v "rows selected" |awk -F " " '{print $1"\t\t\t\t\t\t\tMODIFIED"}' | sort -u >>$TABLE_DIFF_TABLE
######### START - PREPARE CONSOLIDATED SHEET ##############
cat $TABLE_DIFF_TABLE | awk -F " " '{print $1}' |sort -u >$TABLE_DIFF_TEMP
>$TABLE_DIFF_TEMP1
cat $TABLE_DIFF_TEMP | while read line
do
cat $TABLE_DIFF_TABLE | grep -w "$line" | sort | head -1 >>$TABLE_DIFF_TEMP1
done
mv $TABLE_DIFF_TEMP1 $TABLE_DIFF_TABLE
######### END - PREPARE CONSOLIDATED SHEET ##############
cat $TABLE_DIFF_TABLE
######### END - TO SHOW CHANGE TABLE SUMMARY ##############
######### START - TO CREATE SQL FOR ADDED TABLES ##############
>$FINAL_SQL_TABLE
>$TEMP_SQL_FILE
cat $TABLE_DIFF_TABLE | grep "ADDED$" | while read line
do
echo $line |awk -F " " '{print "SELECT DBMS_METADATA.GET_DDL(\x27TABLE\x27,\x27"$1"\x27, \x27""'"${PRIMARY_SCHEMA}"'""\x27) as HEAD from dual;"}' >>$TEMP_SQL_FILE
done
########### START - CREATE SQL FOR ADDED TABLE ##########
>$TABLE_DIFF_TEMP1
sqlplus -s <<EOF> $TABLE_DIFF_TEMP sys/Spice_123 as sysdba
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set lin 200
set pagesize 200
set long 100000
column HEAD format A2000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
spool '$TABLE_DIFF_TEMP1'
@'$TEMP_SQL_FILE'
spool off
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);
exit;
EOF
echo "-- These tables need to be created in $SECONDARY_SCHEMA" >>$FINAL_SQL_TABLE
cat $TABLE_DIFF_TEMP1 | grep -v "^$" | tr -s " " " " | sed -e 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' >>$FINAL_SQL_TABLE
echo " " >>$FINAL_SQL_TABLE
######### END - TO CREATE SQL FOR ADDED TABLES ##############
######### START - TO CREATE SQL FOR MODIFIED TABLES ##############
>$TEMP_SQL_FILE
cat $TABLE_DIFF_TABLE | grep "MODIFIED$" | while read line
do
echo $line |awk -F " " '{print "select dbms_metadata_diff.compare_alter( \x27TABLE\x27, \x27"$1"\x27, \x27"$1"\x27, \x27""'"$SECONDARY_SCHEMA"'""\x27, \x27""'"$PRIMARY_SCHEMA"'""\x27 ) as HEAD from dual;"}' >>$TEMP_SQL_FILE
done
>$TABLE_DIFF_TEMP1
sqlplus -s <<EOF> $TABLE_DIFF_TEMP sys/Spice_123 as sysdba
whenever sqlerror exit sql.sqlcode;
set echo off
set heading off
set lin 20000
set pagesize 20000
set long 100000
column HEAD format A2000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
spool '$TABLE_DIFF_TEMP1'
@'$TEMP_SQL_FILE'
spool off
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',true);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', false);
exit;
EOF
echo "-- These column needs to be modified in $SECONDARY_SCHEMA" >>$FINAL_SQL_TABLE
cat $TABLE_DIFF_TEMP1 | tr -s " " " " | grep -v "^ $" | grep -v "^$" | sed -e 's/"'$PRIMARY_SCHEMA'"/"'$SECONDARY_SCHEMA'"/g' | sed -e 's/$/;/g' >>$FINAL_SQL_TABLE
echo " " >>$FINAL_SQL_TABLE
######### END - TO CREATE SQL FOR MODIFIED TABLES ##############
######### START - TO CREATE SQL FOR DROPPED TABLES ##############
echo "-- These table need to be dropped in $SECONDARY_SCHEMA" >>$FINAL_SQL_TABLE
cat $TABLE_DIFF_TABLE | grep "DROPPED$" | while read line
do
echo $line |awk -F " " '{print "drop table '"$SECONDARY_SCHEMA"'."$1";"}' >>$FINAL_SQL_TABLE
done
echo " " >>$FINAL_SQL_TABLE
######### END - TO CREATE SQL FOR DROPPED TABLES ##############