I am using Oracle SQL developer 2.1 for creating a synonym.
CREATE OR REPLACE SYNONYM "ETKS_PR_RW"."SQ_CLDOS_ATCHMNT_ID"
FOR "CLDOS_ONLINE_DBA"."SQ_CLDOS_ATCHMNT_ID";
How can I check that if this synonym already exists then don't create the synonym if it does.
Connect to your SQL instance, expand the database, and navigate to the Synonyms folder. Right-click on it and choose New Synonym. Enter the required details for the Synonym name, Synonym schema, Database Name, Object schema, Object Type, and name.
ORA-01775: looping chain of synonyms – Basically means that you created a synonym that points to another object in a circle. In order to fix the above problem you need to have one of the synonyms in the chain point to an object like below. SQL> drop synonym s3; Synonym dropped.
You want to work on your own tables. E. You want to use another schema's tables.
SQL Developer will show you the objects in C that C owns - not show you the things that C has access to. If you want to see the synonyms or objects you have SELECT privs on, go to the Other Users item, then open the tables or synonyms item in the tree.
As you're using the replace
keyword there is no need to check whether the synonym exists first. You will over-write whatever synonym existed with the previous name.
The only reason to be wary of using replace
is if you might have a different synonym with the same name. If your database is organised well this shouldn't happen. You should always know what all of your objects are and where the synonyms point.
However, if you do want to there are a couple of options:
replace
. The statement will throw an error if the synonym already exists and won't get over-written.Query the data-dictionary, as you're in multiple schemas all_synonyms
seems like the best bet.
select *
from all_synonyms
where owner = 'ETKS_PR_RW'
and synonym_name = 'SQ_CLDOS_ATCHMNT_ID';
If you want to combine these into a single block then you can do something like this:
declare
l_exists number;
begin
-- check whether the synonym exists
select 1
into l_exists
from all_synonyms
where owner = 'ETKS_PR_RW'
and synonym_name = 'SQ_CLDOS_ATCHMNT_ID';
-- an error gets raise if it doesn-t.
exception when no_data_found then
-- DDL has to be done inside execute immediate in a block.
execute immediate 'CREATE OR REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID
FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID';
end;
/
On a slightly separate not please do not quote your object names. Oracle can have cased objects, but it is very, very rarely worth the hassle. All objects will be upper-cased automatically so you don't need the "
.
I think if you removed the OR REPLACE keyword it will prompt you that it exist
Or you can create pl/sql code using these tables
desc dba_synonyms
desc user_synonyms
To make it more flexible and customized
Assuming Oracle PL/SQL
DECLARE
src_schema VARCHAR2(256) := 'EMPIK_DYNAMO_01';
target_schema VARCHAR2(256) := 'EMPIK_PORTAL_BETA_1';
CURSOR src_objects IS
SELECT table_name AS object_name
FROM all_all_tables
WHERE owner = src_schema
UNION
SELECT sequence_name AS object_name
FROM all_sequences
WHERE sequence_owner = src_schema;
BEGIN
FOR next_row IN src_objects LOOP
BEGIN
EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM '|| target_schema|| '.'
||
next_row.object_name|| ' for '|| src_schema|| '.'||
next_row.object_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line('ERROR WHILE CREATING SYNONYM FOR: '
|| next_row.object_name);
dbms_output.Put_line(SQLERRM);
END;
END LOOP;
END;
/
Here the customization for your problem
BEGIN
EXECUTE IMMEDIATE 'CREATE or REPLACE SYNONYM ETKS_PR_RW.SQ_CLDOS_ATCHMNT_ID FOR CLDOS_ONLINE_DBA.SQ_CLDOS_ATCHMNT_ID';
EXCEPTION
WHEN OTHERS THEN
dbms_output.Put_line ('ERROR WHILE CREATING SYNONYM FOR: SQ_CLDOS_ATCHMNT_ID');
dbms_output.Put_line (SQLERRM);
END;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With