How to Kill a session in 10g OEM

To kill a session in OEM level,

Login to the OEM > Go to the Targets> Select the database > Click on Performance > select Search Sessions (in Additional Monitoring Links) > In the Search filter give the Sid if you know otherwise press the Go button it will list out all the sessions then select the required sid > Click on kill session button > it will ask for the Confirmation, Select the Kill Immediate button then Press Yes >  Then you will get
ORA-00031: Session marked for kill error.
Then the session is no more exist in the database.


Happy Learning :)

Check Temp tablespace usage in oracle 8i

For checking the temp used, allocation and free space, use the below query.

SQL> select ddf.tablespace_name, ddf.allocatedspace "AllocatedSpace(in GB)",
ds.usedspace "UsedSpace(in GB)",dfs.freespace "FreeSpace(in GB)",
round(((dfs.freespace/ddf.allocatedspace)*100),2) "FreeSpace%",round(((ds.usedspace/ddf.allocatedspace)*100),2) "UsedSpace%"
from
(select tablespace_name,round((sum(bytes)/1024/1024/1024),2) allocatedspace from dba_data_files group by tablespace_name)ddf,
(select tablespace_name,round((sum(bytes)/1024/1024/1024),2) usedspace from dba_segments group by tablespace_name)ds,
(select tablespace_name,round((sum(bytes)/1024/1024/1024),2) freespace from dba_free_space group by tablespace_name)dfs
where
ddf.tablespace_name=ds.tablespace_name and
ds.tablespace_name=dfs.tablespace_name and
ddf.tablespace_name='TEMP';


TABLESPACE_NAME                Allocated(in GB)  Used(in GB) Free(in GB)   Free%   Used%
------------------------------                   ---------------------    ---------------     ----------------   ----------   ----------
TEMP                                            1.95              .07                 1.88            96.41     3.59


* If you don't now the the temp tablespace name you can obtain it from dba_tablespaces.

To find out the which user using most temp space can be find by using the below query

SQL> SELECT s.username,s.sid,s.serial#,u.tablespace, u.contents, u.extents, u.blocks FROM v$session s, v$sort_usage u WHERE s.saddr=u.session_addr order by u.blocks desc;

USERNAME SID SERIAL# TABLESPACE CONTENTS EXTENTS BLOCKS 
------------------------------ ---------- ---------- ------------------------------- --------- ---------- ---------- 
SYS 156 36776 TEMP PERMANENT 61 7929 
BGAPP 176 61877 TEMP PERMANENT 1 129 
BGAPP 176 61877 TEMP PERMANENT 1 129

In the above result it clearly shows that most of the temp is used by the SYS user. So you have to find which sql statement consuming more temp by using the below query.

SQL> SELECT SQL_TEXT FROM V$SQLAREA WHERE (ADDRESS, HASH_VALUE) IN (SELECT SQL_ADDRESS, SQL_HASH_VALUE FROM V$SESSION WHERE SID= 156;

SQL_TEXT 

--------------------------------------------- 

analyze table OC.BKP_ODOMREADINGENTRY_2013010 

2 compute statistics

In this case the above sql statement consuming more temp space.