Monitoring Table Usage & Statistics Collections

### Monitoring Table Usage for the apllication & testing purpose when the anyone table not use delete now.. how ...?see
the Scenario's rename the table and see the application break the connection & users face some problem or not when not so drop unused table.

-show parameter '%AUDIT%'
-alter system set audit_trail=db scope=spfile;
-bounce back your database;

-audit select, insert, update, delete, on scott.emp;

-perform DML on EMP table


-select username,obj_name,to_char(timestamp,'dd-mon-yy hh12: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 order by 1

-noaudit select, insert, update, delete, on scott.emp;

## Tracking objects Modifications Statistics

----Perform a quick analyze to load in base statistics
DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT', CASCADE => TRUE);


----Examine the current statistics
SELECT table_name, num_rows, blocks, avg_row_len,monitoring
FROM user_tables
WHERE table_name='EMP';

------Turn on Automatic Monitoring
Now turn on automatic monitoring for the emp table. This can be done using the alter table method.
Starting with Oracle 9i, you can also perform this at the "schema", and "entire database" level. I provide the syntax for all three methods below.
Monitor only the EMP table.

alter table emp monitoring;

-----Monitor all of the tables within Scott's schema. (Oracle 9i and higher)
BEGIN
  DBMS_STATS.alter_schema_tab_monitoring('scott', true);
END;

------Verify that monitoring is turned on.
Note: The results of the following query are from running the alter table ... statement on the emp table only.

SELECT table_name, monitoring
FROM user_tables
ORDER BY monitoring;


----- Delete some rows from the database.

SQL> DELETE FROM emp WHERE rownum < 501;

500 rows deleted.

SQL> commit;

-------Wait until the monitered data is flushed.
before analyze flush first the monitoring information for the statistics collection.
Data can be flushed in several ways.
In Oracle 8i, you can wait it out for 3 hours.
In Oracle 9i and higher, you only need to wait 15 minutes.
In either version, restart the database.
For immediate results in Oracle 9i and higher, use the DBMS_STATS.flush_database_monitoring_info package.
OK, I'm impatient...

exec dbms_stats.flush_database_monitoring_info;

-----Check for what it has collected.
As user "scott", check USER_TAB_MODIFICATIONS to see what it was collected.

SELECT * FROM user_tab_modifications;

------Perform a quick analyze to load in base statistics
DBMS_STATS.GATHER_SCHEMA_STATS ('SCOTT', CASCADE => TRUE);


------Verify that the table is no longer listed in USER_TAB_MODIFICATIONS.
SQL> SELECT * FROM user_tab_modifications;


-----Examine some of new statistics collected.
SELECT table_name, num_rows, blocks, avg_row_len,monitoring
FROM user_tables where table_name='EMP';