Oracle Database Tablespace Related Queries

Find data file details like Used & Free Size, Auto Extend, Allocated, and Maximum Size:

SELECT df.file_id,
       df.file_name,
       df.tablespace_name,
       ROUND(df.bytes/1024/1024/1024, 2) AS allocated_gb,
       ROUND(NVL(fs.free_bytes, 0)/1024/1024/1024, 2) AS free_gb,
       ROUND((df.bytes - NVL(fs.free_bytes, 0))/1024/1024/1024, 2) AS used_gb,
       ROUND((df.bytes - NVL(fs.free_bytes, 0)) / df.bytes * 100, 2) AS pct_used,
       df.autoextensible,
       ROUND(df.maxbytes/1024/1024/1024, 2) AS max_size_gb
FROM   dba_data_files df
       LEFT JOIN (
         SELECT file_id, SUM(bytes) AS free_bytes
         FROM   dba_free_space
         GROUP  BY file_id
       ) fs ON df.file_id = fs.file_id
WHERE  df.tablespace_name = 'TABLESPACE_NAME'
ORDER  BY allocated_gb DESC;

Free Space in Auto Extend Tablespace:

select
a.tablespace_name,
SUM(a.bytes)/1024/1024 "CurMb",
SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) "MaxMb",
(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024)) "TotalUsed",
(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)) - (SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))) "TotalFree",
round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) "UPercent"
from
dba_data_files a,
sys.filext$ b,
(SELECT d.tablespace_name , sum(nvl(c.bytes,0)) "Free" FROM dba_tablespaces d,DBA_FREE_SPACE c where d.tablespace_name = c.tablespace_name(+) group by d.tablespace_name) c
where a.file_id = b.file#(+)
and a.tablespace_name = c.tablespace_name
GROUP by a.tablespace_name, c."Free"/1024
order by round(100*(SUM(a.bytes)/1024/1024 - round(c."Free"/1024/1024))/(SUM(decode(b.maxextend, null, A.BYTES/1024/1024, b.maxextend*8192/1024/1024)))) desc;

Increase Datafile Size with Auto Extend:

alter database datafile '+XXXX/DATAFILE/datafile01.1100.11003438874' AUTOEXTEND ON NEXT 104857600 MAXSIZE 20480M;

Add New Datafile:

alter tablespace TABLESPACE_NAME add datafile '+XXXX' size 1G AUTOEXTEND ON NEXT 1G MAXSIZE 10240M;

Diskgroup Space Details:

SELECT name,Sector_size,Block_size,Allocation_unit_Size/1024,state,type, total_mb/1024 as TOTAL_GB, free_mb/1024 as FREE_GB, free_mb/total_mb*100 as Free_percentage FROM v$asm_diskgroup;

EBS Performance Issue Related Queries

Find Top SQL:

select sql_id, module, count(*) samples
from   gv$active_session_history
where  sample_time > sysdate - 30/1440
group  by sql_id, module
order  by 3 desc
fetch first 10 rows only;

Find Session details from SQL_ID:

select * from gv$session where sql_id='bsd2bz342w5mu';

Find program/concurrent details from above query:

select r.request_id, u.user_name, r.actual_start_date,
       round((sysdate - r.actual_start_date)*24*60) mins_running,
       r.argument_text
from   fnd_concurrent_requests r, fnd_concurrent_programs p, fnd_user u
where  p.concurrent_program_id = r.concurrent_program_id
and    p.application_id = r.program_application_id
and    u.user_id = r.requested_by
and    p.concurrent_program_name = 'POXYZ'
and    r.phase_code = 'R'
order  by r.actual_start_date;

Blockers:

select inst_id, sid, serial#, blocking_instance, blocking_session,
       event, seconds_in_wait, sql_id, module
from   gv$session
where  blocking_session is not null;

What active sessions are waiting on:

select inst_id, wait_class, event, count(*)
from   gv$session
where  status = 'ACTIVE' and type = 'USER' and wait_class <> 'Idle'
group  by inst_id, wait_class, event
order  by 4 desc;