MMAN (Memory Manager) is a background process that manages the dynamic resizing of SGA memory areas as the workload increases or decreases.
This process was introduced in Oracle 10g.
Above the HWM: These blocks are unformatted and have never been used.
Below the HWM: These blocks are in one of the following states:
>Allocated, but currently unformatted and unused
>Formatted and contain data
>Formatted and empty because the data was deleted
If there are too many DMLs in a table, then there is a possibility of too many white spaces below the highwater mark.
So you can only shrink the datafile upto the highwater mark as there are some white spaces below the highwater mark.
So in such cases (where there was too many deletes), to claim more space on the datafile, 1st we have to reorg the table, reset the highwater mark and then again shrink the datafile.
This way we can claim more space at the disk level.
The HWM specifies the limit to which you can resize a datafile.
Script for MAX-Shrink:-
set verify off
column file_name format a50 word_wrapped
column smallest format 999,990 heading “Smallest|Size|Poss.”
column currsize format 999,990 heading “Current|Size”
column savings format 999,990 heading “Poss.|Savings”
break on report
compute sum of savings on report
column value new_val blksize
select value from v$parameter where name = ‘db_block_size’;
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
ceil( blocks*&&blksize/1024/1024) currsize,
ceil( blocks*&&blksize/1024/1024) –
ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm
group by file_id ) b
where a.file_id = b.file_id(+) order by savings desc
The listener registration process (LREG) registers information about the database instance and dispatcher processes with the Oracle Net listener (see “The Oracle Net Listener”). When an instance starts, LREG polls the listener to determine whether it is running. If the listener is running, then LREG passes it relevant parameters. If it is not running, then LREG periodically attempts to contact it.
Note: In releases before Oracle Database 12c, PMON performed the listener registration.
In version 11.2, if there are numerous services (100+) and numerous listeners, then there is a possibility that the PMON process might spend more time on service registration to listeners due to the sheer number of services and listeners. But, in version 12c, this possibility is eliminated as the LREG parameter performs service registration and PMON is freed from listener registration.
registers instance information with the listener.
is a critical background process for each database instance (if it is killed, Oracle goes down).
does everything PMON used to do and report: service_update, service_register, service_died in the listener.log.
LREG can be traced using the same methods as one would trace PMON:
Enabling an Oracle Net server side sqlnet tracing will invoke a trace for LREG on instance startup.
The old PMON trace command now traces LREG: alter system set events = ‘10257 trace name context forever, level 5’;
Listener registration information can also be dumped into the ora_lreg trace file: alter system set events = ‘immediate trace name listener_registration level 3’;
LREG can be traced dynamically.
WAIT #140293705208536: nam=’SQL*Net message from client’ ela= 104 driver id=1650815232 #bytes=1 p3=0 obj#=92275 tim=14190555758807
WAIT #140293705208536: nam=’SQL*Net message to client’ ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=92275 tim=14190555758828
WAIT #140293705208536: nam=‘direct path read’ ela= 1307 file number=6 first dba=289297 block cnt=15 obj#=92275 tim=14190555760193
WAIT #140293705208536: nam=’SQL*Net message from client’ ela= 208 driver id=1650815232 #bytes=1 p3=0 obj#=92275 tim=14190555760508
WAIT #140293705208536: nam=’SQL*Net message to client’ ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=92275 tim=14190555760558