Oracle object on System and sysaux tablespace more then 10Mb size

Oracle Table IDL_UB1$




col SEGMENT_NAME format a30
col owner format a20
select owner,segment_name,segment_type,bytes/(1024*1024) size_m,TABLESPACE_NAME from dba_segments
where tablespace_name = 'SYSTEM'
and bytes/(1024*1024) > 10 --- Mb
union all
select owner,segment_name,segment_type,bytes/(1024*1024) size_m,TABLESPACE_NAME from dba_segments
where tablespace_name = 'SYSAUX'
and bytes/(1024*1024) > 10 -- Mb
order by size_m desc


OWNER SEGMENT_NAME SEGMENT_TYPE SIZE_M TABLESPACE_NAME
-------------------- ------------------------------ ------------------ ---------- ---------------
SYS IDL_UB1$ TABLE 264 SYSTEM
SYS SOURCE$ TABLE 96 SYSTEM
XDB SYS_LOB0000056506C00025$$ LOBSEGMENT 57.125 SYSAUX
SYS I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 48 SYSAUX
SYS IDL_UB2$ TABLE 35 SYSTEM
SYS WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 29 SYSAUX
MDSYS SYS_LOB0000062569C00006$$ LOBSEGMENT 26 SYSAUX
SYS C_TOID_VERSION# CLUSTER 23 SYSTEM
SYS I_WRI$_OPTSTAT_H_ST INDEX 22 SYSAUX
SYS SYS_LOB0000064046C00004$$ LOBSEGMENT 20.125 SYSAUX
SYS C_OBJ#_INTCOL# CLUSTER 20 SYSTEM
SYS WRH$_SYSMETRIC_HISTORY TABLE 15 SYSAUX
SYS ARGUMENT$ TABLE 14 SYSTEM
SYS SYS_LOB0000006213C00038$$ LOBSEGMENT 14 SYSAUX
SYS I_SOURCE1 INDEX 13 SYSTEM
SYS C_OBJ# CLUSTER 12 SYSTEM
SYS IDL_CHAR$ TABLE 12 SYSTEM
SYS JAVA$MC$ TABLE 11 SYSTEM
SYS ORDERS_SHIPPED_IDX INDEX 11 SYSTEM
SYS WRH$_SQL_PLAN TABLE 11 SYSAUX
SYS WRH$_SYSMETRIC_HISTORY_INDEX INDEX 11 SYSAUX




Oracle Java bytecode IDL_UB1$

Java bytecode stored in IDL_UB1$ table:

select o.NAME, i.PIECE from obj$ o, IDL_UB1$ i
where o.type# = 29
and o.obj# = i.obj#

set lines 500 pages 10000

col TSname heading 'TSpace|Name|||'

col TSname format a25

col TSstatus heading 'TSpace|Status|||'

col TSstatus format a9

col TSSizeMb heading 'TSpace|Size|Mb||'

col TSSizeMb format 99999

col TSUsedMb heading 'TSpace|Used|Space|Mb|'

col TSUsedMb format 99999

col TSFreeMb heading 'TSpace|Free|Space|Mb|'

col TSFreeMb format 99999

col TSUsedPrct heading 'TSpace|Used|Space|%|'

col TSUsedPrct format 99999

col TSFreePrct heading 'TSpace|Free|Space|%|'

col TSFreePrct format 99999

col TSSegUsedMb heading 'TSpace|Segmt|Space|Mb|'

col TSSegUsedMb format 99999

col TSExtUsedMb heading 'TSpace|Extent|Space|Mb|'

col TSExtUsedMb format 99999

col AutoExtFile heading 'Auto|Extend|File|?|'

col AutoExtFile format a6

col TSMaxSizeMb heading 'TSpace|MaxSize|Mb||'

col TSMaxSizeMb format a6

col TSMaxUsedPrct heading 'TSpace|Maxed|Used|Space|%'

col TSMaxUsedPrct format a6

col TSMaxFreePrct heading 'TSpace|Maxed|Free|Space|%'

col TSMaxFreePrct format a6

WITH

  ts_total_space AS (SELECT

                       TableSpace_name,

                       SUM(bytes) as bytes,

                       SUM(blocks) as blocks,

                       SUM(maxbytes) as maxbytes

                     FROM dba_data_files

                     GROUP BY TableSpace_name),

  ts_free_space AS (SELECT

                      ddf.TableSpace_name,

                      NVL(SUM(dfs.bytes),0) as bytes,

                      NVL(SUM(dfs.blocks),0) as blocks

                    FROM

                      dba_data_files ddf,

                      dba_free_space dfs

                    WHERE ddf.file_id = dfs.file_id(+)

                    GROUP BY ddf.TableSpace_name),

  ts_total_segments AS (SELECT

                          TableSpace_name,

                          SUM(bytes) as bytes,

                          SUM(blocks) as blocks

                        FROM dba_segments

                        GROUP BY TableSpace_name),

ts_total_extents AS (SELECT

                       TableSpace_name,

                       SUM(bytes) as bytes,

                       SUM(blocks) as blocks

                     FROM dba_extents

                     GROUP BY TableSpace_name)

SELECT

  dt.TableSpace_name as "TSname",

  dt.status as "TSstatus",

  ROUND(ttsp.bytes/1024/1024,0) as "TSSizeMb",

  ROUND((ttsp.bytes-tfs.bytes)/1024/1024,0) as "TSUsedMb",

  ROUND(tfs.bytes/1024/1024,0) as "TSFreeMb",

  ROUND((ttsp.bytes-tfs.bytes)/ttsp.bytes*100,0) as "TSUsedPrct",

  ROUND(tfs.bytes/ttsp.bytes*100,0) as "TSFreePrct",

  ROUND(ttse.bytes/1024/1024,0) as "TSSegUsedMb",

  ROUND(tte.bytes/1024/1024,0) as "TSExtUsedMb",

  CASE

    WHEN ttsp.maxbytes = '0' then 'No' ELSE 'Yes'

  END as "AutoExtFile",

  CASE

    WHEN ttsp.maxbytes = '0' then '-' ELSE TO_CHAR(ROUND(ttsp.maxbytes/1024/1024,0))

  END as "TSMaxSizeMb",

  CASE

    WHEN ttsp.maxbytes = '0' then '-' ELSE TO_CHAR(ROUND((ttsp.bytes-tfs.bytes)/ttsp.maxbytes*100,0))

  END as "TSMaxUsedPrct",

  CASE

    WHEN ttsp.maxbytes = '0' then '-' ELSE TO_CHAR(ROUND((ttsp.maxbytes-(ttsp.bytes-tfs.bytes))/ttsp.maxbytes*100,0))

  END as "TSMaxFreePrct"

FROM

  dba_TableSpaces dt,

  ts_total_space ttsp,

  ts_free_space tfs,

  ts_total_segments ttse,

  ts_total_extents tte

WHERE dt.TableSpace_name = ttsp.TableSpace_name(+)

AND dt.TableSpace_name = tfs.TableSpace_name(+)

AND dt.TableSpace_name = ttse.TableSpace_name(+)

AND dt.TableSpace_name = tte.TableSpace_name(+)

AND dt.TableSpace_name = 'SYSAUX'

;

https://grepora.com/2016/02/08/purge-sysaux-tablespace/

How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)

How to Reduce SYSAUX Tablespace Occupancy Due to Fragmented TABLEs and INDEXes (Doc ID 1563921.1)