CODEWITHSUNDEEP
oracle-databasealtersynonym
Suwaid Akbar
Suwaid Akbar

Reputation: 139

altering Table Owner of multiple synonyms

I have 848 NONEDITIONABLE PUBLIC SYNONYM that have the TABLE_OWNER as Dev.

I want help to alter all these NONEDITIONABLE PUBLIC SYNONYM to Prod, but in order to do so we need to extract all the queries. Is there a query to do so?

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

your time and help is appreciated. Thank You

Upvotes: 1

Views: 833

Answers (1)

APC
APC

Reputation: 146239

we need to extract all the queries. Is there a query to do so?

Yes. Generate the script from 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'
/

For future reference please remember that needing to do this is a failure of process. DDL scripts are just like any other code, and should be kept in a source control repository, and checked out and deployed through managed releases.

Upvotes: 2

Related Questions