[20191122]schama建立同义词.txt

--//建立同义词基本是表或者视图,其它对象相对较少.schema能建立吗?

SYS@book> CREATE SCHEMA SYNONYM  scotest for scott;
CREATE SCHEMA SYNONYM  scotest for scott
              *
ERROR at line 1:
ORA-00901: invalid CREATE command

--//实际上有一个隐含参数_enable_schema_synonyms可以实现:
SYS@book> @ hide _enable_schema_synonyms
NAME                    DESCRIPTION                                                        DEFAULT_VALUE SESSION_VALUE SYSTEM_VALUE ISSES ISSYS_MOD
----------------------- ------------------------------------------------------------------ ------------- ------------- ------------ ----- ---------
_enable_schema_synonyms enable DDL operations (e.g. creation) involving schema synonyms    TRUE          FALSE         FALSE        FALSE FALSE

1.环境:
SYS@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SYS@book> alter system set "_enable_schema_synonyms"=true ;
alter system set "_enable_schema_synonyms"=true
*
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

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

2.测试:
SYS@book> shutdown immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@book> startup
ORACLE instance started.
Total System Global Area  643084288 bytes
Fixed Size                  2255872 bytes
Variable Size             205521920 bytes
Database Buffers          427819008 bytes
Redo Buffers                7487488 bytes
Database mounted.
Database opened.

SYS@book> CREATE  SCHEMA SYNONYM  scotest for scott;
Schema synonym created.

SYS@book> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'SCO%';
     USER# NAME                                          TYPE# CTIME                   SPARE2
---------- ---------------------------------------- ---------- ------------------- ----------
       112 SCOTEST                                           3 2019-11-22 10:28:10         83
        83 SCOTT                                             1 2013-08-24 12:04:21
--//OK!!

SYS@book> select * from scotest . dept where rownum=1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SYS@book> alter user oe account unlock identified by book;
User altered.

SYS@book> grant dba to oe;
Grant succeeded.

--//以oe用户登录:

OE@book> select * from scotest.dept where deptno=20;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        20 RESEARCH       DALLAS


--//真心不知道这个是否有这样的需求.最好不要这样做.

3.收尾还原:

SYS@book> revoke  dba from  oe;
Revoke succeeded.

SYS@book> alter user oe account lock ;
User altered.

SYS@book> drop   SCHEMA SYNONYM  scotest ;
Schema synonym dropped.

SYS@book> select user#,name,type#,ctime,spare2 from sys.user$ where name like 'SCO%';
     USER# NAME                                          TYPE# CTIME                   SPARE2
---------- ---------------------------------------- ---------- ------------------- ----------
        83 SCOTT                                             1 2013-08-24 12:04:21