alter system kill session '2405,2664';
find / -name "*.system.log"
$Header bomstrc.odf 115.19.115102.3 2005/12/14 21:19:47 rfarook ship
$ find ./ -type l -exec ls -ltr {} \; | grep fndcpesr
If file A has control characters and then want to remove follow these steps.
dos2ux A > B
mv B A
A is now clean of control Characters
rm `ls -lrt | grep "Dec 26" | awk '{print $9}'` -- /eb_rda0_p/applcsf
find . -name "*.req" -mtime +80 -exec rm '{}' \; -- keep 80 days and remove all b4 that
select running_processes from fnd_concurrent_queues where concurrent_queue_name = 'WFMLRSVC';---check workflow mailer
ddw7$find . -mtime +10 -exec rm -rf {} \;
ddw7$find . -mtime +10 -exec ls -lrt {} \;
find /tmp -name "Acro*" -mtime +15 -exec rm –f {} \;
ls -l *.log|sort +4 -nr | more
vmstat 1 10
@usrsql_sid.sql
select l1.sid,v1.serial#, ' IS BLOCKING ',l2.sid from gv$lock l1,gv$lock l2,v$session v1 where v1.sid = l1.sid and l1.block =1 and l2.request > 0 and
l1.id1=l2.id1 and l1.id2=l2.id2;
SELECT s.sid, p.spid, 'kill -9 '||p.spid FROM v$session s, v$process p WHERE s.paddr=p.addr(+) AND s.sid=3749
alter tablespace IEXX add datafile '/eb_rdo0_p/oradata/pnoltpdata/iexx104.dbf' size 1000m;
tkprof pnoltp_ora_27707_CSISCHED_CR302445386.trc pnoltp_ora_27707_CSISCHED_CR302445386.txt sys=no sort='(prsela,exeela,fchela)' explain=apps/p0rSche
dba SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';'
> FROM v$session
> WHERE status LIKE 'INACTIVE' AND MODULE LIKE 'JDBC Thin Client'
> AND username NOT LIKE 'APPLSYSPUB' AND TRUNC(logon_time) <
> TRUNC(SYSDATE)ORDER BY logon_time;
>
>
>
>
> /******************** Blocking Session *******************************/
SELECT * FROM dba_locks WHERE blocking_others='Blocking'
>
SELECT * FROM v$locked_object WHERE object_id=2392
/******************* Holder(1)& Waiters(0) **************************/
>
SELECT DECODE(request,0,'Holder: ','Waiter: ')|| sid sess, id1, id2,lmode,request, TYPE FROM V$LOCK WHERE (id1, id2, TYPE) IN (SELECT id1, id2, TYPE FROM V$LOCK WHERE request>0) ORDER BY id1, request
>
>
> /******************* Latch Count *************************************/
SELECT event, COUNT(1) FROM v$session_wait WHERE event IN ('latch free','enqueue','library cache lock','library cache load lock','library cache pin') GROUP BY event HAVING COUNT(1) > 2 ORDER BY event
>
> /******************** Latching Session *******************************/
> SELECT event, w.sid, s.sql_hash_value, s.username, seq#, wait_time,
> seconds_in_wait,
> DECODE( p1text, NULL, NULL, p1text || ' = ' || p1 ) ||
> DECODE( p2text, NULL, NULL, ', ' || p2text || ' = ' || p2 ) ||
> DECODE( p3text, NULL, NULL, ', ' || p3text || ' = ' || p3 )
> parameters,
> DECODE( s.action, 'Concurrent Request', 'CM/' || s.MODULE, s.MODULE
> ) program_name
> FROM v$session s, v$session_wait w
> WHERE event NOT IN ( 'pmon timer','rdbms ipc message','smon timer','WMON
> goes to sleep', 'pipe get', 'pipe put',-- 'Null event',
> 'SQL*Net message from client','client message','slave
> wait','queue messages', 'wakeup time manager', 'i/o slave wait' )
> AND w.sid = s.sid -- and event in ('latch free','enqueue','library
> cache lock','library cache load lock','library cache pin')
> AND event = 'enqueue'
> ORDER BY event, p1, p2;
>
> /******************** Running Sessoin *******************************/
SELECT /*+ choose */ s.status "Status", si.sid, s.MODULE "Module",s.username "DB User", s.osuser "OS User", s.machine "Machine",s.TYPE "Type",s.client_info "Client Info", s.server "Server", s.terminal "Terminal", s.program "Program", p.program "O.S. Program", lockwait "Lock Wait", s.process "Process", p.pid, s.audsid,s.action FROM v$session s, v$process p, sys.V_$SESS_IO si WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid AND s.sid = 2801 AND (s.username IS NOT NULL) ORDER BY 1 ASC,6 DESC
/******************** Running Query *******************************/
SELECT * FROM v$sql WHERE hash_value=:hv
>
select * from v$session where sid=4037
>
> /******************** Killing UNIX Sessoin *******************************/
SELECT s.sid, p.spid, 'kill -9 '||p.spid FROM v$session s, v$process p WHERE s.paddr=p.addr(+) AND s.sid=2244
>
> /********* Killing Long Running FRONT END Sessoin (Executed in each 15
> min)*************/
> SELECT sid, MODULE, status, last_call_et "SECONDS RUNNING",'kill -9
> '||p.spid
> FROM v$session s, v$process p WHERE s.paddr = p.addr AND s.last_call_et >=
> 300
> AND s.status <> 'INACTIVE' AND MODULE IN
> ('CSCCCCRC','HTGWRK','XXHTCSCCCCRC')
> --'JDBC Thin Client','FNDSCSGN') -- include this also to kill all the front
> end user's session.
>
> /******************** Killing stray database sessions
> **********************/
> SELECT 'alter system kill session ''' || sid || ',' || serial# || ''';'
> FROM v$session
> WHERE status LIKE 'INACTIVE' AND MODULE LIKE 'JDBC Thin Client'
> AND username NOT LIKE 'APPLSYSPUB' AND TRUNC(logon_time) <
> TRUNC(SYSDATE)ORDER BY logon_time;
>
>
> /**************** Monitoring/Cleaning stray Oracle processes ************/
> -- OS processes created by Oracle and which does not has corresponding
> oracle sessions
> -- Kill process containing(LOCAL=NO) and have date older than the current
> date
SELECT 'ps -fp '||spid,'kill -9 '||spid FROM v$process WHERE NOT EXISTS
(SELECT 1 FROM v$session WHERE paddr = addr);
>
> /**************** Library Cache Lock/Pins ******************/
> SELECT /*+ ordered */ w1.sid waiting_session, p.spid, h1.sid
> holding_session, p1.spid,w.kgllktype lock_or_pin, w.kgllkhdl address,
> DECODE(h.kgllkmod, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
> 'Unknown') mode_held,
> DECODE(w.kgllkreq, 0, 'None', 1, 'Null', 2, 'Share', 3, 'Exclusive',
> 'Unknown') mode_requested
> FROM DBA_KGLLOCK w, DBA_KGLLOCK h, v$session w1, v$session h1, v$process
> p, v$process p1
> WHERE (((h.kgllkmod != 0) AND (h.kgllkmod != 1) AND ((h.kgllkreq = 0) OR
> (h.kgllkreq = 1)))
> AND (((w.kgllkmod = 0) OR (w.kgllkmod= 1)) AND ((w.kgllkreq != 0) AND
> (w.kgllkreq != 1))))
> AND w.kgllktype = h.kgllktype AND w.kgllkhdl = h.kgllkhdl
> AND w.kgllkuse = w1.saddr
> AND h.kgllkuse = h1.saddr AND p.addr = w1.paddr AND
> p1.addr = h1.paddr
>
> SELECT sid, MODULE, status, sql_hash_value FROM v$session WHERE sid=&sid;
>
SELECT DISTINCT sql_text FROM v$sql WHERE hash_value=&hv;
>
> /***************** Shared Pool free size ********************/
SELECT * FROM v$parameter WHERE name LIKE 'shared%'
>
> SELECT pool,name,bytes/1024/1024 "FREE MB" FROM v$sgastat WHERE pool LIKE
> '%shared pool%' AND name LIKE '%free%'
>
> /***************** User wise session check ********************/
SELECT username, status, COUNT(1) FROM v$session GROUP BY username, status ORDER BY 1
>
> /***************** Gather Stats Monitoring *******************/
> SELECT sid, opname, message, ROUND(((totalwork-sofar)/totalwork),4)*100 "%
> PENDING", time_remaining,
> start_time, username, sql_hash_value, serial# FROM v$session_longops WHERE
> time_remaining >0
>
> /***************** Top most executing queries ****************/
SELECT COUNT(*), SUM(executions) "TotExecs", MODULE,SUBSTR(sql_text,1,100)"SQL" FROM v$sqlarea WHERE executions < 5 GROUP BY SUBSTR(sql_text,1,100),MODULE HAVING COUNT(*) > 30 ORDER BY 1 DESC;
>
>
>
> SELECT * vrom v$process WHERE spid = :spid;
>
> SELECT * FROM V$session WHERE paddr = :addr;
>
>
>
> SELECT 'kill -9 ' ||p.spid
> FROM v$session s, v$process p, sys.V_$SESS_IO si
> WHERE s.paddr = p.addr(+) AND si.sid(+) = s.sid AND
> --s.username IN ('ORASSO_PUBLIC')
> s.username IN ('PORTAL_PUBLIC', 'ORASSO_PUBLIC','PORTAL')
> --('PORTAL_PUBLIC', 'ORASSO_PUBLIC')
> --s.username IN ('PORTAL_PUBLIC')
> --s.username IN ('PORTAL')
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> TO FIND OUT AUDIT++++++++++++++++++++++++++++++++++++++++++++++++
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
> SELECT
> DECODE(NVL(alt,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> alter on '||owner||'.'||object_name||';') alt_stmpt
> ,DECODE(NVL(aud,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> audit on '||owner||'.'||object_name||';') aud_stmt
> ,DECODE(NVL(com,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> comment on '||owner||'.'||object_name||';') com_stmt
> ,DECODE(NVL(del,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> delete on '||owner||'.'||object_name||';') del_stmt
> ,DECODE(NVL(gra,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> grant on '||owner||'.'||object_name||';') gra_stmt
> ,DECODE(NVL(ind,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> index on '||owner||'.'||object_name||';') ind_stmt
> ,DECODE(NVL(ins,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> insert on '||owner||'.'||object_name||';') ins_stmt
> ,DECODE(NVL(loc,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> lock on '||owner||'.'||object_name||';') loc_stmt
> ,DECODE(NVL(ren,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> rename on '||owner||'.'||object_name||';') ren_stmt
> ,DECODE(NVL(sel,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> select on '||owner||'.'||object_name||';') sel_stmt
> ,DECODE(NVL(upd,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> update on '||owner||'.'||object_name||';') upd_stmt
> ,DECODE(NVL(REF,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> reference on '||owner||'.'||object_name||';') ref_stmt
> ,DECODE(NVL(exe,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> execute on '||owner||'.'||object_name||';') exe_stmt
> ,DECODE(NVL(cre,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> create on '||owner||'.'||object_name||';') cre_stmt
> ,DECODE(NVL(rea,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> read on '||owner||'.'||object_name||';') rea_stmt
> ,DECODE(NVL(wri,'-/-'),'-/-',NULL,CHR(0)||CHR(47)||CHR(0),NULL,'noaudit
> write on '||owner||'.'||object_name||';') wri_stmt
> FROM DBA_OBJ_AUDIT_OPTS
> WHERE (ALT <> '-/-'
> OR AUD <> '-/-'
> OR COM <> '-/-'
> OR DEL <> '-/-'
> OR GRA <> '-/-'
> OR IND <> '-/-'
> OR INS <> '-/-'
> OR LOC <> '-/-'
> OR REN <> '-/-'
> OR SEL <> '-/-'
> OR UPD <> '-/-'
> OR REF <> '-/-'
> OR EXE <> '-/-'
> OR CRE <> '-/-'
> OR REA <> '-/-'
> OR WRI <> '-/-' )
> ORDER BY 3, owner, object_type, object_name
>
> USER growth month BY month -
> SELECT TO_CHAR(TRUNC(creation_date),'Mon-yy') "Month", COUNT(*) "No. Of
> users created"
> FROM fnd_user WHERE TRUNC(creation_date) >= '01-Jan-05' GROUP BY
> TO_CHAR(TRUNC(creation_date),'Mon-yy')
> ORDER BY TO_DATE(TO_CHAR(TRUNC(creation_date),'Mon-yy'),'Mon-yy');
>
> USER growth weekly-
> SELECT TO_CHAR(TRUNC(creation_date),'Mon-yy')
> "Month",TO_CHAR(TRUNC(creation_date),'W') "Week", COUNT(*) "No. Of users
> created"
> FROM fnd_user WHERE TRUNC(creation_date) >= '01-Jan-06'
> GROUP BY TO_CHAR(TRUNC(creation_date),'Mon-yy'),
> TO_CHAR(TRUNC(creation_date),'W')
> ORDER BY TO_DATE(TO_CHAR(TRUNC(creation_date),'Mon-yy'),'Mon-yy'),
> TO_CHAR(TRUNC(creation_date),'W');
>
>
> SPACE growth month BY month -
> SELECT b.name,TO_CHAR(a.creation_time,'Mon-yy')
> "Month",SUM(a.bytes/(1024*1024)) "Size of datafile added"
> FROM v$datafile a, v$tablespace b WHERE a.ts#=b.ts# AND
> TRUNC(a.creation_time) >= '01-Jan-05'
> GROUP BY b.name,TO_CHAR(a.creation_time,'Mon-yy') ORDER BY
> b.name,TO_DATE(TO_CHAR(a.creation_time,'Mon-yy'),'Mon-yy');
>
> DATABASE growth month wise -
> ----------------------------------------------------------------------------------------------------
>
> SELECT TO_CHAR(creation_time,'Month,YYYY') "Month",
> ROUND(SUM(bytes/1024/1024/1024),2) "Size in GB"
> FROM v$datafile
> WHERE TRUNC(creation_time) >= '01-jan-05'
> GROUP BY
> TO_CHAR(creation_time,'Month,YYYY'),TO_CHAR(creation_time,'YYYYMM')
> ORDER BY TO_CHAR(creation_time,'YYYYMM')
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++
> DATAFILE Addition Info. ex. you want to see the information of IEXD tablespace
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
SELECT FILE#, TO_CHAR(creation_time, 'DD/MON/YYYY HH24:MI'), ts#, status,bytes/(1024*1024) AS "Size MB", name FROM v$datafile WHERE TS# = (SELECT TS# FROM v$tablespace WHERE name ='IEXD') ORDER BY v$datafile.creation_time DESC ,FILE# DESC;
>
>
> /*****************************************************************/
>
>
> SELECT cr.REQUEST_ID,
> cr.PHASE_CODE,
> cr.STATUS_CODE,
> cr.NUMBER_OF_ARGUMENTS,
> cr.ARGUMENT_TEXT,
> cr.CONCURRENT_PROGRAM_ID,
> cp.CONCURRENT_PROGRAM_ID,
> cp.USER_CONCURRENT_PROGRAM_NAME,
> cr.REQUEST_DATE,
> cr.REQUESTED_START_DATE,
> cr.ACTUAL_START_DATE,
> cr.ACTUAL_COMPLETION_DATE
> FROM FND_CONCURRENT_PROGRAMS_VL cp,
> FND_CONCURRENT_REQUESTS cr
> WHERE cp.CONCURRENT_PROGRAM_ID = cr.CONCURRENT_PROGRAM_ID
> AND cp.USER_CONCURRENT_PROGRAM_NAME ='Purge Obsolete Workflow
> Runtime Data'
> ORDER BY requested_start_date DESC
>
> /******* Trace file name of concurrent running ********/
> SELECT 'Request id: '||request_id ,
> 'Trace id: '||oracle_Process_id,
> 'Trace Flag: '||req.enable_trace,
> 'Trace Name:
> '||dest.value||'/'||lower(dbnm.value)||'_ora_'||oracle_process_id||'.trc',
> 'Prog. Name: '||prog.user_concurrent_program_name,
> 'File Name: '||execname.execution_file_name|| execname.subroutine_name ,
> 'Status : '||decode(phase_code,'R','Running')
> ||'-'||decode(status_code,'R','Normal'),
> 'SID Serial: '||ses.sid||','|| ses.serial#,
> 'Module : '||ses.module
> from fnd_concurrent_requests req, v$session ses, v$process proc,
> v$parameter dest, v$parameter dbnm, fnd_concurrent_programs_vl prog,
> fnd_executables execname
> where req.request_id = :request_id
> and req.oracle_process_id=proc.spid(+)
> and proc.addr = ses.paddr(+)
> and dest.name='user_dump_dest'
> and dbnm.name='db_name'
> and req.concurrent_program_id = prog.concurrent_program_id
> and req.program_application_id = prog.application_id
> and prog.application_id = execname.application_id
> and prog.executable_id=execname.executable_id;
>
>
SELECT SUM(bytes/1024/1024) FROM dba_free_space WHERE tablespace_name='IEXD'
>
> SELECT cr.REQUEST_ID,
> cr.PHASE_CODE,
> cr.STATUS_CODE,
> cr.NUMBER_OF_ARGUMENTS,
> cr.ARGUMENT_TEXT,
> cr.CONCURRENT_PROGRAM_ID,
> cp.CONCURRENT_PROGRAM_ID,
> cp.USER_CONCURRENT_PROGRAM_NAME,
> cr.REQUEST_DATE,
> cr.REQUESTED_START_DATE,
> cr.ACTUAL_START_DATE,
> cr.ACTUAL_COMPLETION_DATE
> FROM FND_CONCURRENT_PROGRAMS_VL cp,
> FND_CONCURRENT_REQUESTS cr
> WHERE cp.CONCURRENT_PROGRAM_ID = cr.CONCURRENT_PROGRAM_ID
> AND cp.USER_CONCURRENT_PROGRAM_NAME ='HTPROMOUPLOADPRO'
> ORDER BY requested_start_date DESC
>
> select * from v$session where sid=190
>
> select * from v$sqltext where hash_value='1172720175' order by piece
>
>
>
> /------------------------------To check the tablespace free space---------/
>
> select fs.tablespace_name "Tablespace", fs.freespace "Free
> MB",df.totalspace "Total MB",
> round(100 * (fs.freespace / df.totalspace)) "Pct. Free"
> from (select tablespace_name,round(sum(bytes) / 1048576) TotalSpace
> from dba_data_files group by tablespace_name ) df,
> (select tablespace_name,round(sum(bytes) / 1048576) FreeSpace
> from dba_free_space group by tablespace_name) fs where df.tablespace_name =
> fs.tablespace_name order by 2 desc
>
> /----------------------------------------------------------------------------------------------------------------/
>
> /---------------------------------------Long running queries time left
> out----------------------/
>
> SELECT
> sid,serial#,USERNAME,opname,start_time,ROUND(((totalwork-sofar)/totalwork),4)*100
>
> "Pct Remaining", MESSAGE,time_remaining FROM v$session_longops WHERE
> time_remaining >0
>
> /----------------------------------------------------------------------------------------------------------------/
>
>
>
> /------------------------------------------To check the tablespace free
> space-------------------------------------/
>
>
> select tablespace_name,max(bytes/1024/1024) from dba_free_space group by
> tablespace_name order by 2 desc
>
>
> /----------------------------------------------------------------------------------------------------------------/
>
>
>
> /oracle/ncrmprddata/csx198.dbf
>
>
> alter tablespace IEXD add datafile '/eb_rdo0_p/oradata/pnoltpdata/iexd103.dbf' size 4g
> 2040m;
select * from v$fast_start_transactions;
SQL> alter tablespace IEXD add datafile '/eb_rdo0_p/oradata/pnoltpdata/iexd103.dbf' size 4082m;
Tablespace altered.
===================tablespace usage=======================
select * from (select s.sid,s.status, s.sql_hash_value sesshash,s.username, u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused , sum(u.extents) noexts, nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60) lastcallet
from v$sort_usage u, v$session s, v$parameter p
where u.session_addr = s.saddr
and p.name = 'db_block_size'
group by sid,status,sql_hash_value,sqlhash,s.username,tablespace,nvl(s.module,s.program),
floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60)
order by 6 desc,3)
where rownum < 11;
============================
select to_char(actual_start_date,'DD-MM-YY HH24:MI'),request_id from fnd_concurrent_requests where oracle_process_id=10583 order by 1;
===========printer=========
SQL> update fnd_profile_option_values set profile_option_value='' where profile_option_id in (select profile_option_id from fnd_profile_options where profile_option_name like 'PRINTER') and profile_option_value like 'ACP_GKJ_ADMIN';
1 row updated.
SQL> select fo.profile_option_id,fv.profile_option_value from fnd_profile_option_values fv,fnd_profile_options fo where fo.profile_option_name like 'PRINTER' and fv.profile_option_id=fo.profile_option_id and fv.profile_option_value like 'ACP_GKJ_A%';
===========================
SQL> update fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='T' where REQUEST_ID in (6297322,6297320);
2 row updated
SQL> update fnd_concurrent_requests set PHASE_CODE='C', STATUS_CODE='T' where REQUEST_ID='6297143';
1 row updated.
SQL> select sysdate from
dual@APS_DBLINK;
SYSDATE
---------------
18-NOV-09
SQL> select sysdate from
dual@D_DNOLTP;
SYSDATE
---------------
18-NOV-09
$ strings -a WSHUTCOB.pls| grep Header | grep WSHUTCOB --> to find the versions
