One of the best sites for scripts related to checking library cache locks... http://allappsdba.blogspot.qa/2012/04/to-check-library-cache-lock-contention.html
COLUMN object_name FORMAT A40 COLUMN number_of_blocks FORMAT 999,999,999,999 SELECT o.object_name, COUNT(*) number_of_blocks FROM DBA_OBJECTS o, V$BH bh WHERE o.data_object_id = bh.OBJD AND o.owner != 'SYS' GROUP BY o.object_Name ORDER BY COUNT(*);
prompt ************************************ prompt **** ASH OVERALL WAIT PROFILE prompt ************************************ set lines 999 SELECT MIN(sample_time) min_ash_available,sysdate-MIN(sample_time) available duration FROM v$active_session_history; select * from ( select NVL(event,'CPU') event,count(*), round((ratio_to_report(sum(1)) over ()*100),1) rr from gv$active_session_history WHERE user_id0 AND sample_timetrunc(sysdate-1) group by event order by 2 desc ) where rownum<10;
Today on one of our 10g (10.2.0.1, don't ask !) production databases running on AIX 5.3 we saw the following message in the alert log. ksvcreate: Process(m000) creation failed The database slowed down to a crawl. We couldn't login even SQLPLUS, even sqlplus -prelim couldn't bring up the instance. On starting the instance we got … Continue reading ksvcreate: Process(m000) creation failed. Error in Alert log due to DNS ip change.
For running a single SQL Command via sqlplus on multiple servers, we have to first do a few pre-requisites 1. Oracle client should be installed on the Unix/Linux Server 2. Create Local TNS entry in the tnsnames.ora file for the databases where you will run the commands 3. All databases should have one common user … Continue reading Running SQL Script for Multiple Databases SQL*PLUS | Mutiple Servers SQL*PLUS in Shell Script | BASH Script Looping SQL*PLUS commands
Even when sessions are inactive they can be executing SQL's in the background. You have to identify the SQL's which are executing. Run this query as sysdba user. If you are using RAC then replace v$active_session_history with gv$active_session_history select ash.SQL_ID , sum(decode(ash.session_state,'ON CPU',1,0)) "CPU", sum(decode(ash.session_state,'WAITING',1,0)) - sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0)) "WAIT" , sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User … Continue reading Active Session History 11g
Sometimes on a system it is important to know the maximum user processes does not increase so much that it consumes all shared memory. Recently we had an incident in our ORganization where a rogue application bug caused user processes to spike upto 20,000 and it consumed all memory and swap and the entire server … Continue reading Monitor User Processes and Send Email Shell Script Solaris10
Here are some scripts related to Tables/Indexes . Tabs w/ Questionable Inds TABLES WITH QUESTIONABLE INDEX(ES) NOTES: Owner - Owner of the table Table Name - Name of the table Column - Name of the column in question The above query shows all tables that have more than one index with the same leading column. … Continue reading Scripts related to Tables/Indexes Oracle
Many times it becomes necessary to append date stamp on your export dumps. In Unix systems, shell scripting is very liberal with variables you can define yourself eg: expdate=`date '+%d%m%Y'` dat=`date '+%m%d%y %H:%M:%S'` And then go onto define in your script as ./expdp system/system directory=export_dir dumpfile=exp_swx_$expdate.dmp logfile=exp_swx_$expdate.log schemas=swx But on Windows it can be done … Continue reading How to Add Date and Time to a Filename with Windows Command Line.