实验测试之创建DBlink，查询远端的yang用户下的abcdedfa表，创建同义词

--测试环境，20远端，30本地；

--准备1,20远端建立表的同义词，用户信息；

1.11

---------------------------------------------------准备阶段，有表有用户可以忽略此步骤-------------------------------------------------------------------

SQL> conn yang/y ERROR: ORA-01017: invalid username/password; logon denied --无效的用户名密码，拒绝进入系统

Warning: You are no longer connected to ORACLE.警告：你是不能尝试连接到oracle

SQL> desc dba_users; ----查询视图

SQL> select USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,DEFAULT_TABLESPACE from dba_users where username='YANG';

no rows selected --没有记录

SQL> create user yang identified by y; --创建用户，授予两个角色

SQL> grant connect,resource to yang;

create table abcdefaefef as select * from hr.employees --创建表，无法查询到-表存在，权限不足导致
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> grant select any table to yang;----授予权限后，角色权限需要在新的用户会话生效（oracle的一致性：你不能说我正查着dba的视图，回收权限后，我报错，返回权限不足吧）

SQL> create table abcdefaefef as select * from hr.employees; --创建测试表

SQL> select USERNAME,USER_ID,ACCOUNT_STATUS,LOCK_DATE,DEFAULT_TABLESPACE from dba_users where username='YANG';---查询用户信息

YANG 107 OPEN USERS

---------------------------------------------------------------------------------------表、用户准备完毕------------------

1.12：

创建私有同义词（自己看到）：

create synonym a for abcdefaefef
*
ERROR at line 1:
ORA-01031: insufficient privileges

--需要权限：SQL> grant create any synonym to yang; =========（操作）==========

SQL> create synonym a for abcdefaefef; ==============（操作）=================

SQL> select count(*) from a; ---当前用户可以使用查询；

select * from yang.a ----SYS用户也可以查询，虽然这个同义词是私有的；

SQL> select count(*) from yang.abcdefaefef; --HR用户无法查询表，同义词更无法识别；

授予权限，让Hr能查询表，验证能否可以使用同义词查询；

SQL> grant select on yang.abcdefaefef to hr;

SQL> select count(*) from yang.abcdefaefef;

COUNT(*) ---------- 107

SQL> select count(*) from yang.a;

COUNT(*) ---------- 107

---同义词，私有情况下，收到的访问约束局限在于表，而不再与同义词本身；

1.13

---创建公共的同义词

1）yang用户创建：

create public synonym b for abcdefaefef
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> grant create public synonym to yang;

SQL> create public synonym b for abcdefaefef;----创建公共的同义词需要的权限不同；一个表的同义词可以有多个；

2）我们回收用户创建私有同义词的权限，公有的不回收，创建私有同义词，验证：

public synonym 权限能否创建私有同义词：

SQL> revoke create any synonym from yang;

SQL> select * from session_privs; ---验证回收的权限------发现次权限会话生效，回收也是；

drop synonym a; ---删除同义词；

create synonym a for abcdefaefef;

ERROR at line 1:
ORA-01031: insufficient privileges ----这个权限有点意思，公有的权限无法创建私有的同义词；

-----------------反过来呢？

revoke create public synonym from yang;

SQL> grant create any synonym to yang;

SQL> select * from session_privs;

create public synonym a for abcdefaefef;

ERROR at line 1:
ORA-01031: insufficient privileges

3) 创建公共同义词a;

然后对比，查询能查询数据的用户对象；

SQL> grant create public synonym to yang;

SQL> create public synonym a for abcdefaefef;

---排除创建本身的用户

--使用SYS用户查询：

SQL> select count(*) from a;

COUNT(*) ---------- 87024

SQL> desc a; ----------------------------发现如果有其它同义词相同，会造成干扰

--测试HR：用户查询公共同义词

select * from a；----------------------OK,可以查询到数据

SQL> create table a(id int); ---创建同义词的表名，干扰

Table created.

SQL> select * from a; -----查询的是本用户下的表名；

no rows selected------------------------------发现如果用户下的表名与公共同义词一样，也会造成干扰

--测试HR，没有访问yang.ABCDEFAEFEF表权限的时候；

SQL> revoke select on yang.ABCDEFAEFEF from hr;

select count(*) from yang.ABCDEFAEFEF
*
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> drop table a;

select count(*) from a
*
ERROR at line 1:
ORA-00942: table or view does not exist---------没有访问表的权限，同义词公共也看不到

SQL> grant select on yang.ABCDEFAEFEF to hr;

SQL> create table a(id int);

select * from yang.ABCDEFAEFEF
SQL> select * from yang.a;
select * from yang.a
*
ERROR at line 1:
ORA-00942: table or view does not exist----

---小结：公共同义词，一样收到表是否能被访问的风险；

--同义词无法username.object指定，无法指定用户+对象；

--同义词收到表名、同义词朋友的干扰，建议名称奇怪一点好；

--建议查询同义词之前先desc 看一下结构是否相同；

--查询同义词：

ORA-01031: insufficient privileges --公共同义词也需要权限才能删除（grant drop public synonym to yang）；

SQL> drop public synonym b;

SQL> select * from dba_synonyms where table_name='ABCDEFAEFEF';

PUBLIC A YANG ABCDEFAEFEF

YANG B YANG ABCDEFAEFEF

-------------------------------------------------------------------------------------------------------------------

以上同义词准备完毕：共有同义词a, 私有同义词b=》用户yang；

以下开始准备dblink配置

----------------------------------------------------------------------------------------------------------------

--创建dblink测试：

dblink，是啥其实就是个连接串，连接到你想查询的数据库上；

创建是在自己本地上创建：

30本地，abc用户，想查询20ip，下的yang用户下的表；

SQL> select * from session_privs;

SQL> select * from user_sys_privs where privilege like upper('%DATABASE LINK%'); ----查询数据库的dblink

no rows selected

create /* public */ database link dblink1 connect to yang identified by y using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.54.20)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=jx)))';

---创建完毕：测试：

select * from ab@dblink1
*
ERROR at line 1:
ORA-12541: TNS:no listener -----发现都找不着TNS服务了

grid$ cd /picclife/app/11.2.0/grid/network/admin/ ---先搞自己的监听，tns配置

listener.ora tnsnames.ora

oracle$ sqlplus hr/hr@192.168.54.30:1521/bj

LISTENER =
(DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
)

Instance "bj", status READY, has 1 handler(s) for this service...

时刻准备着为实例xx服务

The command completed successfully --这个命令是顺利成功的；

grid/network/admin$ cat tnsnames.ora
yang2 =
(DESCRIPTION=
   (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.54.30)(PORT = 1521))
   )
   (CONNECT_DATA=
     (SERVICE_NAME = bj)
   )
)

$ sqlplus hr/hr@yang2 ==============================以上是本地的监听+tns配置

select * from ab@dblink1-----再次查询：报错不一样了；
*
ERROR at line 1:
ORA-02019: connection description for remote database not found

连接为遥远的数据库，连接未找到；

+++++

继续配置，本地的tnsnames,ora文件，存放加入，远程的tns配置文件；（有自己的，但是没有远程的tns配置文件，发不出去）

===在本地: tnsnames.ora文件中加入，远程，远端的tnsnames.ora配置文件信息，保存退出；

--查询验证： sqlplus sys/oracle@yang1 as sysdba

SQL> select instance_name from v$Instance;---查询数据库实例

INSTANCE_NAME ---------------- jx

SQL> host echo $ORACLE_SID +ASM1 --查询本地的sid，实例啥

---本地环境是，使用ASM存储的单节点，因此，tnsname,listener都是grid用户管理的；

---代表什么：可以通过tnsnames.ora文件，去访问其他数据库，登陆其它数据库；

select * from ab@dblink1 ==SYS用户，可以使用，abc用户却不能使用

58 rows selected.

SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';

OWNER -------------------------------------------OBJECT_NAME ----------------------------------

SYS DBLINK1 ---发现搞错了

PUBLIC QQQ -

---------------------------------------------------------------DBLINK创建了一个私有，其它人不能用---------再加个公有的呗

grant create public database link to abc; --授予创建public dblink权限

conn abc/abc;

create public database link yuan1 connect to yang identified by y using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.54.20)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=jx)))';

select * from ab@yuan1 ====OK---查询验证OK

SQL> select * from user_sys_privs where privilege like upper('%DATABASE LINK%');----查询当前用户对DBLINK的权限

USERNAME用户拥有着 ----------- PRIVILEGE权限查询 ---------------- ADMIN是否能联级授予（能否给人钱，有决定权）

ABC CREATE PUBLIC DATABASE LINK NO

SQL> select * from cat@yuan1;

TABLE_NAME --------------------------- TABLE_TYPE -----------------

AB TABLE

ABCDE TABLE

ABCDEFAEFEF TABLE

---回到最初：表太长，建立了同义词，本地还能使用远程的公共同义词进行查询吗？

select * from a@yuan1; ==public 可以查询

select * from b@yuan1; ====私有也可以，证明：远端远程一样可以只有能看表，就能通过同义词查询

======================================================================================

dblink如何查询：

select owner,object_name from dba_objects where object_type='DATABASE LINK';

SYS DBLINK1

PUBLIC QQQ

PUBLIC YUAN1

select * from dba_db_links;---可以查询完整的DBLINK 信息

PUBLIC YUAN1 YANG (DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.54.20)(PORT=1521)))(CONNEC 19-OCT-17
T_DATA=(SERVICE_NAME=jx)))

dblink如何删除：

drop dblink qqq;

select * from dba_db_links where db_link='QQQ';

YANG

ORA-01031: insufficient privileges ---公共的DBLINK，即使是拥有着也没有权限删除

SQL> show user USER is "YANG" SQL> conn / as sysdba Connected.

SQL> drop public database link qqq; =========公共的DBLINK，使用SYS用户删除

grant create database link to abc;

create database link si1 connect to yang identified by y using '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.54.20)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=jx)))';

drop database link si1; ===================私有的DBLINK，自己的用户可以删除

drop database link abc.si1 ========================SYS找不到私有的DBLINK，藏起来了
*
ERROR at line 1:
ORA-02024: database link not found===咋整；

大神有方法，此处抄袭：http://blog.itpub.net/29337971/viewspace-1129917/ --我没成功，先留着以后在学

SQL> select owner||'.'||db_link from dba_db_links where db_link not like '%OGG%';

OWNER||'.'||DB_LINK -------------------------------------------------------------------------------- SYS.DBLINK1

PUBLIC.YUAN1

ABC.SI1

小结：共有，私有的DBLINK与同义词一样，创建的权限分开的；

其次：删除共有的dblink，sys用户，私有谁创建的，谁删除；SYS找不着对象

删除DBLINK:能否禁用呢？

数据库级别禁止功能：

SQL> alter system set open_links=0 scope=spfile;

ORA-02020: too many database links in use==数据库使用过多的DBLINK

SQL> alter system set open_links=4 scope=spfile;

SQL> startup force;

---如何禁止单个呢？

dba_db_links

select * from dba_db_links；

SQL> select count(*) from cat@yuan1;

COUNT(*) ---------- 6

没实现成功！！！！！！！！！！！！！！！！！！

alter session close database link 'dblink_name'

---------------------------------------------片外----------------------------------------------------

同义词--删除同义词依托的对象后，同义词是什么状态：

select object_name,object_type,status from user_objects where object_name='YANG' or object_name='ABCDEFAEFEF';

ABCDEFAEFEF TABLE VALID

drop table ABCDEFAEFEF ;

select * from dba_synonyms where table_name='ABCDEFAEFEF';

select * from a;

ORA-00980: synonym translation is no longer valid 同义词长时间不是有效的

对象、私有同义词、公共同义词是否可以存在三者同名的情况？

对象与公司同义词相等，对象优先级最高；

共有私有同义词一样：

SQL> create table abcdefaefef as select * from hr.employees; ===公私同义词不一样，冲突，提升名称已被使用；

SQL> create synonym a for abcdefaefef;

create public synonym a for yang.abcdefaefef
*
ERROR at line 1:
ORA-00955: name is already used by an existing object