在企業級OceanBase數據庫運維中，你是否遇到過這樣的痛點：數據庫表數量繁多，無法快速判斷哪些表被業務實際使用，哪些屬于“沉睡表”，難以評估表對象的下線可行性？
其實，借助OceanBase的audit審計功能，搭配定時任務調度，就能精準跟蹤表對象的訪問情況，為對象下線評估提供數據支撐。今天就給大家帶來一份完整實戰指南，從環境配置到任務驗證，一步到位實現數據庫對象的定時審計！
一、背景描述
某客戶想通過 Oceanbase audit 的表審計功能來判斷系統中的表是否有被應用訪問，于是參考官方文檔中對于audit審計功能的介紹，通過部署存儲過程和定時任務的方式來對指定的表進行定時審計的方式來判斷表是否有被訪問。
二、部署步驟
2.1、開啟安全審計
1）sys用戶登錄租戶解鎖ORAAUDITOR用戶
[admin@observer01 ~ ]$ obclient -hxx.xx.xx.xx -P2883 -uSYS@oboracle01#obcluster001 -pxxxxxxx
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1172630
Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [SYS]> alter user ORAAUDITOR account unlock;
2）修改ORAAUDITOR密碼
obclient [SYS]> alter user ORAAUDITOR identified by "AAaa11__";
3）設置ORAAUDITOR用戶權限
obclient [SYS]> grant create session, select any dictionary to ORAAUDITOR;
obclient [SYS]> grant create procedure to ORAAUDITOR; --通過存儲過程的方式來開啟和關閉指定對象的審計操作，因此需要創建procedure權限
obclient [SYS]> grant execute on dbms_scheduler to ORAAUDITOR; -- 通過定時任務的方式定時調用存儲過程開啟和關閉審計任務，因此需要執行dbms_scheduler的權限。
4）檢查ORAAUDITOR用戶權限
obclient [SYS]> select * from dba_sys_privs where grantee='ORAAUDITOR';
+------------+-----------------------+--------------+
| GRANTEE | PRIVILEGE | ADMIN_OPTION |
+------------+-----------------------+--------------+
| ORAAUDITOR | CREATE SESSION | NO |
| ORAAUDITOR | SELECT ANY DICTIONARY | NO |
| ORAAUDITOR | CREATE PROCEDURE | NO |
+------------+-----------------------+--------------+
3 rows in set (0.132 sec)
obclient [SYS]> select * from dba_tab_privs where grantee='ORAAUDITOR';
+------------+-----------+----------------+---------+-----------+-----------+-----------+
| GRANTEE | OWNER | TABLE_NAME | GRANTOR | PRIVILEGE | GRANTABLE | HIERARCHY |
+------------+-----------+----------------+---------+-----------+-----------+-----------+
| ORAAUDITOR | oceanbase | DBMS_SCHEDULER | SYS | EXECUTE | NO | NO |
+------------+-----------+----------------+---------+-----------+-----------+-----------+
1 row in set (0.149 sec)
5）啟用審計功能并設置審計記錄的存儲位置。
obclient [SYS]> alter system set audit_trail='DB,EXTENDED';
說明：通過配置項 audit_trail 開啟審計功能，執行完后立即生效。
NONE：關閉審計。
OS：審計記錄寫本地文件。
DB：審計記錄寫內部表。
DB,EXTENDED：審計記錄寫內部表且記錄包含執行的 SQL 語句。
2.2、設置審計規則
1）ORAAUDITOR用戶登錄租戶
[admin@observer01 ~ ]$ obclient -hxx.xx.xx.xx -P2883 -uORAAUDITOR@oboracle01#obcluster001 -pAAaa11__
Welcome to the OceanBase. Commands end with ; or \g.
Your OceanBase connection id is 1172630
Server version: OceanBase 4.2.5.6 (r106000052025082216-22431cc723c4e07a82cac27d4347e8ac2f0afc30) (Built Aug 22 2025 18:05:13)
Copyright (c) 2000, 2018, OceanBase and/or its affiliates. All rights reserved.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
obclient [ORAAUDITOR]>
2）創建開啟審計規則的存儲過程
obclient [ORAAUDITOR]> create or replace procedure ORAAUDITOR.enable_table_audit as
begin
execute immediate 'audit insert,update,delete,select on USERDATA.USER_TABLE_001';
end;
/
注：
1.以上存儲過程僅對USERDATA.USER_TABLE_001表開啟insert,update,delete,select類型的操作審計。
2.如果多個表需要進行審計操作，可以將需要審計的表寫入臨時表中，通過輪詢訪問臨時表中的表對象來對表開啟審計操作。
3.除了insert/update/delete/select操作外，還可以對表進行其他類型的審計，具體參考官方文檔：https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001504072。
3）創建關閉審計規則的存儲過程
obclient [ORAAUDITOR]> create or replace procedure ORAAUDITOR.disable_table_audit as
begin
execute immediate 'noaudit insert,update,delete,select on USERDATA.USER_TABLE_001';
end;
/
注：以上存儲過程為對USERDATA.USER_TABLE_001表關閉insert,update,delete,select類型的操作審計。
4）查看對應存儲過程的狀態
obclient [SYS]> select owner,object_name,object_type,status,created,last_ddl_time from dba_objects where owner='ORAAUDITOR';
+------------+---------------------+-------------+--------+---------------------+---------------------+
| OWNER | OBJECT_NAME | OBJECT_TYPE | STATUS | CREATED | LAST_DDL_TIME |
+------------+---------------------+-------------+--------+---------------------+---------------------+
| ORAAUDITOR | DISABLE_TABLE_AUDIT | PROCEDURE | VALID | 2026-03-03 17:51:05 | 2026-03-03 17:51:05 |
| ORAAUDITOR | ENABLE_TABLE_AUDIT | PROCEDURE | VALID | 2026-03-03 17:49:44 | 2026-03-03 17:49:44 |
| ORAAUDITOR | ORAAUDITOR | DATABASE | VALID | 2026-01-05 15:49:42 | 2026-01-05 15:49:42 |
+------------+---------------------+-------------+--------+---------------------+---------------------+
3 rows in set (1.219 sec)
2.3、配置定時任務
1）配置定時任務，每天早上8點開啟審計。
obclient [ORAAUDITOR]> begin
dbms_scheduler.create_job (
job_name => 'enable_audit_job',
job_type => 'stored_procedure',
job_action => 'oraauditor.enable_table_audit',
number_of_argument => 0,
start_date => trunc(sysdate)+8/24,
repeat_interval => 'FREQ=DAILY; INTERVAL=1',
enabled => true,
auto_drop => FALSE,
comments => '定時開啟表審計'
);
commit;
end;
/
2）配置定時任務，每天晚上20點關閉審計。
obclient [ORAAUDITOR]> begin
dbms_scheduler.create_job (
job_name => 'disable_audit_job',
job_type => 'stored_procedure',
job_action => 'oraauditor.disable_table_audit',
number_of_argument => 0,
start_date => trunc(sysdate)+20/24,
repeat_interval => 'FREQ=DAILY; INTERVAL=1',
enabled => true,
auto_drop => FALSE,
comments => '定時關閉表審計'
);
commit;
end;
/
3）查看JOB狀態。
obclient [SYS]> select owner,job_name,repeat_interval,enabled,state,next_run_date from dba_scheduler_jobs where job_name like '%audit%';
+------------+-------------------+------------------------+---------+-------+------------------------------------+
| OWNER | JOB_NAME | REPEAT_INTERVAL | ENABLED | STATE | NEXT_RUN_DATE |
+------------+-------------------+------------------------+---------+-------+------------------------------------+
| ORAAUDITOR | disable_audit_job | FREQ=DAILY; INTERVAL=1 | 1 | NULL | 2026-03-04 20:00:00.000000 +08:00 |
| ORAAUDITOR | enable_audit_job | FREQ=DAILY; INTERVAL=1 | 1 | NULL | 2026-03-04 08:00:00.000000 +08:00 |
+------------+-------------------+------------------------+---------+-------+------------------------------------+
2.4、查看JOB調度情況
-- 查看JOB是否被調度
obclient [SYS]> select owner,job_name,repeat_interval,enabled,state,next_run_date from dba_scheduler_jobs where job_name like '%audit%';
+------------+-------------------+------------------------+---------+-----------+------------------------------------+
| OWNER | JOB_NAME | REPEAT_INTERVAL | ENABLED | STATE | NEXT_RUN_DATE |
+------------+-------------------+------------------------+---------+-----------+------------------------------------+
| ORAAUDITOR | disable_audit_job | FREQ=DAILY; INTERVAL=1 | 1 | SCHEDULED | 2026-03-05 20:00:00.000000 +08:00 |
| ORAAUDITOR | enable_audit_job | FREQ=DAILY; INTERVAL=1 | 1 | SCHEDULED | 2026-03-05 08:00:00.000000 +08:00 |
+------------+-------------------+------------------------+---------+-----------+------------------------------------+
2 rows in set (0.016 sec)
-- 查看JOB調度歷史
obclient [SYS]> select job_name,code,time,message from dba_scheduler_job_run_details where job_name like '%audit%' and time>trunc(sysdate)+13/24+10/1440 order by time desc;
+-------------------+------+----------------------------+---------+
| JOB_NAME | CODE | TIME | MESSAGE |
+-------------------+------+----------------------------+---------+
| disable_audit_job | 0 | 2026-03-04 20:00:00.280350 | SUCCESS |
| enable_audit_job | 0 | 2026-03-04 08:00:00.264210 | SUCCESS |
+-------------------+------+----------------------------+---------+
2 rows in set (0.016 sec)
從上面結果可以看到：每天8點開啟審計和每天20點關閉審計的JOB任務成功被定時調度。
2.5、查看審計記錄
obclient [SYS]> select username,owner,obj_name,action_name,count(*) from dba_audit_trail where owner='USERDATA' and obj_name='USER_TABLE_001' and timestamp>'2026-03-04 08:00:00' group by username,owner,obj_name,action_name order by 1,2,3,4;
+-----------+----------+----------------+-------------+----------+
| USERNAME | OWNER | OBJ_NAME | ACTION_NAME | COUNT(*) |
+-----------+----------+----------------+-------------+----------+
| USER01 | USERDATA | USER_TABLE_001 | INSERT | 25 |
| USER01 | USERDATA | USER_TABLE_001 | UPDATE | 4 |
| USER01 | USERDATA | USER_TABLE_001 | SELECT | 85338 |
| USER02 | USERDATA | USER_TABLE_001 | DELETE | 8 |
| USER02 | USERDATA | USER_TABLE_001 | INSERT | 1444 |
| USER02 | USERDATA | USER_TABLE_001 | SELECT | 97436 |
+-----------+---------+-----------------+-------------+----------+
6 rows in set (5.419 sec)
從上面結果中可以看到，表有正常被審計到相關的CURD操作。
三、注意事項
1、高頻的審計操作會帶來性能損耗，需進行嚴格的測試，生產環境謹慎使用。
2、可以通過部署SQLAuditStore工具來定期對gv$ob_sql_audit中的數據進行持久化到本地文件系統中，再對指定對象進行分析。
四、官方文檔參考
1、安全審計
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001500082
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001500210
2、DBMS_SCHEDULER
https://www.oceanbase.com/docs/common-oceanbase-database-cn-1000000001503788
如果在部署過程中遇到問題，歡迎在評論區留言交流，后續我們會持續分享更多實戰運維干貨，記得關注不迷路，下次見~
