Archive for June, 2012
I found this handy script to check out Oracle Log Switch Frequency from link.
--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;
Automatic Diagnostic Repository (adrci) is one of the new features in Oracle 11g. it’s a handy Command Line Utility to deal with Database Alert Log. Followings are some useful adrci Commands.
-- To list Oracle Base
adrci> show base;
-- To list ADR HOMEs
adrci> show homes;
-- To set Single Home Path Location
adrci> set homepath 'location';
-- To tail Alert Log File
adrci> show alert -tail -f;
-- To search a Specific Message
adrci> show alert -p "message_text like 'ORA-00600'" -term
-- To search within Specific Time Limit
adrci> show alert -p "message_text like '%ORA-%' and originating_timestamp >= systimestamp - 1/24" -term
-- To purge 10080 Minutes old alert
adrci> purge -alert 10080 -type alert;
-- To list Incidents
adrci> show incident;
-- To list Problems
adrci> show problem;
Thanks
A Rahim Khan
Execute this script to find out Blocker/Waiter Session.
SELECT DECODE(request,0,'Holder: ','Waiter: ') || sid sess_sid,inst_id
FROM GV$LOCK
WHERE (id1, id2, type) IN ( SELECT id1, id2, type FROM GV$LOCK WHERE request>0)
ORDER BY sess_sid, request;
This one is to determine why.
SELECT count(*), event
FROM v$session_wait
WHERE wait_time = 0
AND event NOT IN ('smon timer','pmon timer','rdbms ipc message',
'SQL*Net message from client')
GROUP BY event
ORDER BY 1 DESC;
Thanks
A Rahim Khan
Here, I will generate script to rebuild all index for a specific Schema. First of all, let’s all Index for a specific Owner.
SQL> select index_name from dba_indexes where owner=upper('hr');
INDEX_NAME
------------------------------
LOC_CITY_IX
LOC_ID_PK
REG_ID_PK
COUNTRY_C_ID_PK
LOC_STATE_PROVINCE_IX
LOC_COUNTRY_IX
DEPT_ID_PK
DEPT_LOCATION_IX
JOB_ID_PK
EMP_EMAIL_UK
EMP_EMP_ID_PK
EMP_DEPARTMENT_IX
EMP_JOB_IX
EMP_MANAGER_IX
EMP_NAME_IX
JHIST_EMP_ID_ST_DATE_PK
JHIST_JOB_IX
JHIST_EMPLOYEE_IX
JHIST_DEPARTMENT_IX
19 rows selected.
To set a Connection Time out in SQLNET.ORA put this line in file and reload Listener.
sqlnet.expire_time = 10
To enable valid Node checking
tcp.validnode_checking=yes
tcp.invited_nodes=(ip1,ip2,ip3)
Thanks
A Rahim Khan
Lets start with creating a stored procedure that we will use to create a job.
CREATE PROCEDURE hr.test_proc1 AS
BEGIN
insert into test1 values(25,'baju');
END;
Use this following script to create a Scheduled Job.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'job_test1',
JOB_TYPE => 'STORED_PROCEDURE',
JOB_ACTION => 'hr.test_proc1',
START_DATE => '10-JUN-12 12.23.00 PM',
repeat_interval => 'FREQ=MINUTELY;INTERVAL=2');
END;
Oracle Learnings | A Rahim Khan 