[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。好像意义不大。