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)
No comments:
Post a Comment