SQL> select owner,object_name,object_id from dba_objects where object_name = 'MYTABLE' SQL> select object_id,locked_mode from v$locked_object
#Create syn update
The locks are acquired on the base object: SQL> select * from DEMO2.MYTABLE for update OWNER OBJECT_NAM OBJECT_ID DATA_OBJECT_ID SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name = 'MYTABLE' OPERATION OBJECT_OWN OBJECT_NAM OBJECT_TYPĪnd the rowid shows that data comes from the DEMO table: SQL> select rowid, dbms_rowid.rowid_object(rowid),MYTABLE.* from DEMO2.MYTABLE SQL> select operation,object_owner,object_name,object_type from plan_table order by id The execution plan show the schema synonym DEMO2 as the object owner: SQL> explain plan for select * from DEMO2.MYTABLE I can create a table using the schema synonym: SQL> create table DEMO2.MYTABLE as select * from dual Īnd query it from both: SQL> select * from DEMO2.MYTABLE I’ve selected the SPARE2 because this is where the link to the target schema of the synonym is stored. It’s not a user: SQL> select user_id,username from dba_users where username like 'DEMO%' īut it’s another entry in user$ with type 3 which is for schema synonyms: SQL> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'DEMO%' Which is type 1 in user$ (type 0 is for roles): SQL> select user#,name,type#,ctime from sys.user$ where name like 'DEMO%' Īnd I create a synonym DEMO2 for it: SQL> create schema synonym DEMO2 for DEMO So I have a DEMO schema: SQL> select user_id,username from dba_users where username like 'DEMO%' SQL> alter system set "_enable_schema_synonyms" = true scope=spfile Ok, let’s go: SQL> connect sys/oracle as sysdba But there is an undocumented parameter: SQL> select ksppinm, ksppstvl from x$ksppi a, x$ksppsv b where a.indx=b.indx and ksppinm like '%schema%synonym%' Let’s give a try to the syntax: SQL> create schema synonym DEMO2 for DEMO ĭoesn’t work. But that’s not a reason not to try it – in lab only of course. But that feature is not yet supported: its implementation has probably been postponed again. There are a lot of internal references to 12g’ written when the ‘c’ was not yet decided. So this blog post is not about mulitenant but about Schema Synonyms. insert into audit_actions values (224, 'DROP SCHEMA SYNONYM') insert into audit_actions values (222, 'CREATE SCHEMA SYNONYM') I’ll blog about it soon, but in the meantime when checking its definition in cataudit.sql it came upon the following: /* SCHEMA SYNONYMS will be added in 12g */ Now, users of other schemas can reference the table called suppliers without having to prefix the table name with the schema named app.Ivica Arsov ( has made an interesting comment about AUDIT_ACTIONS object link table.
#Create syn how to
This first CREATE SYNONYM example demonstrates how to create a synonym called suppliers.
Let's look at an example of how to create a synonym in Oracle.įor example: CREATE PUBLIC SYNONYM suppliers object_name The name of the object for which you are creating the synonym. If this phrase is omitted, Oracle assumes that you are referring to your own schema. Remember though that the user must first have the appropriate privileges to the object to use the synonym. PUBLIC It means that the synonym is a public synonym and is accessible to all users. The syntax to create a synonym in Oracle is: CREATE SYNONYM synonym_nameįOR object_name dblink] OR REPLACE Allows you to recreate the synonym (if it already exists) without having to issue a DROP synonym command. You may wish to create a synonym so that users do not have to prefix the table name with the schema name when using the table in a query.