Posts tagged sql

See which queries are executing – Oracle

2 years
by in oracle, sql

See which queries are currently executing in Oracle (11g).

select s.username, s.sid, s.osuser, sql_text 
from v$sqltext_with_newlines t,v$session s 
where t.address = s.sql_address 
and t.hash_value = s.sql_hash_value 
and s.status = 'ACTIVE' 
and s.username <> 'SYSTEM' 
order by s.sid, t.piece

Finding and cleaning Oracle locks

2 years
by in howto, oracle, sql

How to find blocking sessions in Oracle (11g). This is used to make sure that the object which we are trying to update is actually locked by another session:

select
 c.owner,
 c.object_name,
 c.object_type,
 b.sid,
 b.serial#,
 b.status,
 b.osuser,
 b.machine
from
 v$locked_object a ,
 v$session b,
 dba_objects c
where
 b.sid = a.session_id
and
 a.object_id = c.object_id;

And this query tells which are the problem sessions:

select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2;

So now we have the SID of the problem session and we only need the serial number to kill it.

SELECT serial# FROM v$session WHERE sid=<sid>

And now kill it:

ALTER SYSTEM KILL SESSION '<sid>,<session#>'

Check tablespace free space and total used – Oracle

2 years
by in howto, oracle, sql

Use the below SQL query to check the total, used and free space in all the tablespaces in the database

select 
   fs.tablespace_name                          "Tablespace", 
   (df.totalspace - fs.freespace)              "Used MB", 
   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;

If you are running this in sqlplus you might want to format the columns first

column "Tablespace" format a13
column "Used MB" format 99,999,999
column "Free MB" format 99,999,999
column "Total MB" format 99,999,999

 

Go to Top