How to monitor data transaction on one table
First, we need to see if there is an attribute "MONITORING" of the table.
Just query 'all_tables':
SQL> select owner,MONITORING from all_tables where TABLE_NAME=''EVENT_BILLING_PRODUCT_T";
OWNER MON
------------------------------ ---
PIN YES
###MONITORING VARCHAR2(3) Whether the table has the MONITORING attribute set
###一般情況下,在建表時我們會經常使用ORACLE的預設引數。而MONITORING這個引數9i 中預設是關閉的, 10g預設為開啟。非預設時使用NOMONITORING引數。
If the value for 'MONITORING' is 'No', we should alter the value:
SQL> alter table EVENT_BILLING_PRODUCT_T monitoring;
Table altered.
Then, query the ALL_TAB_MODIFICATIONS, which describes tables accessible to the current user that have been modified since the last time statistics were gathered on the tables.
###其中記錄了該表或表的PARTITION的INSERT/UPDATE/DELETE/DROP_SEGMENTS/TRUNCAE的次數。但要注意的一點就是:INSERT/UPDATE/DELETE只能記錄一個大概次數。而不是十分精確的次數。以上為ORACLE官方文件提供的 DBA_TAB_MODIFICATIONS欄位及其說明。
SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from all_TAB_MODIFICATIONS where table_name='EVENT_BILLING_PRODUCT_T';
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ -------------------- ---------- ---------- ---------- ---------
EVENT_BILLING_PRODUCT_T 9140 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_HISTORIC 4042 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_LAST 5098 0 0 07-OCT-11
SQL> /
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ -------------------- ---------- ---------- ---------- ---------
EVENT_BILLING_PRODUCT_T 9140 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_HISTORIC 4042 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_LAST 5098 0 0 07-OCT-11
###需要注意的重要一點,出於保護ORACLE資料庫效能方面的考慮,在XXX_TAB_MODIFICATIONS表中顯示的資料並不是實時的資料,如果需要看到最近發生的最新資料,可以使用DBMS_STAT包來統計最新變化。
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed
更進一步的探討,可以參考以下的連結:
Just query 'all_tables':
SQL> select owner,MONITORING from all_tables where TABLE_NAME=''EVENT_BILLING_PRODUCT_T";
OWNER MON
------------------------------ ---
PIN YES
###MONITORING VARCHAR2(3) Whether the table has the MONITORING attribute set
###一般情況下,在建表時我們會經常使用ORACLE的預設引數。而MONITORING這個引數9i 中預設是關閉的, 10g預設為開啟。非預設時使用NOMONITORING引數。
If the value for 'MONITORING' is 'No', we should alter the value:
SQL> alter table EVENT_BILLING_PRODUCT_T monitoring;
Table altered.
Then, query the ALL_TAB_MODIFICATIONS, which describes tables accessible to the current user that have been modified since the last time statistics were gathered on the tables.
###其中記錄了該表或表的PARTITION的INSERT/UPDATE/DELETE/DROP_SEGMENTS/TRUNCAE的次數。但要注意的一點就是:INSERT/UPDATE/DELETE只能記錄一個大概次數。而不是十分精確的次數。以上為ORACLE官方文件提供的 DBA_TAB_MODIFICATIONS欄位及其說明。
SQL> select TABLE_NAME,PARTITION_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP from all_TAB_MODIFICATIONS where table_name='EVENT_BILLING_PRODUCT_T';
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ -------------------- ---------- ---------- ---------- ---------
EVENT_BILLING_PRODUCT_T 9140 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_HISTORIC 4042 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_LAST 5098 0 0 07-OCT-11
SQL> /
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
------------------------------ -------------------- ---------- ---------- ---------- ---------
EVENT_BILLING_PRODUCT_T 9140 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_HISTORIC 4042 0 0 07-OCT-11
EVENT_BILLING_PRODUCT_T PARTITION_LAST 5098 0 0 07-OCT-11
###需要注意的重要一點,出於保護ORACLE資料庫效能方面的考慮,在XXX_TAB_MODIFICATIONS表中顯示的資料並不是實時的資料,如果需要看到最近發生的最新資料,可以使用DBMS_STAT包來統計最新變化。
SQL> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed
更進一步的探討,可以參考以下的連結:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/628922/viewspace-708750/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- SAP ECC & APO整合 - Master Data & Transaction Data TransferAST
- How boltdb Write its Data?
- 【CURSOR】How to Monitor and tune Open and Cached Cursors (Doc ID 1430255.1)
- drop apply INSTANTIATION for one tableAPP
- how to move a MediaWiki wiki from one server to anotherServer
- How to get the exact duration of an audio file in js All In OneJS
- How to create the Gold gold using RGB color values All In OneGo
- [轉帖]mysqldump --master-data=2 --single-transactionMySqlAST
- How to use VS Code to debug Next.js applications All In OneJSAPP
- Oracle 12.2 How to Generate AWRs in Active Data Guard Standby DatabasesOracleDatabase
- how BabyFile app transfer files with Windows PC though data cableAPPWindows
- use azure data studio to create external table for oracleOracle
- How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1OracleGo
- [UE] Data Table 對比工具 —— 用於 Data Table 對比以前的資料,檢視有什麼修改
- [20201110]How to get the Data Guard broker configuration from a SQL query.txtSQL
- 深入理解mysqldump原理 --single-transaction --lock-all-tables --master-dataMySqlAST
- 2788647047_monitor
- 用 MongoTemplate 操作 documentDB 與 用 Spring Data MongoDB 操作 documentDB 有什麼不同,write concern,transactionSpringMongoDB
- Postman的Monitor功能Postman
- Verilog 監控 Monitor
- tomcat one connection one thread one request one threadTomcatthread
- IDBObjectStore.transactionObject
- IDBDatabase.transaction()Database
- BTC的Transaction
- How to ssh
- Azure Monitor(二)Log Analytics
- DAPPER 事務 TRANSACTIONAPP
- indexedDB transaction 事務Index
- SAP Retail Transaction ListAI
- Boost UDP Transaction PerformanceUDPORM
- Transaction註解原理
- How to find dependency
- synchronized的monitor監視器synchronized
- SAP Workflow Tcodes ( Transaction Codes )
- Django處理事務:transactionDjango
- create table,show tables,describe table,DROP TABLE,ALTER TABLE ,怎麼使用?
- Oracle 效能調優工具:SQL MonitorOracleSQL
- plsql developer工具生成sql monitor reportSQLDeveloper
- [譯] WebAssembly: How and whyWeb