Oracle Tablespace High water mark

Tablespace High water mark
Tablespace HWM



col tablespace_name format a15
col file_size format 99999
col file_name format a50
col hwm format 99999
col can_save format 99999

SELECT tablespace_name, file_name, file_size, hwm, file_size-hwm can_save
FROM (SELECT /*+ RULE */ ddf.tablespace_name, ddf.file_name file_name,
ddf.bytes/1048576 file_size,(ebf.maximum + de.blocks-1)*dbs.db_block_size/1048576 hwm
FROM dba_data_files ddf,(SELECT file_id, MAX(block_id) maximum FROM dba_extents GROUP BY file_id) ebf,dba_extents de,
(SELECT value db_block_size FROM v$parameter WHERE name='db_block_size') dbs
WHERE ddf.file_id = ebf.file_id
AND de.file_id = ebf.file_id
AND de.block_id = ebf.maximum
ORDER BY 1,2);

TABLESPACE_NAME FILE_NAME                                          FILE_SIZE    HWM CAN_SAVE
--------------- -------------------------------------------------- --------- ------ --------
ANUJTEST        /opt/app/oracle/oradata/orcl/anujtest.dbf                 10      8        2
EXAMPLE         /opt/app/oracle/oradata/orcl/example01.dbf               100     81       19
PERFSTAT        /opt/app/oracle/oradata/orcl/anuj_perfstat.dbf          1000    115      885
RMAN            /opt/app/oracle/oradata/orcl/rman.dbf                     50      7       43
SYSAUX          /opt/app/oracle/oradata/orcl/sysaux01.dbf                830    777       53
SYSTEM          /opt/app/oracle/oradata/orcl/system01.dbf                840    823       17
TSAPEXU         /opt/app/oracle/oradata/orcl/tsapexu01.dbf               110     97       13
UNDOTBR         /opt/app/oracle/oradata/orcl/undotbR.dbf                 500     46      454
USERS           /opt/app/oracle/oradata/orcl/users01.dbf                 609    576       33

9 rows selected.

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

 set linesize 300

 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;

set lines 500 pages 10000 col TSname format a25 heading 'TSpace|Name|||' col TSstatus format a9 heading 'TSpace|Status|||' col TSSizeMb format 99999999 heading 'TSpace|Size|Mb||' col TSUsedMb format 99999999 heading 'TSpace|Used|Space|Mb|' col TSFreeMb format 99999999 heading 'TSpace|Free|Space|Mb|' col TSUsedPrct format 99999999 heading 'TSpace|Used|Space|%|' col TSFreePrct format 99999999 heading 'TSpace|Free|Space|%|' col TSSegUsedMb format 99999999 heading 'TSpace|Segmt|Space|Mb|' col TSExtUsedMb format 99999999 heading 'TSpace|Extent|Space|Mb|' col AutoExtFile format a6 heading 'Auto|Extend|File|?|' col TSMaxSizeMb format a6 heading 'TSpace|MaxSize|Mb||' col TSMaxUsedPrct format a6 heading 'TSpace|Maxed|Used|Space|%' col TSMaxFreePrct format a6 heading 'TSpace|Maxed|Free|Space|%' 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' ; TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace TSpace Auto TSpace TSpace TSpace Name Status Size Used Free Used Free Segmt Extent Extend MaxSiz Maxed Maxed Mb Space Space Space Space Space Space File Mb Used Free Mb Mb % % Mb Mb ? Space Space % % ------------------------- --------- --------- --------- --------- --------- --------- --------- --------- ------ ------ ------ ------ SYSAUX ONLINE 149103 6724 142379 5 95 6718 6718 Yes 196607 3 97 SQL> SQL>

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)