I need to write a unix shell script to connect to oracle database and retrieve data from a table, the script should count the total number of rows till date in that table
number of rows which are getting filled per hour on current day and the peak hour of the current day
number of rows which are getting filled per day and per week
This is to be done for multiple tables
This count helps me in calculating the dataflow per hour,day,week
Thanks in advance
I'm not a DBA, but this kind of thing is available in products like Spotlight and Toad.
What you are asking for is database I/O, and I do not believe this is available on anything more fine-grained than a tablespace. Many tables can live in a tablespace. If your table is the only one in its tablespace you may be in luck.
And unless your table has an actively maintained "activity_date" column (timestamp or sysdate) you will never get insert rates, except going forward.
Each table has actively maintained date column and I use toad to connect to oracle database.
Is there any way to find out that the certain table is the only table in the tablespace?
You may can find with the help of DBA_TABLES (view) the table which is placed in the tablespace.
Calculating the dataflow per hour,day,week?
---Check the logswitches per day with given below query hope it will helpfull.
Note:- It will not give you insertion,deletion,updation of the data.but gives you total transction of the database.
#####################################################
-- Author : Mohammed Fareed.
-- Script : log_switch.sql
-- Description : Get log switches on total , daily and hourly basis
set heading off;
select '******************************************************' from dual;
select '**** Redolog Daily and Hourly volume calculated ****' from dual;
select '******************************************************' from dual;
timing start Redovol;
--#######################################################################################
--## PL/SQL used here to gather and display average redo volumes ##
--#######################################################################################
set serveroutput on;
declare
v_log number;
v_days number;
v_logsz number;
v_adsw number;
V_advol number;
v_ahsw number;
v_ahvol number;
begin
select count(first_time) into v_log from v$log_history;
select count(distinct(to_char(first_time,'dd-mon-rrrr'))) into v_days from v$log_history;
select max(bytes)/1024/1024 into v_logsz from v$log;
v_adsw := round(v_log / v_days);
v_advol := round(v_adsw * v_logsz);
v_ahsw := round(v_adsw / 24);
v_ahvol := round((v_adsw / 24 )) * v_logsz;
dbms_output.put ('Total Switches' || ' '||v_log||' ==> ');
dbms_output.put ('Total Days' || ' '|| v_days||' ==> ');
dbms_output.put_line ('Redo Size' || ' ' || v_logsz);
dbms_output.put ('Avg Daily Switches' || ' ' || v_adsw||' ==> ');
dbms_output.put_line ('Avg Daily Volume in Meg' || ' ' || v_advol);
dbms_output.put ('Avg Hourly Switches' || ' ' || v_ahsw||' ==> ');
dbms_output.put_line ('Avg Hourly Volume in Meg' || ' ' || v_ahvol);
end;
/
--#######################################################################################
--## END of PL/SQL routine ##
--#######################################################################################
timing stop Redovol;
###########################################################
You can view the contents of your tablespaces in TOADs schema browser or query your database for it:
SELECT tablespace_name,table_name
FROM user_tables;
To count inserted rows you need a list of tables you'd like to anylse and the names of the date-columns. I'd put them in a file like this:
tablename1:datecolumnname1
tablename2:datecolumnname2
...
Then a simple script can read that file and query your database:
TABLE_DEF=/path/to/your/file.that.lists.tables.and.datecolumns
DB_USER=your_db_user
DB_PASSWORD=your_password
OLD_IFS=$IFS
IFS=:
while read TABLE_NAME DATE_COLUMN
do
sqlplus -s $DB_USER/$DB_PASSWORD@$ORACLE_SID <<EOF
set heading off
set feedback off
prompt Dataflow for table $TABLE_NAME
select 'Total number of rows',count(*)
from $TABLE_NAME;
select 'Number of rows inserted today',count(*)
from $TABLE_NAME
where trunc($DATE_COLUMN) = trunc(sysdate);
prompt Number of rows per hour today, peak hour first
set heading on
select to_char($DATE_COLUMN,'HH24') "hr",count(*) "rows inserted"
from $TABLE_NAME
where trunc($DATE_COLUMN) = trunc(sysdate)
group by to_char($DATE_COLUMN,'HH24')
order by 2 desc;
set heading off
select 'Number of rows inserted this week',count(*)
from $TABLE_NAME
where trunc($DATE_COLUMN,'IW') = trunc(sysdate,'IW');
EOF
done <$TABLE_DEF
IFS=$OLD_IFS
Its giving me error saying Table or View doesn't exist
I gave all the correct table details