-
Notifications
You must be signed in to change notification settings - Fork 186
Expand file tree
/
Copy pathuninstall_synonyms.sql
More file actions
81 lines (72 loc) · 2.5 KB
/
uninstall_synonyms.sql
File metadata and controls
81 lines (72 loc) · 2.5 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
/*
utPLSQL - Version 3
Copyright 2016 - 2026 utPLSQL Project
Licensed under the Apache License, Version 2.0 (the "License"):
you may not use this file except in compliance with the License.
You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
*/
set echo off
set feedback off
declare
i integer := 0;
begin
dbms_output.put_line('Dropping synonyms pointing to non-existing objects in schema '||upper('&&ut3_owner'));
for syn in (
select
case when owner = 'PUBLIC'
then 'public synonym '
else 'synonym ' || owner || '.' end || synonym_name as syn_name,
table_owner||'.'||table_name as for_object
from all_synonyms s
where table_owner = upper('&&ut3_owner') and table_owner != owner
and not exists (select 1 from all_objects o where o.owner = s.table_owner and o.object_name = s.table_name)
)
loop
begin
execute immediate 'drop '||syn.syn_name;
dbms_output.put_line('Dropped '||syn.syn_name||' for object '||syn.for_object);
i := i + 1;
exception
when others then
dbms_output.put_line('FAILED to drop '||syn.syn_name||' for object '||syn.for_object);
end;
end loop;
dbms_output.put_line('&&line_separator');
dbms_output.put_line(i||' synonyms dropped');
end;
/
declare
i integer := 0;
begin
dbms_output.put_line('Dropping synonyms pointing to PL/SQL code coverage objects on 12.2 ' || upper('&&ut3_owner'));
for syn in (
select
case when owner = 'PUBLIC' then 'public synonym '
else 'synonym ' || owner || '.'
end || synonym_name as syn_name,
table_owner || '.' || table_name as for_object
from all_synonyms s
where 1 = 1
and table_owner = upper('&&ut3_owner')
and synonym_name in ('DBMSPCC_BLOCKS','DBMSPCC_RUNS','DBMSPCC_UNITS')
)
loop
begin
execute immediate 'drop '||syn.syn_name;
dbms_output.put_line('Dropped '||syn.syn_name||' for object '||syn.for_object);
i := i + 1;
exception
when others then
dbms_output.put_line('FAILED to drop '||syn.syn_name||' for object '||syn.for_object);
end;
end loop;
dbms_output.put_line('&&line_separator');
dbms_output.put_line(i||' synonyms dropped');
end;
/