创建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进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
