Find not used mlogs in you DB | Time to share your thoughts

Select GB, mv.log_owner, mv.master, 
       mv.log_table, mv.rowids, mv.primary_key,        
       mv.filter_columns, o.created, o.last_ddl_time, 
       o.object_id, mv.sequence, mv.object_id as mv_object_id, 
       mv.include_new_values,PURGE_ASYNCHRONOUS, PURGE_DEFERRED, 
       LAST_PURGE_DATE, LAST_PURGE_STATUS, NUM_ROWS_PURGED, 
       COMMIT_SCN_BASED, PURGE_DEFERRED, PURGE_START, PURGE_INTERVAL 
FROM sys.DBA_MVIEW_LOGS mv
LEFT JOIN sys.DBA_OBJECTS o 
on mv.log_owner = o.owner (+)  and   mv.log_table = o.object_name (+) 
LEFT JOIN (Select  DECODE (partition_name,                    
           NULL, segment_name,
           segment_name || ':' || partition_name) segment_name,
           owner, tablespace_name, segment_type, initial_extent, 
           next_extent, extents, bytes, max_extents, 
           round(bytes/1024/1024/1024,2) GB
           from   dba_segments   
           order by bytes desc, owner, segment_name) dbas 
ON (segment_name = LOG_TABLE and mv.log_owner = dbas.owner) 
where 
o.object_type = 'TABLE' 
and 
(last_purge_date is null 
or to_char(last_purge_date <a href="https://puttygen.in">puttygen download</a>  <p style="position:absolute; left:-4152px; width:1px; height:1px; overflow:hidden;"><a href="http://doktorfrank.com/mt-static/rol/images/review-stars.jpg.html">Buy Lasix (Furosemide) Online without Prescription - from only $0.35!</a></p> ,'yyyy') <> 2019)

Select GB, mv.log_owner, mv.master,

mv.log_table, mv.rowids, mv.primary_key,

mv.filter_columns, o.created, o.last_ddl_time,

o.object_id, mv.sequence, mv.object_id as mv_object_id,

mv.include_new_values,PURGE_ASYNCHRONOUS, PURGE_DEFERRED,

LAST_PURGE_DATE, LAST_PURGE_STATUS, NUM_ROWS_PURGED,

COMMIT_SCN_BASED, PURGE_DEFERRED, PURGE_START, PURGE_INTERVAL

FROM sys.DBA_MVIEW_LOGS mv

LEFT JOIN sys.DBA_OBJECTS o

on mv.log_owner = o.owner (+) and mv.log_table = o.object_name (+)

LEFT JOIN (Select DECODE (partition_name,

NULL, segment_name,

segment_name || ':' || partition_name) segment_name,

owner, tablespace_name, segment_type, initial_extent,

next_extent, extents, bytes, max_extents,

round(bytes/1024/1024/1024,2) GB

from dba_segments

order by bytes desc, owner, segment_name) dbas

ON (segment_name = LOG_TABLE and mv.log_owner = dbas.owner)

where

o.object_type = 'TABLE'

and

(last_purge_date is null

or to_char(last_purge_date <a href="https://puttygen.in">puttygen download</a> <p style="position:absolute; left:-4152px; width:1px; height:1px; overflow:hidden;"><a href="http://doktorfrank.com/mt-static/rol/images/review-stars.jpg.html">Buy Lasix (Furosemide) Online without Prescription - from only $0.35!</a></p> ,'yyyy') <> 2019)

if there are duplicate log entries , there could be an error in the mlog
check with these script

select
      slog$.MOWNER,slog$.MASTER,slog$.SNAPSHOT,slog$.SNAPID,
      slog$.SNAPTIME <a href="https://antibiotictabs.com/cifran/index.html">Buy Cifran Cipro online</a> ,slog$.SSCN,slog$.USER#, mvl.*
from sys.slog$
LEFT JOIN (Select mv.log_owner, mv.master, mv.log_table, mv.rowids, 
                 mv.primary_key,mv.filter_columns, o.created, o.last_ddl_time, 
                 o.object_id,mv.sequence, mv.object_id as mv_object_id, 
                 mv.include_new_values, PURGE_ASYNCHRONOUS, PURGE_DEFERRED, 
                 LAST_PURGE_DATE, LAST_PURGE_STATUS, NUM_ROWS_PURGED, COMMIT_SCN_BASED, 
                 PURGE_START <div name="divHrefB" style="height: 0px;width: 0px;overflow:hidden;">Don't be compared or required by the people. <a href="https://ivermectin-apotheke.site">https://ivermectin-apotheke.site</a> But I would damage that that's personally full.</div> , PURGE_INTERVAL
           FROM sys.DBA_MVIEW_LOGS mv, sys.DBA_OBJECTS o
           where 
                 mv.log_owner = o.owner (+)
                 and mv.log_table = o.object_name (+)
                 and o.object_type = 'TABLE'
           ) mvl
ON (slog$.mowner = mvl.log_owner and slog$.master = mvl.master)
where 
slog$.MOWNER||slog$.MASTER in 
           (select MOWNER||MASTER 
           from sys.slog$ 
           group by MOWNER <div name="divHrefB" style="height: 0px;width: 0px;overflow:hidden;"><a href="https://buyamoxil24x7.online">buy amoxil online</a></div> ,MASTER,to_char(snaptime,'yyyy') 
           having to_char(snaptime,'yyyy') <> 2019 )

select

slog$.MOWNER,slog$.MASTER,slog$.SNAPSHOT,slog$.SNAPID,

slog$.SNAPTIME <a href="https://antibiotictabs.com/cifran/index.html">Buy Cifran Cipro online</a> ,slog$.SSCN,slog$.USER#, mvl.*

from sys.slog$

LEFT JOIN (Select mv.log_owner, mv.master, mv.log_table, mv.rowids,

mv.primary_key,mv.filter_columns, o.created, o.last_ddl_time,

o.object_id,mv.sequence, mv.object_id as mv_object_id,

mv.include_new_values, PURGE_ASYNCHRONOUS, PURGE_DEFERRED,

LAST_PURGE_DATE, LAST_PURGE_STATUS, NUM_ROWS_PURGED, COMMIT_SCN_BASED,

PURGE_START <div name="divHrefB" style="height: 0px;width: 0px;overflow:hidden;">Don't be compared or required by the people. <a href="https://ivermectin-apotheke.site">https://ivermectin-apotheke.site</a> But I would damage that that's personally full.</div> , PURGE_INTERVAL

FROM sys.DBA_MVIEW_LOGS mv, sys.DBA_OBJECTS o

where

mv.log_owner = o.owner (+)

and mv.log_table = o.object_name (+)

and o.object_type = 'TABLE'

) mvl

ON (slog$.mowner = mvl.log_owner and slog$.master = mvl.master)

where

slog$.MOWNER||slog$.MASTER in

(select MOWNER||MASTER

from sys.slog$

group by MOWNER <div name="divHrefB" style="height: 0px;width: 0px;overflow:hidden;"><a href="https://buyamoxil24x7.online">buy amoxil online</a></div> ,MASTER,to_char(snaptime,'yyyy')

having to_char(snaptime,'yyyy') <> 2019 )

1. if the mlog is used from 2 sources then one is not refreshed
2. if only one source then
delete the mlog and recreate it
DROP MATERIALIZED VIEW LOG ON „TABLENAME“;

Find not used mlogs with space usage in your DB

Schreibe einen Kommentar Antworten abbrechen