Modifying the Owner of Several Synonyms for a Table

Check out the following hyperlinks:
– Granting alter permission pertains to database level, whereas granting alter server role pertains to server level.
Since our Live database interacts with various system databases on different servers, our functions and SPs contain references to these servers. As part of our desensitization process, we must perform a find and replace action on these references, replacing them either with their respective test environment server or with a nonexistent dummy server.

Question:

With 848 entries of

NONEDITIONABLE PUBLIC SYNONYM

, all of which have Dev as their

TABLE_OWNER

.

Can someone assist me in modifying all the

NONEDITIONABLE PUBLIC SYNONYM

to Prod? However, we first need to extract all the queries. Is there a specific query that can help us achieve this?

current:

CREATE OR REPLACE NONEDITIONABLE PUBLIC SYNONYM "CONFIG_SEQ" FOR "Dev"."CONFIG_SEQ";

expected:

CREATE OR REPLACE NONEDITIONABLE PUBLIC SYNONYM "CONFIG_SEQ" FOR "Prod"."CONFIG_SEQ";

We deeply value both your assistance and the time you have dedicated. Thank you.


Solution:

Is there a way to gather all the queries?

Affirmative. Create the code using the data dictionary.

select 'CREATE OR REPLACE NONEDITIONABLE PUBLIC SYNONYM "'
       || synonym_name ||'" FOR "PROD"."' || table_name || '";'
from all_synonyms
where owner = 'PUBLIC'
and   table_owner = 'DEV'
/

It is important to note that having to perform this task indicates a process failure. DDL scripts should be treated similarly to other codes by storing them in a source control repository, and deploying them through managed releases after being checked out.

Frequently Asked Questions