C:\Users\Administrator>echo %ORACLE_HOME%
C:\app\Administrator\virtual\product\12.2.0\dbhome_2
C:\Users\Administrator>sqlplus / as sysdba
創(chuàng)新互聯(lián)擁有一支富有激情的企業(yè)網(wǎng)站制作團(tuán)隊，在互聯(lián)網(wǎng)網(wǎng)站建設(shè)行業(yè)深耕10多年，專業(yè)且經(jīng)驗豐富。10多年網(wǎng)站優(yōu)化營銷經(jīng)驗，我們已為上千多家中小企業(yè)提供了成都做網(wǎng)站、成都網(wǎng)站建設(shè)解決方案，按需定制開發(fā)，設(shè)計滿意，售后服務(wù)無憂。所有客戶皆提供一年免費網(wǎng)站維護(hù)!
SQL*Plus: Release 12.2.0.1.0 Production on 星期六 12月 16 09:11:50 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
重慶分公司
重慶分公司
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDBTEST READ WRITE NO
4 CLONEPDB_PLUG MOUNTEDSQL> alter pluggable database clonepdb_plug open;
插接式數(shù)據(jù)庫已變更。
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDBTEST READ WRITE NO
4 CLONEPDB_PLUG READ WRITE NO SQL> alter session set container=CLONEPDB_PLUG;
會話已更改。
SQL> show user
USER 為 "SYS"
SQL> show con_name;
CON_NAME ------------------------------
CLONEPDB_PLUG
SQL> @%ORACLE_HOME%\rdbms\admin\utlsamplpdb.sql
從 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 斷開
C:\Users\Administrator>sqlplus / as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on 星期六 12月 16 09:22:07 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> alter session set container=CLONEPDB_PLUG;
會話已更改。
SQL> select username from dba_users where username = 'SCOTT';
SCOTT
SQL> conn scott/tiger@CLONEPDB_PLUG 已連接。
SQL> select table_name from user_tables;
DEPT
EMP
BONUS
SALGRADE
深入一步
C:\Users\Administrator>sqlplus/ as sysdba
SQL*Plus: Release 12.2.0.1.0 Production on 星期六 12月 16 10:13:16 2017
Copyright (c) 1982, 2016, Oracle. All rights reserved.
連接到:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED 2 PDB$SEED READ ONLY NO
3 PDBTEST READ WRITE NO
4 CLONEPDB_PLUG READ WRITE NOSQL> alter session set container=CLONEPDB_PLUG;
會話已更改。
SQL> -- Grant/Revoke system privileges
SQL> grant create session to ROLE_APPPOSERP;
授權(quán)成功。
SQL> grant delete any table to ROLE_APPPOSERP;
授權(quán)成功。
SQL> grant execute any procedure to ROLE_APPPOSERP;
授權(quán)成功。
SQL> grant insert any table to ROLE_APPPOSERP;
授權(quán)成功。
SQL> grant select any sequence to ROLE_APPPOSERP;
授權(quán)成功。
SQL> grant select any table to ROLE_APPPOSERP;
授權(quán)成功。
SQL> grant update any table to ROLE_APPPOSERP;
授權(quán)成功。
SQL> create user DBLINKUSR
2 identified by DBLINKUSR
3 profile DEFAULT;
用戶已創(chuàng)建。
SQL> grant role_appposerp to DBLINKUSR;
授權(quán)成功。
SQL> alter session set container=pdbtest;
X
SQL> select * from dba_db_links;
OWNER DB_LINK USERNAME HOST CREATED HIDDEN
SYS SYS_HUB SEEDDATA 2017/3/8 16 NO
SYS CLONEPDB_PLUG DBLINKUSR CLONEPDB_PLUG 2017/12/16 NO
PUBLIC DBMS_CLRDBLINK ORACLR_CONNECTION_DATA 2017/12/13 NO
SQL> select * from link$;
OWNER# NAME CTIME HOST USERID PASSWORD FLAG AUTHUSR AUTHPWD PASSWORDX AUTHPWDX 0 SYS_HUB 2017/3/8 16 SEEDDATA 0
0 CLONEPDB_PLUG 2017/12/16 CLONEPDB_PLUG DBLINKUSR 2 0706B6180908B91436DD15579A9716C7AA59A171BE0F860141B44815E4A93536B3ECE15A339EB407
1 DBMS_CLRDBLINK 2017/12/13 ORACLR_CONNECTION_DATA 0 SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ----------
3 PDBTEST READ WRITE NO SQL> create public synonym emp for scott.emp@CLONEPDB_PLUG;
同義詞已創(chuàng)建。
SQL> desc emp
名稱 是否為空? 類型
EMPNO
NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB
VARCHAR2(9)
MGR
NUMBER(4)
HIREDATE
DATE
SAL
NUMBER(7,2)
COMM
NUMBER(7,2)
DEPTNO
NUMBER(2)
SQL> select * from dba_synonyms where SYNONYM_NAME='EMP';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ORIGIN_CON_ID
PUBLIC EMP SCOTT EMP CLONEPDB_PLUG 3
SQL> create synonym scottemp for scott.emp@CLONEPDB_PLUG;
同義詞已創(chuàng)建。
SQL> select * from dba_synonyms where SYNONYM_NAME='SCOTTEMP';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK ORIGIN_CON_ID
SYS SCOTTEMP SCOTT EMP CLONEPDB_PLUG 3
Oracle同義詞在數(shù)據(jù)庫鏈中的作用
數(shù)據(jù)庫鏈?zhǔn)且粋€命名的對象，說明一個數(shù)據(jù)庫到另一個數(shù)據(jù)庫的路徑，通過其可以實現(xiàn)不同數(shù)據(jù)庫之間的通信。
Create database link 數(shù)據(jù)庫鏈名 connect to user名 identified by 口令 using ‘Oracle連接串’; 訪問對象要通過 object名@數(shù)據(jù)庫鏈名。同義詞在數(shù)據(jù)庫鏈中的作用就是提供位置透明性。
SQL> drop synonym scottemp
2 ;
同義詞已刪除。
