Drop tempfiles from database
(1) alter tablespace temp add tempfile ‘/db7/oradata/WEBARCH/temp01.dbf’ size 1024M; (2) alter database tempfile ‘/db2/oradata/WEBARCH/temp_001.dbf’ drop including datafiles;
Author: Admin
sid_wise_cursor.sql find open cursor basis on username or SID
accept u_name prompt ‘Enter User Name (Enter NONE if want SID specific query): ‘ accept s_id prompt ‘Enter SID (Enter 0 for Userspecific Query): ‘ select a.sid, b.sql_text, a.sql_id, c.spid, b.user_name from v$session a, v$open_cursor b, v$process c where a.sql_id = b.sql_id and (a.username = upper(‘&u_name’) or a.sid = &s_id ) and a.paddr = c.addr…
Read More “sid_wise_cursor.sql find open cursor basis on username or SID” »
find_open_cur.sql Find open cursorts per session
set lines 132 set pages 300 column numopencursors format d8 column machine format a30 column osuser format a20 column username format a20 SELECT s.sid, v.value as numopencursors ,s.machine ,s.osuser,s.username FROM V$SESSTAT v, V$SESSION s WHERE v.statistic# = 3 and v.sid = s.sid ;
TRUNCATE Privs
To truncate a table or cluster, the table or cluster must be in your schema or you must have DROP ANY TABLE system privilege.
Single character replacement in Unix
uocdbprod21->WEBARCH@(/db2/oradata)ls -rlt /db?/oradata/WEBP18F /db6/oradata/WEBP18F: total 0 /db5/oradata/WEBP18F: total 0 /db3/oradata/WEBP18F: total 0 /db4/oradata/WEBP18F: total 0 /db2/oradata/WEBP18F: total 0 uocdbprod21->WEBARCH@(/db2/oradata)
Create type and Grant on it.
conn cs/XXX Create TYPE VARCHAR2_ARRAY AS VARRAY(5000) OF VARCHAR2(50); Grant execute on VARCHAR2_ARRAY to r_exec_cs;
Flowers Resize datafiles
In flowers we resize datafile using following command: alter database resize datafile ‘/path1/filename.dbf’ resize 500M; this will change that datafles ‘SIZE’ column and allocate that much space for that datafile. If you are having standby database pls make sure that enough space is available for that file expansion.
age_alert.ksh aging out alert.log
!/bin/ksh . /export/home/oracle/envs/oracle_env_PNETP save_date=`date +%Y%m%d%H%M` # Set ORATAB variable if [ -f /etc/oratab ] ; then ORATAB=/etc/oratab else if [ -f /var/opt/oracle/oratab ] ; then ORATAB=/var/opt/oracle/oratab else echo “” echo “`date` Cannot find oratab!” echo “” exit 2 fi fi export ORATAB # search the alert log for errors for all databases # for ORACLE_SID…
cur_sql.sql
spool cur_sql set lines 132 set pages 300 col USR format a14 col PAR heading “Parsing ID” format 9999999 col ELA heading “ELP time(Sec)” format 9999999.0 col HASH heading “Hash Value” col USR heading “Parsing User” col T heading “SQL Text having Elapsed time > 1 sec” break on HASH on USR on ELA skip…
avail.sh ( find filesystem spae usage)
df -k | grep -i “/EMCdb”|awk ‘{tot += $2; used += $3; avail += $4;} END {print “Total”,tot/1024/1024,”Used “,used/1024/1024,”Avail “avail/102 4/1024, “Avail % ” avail*100/tot, “Used % ” used*100/tot}’ df -k | grep -i “/testdb”|awk ‘{tot += $2; used += $3; avail += $4;} END {print “Total”,tot/1024/1024,”Used “,used/1024/1024,”Avail “avail/10 24/1024, “Avail % ” avail*100/tot, “Used…