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
where extents >= max_extents -5;
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.
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:
Resources (CPU and Memory)
This can be generated using the following query: