This was first published on https://blog.dbi-services.com/create-schema-synonym-in-oracle-unsupported-feature (2014-12-28)
Republishing here for new followers. The content is related to the the versions available at the publication date
Ivica Arsov (@IvicaArsov) has made an interesting comment about AUDIT_ACTIONS object link table. 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 */ -- insert into audit_actions values (222, 'CREATE SCHEMA SYNONYM'); -- insert into audit_actions values (224, 'DROP SCHEMA SYNONYM');
which caught my attention.
So this blog post is not about mulitenant but about Schema Synonyms. There are a lot of internal references to 12g’ written when the ‘c’ was not yet decided. But that feature is not yet supported: its implementation has probably been postponed again. But that’s not a reason not to try it – in lab only of course.
Let’s give a try to the syntax:
SQL> create schema synonym DEMO2 for DEMO; create schema synonym DEMO2 for DEMO * ERROR at line 1: ORA-00901: invalid CREATE command
Doesn’t work. 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%'; KSPPINM KSPPSTVL ------------------------- ---------- _enable_schema_synonyms FALSE
Ok, let’s go:
SQL> connect sys/oracle as sysdba Connected. SQL> alter system set "_enable_schema_synonyms" = true scope=spfile; System altered. SQL> startup force;
So I have a DEMO schema:
SQL> select user_id,username from dba_users where username like 'DEMO%'; USER_ID USERNAME ---------- -------------------- 111 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%'; USER# NAME TYPE# CTIME ---------- ------------- ---------- --------- 111 DEMO 1 09-NOV-14
And I create a synonym DEMO2 for it:
SQL> create schema synonym DEMO2 for DEMO; Schema synonym created.
It’s not a user:
SQL> select user_id,username from dba_users where username like 'DEMO%'; USER_ID USERNAME ---------- -------------------- 111 DEMO
but 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%'; USER# NAME TYPE# CTIME SPARE2 ---------- ---------- ---------- --------- ---------- 111 DEMO 1 09-NOV-14 159 DEMO2 3 28-DEC-14 111
I’ve selected the SPARE2 because this is where the link to the target schema of the synonym is stored.
I can create a table using the schema synonym:
SQL> create table DEMO2.MYTABLE as select * from dual; Table created.
and query it from both:
SQL> select * from DEMO2.MYTABLE; D - X SQL> select * from DEMO.MYTABLE; D - X
the execution plan show the schema synonym DEMO2 as the object owner:
SQL> explain plan for select * from DEMO2.MYTABLE; Explained. SQL> select operation,object_owner,object_name,object_type from plan_table order by id; OPERATION OBJECT_OWN OBJECT_NAM OBJECT_TYP -------------------- ---------- ---------- ---------- SELECT STATEMENT TABLE ACCESS DEMO2 MYTABLE TABLE
and the rowid shows that data comes from the DEMO table:
SQL> select rowid, dbms_rowid.rowid_object(rowid),MYTABLE.* from DEMO2.MYTABLE; ROWID DBMS_ROWID.ROWID_OBJECT(ROWID) D ------------------ ------------------------------ - AAAXaYAAGAAAuGbAAA 95896 X SQL> select owner,object_name,object_id,data_object_id from dba_objects where object_name = 'MYTABLE'; OWNER OBJECT_NAM OBJECT_ID DATA_OBJECT_ID ---------- ---------- ---------- -------------- DEMO MYTABLE 95896 95896
The locks are acquired on the base object:
SQL> select * from DEMO2.MYTABLE for update; D - X SQL> select object_id,locked_mode from v$locked_object; OBJECT_ID LOCKED_MODE ---------- ----------- 95896 3 SQL> select owner,object_name,object_id from dba_objects where object_name = 'MYTABLE'; OWNER OBJECT_NAM OBJECT_ID ---------- ---------- ---------- DEMO MYTABLE 95896
We can use the schema synonym as the default prefix:
SQL> alter session set current_schema=DEMO2; Session altered.
and we cannot drop it when the underlying user is connected:
SQL> connect DEMO/demo; Connected. SQL> drop schema synonym DEMO2; drop schema synonym DEMO2 * ERROR at line 1: ORA-42297: cannot drop a schema synonym for a schema of a user who is currently connected
we cannot create synonyms for system schemas:
SQL> create schema synonym ROOT for SYS; create schema synonym ROOT for SYS * ERROR at line 1: ORA-42288: may not create a schema synonym for the specified schema
and schema synonyms is in the same namespace as users and roles:
SQL> create role DEMO2; create role DEMO2 * ERROR at line 1: ORA-42294: role name conflicts with another user, role or schema synonym name
Those new error messages include ‘or schema synonym name’ when the “_enable_schema_synonyms” is enabled.
Now playing with undocumented stuff comes with unexpected behaviour:
SQL> truncate table DEMO2.MYTABLE; Table truncated.
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; no rows selected SQL> select * from DEMO.MYTABLE; select * from DEMO.MYTABLE * ERROR at line 1: ORA-08103: object no longer exists
Ok it seems that some invalidations didn’t follow the synonyms… Let’s try to flush the shared pool:
SQL> alter system flush shared_pool; System altered. SQL> select * from DEMO.MYTABLE; no rows selected SQL> select * from DEMO2.MYTABLE; no rows selected
This is what happens when we use features that are not totally implemented…
That feature is not yet totally implemented. Do you have a need for it? then maybe create an OTN database idea.
Hi Franck,
Nice post, after reading your post I played a little with schema synonyms and what I found interesting to me is that we can define schema synonyms with ‘c##’ prefix which is reserved for common user definition. Also, this feature for schema synonym defined with ‘c##’ prefix later can cause problems if we try to create common users. I don’t know if this should be normal behavior.
SQL> conn sys@xdb1 as sysdba Enter password: Connected. SQL> SQL> drop user iarsov cascade;
User dropped.
SQL> SQL> create user iarsov identified by iarsov;
User created.
SQL> grant create session, create table to iarsov;
Grant succeeded.
SQL> alter user iarsov quota unlimited on users;
User altered.
SQL> SQL> conn iarsov/iarsov@xdb1 Connected. SQL> SQL> create table t(x number);
Table created.
SQL> insert into t values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> conn sys@xdb1 as sysdba Enter password: Connected. SQL> SQL> SQL> create user c##iarsov_g identified by iarsov; –obvious I can’t create common user in container different from ROOT. create user c##iarsov_g identified by iarsov * ERROR at line 1: ORA-65094: invalid local user or role name
SQL> SQL> SQL> create schema synonym c##iarsov_g for iarsov; –but, I can create schema synonym with c## as prefix in container other than ROOT.
Schema synonym created.
SQL> SQL> select * from c##iarsov_g.t;
X ———- 1
SQL> SQL> conn / as sysdba Connected. SQL> SQL> create user c##iarsov_g identified by iarsov; –now, when I tried to create common user I got an error. create user c##iarsov_g identified by iarsov * ERROR at line 1: ORA-65048: error encountered when processing the current DDL statement in pluggable database XDB1 ORA-42293: user name conflicts with another user, role or schema synonym name
I think when they will implement this feature in production (if they do) ‘c##’ prefix should be restricted (as it is for local users) for schema synonyms because later if we try to create common user we will hit an error.
btw, thanks for mentioning me in the post (I got few new followers on twitter )
Regards, Ivica
Hi Ivica, good idea to test in multitenant. So it seems that the synonym became common… What is in sys.user$ ?
It’s same as you already posted (type = 3, most likely for schema synonyms)
SQL> SQL> show con_id con_name
CON_ID —————————— 3
CON_NAME —————————— XDB1 SQL> select user#,name,type# from sys.user$ where name = ‘C##IARSOV_G';
USER# NAME TYPE# ———- ————— ———- 122 C##IARSOV_G 3
SQL>