Tuesday, May 18, 2021

Why System Tablespace space increasing ??

Why System Tablespace  space increasing ?? 

#!/bin/ksh
bgt()
{
sqlplus -s $ID/$PASS <<eof
select owner,segment_name,segment_type,tablespace_name,bytes/1024/1024 from dba_segments where segment_name='AUD$';
exit
set lines 200
col OWNER for a10
col segment_name for a40
col segment_type for a20
select owner,segment_name,segment_type,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM'
order by size_m desc;
eof
}

bgt  > /tmp/sys_table_object.log

vi /tmp/sys_table_object.log

This above script will give the object size information of system tablespace, In my case the audit object is highest space consumer so I have to deleted some record from audit object and following approach used to do that.


-- Save data of last 60 days

create table tmpAud as

select * from sys.aud$

where to_date(trunc(ntimestamp#)) > trunc(sysdate-60);

 -- Cleanup and coalesce aud$

truncate table sys.aud$;

 -- Repopulate

insert into sys.aud$ select * from tmpAud;

commit;

-- Cleanup

drop table tmpAud;

 sqlplus / as sysdba

 SQL>  select  count(*) from unified_audit_trail;

  COUNT(*)

----------

      2619

 SQL> 

======================Below Step also Performed on The Basis of requirment.

SQL>

 BEGIN

DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(

audit_trail_type         =>  DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,

use_last_arch_timestamp  =>  FALSE);

END;

/

PL/SQL procedure successfully completed.

 SQL>  select  count(*) from unified_audit_trail;

  COUNT(*)

----------

         1

************************************EOD************************************


No comments:

Post a Comment