/*

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 ' || 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;

/