Creating a schema synonym in Oracle – an unsupported feature

By Franck Pachot

.
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

Undocumented parameter

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.

Usage

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

Limits

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.

Don’t use it!

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.