[20191122]schama建立同義詞.txt

[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