Oracle : Important Queries for Checking Temp Usage.

Checking Default_Temp_Tablespace :  col property_name for a35 col property_value for a20 col description for a45 set linesize 300 SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’;   Temp tablespace usage : set linesize 300 SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,… Read More

Oracle : How to Purge CURSORS related to a single SQL_ID from library cache

********************************************************************* How to Purge CURSORS related to a single SQL_ID from library cache ********************************************************************* SQL> select ADDRESS, HASH_VALUE from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; select ADDRESS, HASH_VALUE,plan_hash_value from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; SQL> exec DBMS_SHARED_POOL.PURGE (‘000000180DC67320,552744047′,’C’);  PL/SQL procedure successfully completed.  SQL> select ADDRESS, HASH_VALUE… Read More

Oracle : List all the hidden parameter and their values – &&&&&& – Simulate ORA error in Alert log

************************************************************List all the hidden parameter and their values************************************************************SET PAUSE ONSET PAUSE ‘Press Return to Continue’SET PAGESIZE 60SET LINESIZE 300 COLUMN ksppinm FORMAT A50COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvlFROM x$ksppi a, x$ksppsv bWHERE a.indx=b.indx AND substr(ksppinm,1,1) = ‘_’ORDER BY ksppinm;   *********************************Simulate ORA error in… Read More

Oracle : HOW TO TRACE SESSIONS

Oracle : HOW TO TRACE SESSIONS ==========================================================For TRACING your own session , use the following query to find details on your own session:==========================================================set linesize 132set pagesize 1000set echo oncol rbs format a6col oracle_user format a12 heading “Oracle|Username”col sid format 99999 heading “Oracle|SID”col serial# format 99999… Read More

Oracle : Script for Email alert for Blocking sessions in Database.

Oracle : Script for Email alert for Blocking sessions in Database. #####################view /u01/app/oracle/admin/orcl/scripts/DBA_Blocking_Sess.sh#!/bin/kshset -xexport ORACLE_HOME=/u01/app/oracle/product/11.2.0.2/db_1export ORACLE_SID=$1export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oracle/bin:/u01/app/oracle/product/11.2.0.2/db_1/bin export LOG_FILE=/u01/app/oracle/admin/orcl/scripts/log/orcl_Blocking_Sess_date +%F%s.log sqlplus -s / as sysdba <<EOF >> $LOG_FILE@/u01/app/oracle/admin/orcl/scripts/sql/orcl_DBA_Blocking_sess.sqlEOF chk_count=grep “it’s BLOCKING user” $LOG_FILE | wc -l if [ $chk_count -gt 0 ]; thenmailx -s “orcl… Read More

Executing job in Background for one sql

cat g_stats_1_test.shsqlplus / as sysdba <<EOFset head offconn / as sysdbaset serveroutput onset timing onset echo onspool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool offset timing offset serveroutput offexit; EOF chmod 777 g_stats_1_test.shnohup ./g_stats_1_test.sh &

Generate output in HTML

set linesize 200set pagesize 500 spool /tmp/Oct29.htmlset markup html onset numwidth 40 select * from emp; set long 100000select sql_id,sql_text from DBA_HIST_SQLTEXT where sql_id in (select sql_id from v$session); spool off