-
Oracle dblink 跨库查询详解
Step1: 创建DBLINK之前需要查看当前用户的权限。需要使用sysdba 登录，并查询权限。
- [oracle@oracle-db-19c ~]$
- [oracle@oracle-db-19c ~]$ su - oracle
- Password:
- [oracle@oracle-db-19c ~]$ sqlplus / as sysdba
- SQL*Plus: Release 19.0.0.0.0 - Production on Fri Nov 18 13:44:55 2022
- Version 19.3.0.0.0
- Copyright (c) 1982, 2019, Oracle. All rights reserved.
- Connected to:
- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
- Version 19.3.0.0.0
- SQL> set pagesize 200
- SQL> set linesize 200
- SQL> alter session set container=PDB1;
- Session altered.
- SQL> show user
- USER is "SYS"
- SQL> show con_name
- CON_NAME
- ------------------------------
- PDB1
- SQL> select * from user_sys_privs t where t.privilege like upper('%link%');
- USERNAME PRIVILEGE ADM COM INH
- -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- --- --- ---
- SYS DROP PUBLIC DATABASE LINK NO YES YES
- SYS CREATE PUBLIC DATABASE LINK NO YES YES
- SYS CREATE DATABASE LINK NO YES YES
- SQL>
根据上述查询结果显示数据库中dblink拥有三种权限：
CREATE DATABASE LINK--所创建的dblink只有自己可用，其他用户无法使用。
CREATE PUBLIC DATABASE LINK--public表示所创建的dblink所有用户均可使用
DROP PUBLIC DATABASE LINK--删除指定dblinkStep2 给SCOTT账号授予创建dblink和删除dblink的权限（必须在sys用户下授权）
- SQL> show user;
- USER is "SYS"
- SQL>
- SQL> grant CREATE PUBLIC DATABASE LINK,DROP PUBLIC DATABASE LINK to scott;
- Grant succeeded.
- SQL>
Step3 查看 scott 是否已经拥有dblink的相关权限
- SQL> show user;
- USER is "SCOTT"
- SQL> select * from session_privs;
- PRIVILEGE
- ----------------------------------------
- CREATE SESSION
- UNLIMITED TABLESPACE
- CREATE TABLE
- CREATE CLUSTER
- CREATE VIEW
- CREATE SEQUENCE
- CREATE PUBLIC DATABASE LINK
- DROP PUBLIC DATABASE LINK
- CREATE PROCEDURE
- CREATE TRIGGER
- CREATE TYPE
- CREATE OPERATOR
- CREATE INDEXTYPE
- SET CONTAINER
- 14 rows selected.
- SQL>
Step4 查看dblink的两种方式：
- SQL> column owner for a40
- SQL> column object_name for a40
- SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
- OWNER OBJECT_NAME
- ---------------------------------------- ----------------------------------------
- SYS SYS_HUB
- SQL> COLUMN owner FORMAT A30
- SQL> COLUMN db_link FORMAT A30
- SQL> COLUMN username FORMAT A30
- SQL> COLUMN host FORMAT A30
- SQL> SELECT owner,
- 2 db_link,
- 3 username,
- 4 host
- 5 FROM dba_db_links
- 6 ORDER BY owner, db_link;
- OWNER DB_LINK USERNAME HOST
- ------------------------------ ------------------------------ ------------------------------ ------------------------------
- SYS SYS_HUB SEEDDATA
- SQL>
- SQL>
- SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
- OWNER OBJECT_NAME
- ------------------------------ ----------------------------------------
- SYS SYS_HUB
- SQL>
Step5.创建dblink
- SQL>
- SQL> show user
- USER is "SCOTT"
- SQL>
- SQL> create public database link LINK_ORCLPDB1
- 2 connect to hr identified by "hr"
- 3 using
- 4 '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = ORCLPDB1)))';
- Database link created.
- SQL>
Step6.核查dblink是否建立成功。
- SQL>
- SQL>
- SQL> show user;
- USER is "SYS"
- SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
- OWNER OBJECT_NAME
- ------------------------------ ----------------------------------------
- SYS SYS_HUB
- PUBLIC LINK_ORCLPDB1
- SQL>
- SQL>
- SQL> COLUMN owner FORMAT A30
- SQL> COLUMN db_link FORMAT A30
- SQL> COLUMN username FORMAT A30
- SQL> COLUMN host FORMAT A30
- SQL> SELECT owner,
- 2 db_link,
- 3 username,
- 4 host
- 5 FROM dba_db_links
- 6 ORDER BY owner, db_link;
- OWNER DB_LINK USERNAME HOST
- ------------------------------ ------------------------------ ------------------------------ ------------------------------
- PUBLIC LINK_ORCLPDB1 HR (DESCRIPTION =(ADDRESS = (PROT
- OCOL = TCP)(HOST = xxx.xxx.xxx
- .xxx)(PORT = 1521))(CONNECT_DA
- TA =(SERVER = DEDICATED)(SERVI
- CE_NAME = ORCLPDB1)))
- SYS SYS_HUB SEEDDATA
- SQL>
Step7 使用dblink跨库访问hr schema中的表中数据
- SQL> set pagesize 200
- SQL> set linesize 200
- SQL> select * from hr.employees@LINK_ORCLPDB1;
- EMPLOYEE_ID FIRST_NAME LAST_NAME EMAIL PHONE_NUMBER HIRE_DATE JOB_ID SALARY COMMISSION_PCT MANAGER_ID DEPARTMENT_ID
- ----------- -------------------- ------------------------- ------------------------- -------------------- --------- ---------- ---------- -------------- ---------- -------------
- 198 Donald OConnell DOCONNEL 650.507.9833 21-JUN-07 SH_CLERK 2600 124 50
- 199 Douglas Grant DGRANT 650.507.9844 13-JAN-08 SH_CLERK 2600 124 50
- 200 Jennifer Whalen JWHALEN 515.123.4444 17-SEP-03 AD_ASST 4400 101 10
- 201 Michael Hartstein MHARTSTE 515.123.5555 17-FEB-04 MK_MAN 13000 100 20
- 202 Pat Fay PFAY 603.123.6666 17-AUG-05 MK_REP 6000 201 20
- 203 Susan Mavris SMAVRIS 515.123.7777 07-JUN-02 HR_REP 6500 101 40
- 204 Hermann Baer HBAER 515.123.8888 07-JUN-02 PR_REP 10000 101 70
- 205 Shelley Higgins SHIGGINS 515.123.8080 07-JUN-02 AC_MGR 12008 101 110
- 206 William Gietz WGIETZ 515.123.8181 07-JUN-02 AC_ACCOUNT 8300 205 110
- 100 Steven King SKING 515.123.4567 17-JUN-03 AD_PRES 24000 90
- 101 Neena Kochhar NKOCHHAR 515.123.4568 21-SEP-05 AD_VP 17000 100 90
- 102 Lex De Haan LDEHAAN 515.123.4569 13-JAN-01 AD_VP 17000 100 90
- 103 Alexander Hunold AHUNOLD 590.423.4567 03-JAN-06 IT_PROG 9000 102 60
- 104 Bruce Ernst BERNST 590.423.4568 21-MAY-07 IT_PROG 6000 103 60
- 105 David Austin DAUSTIN 590.423.4569 25-JUN-05 IT_PROG 4800 103 60
- 106 Valli Pataballa VPATABAL 590.423.4560 05-FEB-06 IT_PROG 4800 103 60
- 107 Diana Lorentz DLORENTZ 590.423.5567 07-FEB-07 IT_PROG 4200 103 60
- 108 Nancy Greenberg NGREENBE 515.124.4569 17-AUG-02 FI_MGR 12008 101 100
- 109 Daniel Faviet DFAVIET 515.124.4169 16-AUG-02 FI_ACCOUNT 9000 108 100
- 110 John Chen JCHEN 515.124.4269 28-SEP-05 FI_ACCOUNT 8200 108 100
- 111 Ismael Sciarra ISCIARRA 515.124.4369 30-SEP-05 FI_ACCOUNT 7700 108 100
- 112 Jose Manuel Urman JMURMAN 515.124.4469 07-MAR-06 FI_ACCOUNT 7800 108 100
- 113 Luis Popp LPOPP 515.124.4567 07-DEC-07 FI_ACCOUNT 6900 108 100
- 114 Den Raphaely DRAPHEAL 515.127.4561 07-DEC-02 PU_MAN 11000 100 30
- 115 Alexander Khoo AKHOO 515.127.4562 18-MAY-03 PU_CLERK 3100 114 30
- 116 Shelli Baida SBAIDA 515.127.4563 24-DEC-05 PU_CLERK 2900 114 30
- 117 Sigal Tobias STOBIAS 515.127.4564 24-JUL-05 PU_CLERK 2800 114 30
- 118 Guy Himuro GHIMURO 515.127.4565 15-NOV-06 PU_CLERK 2600 114 30
- 119 Karen Colmenares KCOLMENA 515.127.4566 10-AUG-07 PU_CLERK 2500 114 30
- 120 Matthew Weiss MWEISS 650.123.1234 18-JUL-04 ST_MAN 8000 100 50
- 121 Adam Fripp AFRIPP 650.123.2234 10-APR-05 ST_MAN 8200 100 50
- 122 Payam Kaufling PKAUFLIN 650.123.3234 01-MAY-03 ST_MAN 7900 100 50
- 123 Shanta Vollman SVOLLMAN 650.123.4234 10-OCT-05 ST_MAN 6500 100 50
- 124 Kevin Mourgos KMOURGOS 650.123.5234 16-NOV-07 ST_MAN 5800 100 50
- 125 Julia Nayer JNAYER 650.124.1214 16-JUL-05 ST_CLERK 3200 120 50
- 126 Irene Mikkilineni IMIKKILI 650.124.1224 28-SEP-06 ST_CLERK 2700 120 50
- 127 James Landry JLANDRY 650.124.1334 14-JAN-07 ST_CLERK 2400 120 50
- 128 Steven Markle SMARKLE 650.124.1434 08-MAR-08 ST_CLERK 2200 120 50
- 129 Laura Bissot LBISSOT 650.124.5234 20-AUG-05 ST_CLERK 3300 121 50
- 130 Mozhe Atkinson MATKINSO 650.124.6234 30-OCT-05 ST_CLERK 2800 121 50
- 131 James Marlow JAMRLOW 650.124.7234 16-FEB-05 ST_CLERK 2500 121 50
- 132 TJ Olson TJOLSON 650.124.8234 10-APR-07 ST_CLERK 2100 121 50
- 133 Jason Mallin JMALLIN 650.127.1934 14-JUN-04 ST_CLERK 3300 122 50
- 134 Michael Rogers MROGERS 650.127.1834 26-AUG-06 ST_CLERK 2900 122 50
- 135 Ki Gee KGEE 650.127.1734 12-DEC-07 ST_CLERK 2400 122 50
- 136 Hazel Philtanker HPHILTAN 650.127.1634 06-FEB-08 ST_CLERK 2200 122 50
- 137 Renske Ladwig RLADWIG 650.121.1234 14-JUL-03 ST_CLERK 3600 123 50
- 138 Stephen Stiles SSTILES 650.121.2034 26-OCT-05 ST_CLERK 3200 123 50
- 139 John Seo JSEO 650.121.2019 12-FEB-06 ST_CLERK 2700 123 50
- 140 Joshua Patel JPATEL 650.121.1834 06-APR-06 ST_CLERK 2500 123 50
- 141 Trenna Rajs TRAJS 650.121.8009 17-OCT-03 ST_CLERK 3500 124 50
- 142 Curtis Davies CDAVIES 650.121.2994 29-JAN-05 ST_CLERK 3100 124 50
- 143 Randall Matos RMATOS 650.121.2874 15-MAR-06 ST_CLERK 2600 124 50
- 144 Peter Vargas PVARGAS 650.121.2004 09-JUL-06 ST_CLERK 2500 124 50
- 145 John Russell JRUSSEL 011.44.1344.429268 01-OCT-04 SA_MAN 14000 .4 100 80
- 146 Karen Partners KPARTNER 011.44.1344.467268 05-JAN-05 SA_MAN 13500 .3 100 80
- 147 Alberto Errazuriz AERRAZUR 011.44.1344.429278 10-MAR-05 SA_MAN 12000 .3 100 80
- 148 Gerald Cambrault GCAMBRAU 011.44.1344.619268 15-OCT-07 SA_MAN 11000 .3 100 80
- 149 Eleni Zlotkey EZLOTKEY 011.44.1344.429018 29-JAN-08 SA_MAN 10500 .2 100 80
- 150 Peter Tucker PTUCKER 011.44.1344.129268 30-JAN-05 SA_REP 10000 .3 145 80
- 151 David Bernstein DBERNSTE 011.44.1344.345268 24-MAR-05 SA_REP 9500 .25 145 80
- 152 Peter Hall PHALL 011.44.1344.478968 20-AUG-05 SA_REP 9000 .25 145 80
- 153 Christopher Olsen COLSEN 011.44.1344.498718 30-MAR-06 SA_REP 8000 .2 145 80
- 154 Nanette Cambrault NCAMBRAU 011.44.1344.987668 09-DEC-06 SA_REP 7500 .2 145 80
- 155 Oliver Tuvault OTUVAULT 011.44.1344.486508 23-NOV-07 SA_REP 7000 .15 145 80
- 156 Janette King JKING 011.44.1345.429268 30-JAN-04 SA_REP 10000 .35 146 80
- 157 Patrick Sully PSULLY 011.44.1345.929268 04-MAR-04 SA_REP 9500 .35 146 80
- 158 Allan McEwen AMCEWEN 011.44.1345.829268 01-AUG-04 SA_REP 9000 .35 146 80
- 159 Lindsey Smith LSMITH 011.44.1345.729268 10-MAR-05 SA_REP 8000 .3 146 80
- 160 Louise Doran LDORAN 011.44.1345.629268 15-DEC-05 SA_REP 7500 .3 146 80
- 161 Sarath Sewall SSEWALL 011.44.1345.529268 03-NOV-06 SA_REP 7000 .25 146 80
- 162 Clara Vishney CVISHNEY 011.44.1346.129268 11-NOV-05 SA_REP 10500 .25 147 80
- 163 Danielle Greene DGREENE 011.44.1346.229268 19-MAR-07 SA_REP 9500 .15 147 80
- 164 Mattea Marvins MMARVINS 011.44.1346.329268 24-JAN-08 SA_REP 7200 .1 147 80
- 165 David Lee DLEE 011.44.1346.529268 23-FEB-08 SA_REP 6800 .1 147 80
- 166 Sundar Ande SANDE 011.44.1346.629268 24-MAR-08 SA_REP 6400 .1 147 80
- 167 Amit Banda ABANDA 011.44.1346.729268 21-APR-08 SA_REP 6200 .1 147 80
- 168 Lisa Ozer LOZER 011.44.1343.929268 11-MAR-05 SA_REP 11500 .25 148 80
- 169 Harrison Bloom HBLOOM 011.44.1343.829268 23-MAR-06 SA_REP 10000 .2 148 80
- 170 Tayler Fox TFOX 011.44.1343.729268 24-JAN-06 SA_REP 9600 .2 148 80
- 171 William Smith WSMITH 011.44.1343.629268 23-FEB-07 SA_REP 7400 .15 148 80
- 172 Elizabeth Bates EBATES 011.44.1343.529268 24-MAR-07 SA_REP 7300 .15 148 80
- 173 Sundita Kumar SKUMAR 011.44.1343.329268 21-APR-08 SA_REP 6100 .1 148 80
- 174 Ellen Abel EABEL 011.44.1644.429267 11-MAY-04 SA_REP 11000 .3 149 80
- 175 Alyssa Hutton AHUTTON 011.44.1644.429266 19-MAR-05 SA_REP 8800 .25 149 80
- 176 Jonathon Taylor JTAYLOR 011.44.1644.429265 24-MAR-06 SA_REP 8600 .2 149 80
- 177 Jack Livingston JLIVINGS 011.44.1644.429264 23-APR-06 SA_REP 8400 .2 149 80
- 178 Kimberely Grant KGRANT 011.44.1644.429263 24-MAY-07 SA_REP 7000 .15 149
- 179 Charles Johnson CJOHNSON 011.44.1644.429262 04-JAN-08 SA_REP 6200 .1 149 80
- 180 Winston Taylor WTAYLOR 650.507.9876 24-JAN-06 SH_CLERK 3200 120 50
- 181 Jean Fleaur JFLEAUR 650.507.9877 23-FEB-06 SH_CLERK 3100 120 50
- 182 Martha Sullivan MSULLIVA 650.507.9878 21-JUN-07 SH_CLERK 2500 120 50
- 183 Girard Geoni GGEONI 650.507.9879 03-FEB-08 SH_CLERK 2800 120 50
- 184 Nandita Sarchand NSARCHAN 650.509.1876 27-JAN-04 SH_CLERK 4200 121 50
- 185 Alexis Bull ABULL 650.509.2876 20-FEB-05 SH_CLERK 4100 121 50
- 186 Julia Dellinger JDELLING 650.509.3876 24-JUN-06 SH_CLERK 3400 121 50
- 187 Anthony Cabrio ACABRIO 650.509.4876 07-FEB-07 SH_CLERK 3000 121 50
- 188 Kelly Chung KCHUNG 650.505.1876 14-JUN-05 SH_CLERK 3800 122 50
- 189 Jennifer Dilly JDILLY 650.505.2876 13-AUG-05 SH_CLERK 3600 122 50
- 190 Timothy Gates TGATES 650.505.3876 11-JUL-06 SH_CLERK 2900 122 50
- 191 Randall Perkins RPERKINS 650.505.4876 19-DEC-07 SH_CLERK 2500 122 50
- 192 Sarah Bell SBELL 650.501.1876 04-FEB-04 SH_CLERK 4000 123 50
- 193 Britney Everett BEVERETT 650.501.2876 03-MAR-05 SH_CLERK 3900 123 50
- 194 Samuel McCain SMCCAIN 650.501.3876 01-JUL-06 SH_CLERK 3200 123 50
- 195 Vance Jones VJONES 650.501.4876 17-MAR-07 SH_CLERK 2800 123 50
- 196 Alana Walsh AWALSH 650.507.9811 24-APR-06 SH_CLERK 3100 124 50
- 197 Kevin Feeney KFEENEY 650.507.9822 23-MAY-06 SH_CLERK 3000 124 50
- 107 rows selected.
- SQL>
Step 8 ： 删除dblink
- SQL>
- SQL> show user;
- USER is "SCOTT"
- SQL> drop public database link LINK_ORCLPDB1;
- Database link dropped.
- SQL>
Step 9 : 核查 dblink LINK_ORCLPDB1 已删除。
- SQL>
- SQL> show user
- USER is "SYS"
- SQL> select owner,object_name from dba_objects where object_type='DATABASE LINK';
- OWNER OBJECT_NAME
- ------------------------------ ----------------------------------------
- SYS SYS_HUB
- SQL>
-
相关阅读:
大模型浪潮席卷！和鲸科技入选创业邦“2023值得关注的125家AIGC企业”榜单
FL Studio21.2破解版更新下载
Vue源码学习(六):(支线)渲染函数中with(),call()的使用以及一些思考
【QT】Qt Application Manager启动应用源码分析
java计算机毕业设计交通非现场执法系统源码+mysql数据库+系统+lw文档+部署
前端也该刷点算法题——双指针解“链表”题也太香了叭！
Cube.js 试试这个新的数据分析开源工具
1.8 信息系统服务管理、1.9 信息系统规划
Java 可重入锁的那些事（一）
【Linux】 df命令使用
- 原文地址：https://blog.csdn.net/u011868279/article/details/127921016
