命令记载日志-SYS_COMMAND_LOG
📄字数 2.2K
👁️阅读量 加载中...
功能描述
SYS_COMMAND_LOG系统表用于查询当前节点记录的SQL命令，该表在启用记录SQL命令且生成COMMAND.LOG日志文件后，可查询结果，否则会报外部表文件不存在。
字段说明
| 序号 | 字段名 | 类型 | 说明 | V11 | V12 |
|---|---|---|---|---|---|
| 0 | NODEID | INTEGER | 节点ID | × | √ |
| 1 | DB_ID | VARCHAR | 库ID | × | √ |
| 2 | USER | VARCHAR | 用户名 | × | √ |
| 3 | SESSION_ID | INTEGER | 会话ID | × | √ |
| 4 | CLIENT_IP | CHAR | 客户端IP | × | √ |
| 5 | EX_TIME | DATETIME | SQL执行时间 | × | √ |
| 6 | SQL_STR | VARCHAR | 执行SQL | × | √ |
| 7 | SQL_PARAS | VARCHAR | 参数信息 | × | √ |
参数说明
当有传入参数时（如预处理语句），将记录传入参数信息，其记录格式如下：
{PN=参数个数,GN=参数组数 <参数序号,参数类型,参数值><参数序号,参数类型,参数值>...}参数个数：本组传输的参数个数
参数组数：本组传输的参数所属的组数
参数序号：本组传输的当前参数序号，从 1 开始
参数类型：本组传输的当前参数类型，下述类型括号中数字为类型 ID
B：表示类型为TYPE_BOOL(2)的布尔数据I1：表示类型为TYPE_I1(3)的 1 字节整型数据I2：表示类型为TYPE_I2(4)的 2 字节整型数据I4：表示类型为TYPE_I4(5)的 4 字节整型数据I8：表示类型为TYPE_I8(6)的 8 字节整型数据R4：表示类型为TYPE_R4(8)的 4 字节浮点数据R8：表示类型为TYPE_R8(9)的 8 字节浮点数据S：表示类型为TYPE_CHAR(30)的字符串数据TID:0：表示类型为上述类型之外的类型，其中0将替换为类型 ID，若本参数值为NULL，则0将替换为NULL
参数值：本组传输的当前参数值
B：输出真值T或假值FI1：输出 1 字节整型值表示字符串I2：输出 2 字节整型值表示字符串I4：输出 4 字节整型值表示字符串I8：输出 8 字节整型值表示字符串R4：输出 4 字节浮点值表示字符串R8：输出 8 字节浮点值表示字符串S：输出由单引号'包裹的字符串，最长为 128 字节- 当类型为上述类型之外的类型，不输出参数值部分
应用举例
- 记载当前节点的操作命令
sql
-- 开启命令记载
SQL> SET reg_command ON;
SQL> SELECT * FROM SYS_COMMAND_LOG ORDER BY EX_TIME DESC LIMIT 1;
+--------+-------+--------+------------+---------------+--------------------------+--------------------------------------------------------------+-----------+
| NODEID | DB_ID | USER | SESSION_ID | CLIENT_IP | EX_TIME | SQL_STR | SQL_PARAS |
+--------+-------+--------+------------+---------------+--------------------------+--------------------------------------------------------------+-----------+
| 1 | 1 | SYSDBA | 17 | 192.168.2.236 | 2025-06-28 17:00:21.000 | SELECT * FROM SYS_COMMAND_LOG ORDER BY EX_TIME DESC LIMIT 1; | <NULL> |
+--------+-------+--------+------------+---------------+--------------------------+--------------------------------------------------------------+-----------+
SQL> SET reg_command OFF;- 查看当前节点的操作记载
sql
SQL> SELECT * FROM SYS_COMMAND_LOG LIMIT 10;
+--------+-------+--------+------------+---------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| NODEID | DB_ID | USER | SESSION_ID | CLIENT_IP | EX_TIME | SQL_STR | SQL_PARAS |
+--------+-------+--------+------------+---------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------+
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:56:20.000 | select * from SYS_SYNONYMS; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:57:06.000 | CREATE TABLE tbsyns_info(id INT); | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:57:16.000 | CREATE SYNONYM sync FOR tbsyns_info; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:57:24.000 | SELECT ss.db_id,sm.schema_name,ss.syno_id,syno_name FROM SYS_SYNONYMS ss JOIN SYS_SCHEMAS sm USING(db_id,schema_id) WHERE ss.syno_name='SYNC'; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:57:40.000 | INSERT INTO sync values(1); | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:57:54.000 | SELECT * FROM sync; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 14:58:01.000 | SELECT * FROM tbsyns_info; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 15:33:53.000 | select * from SYS_TABLES; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 15:34:01.000 | select * from SYS_TABLES; | <NULL> |
| 1 | 1 | SYSDBA | 0 | 192.168.2.236 | 2025-06-26 15:44:45.000 | CREATE TABLE tab_heap(id INT); | <NULL> |
+--------+-------+--------+------------+---------------+--------------------------+------------------------------------------------------------------------------------------------------------------------------------------------+-----------+- SQL_PARAS字段举例
sql
SQL> SET reg_command ON;
-- 使用JAVA程序传参
PrepareStatement pre = connection.prepareStatement("SELECT ?+1 FROM DUAL");
pre.setInt(1,2);
pre.execute;
SQL> SET reg_command OFF;
-- 查询SYS_COMMAND_LOG
SQL> SELECT * FROM SYS_COMMAND_LOG ORDER BY EX_TIME DESC LIMIT 10;
+--------+-------+------------+------------+----------------+--------------------------+----------------------------------------------+------------------------+
| NODEID | DB_ID | USER | SESSION_ID | CLIENT_IP | EX_TIME | SQL_STR | SQL_PARAS |
+--------+-------+------------+------------+----------------+--------------------------+----------------------------------------------+------------------------+
| 1 | 1 | SYSDBA | 29 | 192.168.30.236 | 2025-06-28 16:49:29.000 | Prepare st27aae97b0 as SELECT ?+1 FROM DUAL; | <NULL> |
| 1 | 1 | SYSDBA | 29 | 192.168.30.236 | 2025-06-28 16:49:29.000 | ? st27aae97b0 | {PN=1,GN=1 <1,S,'2'>} |
+--------+-------+------------+------------+----------------+--------------------------+----------------------------------------------+------------------------+相关系统表
提示
开启/关闭命令记载：SET reg_command ON/OFF;