Wednesday, July 14, 2010

oradebug CLOSE_TRACE

I read oracle-l - How to free inodes in Linux without killing processes From: Tanel Poder
I learned with oradebug ...
Example:
$ mv orcl_lgwr_14604.trc orcl_lgwr_14604.trc.old
$ ls -la *14604*
-rw-r----- 1 oracle oinstall 18764 Jul 13 22:38 orcl_lgwr_14604.trc.old

SQL> oradebug help
CLOSE_TRACE Close trace file

SQL> oradebug setospid 14604
Oracle pid: 6, Unix process pid: 14604, image: oracle@oratest (LGWR)

SQL> oradebug close_trace
Statement processed.

SQL> oradebug TRACEFILE_NAME
/u01/app/oracle/admin/orcl/bdump/orcl_lgwr_14604.trc

SQL> !ls -la *14604*
-rw-r----- 1 oracle oinstall 679 Jul 14 18:02 orcl_lgwr_14604.trc
-rw-r----- 1 oracle oinstall 18948 Jul 14 18:01 orcl_lgwr_14604.trc.old

FLUSH_DATABASE_MONITORING_INFO Procedure

This is just some procedure, I haven't used it and It's not new... just interest
A FLUSH_DATABASE_MONITORING_INFO is Procedure in DMBS_STATS package. This procedure flush monitoring information for all tables. Corresponding entries in the *_TAB_MODIFICATIONS, *_TAB_STATISTICS and *_IND_STATISTICS views are updated immediately.
SQL> update tb_obj set object_id=100 where rownum <= 50; 50 rows updated. SQL> commit;

Commit complete.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TB_OBJ 0 0 1

SQL> delete from tb_obj where rownum <= 50; 50 rows deleted. SQL> commit;

Commit complete.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TB_OBJ 0 0 1
Test to use FLUSH_DATABASE_MONITORING_INFO Procedure:
SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO;

PL/SQL procedure successfully completed.

SQL> select table_name, inserts, updates, deletes from user_tab_modifications;

TABLE_NAME INSERTS UPDATES DELETES
------------------------------ ---------- ---------- ----------
TB_OBJ 0 50 51
That shows monitoring information after it's flushed from memory.