Monday, May 19, 2008

Table Monitoring worked well

I was logged in one of our forgotten databases to partition some tables and maintain dba routines. After all my work is done i decided to examine the tables which are not used any more.

First thing is to find if it worths to spend some time on it. I checked the sizes of the tables and found that one of the unused tables consume approximately 21Gb of space. That should be so valuable to drop this table on this database because of the problematic disk space usage.

select
segment_name, sum(bytes/1024/1024) as MB
from
dba_segments
where
segment_name in ('WAP_STATREC','PAYFORME_TEMP','PAY4ME_TEMP') and
owner ='PQ'
group by
segment_name
order by MB desc;

SEGMENT_NAME MB
------------ --
WAP_STATREC 21443
PAYFORME_TEMP 145
PAY4ME_TEMP 1


To obtain if these tables are actively used by developers or any application, i altered these tables to enable monitoring and logout the system.


alter table pq.wap_statrec monitoring
alter table pq.payforme_temp monitoring
alter table pq.pay4me_temp monitoring


Today, when i remembered what have i done for a few weeks earlier, i logged in the database and check the dba_tab_modifications management view to find if any modifications done to the tables.


select
table_owner, table_name, inserts, updates, deletes, timestamp, truncated
from
sys.dba_tab_modifications
where
table_name in ('WAP_STATREC','PAYFORME_TEMP','PAY4ME_TEMP');

TABLE_OWNER TABLE_NAME INSERTS UPDATES DELETES TIMESTAMP TRUNCATED
----------- ---------- ------- ------- ------- --------- ---------
PQ PAYFORME_TEMP 10695476 0 0 5/7/2008 YES



As you can see only one of the tables is actively used by an application and the others are not used for 2 weeks. Now time to talk with the application operation or development group to drop their unused tables.

2 comments:

  1. You are right, i missed about reads. One can read about database audit option to figure out reads on any table.

    ReplyDelete