![create syn create syn](https://docs.oracle.com/cd/B12037_01/server.101/b10759/create_synonym.gif)
Ok it seems that some invalidations didn’t follow the synonyms... I’ve truncated the table using the synonym, then let’s select from it though the synonym or with the real schema: SQL> select * from DEMO2.MYTABLE Now playing with undocumented stuff comes with unexpected behaviour: SQL> truncate table DEMO2.MYTABLE Those new error messages include ‘or schema synonym name’ when the “_enable_schema_synonyms” is enabled. ORA-42294: role name conflicts with another user, role or schema synonym name ORA-42288: may not create a schema synonym for the specified schemaĪnd schema synonyms is in the same namespace as users and roles: SQL> create role DEMO2 We cannot create synonyms for system schemas: SQL> create schema synonym ROOT for SYS ORA-42297: cannot drop a schema synonym for a schema of a user who is currently We can use the schema synonym as the default prefix: SQL> alter session set current_schema=DEMO2 Īnd we cannot drop it when the underlying user is connected: SQL> connect DEMO/demo 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
![create syn create syn](https://www.oracletutorial.com/wp-content/uploads/2019/06/oracle-create-synonym-example.png)
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 */
![create syn create syn](https://4.bp.blogspot.com/-1xdA9GITFZM/Vs0RtIj0TqI/AAAAAAAAh5A/PiC7Q2A-hJw/w1200-h630-p-k-no-nu/201602241013001.png)
![create syn create syn](https://img-aws.ehowcdn.com/600x600/photos.demandstudios.com/getty/article/56/152/57449302.jpg)
Ivica Arsov ( has made an interesting comment about AUDIT_ACTIONS object link table.