Essential Oracle SQL Queries for DBAs: Tablespaces, Rollback Segments, and Performance Insights

This article provides a curated set of Oracle SQL queries that enable database administrators to inspect various aspects of an Oracle database, ranging from tablespace metrics to session activity and cache statistics.

1. Tablespace names and sizes

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2. Physical data file names and sizes

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3. Rollback segment names and sizes

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name;

4. Control file names

select name from v$controlfile;

5. Log file members

select member from v$logfile;

6. Tablespace usage statistics

select sum(bytes)/(1024*1024) as free_space, tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED", (C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

7. Database object summary

select owner, object_type, status, count(*) count#
from all_objects
group by owner, object_type, status;

8. Database version

Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';

9. Creation date and archive mode

Select Created, Log_Mode, Log_Mode From V$Database;

10. Long‑running SQL capture

column username format a12
column opname format a16
column progress format a8
select username, sid, opname,
round(sofar*100 / totalwork,0) || '%' as progress,
time_remaining, sql_text
from v$session_longops, v$sql
where time_remaining <> 0
and sql_address = address
and sql_hash_value = hash_value;

11. Table partition parameters

SELECT   partition_name, high_value, high_value_length, tablespace_name,
 pct_free, pct_used, ini_trans, max_trans, initial_extent,
 next_extent, min_extent, max_extent, pct_increase, FREELISTS,
 freelist_groups, LOGGING, BUFFER_POOL, num_rows, blocks,
 empty_blocks, avg_space, chain_cnt, avg_row_len, sample_size,
 last_analyzed
FROM dba_tab_partitions
ORDER BY partition_position;

12. Uncommitted transactions

select * from v$locked_object;

select * from v$transaction;

13. Processes using a specific object

select p.spid, s.sid, s.serial# serial_num, s.username user_name,
 a.type  object_type, s.osuser os_user_name, a.owner, a.object object_name,
 decode(sign(48 - command), 1, to_char(command), 'Action Code #'||to_char(command)) action,
 p.program oracle_process, s.terminal terminal, s.program program, s.status session_status
from v$session s, v$access a, v$process p
where s.paddr = p.addr and s.type = 'USER' and a.sid = s.sid and a.object='SUBSCRIBER_ATTR'
order by s.username, s.osuser;

14. Rollback segment details

select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents Extents,
 v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
 v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
 sys.dba_rollback_segs.status status
from v$rollstat, sys.dba_rollback_segs, v$rollname
where v$rollname.name(+) = sys.dba_rollback_segs.segment_name
and v$rollstat.usn(+) = v$rollname.usn
order by rownum;

15. Resource‑intensive sessions (top sessions)

select s.schemaname schema_name,
 decode(sign(48 - command),1,to_char(command),'Action Code #'||to_char(command)) action,
 s.status session_status, s.osuser os_user_name, s.sid, p.spid, s.serial# serial_num,
 nvl(s.username,'[Oracle process]') user_name, s.terminal terminal, s.program program,
 st.value criteria_value
from v$sesstat st, v$session s, v$process p
where st.sid = s.sid and st.statistic# = to_number('38')
and ('ALL' = 'ALL' or s.status = 'ALL')
and p.addr = s.paddr
order by st.value desc, p.spid asc, s.username asc, s.osuser asc;

16. Lock information

select /*+ RULE */ ls.osuser os_user_name, ls.username user_name,
 decode(ls.type,'RW','Row wait enqueue lock','TM','DML enqueue lock','TX','Transaction enqueue lock','UL','User supplied lock') lock_type,
 o.object_name object,
 decode(ls.lmode,1,null,2,'Row Share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive',null) lock_mode,
 o.owner, ls.sid, ls.serial# serial_num, ls.id1, ls.id2
from sys.dba_objects o,
 (select s.osuser, s.username, l.type, l.lmode, s.sid, s.serial#, l.id1, l.id2
  from v$session s, v$lock l where s.sid = l.sid) ls
where o.object_id = ls.id1 and o.owner <> 'SYS'
order by o.owner, o.object_name;

17. Wait statistics

SELECT v$waitstat.class, v$waitstat.count count, SUM(v$sysstat.value) sum_value
FROM v$waitstat, v$sysstat
WHERE v$sysstat.name IN ('db block gets','consistent gets')
GROUP BY v$waitstat.class, v$waitstat.count;

18. SGA status

SELECT NAME, BYTES FROM SYS.V_$SGASTAT ORDER BY NAME ASC;

19. Cached object information

SELECT owner, name, db_link, namespace, type, sharable_mem, loads, executions,
 locks, pins, kept FROM v$db_object_cache;

20. V$SQLAREA details

SELECT SQL_TEXT, SHARABLE_MEM, PERSISTENT_MEM, RUNTIME_MEM, SORTS,
 VERSION_COUNT, LOADED_VERSIONS, OPEN_VERSIONS, USERS_OPENING, EXECUTIONS,
 USERS_EXECUTING, LOADS, FIRST_LOAD_TIME, INVALIDATIONS, PARSE_CALLS, DISK_READS,
 BUFFER_GETS, ROWS_PROCESSED FROM V$SQLAREA;

All promotional content about the DTCC conference has been omitted to keep the focus on the technical Oracle SQL reference.