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
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);
truncate
table sys.aud$;
insert
into sys.aud$ select * from tmpAud;
commit;
--
Cleanup
drop
table tmpAud;
----------
2619
======================Below Step also Performed on The Basis of requirment.
SQL>
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.
----------
1
************************************EOD************************************