A script to check the frequency of Oracle log switches

I was looking for a script that showed me the frequency of my redo log switching through a SQL query. Although I can use Grid Control to monitor my redo logs, and use his several advisors, I prefer to use SQLPlus when it’s possible to keep the control of what I’m doing.

What I found is a fancy script written by Donald K. Burleson, that can be found in the following link

http://www.remote-dba.cc/oracle_tips_log_switches.htm

The script, in resume will show the frequency of your redo logs by hour, but unfortunately the view v$log_history has changed since the time that this script was done (I’m using Oracle 10g), so the select fails with the following error:

group by substr(time,1,5)
*
ERROR at line 27:
ORA-00904: “TIME”: invalid identifier

So I tried to fix the script to adapt it to Oracle 10g, but being unable to make the conversion, I decided to make a new script.

This is the resultant script:

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,’YYYY-MM-DD’) DAY,
TO_CHAR(FIRST_TIME,’HH24′) HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ORDER BY TO_CHAR(FIRST_TIME,’YYYY-MM-DD’),TO_CHAR(FIRST_TIME,’HH24′)
ASC;

And there is an output example:

DAY             HOUR TOTAL
————— —- —–
2010-04-30      01      24
2010-04-30      02       1
2010-04-30      13       1
2010-04-30      22       1
2010-05-01      00       1
2010-05-01      01      23
2010-05-01      03       1
2010-05-01      22       1
2010-05-02      00       1
2010-05-02      01      23
2010-05-02      22       1
2010-05-03      00       1
2010-05-03      01      23
2010-05-03      11       1
2010-05-03      22       1
2010-05-04      00       1
2010-05-04      01      24
2010-05-04      02       1
2010-05-04      12       1
2010-05-04      14       1
2010-05-05      00       1
2010-05-05      01      24
2010-05-05      02       1
2010-05-05      12       1
2010-05-05      22       1
2010-05-06      00       1
2010-05-06      01      23
2010-05-06      02       1
2010-05-06      12       1
2010-05-06      16       1
2010-05-07      00       1
2010-05-07      01      24
2010-05-07      02       1
2010-05-07      12       1
2010-05-07      22       1
2010-05-08      01      24
2010-05-08      03       1
2010-05-09      00       1
2010-05-09      01      24
2010-05-10      00       1
2010-05-10      01      24
2010-05-10      12       1
2010-05-10      22       1
2010-05-11      01      24
2010-05-11      02       1

Indiana Jones and the Lost Table

Let’s put it this way: you have some data that you want to find in the database, but you have no idea of what table it is. You don’t even know the name of the column, so you have to search a single number IN EVERY COLUMN OF EVERY TABLE. At least you know the schema where it is…

So, we have to find where is the data 1662 only knowing the name of the schema. The name of the schema is Absys. Don’t worry, we can still do it, but it won’t be an easy task.

We can do one of these two things:

1. Search manually on every table, looking in every column for a coincidence of the numer 1161.

2. Create a script that does it.

I chose the nº2, so there is my script in case it may help you:

sqlplus “/as sysdba”
set lines 200;
set pages 50000;
spool search.sql
SELECT ‘SELECT * FROM ABSYS.’|| TABLE_NAME||’ WHERE ‘||COLUMN_NAME||’=1161;’ FROM ALL_TAB_COLUMNS WHERE OWNER=’ABSYS’;
spool off;
spool search_findings.txt
@search.sql
spool off;

Ét voilà! You will have the coincidences of this data in every column of every table of the schema named ABSYS. Although the execution of your script may take a while 🙂