Useful queries for DBA's !!!!

======================
query to check temp usage
======================

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;


**********************************************
CHECKING FREE SPACE IN DATA FILES
***********************************************


SELECT
   a.tablespace_name,
   a.file_name,
   (a.bytes/1024/1024) allocated_bytes,
   b.free_bytes
FROM
   dba_data_files a,
   (SELECT file_id, (SUM(bytes)/1024/1024) free_bytes
    FROM dba_free_space b GROUP BY file_id) b
WHERE
   a.file_id=b.file_id
ORDER BY
   a.tablespace_name;


-----------------------------
Number of Sessions::
-------------------------

select SESSIONS_MAX,
SESSIONS_WARNING,
SESSIONS_CURRENT,
SESSIONS_HIGHWATER,
USERS_MAX
from v$license;




-----------------------------
Number of Connected users:
-----------------------------

select count(distinct d.user_name) from apps.fnd_logins a,
v$session b, v$process c, apps.fnd_user d
where b.paddr = c.addr
and a.pid=c.pid
and a.spid = b.process
and d.user_id = a.user_id
and (d.user_name = 'USER_NAME' OR 1=1);




************************************************
tableevel locks/locks specific to particular table
**********************************************************

SET ECHO off
set linesize 200 feedback off heading on
column sid format 9999
column res heading 'Resource Type' format a20
column id1 format 9999999
column id2 format 9999999
column lmode heading 'Lock Held' format a14
column request heading 'Lock Req.' format a14
column serial# format 99999
column username  format a10
column terminal heading Term format a6
column tab format a30
column owner format a8
select  l.sid,s.serial#,s.username,s.terminal,
        decode(l.type,'RW','RW - Row Wait Enqueue',
                    'TM','TM - DML Enqueue',
                    'TX','TX - Trans Enqueue',
                    'UL','UL - User',l.type||'System') res,
        substr(t.name,1,30) tab,u.name owner,
        l.id1,l.id2,
        decode(l.lmode,1,'No Lock',
                2,'Row Share',
                3,'Row Exclusive',
                4,'Share',
                5,'Shr Row Excl',
                6,'Exclusive',null) lmode,
        decode(l.request,1,'No Lock',
                2,'Row Share',
                3,'Row Excl',
                4,'Share',
                5,'Shr Row Excl',
                6,'Exclusive',null) request
from v$lock l, v$session s,
sys.user$ u,sys.obj$ t
where l.sid = s.sid
and s.type != 'BACKGROUND'
and t.obj# = l.id1
and u.user# = t.owner#
and t.name like '%PO%'
/
set feedback on




==================================
Need all tables details on which the user have access
==================================

set pages 0
 set linesize 200
 col GRANTOR for a20
 col GRANTEE for a20
 spool GSDW_NRPS_SCH.txt
 select * from dba_tab_privs where GRANTEE ='GSDW_NRPS_SCH';



==================================================
 To get lock details with holders and waiters
=========================================

SELECT lpad('-->',DECODE(a.request,0,0,5),' ')||a.sid sess
, a.id1
, a.id2
, a.lmode
, a.request req, a.type
, b.event
, b.seconds_in_wait
FROM V$LOCK a, v$session_wait b
WHERE a.id1 IN (SELECT id1 FROM V$LOCK WHERE lmode = 0)
and a.sid=b.sid
ORDER BY id1,request
/





=========================================
 To get why a session is waiting
=========================================

col event for a25 word_wrap;
select a.event event, a.seconds_in_wait, s.status
from v$session_wait a, v$session s
where a.sid=s.sid
and a.sid=&1
/



========================================================================
To get row information of a session, if the information is changing that means the session is actually active else it means inactive
========================================================================


column name format a30 word_wrapped
column vlu format 999,999,999,999
select b.name, a.value vlu
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and sid =&1
and a.value != 0
and b.name like '%row%'
/


===============
To check locks
===============
select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' )  is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;



================
What's in undo
================

 select tablespace_name,status,count(*) as HOW_MANY from dba_undo_extents group by tablespace_name,status;



=====================================================
Show segments that are approaching max_extents
=====================================================

col segment_name format a40
select owner,segment_type,segment_name,max_extents - extents as "spare",max_extents from dba_segments where owner not in ('SYS','SYSTEM')
and (max_extents - extents) < 10
order by 4
/

To change maxextents
alter <segment_type> <segment_name> storage(maxextents 150);



=====================================
List all objects in a tablespace
=====================================

set pages 999
col owner format a15
col segment_name format a40
col segment_type format a20
select owner,segment_name,segment_type from dba_segments where lower(tablespace_name) like lower('%&tablespace%')
order by owner, segment_name
/

===========================================
Show the files that comprise a tablespace
==================================

set lines 100
col file_name format a70
select file_name,ceil(bytes / 1024 / 1024) "size MB" from dba_data_files where tablespace_name like '&TSNAME'
/

===============================
User quotas on all tablespaces
================================

col quota format a10
select username,tablespace_name,decode(max_bytes, -1, 'unlimited',ceil(max_bytes/ 1024/1024)||'M') "QUOTA" from dba_ts_quotas
where tablespace_name not in('TEMP')
/


=====================================
Show all tablespaces used by a user
=======================================

select tablespace_name,ceil(sum(bytes)/1024/ 1024) "MB" from dba_extents where owner like '&user_id' group by tablespace_name
order by tablespace_name
/
==================================
Display the rollback segments
=========================================

 select segment_name,status from dba_rollback_segs
/

========================
list open cursors
=========================

set lines 100 pages 999
select count(hash_value) cursors,sid,user_name from v$open_cursorgroup by sid,user_nameorder by cursors
/



==================================
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
/



========================================
Show user info including os pid        
=========================================

col "SID/SERIAL" format a10
col username format a15
col osuser format a15
col program format a40
select s.sid ||','|| s.serial# "SID/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)
/

=========================================
Sessions sorted by logon time
==============================================

set lines 100 pages 999
col ID format a15
col osuser format a15
col login_time format a14
select username,osuser,sid ||','|| serial# "ID",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
/


===========================================
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
/
=============
Find a role
==================
 select * from dba_roles where role like '&role'
/
============================
Display pool usage
===========================
 select name,sum(bytes) from v$sgastat where poo like 'shared pool' group by name
/
==================================
PGA usage by username
===================================

 select st.sid "SID",sn.name "TYPE",ceil(st.value / 1024 / 1024) "MB" from v$sesstat st,v$statname sn
where st.statistic#=sn.statistic#
and sid in(select sid from v$session where username like '&user') and upper(sn.name) like '%PGA%' order by st.sid,st.value desc
/




============================================
To find out the Temp Tablespace Usage:
================================================
set lines 152
col FreeSpaceGB format 999.999
col UsedSpaceGB format 999.999
col TotalSpaceGB format 999.999
col host_name format a30
col tablespace_name format a30
select tablespace_name,
(free_blocks*8)/1024/1024 FreeSpaceGB,
(used_blocks*8)/1024/1024 UsedSpaceGB, (total_blocks*8)/1024/1024 TotalSpaceGB,
i.instance_name,i.host_name
from gv$sort_segment ss,gv$instance i where ss.tablespace_name in (select tablespace_name from dba_tablespaces where contents='TEMPORARY') and
i.inst_id=ss.inst_id;