blocking sessions and tabelspace

set pages 1000
set long 2000
    set colsep '  |  '
    set linesize 190
    col LOCKED_EVT for a15
    col SEC_WAIT for 99999999
    col LOCKED_USER for a60
    col LOCKER_USER for a60


    with t as (
        select
            a.event as LOCKED_EVT,
            a.wait_time_micro/1000000 as LOCKED_WAIT_SEC,
            a.osuser as locked_osuser,
            a.schemaname as LOCKED_SCHEMANAME,
            a.machine as locked_machine,
            a.sid as locked_sid,
            a.serial# as LOCKED_SERIAL,
            a.program as locked_program,
            a.Action as locked_Action,
            a.Module as locked_Module,
            prior a.sid as LOCKER_SID,
            prior a."SERIAL#" as LOCKER_SERIAL,
            prior a.osuser as locker_osuser,
            prior a.schemaname as LOCKER_SCHEMANAME,
            prior a.machine as locker_mashine,
            prior a.program as locker_program,
            prior a.Action as locker_Action,
            prior a.Module as locker_Module,
            prior a.seconds_in_wait as LOCKER_SECONDS_IN_WAIT,
            a.sql_id as locked_sql_id,
            a.prev_sql_id as locked_prev_sql_id,
            prior a.sql_id as locker_sql_id,
            prior a.prev_sql_id as locker_prev_sql_id,
            prior a.sql_address,
            prior a.event as LOCKER_EVT,
            a.final_blocking_session,
            level lvl,
            prior a.status
        from
            gv$session a
        where
            a.blocking_session is not null
        and
            level>1
        connect by
            nocycle prior a.sid= a.blocking_session
        start with
            a.blocking_session is null
    )
    select
        t.LOCKED_WAIT_SEC as SEC_WAIT,
        t.LOCKED_EVT,
        'Sid: ' || t.locked_sid || chr(10) ||
            'Serial: ' || t.LOCKED_SERIAL || chr(10) ||
            'OS user: ' || t.locked_osuser  || chr(10) ||
            'Schema: ' || t.LOCKED_SCHEMANAME || chr(10)||
            'Machine: ' || t.locked_machine || chr(10) ||
            'Program: ' || locked_program || chr(10) ||
            'ACTION: ' || locked_Action || chr(10) ||
            'Module: ' || locked_Module || chr(10) || chr(10) ||
            'Curr sql: ' || t.locked_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locked_sql_id and s.loaded_versions=1  and rownum=1),0,250) || chr(10) || chr(10) ||
            'Prev sql: ' || t.locked_prev_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locked_prev_sql_id and s.loaded_versions=1 and rownum=1),0,250) LOCKED_USER,
        'Sid: ' || t.LOCKER_SID || chr(10) ||
            'Serial: ' || t.LOCKER_SERIAL || chr(10) ||
            'OS user: ' || t.locker_osuser  || chr(10) ||
            'Schema: ' || t.LOCKER_SCHEMANAME || chr(10)||
            'Machine: ' || t.locker_mashine || chr(10) ||
            'Program: ' || locker_program || chr(10) ||
            'Action: ' || locker_Action || chr(10) ||
            'Module: ' || locker_Module || chr(10) || chr(10) ||
            'Curr sql: ' || t.locker_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locker_sql_id and s.loaded_versions=1 and rownum=1),0,250) || chr(10) || chr(10) ||
            'Prev sql: ' || t.locker_prev_sql_id || chr(10)||
            SUBSTR((select s.sql_text from v$sql s where s.sql_id=t.locker_prev_sql_id and s.loaded_versions=1 and rownum=1),0,250) LOCKER_USER
    from
        t
    -- where locked_evt like 'enq%'
    order by
        lvl desc,decode(substr(locked_evt,1,3),'enq',0,'log',2,1), LOCKED_WAIT_SEC desc
    /


Tablespace usage

set lines 160
col name for a30
col file_name for a60
set pages 1000
select (select decode(extent_management,'LOCAL','*',' ')
          from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
             nvl(a.tablespace_name,'UNKOWN')) name,
       Mega_alloc Mega,
       Mega_alloc-nvl(Mega_free,0) used,
       nvl(Mega_free,0) free,
       ((Mega_alloc-nvl(Mega_free,0))/
                          Mega_alloc)*100 pct_used,
       nvl(largest,0) largest,
       nvl(Mega_max,Mega_alloc) Max_Size,
       decode( Mega_max, 0, 0, (Mega_alloc/Mega_max)*100) pct_max_used
from ( select sum(bytes)/1024/1024 Mega_free,
              max(bytes)/1024/1024 largest,
              tablespace_name
       from  sys.dba_free_space
       group by tablespace_name ) a,
     ( select sum(bytes)/1024/1024 Mega_alloc,
              sum(maxbytes)/1024/1024 Mega_max,
              tablespace_name
       from sys.dba_data_files
       group by tablespace_name
       union all
      select sum(bytes)/1024/1024 Mega_alloc,
              sum(maxbytes)/1024/1024 Mega_max,
              tablespace_name
       from sys.dba_temp_files
       group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
and a.tablespace_name='&tablespace_name' order by 5;

select file_name,bytes/1024/1024/1024,maxbytes/1024/1024/1024,AUTOEXTENSIBLE,status,INCREMENT_BY from dba_data_files where tablespace_name = '&NAME' order by 2 desc;