添加Scott用户-白红宇

C:\Users\Administrator>echo %ORACLE_HOME%

C:\app\Administrator\virtual\product\12.2.0\dbhome_2

C:\Users\Administrator>sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on 星期六 12月 16 09:11:50 2017

连接到:

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                  MOUNTED

SQL> alter pluggable database clonepdb_plug open;

插接式数据库已变更。

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

连接到:

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';

USERNAME

SCOTT

SQL> conn scott/tiger@CLONEPDB_PLUG 已连接。

SQL> select table_name from user_tables;

TABLE_NAME

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

连接到:

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 NO

SQL> alter session set container=CLONEPDB_PLUG;

会话已更改。

SQL> -- Grant/Revoke system privileges

SQL> grant create session to ROLE_APPPOSERP;

授权成功。

SQL> grant delete any table to ROLE_APPPOSERP;

授权成功。

SQL> grant execute any procedure to ROLE_APPPOSERP;

授权成功。

SQL> grant insert any table to ROLE_APPPOSERP;

授权成功。

SQL> grant select any sequence to ROLE_APPPOSERP;

授权成功。

SQL> grant select any table to ROLE_APPPOSERP;

授权成功。

SQL> grant update any table to ROLE_APPPOSERP;

授权成功。

SQL> create user DBLINKUSR

2 identified by DBLINKUSR

3 profile DEFAULT;

用户已创建。

SQL> grant role_appposerp to DBLINKUSR;

授权成功。

SQL> alter session set container=pdbtest;

会话已更改。
SQL> create database link
2 CLONEPDB_PLUG connect to DBLINKUSR identified by DBLINKUSR
3 using 'CLONEPDB_PLUG';
数据库链接已创建。
SQL> set wrap off
SQL> set linesize 200
SQL> select * from dual@CLONEPDB_PLUG;
DU

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;

同义词已创建。

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;

同义词已创建。

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同义词在数据库链中的作用

数据库链是一个命名的对象，说明一个数据库到另一个数据库的路径，通过其可以实现不同数据库之间的通信。

Create database link 数据库链名 connect to user名 identified by 口令 using ‘Oracle连接串’; 访问对象要通过 object名@数据库链名。同义词在数据库链中的作用就是提供位置透明性。

SQL> drop synonym scottemp

2 ;

同义词已删除。

本文转自whshurk 51CTO博客，原文链接：http://blog.51cto.com/shurk/2051237，如需转载请自行联系原作者

USERNAME

TABLE_NAME

会话已更改。 SQL> create database link 2 CLONEPDB_PLUG connect to DBLINKUSR identified by DBLINKUSR 3 using 'CLONEPDB_PLUG'; 数据库链接已创建。 SQL> set wrap off SQL> set linesize 200 SQL> select * from dual@CLONEPDB_PLUG; DU

会话已更改。
SQL> create database link
2 CLONEPDB_PLUG connect to DBLINKUSR identified by DBLINKUSR
3 using 'CLONEPDB_PLUG';
数据库链接已创建。
SQL> set wrap off
SQL> set linesize 200
SQL> select * from dual@CLONEPDB_PLUG;
DU