ITPub博客

首页 > 数据库 > Oracle > 1229Create schema synonym in Oracle 12c

1229Create schema synonym in Oracle 12c

原创 Oracle 作者:lfree 时间:2015-01-03 21:44:16 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1388727/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2699
  • 访问量
    6485165