Oracle : Procedure to capture the Tables row count and Actual table size

Follow below procedure to capture following information :

1) Tables row count.
2) Table Actual Size.
3) DBA_Tab_Modifications information.

Pre requisite: This Procedure needs to be implemented if we are gathering stats daily.

#############On any Dev server : (Create DB link to Standby DB from DEV server)

“If you don’t want to implement in production DB”

Step 1 : (One Time Activity)

create public database link
PROD_REP_12c
connect to
SYSTEM
identified by
<PW>
using ‘PROD_STBY’;

step 2 :  (One Time Activity)

Creating Table for storing row count and Actual table size for APP schema tables:

create table scott.APP_table_row_counts_prod as
select sysdate count_date,owner,table_name,num_rows,last_analyzed, round((num_rows*avg_row_len)/(1024*1024)) Actual_Size_in_MB from dba_tables@PROD_REP_12c where owner=’APP_SCHEMA’ order by 6 desc;

We are also capturing DBA_TAB_MODIFICATIONS for DML Operations.
create table scott.APP_dba_modifications_prod
as
select
sysdate changes_date,
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
TRUNCATED,
DROP_SEGMENTS
from dba_tab_modifications@PROD_REP_12c where table_owner=’APP_SCHEMA’ order by table_name;

Step 3 : Setting up cron job to capture data.

$ cat /home/oracle/dba/scripts/APP_table_row_counts_prod.sh
#!/bin/bash
#set -x
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/u01/app/oracle/product/12102/bin
export ORACLE_HOME=/u01/app/oracle/product/12102
export ORACLE_SID=ORCL11
sqlplus -s / as sysdba <<EOF
conn scott/PW
insert into scott.APP_table_row_counts_prod
select sysdate count_date,owner,table_name,num_rows,last_analyzed, round((num_rows*avg_row_len)/(1024*1024)) Actual_Size_in_MB
from dba_tables@PROD_REP_12c where owner=’APP_SCHEMA’ order by 6 desc;
commit;

insert into scott.APP_dba_modifications_prod
select
sysdate changes_date,
TABLE_OWNER,
TABLE_NAME,
PARTITION_NAME,
SUBPARTITION_NAME,
INSERTS,
UPDATES,
DELETES,
TIMESTAMP,
TRUNCATED,
DROP_SEGMENTS
from dba_tab_modifications@PROD_REP_12c where table_owner=’APP_SCHEMA’ order by table_name;
commit;

exit;
EOF

Step 4 :

cd /home/oracle/dba/scripts/
chmod 755 APP_table_row_counts_prod.sh

Step 5 :

00 07 * * * /home/oracle/dba/scripts/APP_table_row_counts_prod.sh

Validation :

From Dev :

select count(*) from scott.APP_table_row_counts_prod;
select count(*) from scott.APP_dba_modifications_prod;

select count_date,table_name,num_rows,actual_size_in_MB from scott.app_table_row_counts_prod where to_char(count_date,’DD-MON-YYYY’) = ’05-DEC-2016′  order by table_name;

Note: If you have any questions regarding implementation please comment below.