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.