Oracle DBA to find locks on a table and space used

select    'SID ' || l1.sid ||' is blocking  ' || l2.sid blocking
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;

select username,sid,serial#,program,status from v$session --where sid=101;

to check the tableSpace:
1:
select  tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
, decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
, decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% used"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
 from  dba_data_files group by tablespace_name union all
 select  tablespace_name || '  **TEMP**'
 , sum(bytes)/1024/1024 used_mb
 from  dba_temp_files group by tablespace_name) tsu
, (select tablespace_name, sum(bytes)/1024/1024 free_mb
 from  dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
order by 4

2:
select
a.TABLESPACE_NAME,a.MB Total_MB,b.MB Free_MB,round(((a.MB-b.MB)/a.MB)*100,2) percent_used
from
(select TABLESPACE_NAME,sum(BYTES)/1048576 MB from dba_data_files group by TABLESPACE_NAME) a,
(select TABLESPACE_NAME,sum(BYTES)/1048576 MB from dba_free_space group by TABLESPACE_NAME) b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME order by ((a.MB-b.MB)/a.MB) desc;

More ways to find locks on DB 10g


SQL> select session_id, oracle_username, os_user_name,locked_mode,object_name,object_type from v$locked_object a,dba_objects b
where a.object_id=b.object_id;

SQL> select s1.username || '@' || s1.machine
  2    || ' ( SID=' || s1.sid || ' )  is blocking '
  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
  3    4    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
  5    6    7    and l1.id1 = l2.id1
  and l2.id2 = l2.id2 ;