1229Create schema synonym in Oracle 12c

[20141229]Create schema synonym in Oracle 12c.txt

連結:

http://www.dbi-services.com/index.php/blog/entry/create-schema-synonym-in-oracle-unsupported-feature

--如果我們檢視12c的cataudit.sql檔案，可以發現如下:

/* 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');

--雖然註解了，說明oracle可能在某個時候可能會支援，給schema建立別名的方式。自己測試看看：

SYS@test01p> @ver1

PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.1.0.1.0     Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production              0

SYS@test01p> create schema synonym scottx for scott;
create schema synonym scottx for scott
              *
ERROR at line 1:
ORA-00901: invalid CREATE command

SYS@test01p> @hide synonym
old  10:  and lower(a.ksppinm) like lower('%&1%')
new  10:  and lower(a.ksppinm) like lower('%synonym%')
NAME                       DESCRIPTION                                                        DEFAULT_VALUE  SESSION_VALUE  SYSTEM_VALUE
-------------------------- ------------------------------------------------------------------ -------------- -------------- ---------------
_enable_schema_synonyms    enable DDL operations (e.g. creation) involving schema synonyms    TRUE           FALSE          FALSE
_synonym_repoint_tracing   whether to trace metadata comparisons for synonym repointing       TRUE           FALSE          FALSE

--修改看看，不支援pluggable資料庫。
SYS@test01p> alter system set "_enable_schema_synonyms" = true scope=spfile;
alter system set "_enable_schema_synonyms" = true scope=spfile
*
ERROR at line 1:
ORA-65040: operation not allowed from within a pluggable database

SYS@test> alter system set "_enable_schema_synonyms" = true scope=spfile;
System altered.

--重啟看看.

SYSTEM@test01p> create schema synonym scottx for scott;
Schema synonym created.

--OK，建立成功
SYSTEM@test01p> select * from scottx.dept;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

--看看一些檢視的情況:
SYSTEM@test01p> select user_id,username from dba_users where username like 'SCOTT%';
   USER_ID USERNAME
---------- --------------------
       109 SCOTT

SYS@test01p> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'SCOTT%';

     USER# NAME                      TYPE# CTIME                   SPARE2
---------- -------------------- ---------- ------------------- ----------
       109 SCOTT                         1 2013-06-28 11:35:40
       119 SCOTTX                        3 2014-12-29 21:32:41        109

--猜測一下，透過spare2建立關聯。

SCOTT@test01p> create table scottx.dept as select * from dept;
create table scottx.dept as select * from dept
                    *
ERROR at line 1:
ORA-00955: name is already used by an existing object

SCOTT@test01p> create table scottx.deptx as select * from dept;
Table created.

SCOTT@test01p> select owner,object_name from dba_objects where object_name='DEPTX';
OWNER  OBJECT_NAME
------ --------------------
SCOTT  DEPTX

--可以發現物件屬於scott使用者，scottx僅僅是一個別名。

--truuncate看看：
SCOTT@test01p> truncate table scott.deptx;
Table truncated.

SCOTT@test01p> select * from scott.deptx;
no rows selected

SCOTT@test01p> select * from scottx.deptx;
select * from scottx.deptx
                     *
ERROR at line 1:
ORA-08103: object no longer exists

--有點小bug，畢竟沒有公開。

SCOTT@test01p> alter system flush shared_pool;
System altered.

SCOTT@test01p> select * from scottx.deptx;
no rows selected

SYS@test01p> select user#,name,password,ctime,spare2,spare4 from sys.user$ where name like 'SCOTT%';
     USER# NAME                 PASSWORD             CTIME                   SPARE2 SPARE4
---------- -------------------- -------------------- ------------------- ---------- --------------------------------------------------------------------------------
       109 SCOTT                57964D8CE8DC6EB1     2013-06-28 11:35:40            S:0202A6123C4EF872E3EE499D93635FBA5C57EAAB2668B671FFD145F2C7D6;H:AFB3A8C4DBB1F9C
                                                                                    3271E68E986F0772B

       120 SCOTTX                                    2014-12-29 21:54:11        109

--沒有口令在scottx上。使用scott的口令無法登入。
SYS@test01p> alter USER SCOTTX  IDENTIFIED BY 123;
alter USER SCOTTX  IDENTIFIED BY 123
           *
ERROR at line 1:
ORA-01918: user 'SCOTTX' does not exist

SYSTEM@test01p> alter session set current_schema=scottx;
Session altered.

SYSTEM@test01p> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

--收尾：
SCOTT@test01p> drop schema synonym scottx ;
drop schema synonym scottx
*
:wERROR at line 1:
ORA-42297: cannot drop a schema synonym for a schema of a user who is currently connected

--自己無法刪除。換sys使用者測試：
SYS@test01p> drop schema synonym scottx ;
drop schema synonym scottx
*
ERROR at line 1:
ORA-42297: cannot drop a schema synonym for a schema of a user who is currently connected

--噢,還有1個使用者scott沒有退出。退出後ok。

SYS@test01p> drop schema synonym scottx ;
Schema synonym dropped.

--這項功能主要用在什麼場合呢？一般的應用很少使用schema。好像意義不大。