SQL QUERY TO FIND USERS AND OBJECTS WHICH GENERATE MORE ARCHIVES ORACLE

SQL QUERY TO FIND USERS AND OBJECTS WHICH GENERATE MORE ARCHIVES ORACLE

set lines 200 pages 1000
col OBJECT_NAME for a30
col OWNER for a10
def arccol="do.OWNER,dhse.OBJ#,do.OBJECT_NAME,to_char(al.COMPLETION_TIME,'DD-MM-YY HH24:MI') ARCTIME,(sum(BLOCKS*BLOCK_SIZE)/1073741824) ARCSIZE_GB"
def arccol2="count(*) ARCOUNT,((sum(BLOCKS*BLOCK_SIZE)/1073741824)/(select value/1073741824 from v$parameter where name='db_recovery_file_dest_size')) ARCPCT"
def arcview="v$archived_log al"
def arcgrp="group by do.OWNER,dhse.OBJ#,do.OBJECT_NAME,to_char(al.COMPLETION_TIME,'DD-MM-YY HH24:MI')"
def arcord="order by ARCOUNT desc"
def snap="inner join dba_hist_snapshot dhss on to_char(al.COMPLETION_TIME,'DD-MM-YY HH24') = to_char(dhss.BEGIN_INTERVAL_TIME,'DD-MM-YY HH24')"
def seg="inner join dba_hist_seg_stat dhse on dhse.SNAP_ID = dhss.SNAP_ID"
def obj="inner join dba_objects do on do.OBJECT_ID = dhse.OBJ#"
SELECT &arccol,&arccol2 from
           &arcview
           &snap
           &seg
           &obj
           &arcgrp
           &arcord
/

OWNER            OBJ# OBJECT_NAME                    ARCTIME        ARCSIZE_GB    ARCOUNT     ARCPCT
---------- ---------- ------------------------------ -------------- ---------- ---------- ----------
SYS             11358 WRH$_CON_SYSMETRIC_SUMMARY     20-10-23 12:20  .06503582          2 .005230653
SYS             73576 WRH$_SYSSTAT                   20-10-23 12:20  .06503582          2 .005230653
SYS                37 I_OBJ2                         24-10-23 11:25 .197018623          2  .01584567
SYS                65 I_OBJ#_INTCOL#                 24-10-23 11:25 .197018623          2  .01584567
SYS                68 HIST_HEAD$                     24-10-23 11:25 .197018623          2  .01584567
SYS                18 OBJ$                           27-10-23 18:14 .161505699          2 .012989462

Leave a Reply

%d