创建dblink
SQL> conn system/oracle Connected. SQL> create database link testlink connect to system identified by oracle using 'utf8db'; Database link created.复制
V$DBLINK无记录。
V$DBLINK描述了由执行V$DBLINK查询的会话打开的所有数据库链接（具有IN_TRANSACTION = YES的链接）。这些数据库链接在关闭之前必须进行提交或回滚。
SQL> SELECT * FROM V$DBLINK; no rows selected SQL> set lines 200 col db_link for a20 col host for a20 select * from dba_db_links; OWNER DB_LINK USERNAME HOST CREATED ------------------------------ -------------------- ------------------------------ -------------------- ------------------ SYSTEM TESTLINK SYSTEM utf8db 17-JUL-23复制
使用dblink
select * from dual@TESTLINK;复制
当前会话可以查看到所打开的dblink
SQL> select * from dba_db_links; DB_LINK OWNER_ID LOG HET PROTOC OPEN_CURSORS IN_ UPD COMMIT_POINT_STRENGTH -------------------- ---------- --- --- ------ ------------ --- --- --------------------- TESTLINK 5 YES YES UNKN 0 YES NO 1复制
x$k2gte
x$k2gte（[K]ernel [2]-phase commit [G]lobal [T]ransaction [E]ntry）视图可以知道本数据库和远程数据库通过dblink发起的sid。
Fixed X$ Tables ，虚拟表，提供了对Oracle内存结构查询的SQL接口，生产环境请谨慎查询，可以引起性能问题。
set lines 300 col MACHINE for a10 col username for a10 select username, osuser, status, sid, serial#, machine, process, terminal, program from v$session where saddr in (select k2gtdses from sys.x$k2gte ); USERNAME OSUSER STATUS SID SERIAL# MACHINE PROCESS TERMINAL PROGRAM ----------- ------------------------------ -------- ---------- ---------- ---------- ------------------------ ------------------------------ ------------------------------------------------ SYSTEM oracle INACTIVE 15 729 linux8 9570 oracle@linux8 (TNS V1-V3) SYSTEM oracle INACTIVE 21 879 linux8 9568 pts/0 sqlplus@linux8 (TNS V1-V3)复制
审计表记录
当前审计配置，没有特殊配置，默认为DB审计
SQL> show parameter audit NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ audit_file_dest string /u01/app/oracle/admin/utf8db/a dump audit_sys_operations boolean FALSE audit_syslog_level string audit_trail string DB SQL>复制
dblink打开会记录一条记录，关闭会记录一条记录
set lines 1000 column userid format a15 column ntimestamp# format a30 column sqltext format a40 column comment$text format a200 set lines 1000 column userid format a15 column timestamp# format a30 column sqltext format a40 column comment$text format a200 select userid,from_tz(ntimestamp#,'00:00') at local timestamp#, sqltext, comment$text from sys.aud$ where comment$text like '%DBLINK%'; USERID NTIMESTAMP# SQLTEXT COMMENT$TEXT --------------- ------------------------------ ---------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- SYSTEM 17-JUL-23 02.51.27.688296 PM + Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.83)(PORT=52798)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820801) 08:00 SYSTEM 17-JUL-23 02.52.33.394448 PM + DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820801) 08:00 SYSTEM 17-JUL-23 02.54.27.237096 PM + Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.83)(PORT=52944)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820806) 08:00 SYSTEM 17-JUL-23 02.54.59.646491 PM + DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820806) 08:00 SQL> col OS_USERNAME for a10 col USERHOST for a15 col TERMINAL for a30 col TIMESTAMP for a30 col OWNER for a10 select OS_USERNAME,USERNAME,USERHOST,TERMINAL,TIMESTAMP,OWNER,ACTION_NAME,SESSIONID,SQL_TEXT,COMMENT_TEXT from dba_audit_trail where COMMENT_TEXT like '%DBLINK%' OS_USERNAM USERNAME USERHOST TERMINAL TIMESTAMP OWNER ACTION_NAME SESSIONID ---------- ------------------------------ --------------- ------------------------------ ------------------------------ ---------- ---------------------------- ---------- SQL_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- COMMENT_TEXT ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- oracle SYSTEM linux8 2023-07-17 14:54:27 LOGON 820809 Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.83)(PORT=52944)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820806) oracle SYSTEM linux8 2023-07-17 14:51:27 LOGON 820802 Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.83)(PORT=52798)); DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820801) oracle SYSTEM linux8 2023-07-17 14:54:59 LOGOFF 820809 DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820806) oracle SYSTEM linux8 2023-07-17 14:52:33 LOGOFF 820802 DBLINK_INFO: (SOURCE_GLOBAL_NAME=utf8db.820801) SQL>复制
listener.log记录
17-JUL-2023 14:51:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=utf8db)(CID=(PROGRAM=oracle)(HOST=linux8)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.83)(PORT=52798)) * establish * utf8db * 0 17-JUL-2023 14:54:27 * (CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=utf8db)(CID=(PROGRAM=oracle)(HOST=linux8)(USER=oracle))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.31.83)(PORT=52944)) * establish * utf8db * 0复制
订阅号：DongDB手记
墨天轮：https://www.modb.pro/u/231198
最后修改时间：2023-07-17 21:51:38
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
