Posts tagged 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
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#>'
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