Andre Spadini
André Spadini | MultiCloud | DBA

André Spadini | MultiCloud | DBA

Oracle: Limpeza sys.aud$ no 11g

Oracle: Limpeza sys.aud$ no 11g

Andre Spadini's photo
Andre Spadini

Published on Nov 10, 2021

1 min read

Subscribe to my newsletter and never miss my upcoming articles

Segue abaixo como identificar e limpeza a tabela de sys.aud$ no oracle 11g.

-- Identificar o tamanho da tabela
SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name 
FROM DBA_SEGMENTS 
WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION', 'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY', 'LOBINDEX', 'LOBSEGMENT', 'LOB PARTITION')
AND TABLESPACE_NAME LIKE 'SYSTEM' 
AND ROUND(bytes/(1024*1024),2) > 1000 
ORDER BY bytes DESC;


OWNER   SEGMENT_NAME  SEGMENT_TYPE    SIZE_MB TABLESPACE_NAME
------- ------------- ----------------------- ------------------------------
SYS     AUD$          TABLE          31599.19 SYSTEM

Como a tabela está grande, fiz um truncate para não gerar archivelog.

TRUNCATE table sys.AUD$;

Segue script de shell para limpeza dos logs maiores que 3 meses.

#!/bin/bash

source /home/oracle/.bash_profile
$ORACLE_HOME/bin/sqlplus / AS SYSDBA <<EOF

alter table AUD$ nologging;
delete from aud$ where TIMESTAMP# <= sysdate-90;
alter table AUD$ nologging;

EXIT;

EOF

Abs!

 
Share this