Tuesday, March 4, 2014

TEMP, Session and UNDO Tablespace Administration


TEMP Tablespace :

SELECT tablespace_name, SUM(bytes_used), SUM(bytes_free)
FROM   V$temp_space_header
GROUP  BY tablespace_name;


Free space in a temporary tablespace.

select * from (select a.tablespace_name,
sum(a.bytes/1024/1024) allocated_mb
from dba_temp_files a
where a.tablespace_name = upper('&&temp_tsname') group by a.tablespace_name) x,
(select sum(b.bytes_used/1024/1024) used_mb,
sum(b.bytes_free/1024/1024) free_mb
from v$temp_space_header b
where b.tablespace_name=upper('&&temp_tsname') group by b.tablespace_name);


find out which SQL statement is using up space in a sort segment.

select s.sid || ',' || s.serial# sid_serial, s.username,
o.blocks * t.block_size / 1024 / 1024 mb_used, o.tablespace,
o.sqladdr address, h.hash_value, h.sql_text
from v$sort_usage o, v$session s, v$sqlarea h, dba_tablespaces t
where o.session_addr = s.saddr
and o.sqladdr = h.address (+)
and o.tablespace = t.tablespace_name
order by s.sid;



select file_name,sum(bytes)/1024/1024 from dba_temp_files where tablespace_name='TEMP' group by file_name;

select file_name, sum(bytes/1024/1024/1024),tablespace_name from dba_temp_files where tablespace_name='TEMP'group by file_name, tablespace_name ; 

UNDO tablespace

set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
       to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
       unxpstealcnt,
       expstealcnt,
       activeblks,
       unexpiredblks,
       expiredblks,
       tuned_undoretention
from v$undostat
order by end_time;

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

set lines 150
select to_char(begin_time,'hh24:mi dd-mon-yyyy') "START",
       to_char(end_time,'hh24:mi dd-mon-yyyy') "END",
       undoblks,
       expblkreucnt,
       ssolderrcnt,
       nospaceerrcnt,
       activeblks,
       unexpiredblks,
       expiredblks,
       tuned_undoretention
from v$undostat
order by end_time;

 

Show all connected users

set lines 100 pages 999
col ID format a15
select username , sid , serial# , status, last_call_et "Last Activity" from v$session
where username is not null
order by status desc, last_call_et desc 


2. Time since last user activity

set lines 100 pages 999
select username, floor(last_call_et / 60) "Minutes", status from v$session where username is not null order by last_call_et


3. Sessions sorted by logon time


set lines 100 pages 999
set linesize 200
col ID format a15col osuser format a15
col login_time format a14
select username, osuser, sid , serial# , status, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time, last_call_et from v$session where username is not null order by login_time



4. Show user info including os pid


col "SID/SERIAL" format a10
col username format a15col
osuser format a15col
program format a40
select s.sid , s.serial# , s.username, s.osuser, p.spid "OS PID", s.program from v$session s, v$process p Where s.paddr = p.addr order by to_number(p.spid)



5. Show a users current sql

Select sql_text from v$sqlarea where (address, hash_value) in(select sql_address, sql_hash_value from v$session where username like '&username')


6. Session status associated with the specified os process id 


select s.username, s.sid, s.serial#, p.spid, last_call_et, status from V$SESSION s, V$PROCESS p where s.PADDR = p.ADDR and p.spid='&pid'

8. Display any long operations

set lines 100 pages 999
col username format a15
col message format a40
col remaining format 9999
select username, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops
where time_remaining = 0 order by time_remaining desc


9. List open cursors per user

set pages 999
select sess.username, sess.sid, sess.serial#, stat.value cursors from v$sesstat stat, v$statname sn, v$session sess where sess.username is not null
and sess.sid = stat.sid and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current' order by value

Number of connection for the particular user from host

column username format a20
column machine  format a30
column session_count format 9,999
set lines 100
set pages 100
select username, machine, count(*) session_count
from v$session
where username = 'username'
and upper(machine) like 'hostname%'
group by username, machine
order by username, machine;


No comments:

Post a Comment