Saturday, April 2, 2016

Oracle Trainings in support.oracle.com

Hello All,

Oracle is maintaining all the recorded Webcast recorded sessions in the form of Oracle Documents in http://support.oracle.com .


The document id is 740964.1 [Advisor Webcast Archived Recordings ]

You can get all available Archived Recordings Technology wise from this document.

Happy Learning.

~~~ Cheers

Finding Owner of Forms Process

Finding Owner of Forms Process

$ ps -ef | grep f60webmx  (on the forms server)

 applmgr 11213     1  0   Dec 10 ?        0:17 f60srvm em=myserv_9000_MYDB
port=9000 mode=socket exe=f60webmx
 applmgr  3276 11213  0 06:33:07 ?        0:15 f60webmx
webfile=5,9067,myserv_9000_MYDB
 applmgr 21199 11213  0 11:13:44 ?        0:47 f60webmx
webfile=5,8876,myserv_9000_MYDB

You can see that Process 11213 (f60srvm) started off processes 3276 and 21199 (among others).... Once you know the UNIX process of the Forms process, you can derive the Oracle Session ID by selecting from v$session where process = (this id). From there on, you can use the following:

select s.sid, user_name, description,
to_char(start_time,'DD-MON-YY HH24:MI:SS') start_time
from applsys.fnd_user u, applsys.fnd_logins l, v$session s, v$process p
where u.user_id = l.user_id
and s.paddr=p.addr  and s.process in ('21199','3276')
and process_spid = p.spid
and start_time > sysdate - 5
and terminal_id != 'Concurrent'

This assumes of course that you have enabled the System profile :
'Sign-On:Audit Level' to at least 'USER'

~~~ Cheers

Space Usage on Table spaces


set lines 132
set pages 100
clear break 
clear compute
ttitle center 'Space Usage on Tablespaces' skip 1 
center '*****************************************' skip 2
accept tablespace_name char prompt 'Enter Tablespace Name :'
spool check_free_space
select a.TABLESPACE_NAME,
       round(a.bytes_used/(1024*1024),2) TOTAL_SPACE_IN_MB,
       round(b.bytes_free/(1024*1024),2) FREE_SPACE_IN_MB,
--       round(b.smallest/(1024*1024),2) min_size_in_MB,
--       round(b.largest/(1024*1024),2) max_size_in_MB,
       round(((a.bytes_used-b.bytes_free)/a.bytes_used)*100,2) percent_used
  from
  ( select TABLESPACE_NAME, sum(bytes) bytes_used
    from dba_data_files
   group by TABLESPACE_NAME
  ) a,
  ( select TABLESPACE_NAME, sum(BYTES) bytes_free, min(BYTES) smallest, max(BYTES) largest
    from dba_free_space
   group by TABLESPACE_NAME
  ) b
 where a.TABLESPACE_NAME=b.TABLESPACE_NAME(+)
   and a.tablespace_name = decode('&tablespace_name',null,a.tablespace_name,'&tablespace_name')
 order by ((a.BYTES_used-b.BYTES_free)/a.BYTES_used) desc;
spool off


-- list all tablespaces with their associated files, the 
-- tablespace's allocated space, free space, and the 
-- next free extent:
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, 
dd.bytes/1024 TABLESPACE_KB, 
SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;


-- list datafiles, tablespace names, and size in MB:
col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;


-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann 
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
      ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
      FROM dba_data_files
      GROUP BY tablespace_name) df,
     (SELECT tablespace_name, SUM(bytes) FREE_SPACE,
       ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
       FROM dba_free_space
       GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;

Clearing the Sessions With Satus "Killed"

After clearing the session from Database with alter system kill session The status showing killed in v$session. And not getting relesed, Casuing performace issues,
select USERNAME,STATUS,count(1) from v$session 
where STATUS='KILLED' group by USERNAME,STATUS 
order by 1 desc;

USERNAME STATUS COUNT(1)
----------------- -------- ----------
ABC KILLED 158
XYZ KILLED 73
TEST KILLED 21
the above schema sessions status marked for killed,We were unable to locate the spid for the Killed Sessions,

sql>SELECT b.spid from v$session a,v$process 
b where a.paddr=b.addr and a.status ='KILLED';
no rows selected.
The following Query helped us in identifying and clearing the sessions with satsus "Killed"

select spid, program from v$process 
where program!= 'PSEUDO'
and addr not in (select paddr from v$session)
and addr not in (select paddr from v$bgprocess)
and addr not in (select paddr from v$shared_server); 

Note : How To Find The Process Identifier (pid, spid) After The Corresponding Session Is Killed? (Doc ID 387077.1)
After clearing the sessions from OS level , The systen performance restored back to normal, Thus we were able to aviod the Database bounce.
~~ Cheers