useful SQL queries to find Segments that can’t extend

The following SQL query, will provide the list of segments that can’t be extended:


Select s.owner segowner, s.segment_name segname,s.segment_type segtype,
s.tablespace_name segtablespace, s.next_extent segnextext, s.bytes
from dba_segments s
where (s.next_extent * 5) > (select nvl((select max(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name),0)  from dual)
order by segowner


Also, this query will list segments that are approaching max extents:


Select segment_name, segment_type
from dba_segments
where extents >= max_extents -5;


Real Time ADDM (Automatic Database Diagnostic Monitoring) in Oracle 12c

Automatic Database Diagnostic Monitoring (ADDM) is one of the ways to use if you are facing performance problems with your database to gather information. Moreover, in previous releases the ADDM is generated based on 2 AWR (Automatic Workload Repository) Report snapshots which is taken on hourly basis and retained for 8 days by default. In oracle 12c, a new enhanced feature is introduced which is Real Time-ADDM. The real time ADDM is based on information fetched from memory triggered by manageability monitor process (MMON) especially when the database is in “hanging” state.

Database statistics gathering requires that STATISTICS_LEVEL parameter is set to TYPICAL or ALL. Also, check CONTROL_MANAGEMENT_PACK_ACCESS parameter.

ADDM DB Parameters

Let us assume a scenario that your database is in “hanging” state:

sqlplus ‘/ as sysdba’  (if you can’t access then use: sqlplus -prelim “/as sysdba”)


Now you have successfully generated a Real-Time ADDM, check it and see what is causing the problem (the file will be in HTML format).

Another important report you can generated is “Performance Hub Active Report” which is a comprehensive HTML report that will show different tab information such as:



ADDM findings

Resources (CPU and Memory)


This can be generated using the following query:


perf hub