Steps to Monitor Oracle Table Usage

If the application is experiencing performance issues, As part of your overall tuning strategy, you want to obtain a better understanding of the application by determining which tables are being used by what types of SQL statements. Tables that aren’t being used can be renamed and later dropped. By removing unused tables, you can free up space, reduce the clutter, and focus your performance analysis on actively used tables.

Use Oracle’s standard auditing feature to determine which tables are being used. Auditing is enabled as follows:

1. Set the AUDIT_TRAIL parameter.

2. Stop and start your database to enable the setting of AUDIT_TRAIL.

3. Use the AUDIT statement to enable auditing of specific database operations.

Oracle’s standard auditing feature is enabled through setting the AUDIT_TRAIL initialization parameter. When you set the AUDIT_TRAIL parameter to DB, this specifies that Oracle will write audit records to an internal database table named AUD$. For example, when using an spfile, here’s how to set the AUDIT_TRAIL parameter:

SQL> alter system set audit_trail=db scope=spfile;

If you are using an init.ora file, open it with a text editor and set the AUDIT_TRAIL value to DB. After you’ve set the AUDIT_TRAIL parameter, you’ll need to stop and restart your database for it to take effect.

Now you can enable auditing for a specific database operation. For example, the following statement enables auditing on all DML statements on the EMP table owned by INV_MGMT:

SQL> audit select, insert, update, delete on inv_mgmt.emp;

From this point on, any DML access to the EMP table will be recorded in the SYS.AUD$ table. Oracle provides several auditing views based on the AUD$ table, such as DBA_AUDIT_TRAIL or DBA_AUDIT_OBJECT.

You can query these views to report on auditing actions—for example:

Select username ,obj_name ,to_char(timestamp,’dd-mon-yy hh24:mi’) event_time ,substr(ses_actions,4,1) del ,substr(ses_actions,7,1) ins ,substr(ses_actions,10,1) sel ,substr(ses_actions,11,1) upd from dba_audit_object;

Here is some sample output:

USERNAME OBJ_NAME EVENT_TIME DEL INS SEL UPD

—————————— ———- ——————— — — — —

INV_MGMT EMP 05-feb-11 15:08 – S – S

INV_MGMT EMP 05-feb-11 15:10 – – S –

INV_MGMT EMP 05-feb-11 15:10 S – – –

In the prior SQL statement, notice the use of the SUBSTR function to reference the SES_ACTIONS column of the DBA_AUDIT_OBJECT view. That column contains a 16-character string in which each character means that a certain operation has occurred. The 16 characters represent the following operations in this order: ALTER, AUDIT, COMMENT, DELETE, GRANT, INDEX, INSERT, LOCK, RENAME, SELECT, UPDATE, REFERENCES, and EXECUTE. Positions 14, 15, and 16 are reserved by Oracle for future use. The character of S represents success, F represents failure, and B represents both success and failure. To turn off auditing on an object, use the NOAUDIT statement:

SQL> noaudit select, insert, update, delete on inv_mgmt.emp;

Sometimes it’s handy when troubleshooting disk space or performance issues to know which tables in the database are actually being used by the application. If you’ve inherited a database that contains a large number of tables, it may not be obvious which objects are being accessed. Enabling auditing allows you to identify which types of SQL statements are accessing a table of interest. Once you have identified tables that are not being used, you can simply rename the tables and see if this breaks the application or if any users complain. If there are no complaints, then after some time you can consider dropping the tables. Make sure you take a good backup of your database with both RMAN and Data Pump before you drop any tables you might have to later recover.

If you simply need to know whether a table is being inserted, updated, or deleted from, you can use the DBA/ALL/USER_TAB_MODIFICATIONS view to report on that type of activity. This view has columns, such as INSERTS, UPDATES, DELETES, and TRUNCATED, that will provide information as to how data in the table is being modified—for example:

select table_name, inserts, updates, deletes, truncated from user_tab_modifications;

In normal conditions, this view is not instantly updated by Oracle. If you need to immediately view table modifications, then use the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to update the view:

SQL> exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO();

In case of any ©Copyright or missing credits issue please check CopyRights page for faster resolutions.