Wir hatten letzte Woche das Problem, dass unsere Oracle 23ai FREE Version an ihr Speicherlimit von 12GB (ORA-12954 The request exceeds the maximum allowed database size of 12 GB.) gekommen ist. 
Obwohl wir nur Daten mit maximal 1 GB besitzen, wollte die FREE Edition uns partout keinen Speicherplatz mehr gönnen.

Was war passiert ? Der SYSAUX Tablespace hatte in der CDB 3.5 GB und in der PDB bereits 4 GB erreicht. Obwohl in beiden Tablespace mindestens 2GB frei waren!
Diesen Speicher zählt Oracle also mit in die 12 GB hinein. Gut kein Problem, dachten wir, dann verkleinern wir den SYSAUX Tablespace halt…

…Honey i shrunk the SYSAUX Tablespace…

Daraus ist dieser BLOG Eintrag entstanden und viiiiiiel internes Fachwissen, welche Objekte mit Samthandschuhen angefasst werden möchten.

Offiziell sind viele dieser Tipps hier auf der Seite “not supported” von Oracle. Das Ganze also bitte nur auf Test-Datenbanken durchführen. Wir haben es zwar auf zwei unserer Produktiv-Datenbanken erfolgreich durchgeführt, trotzdem können 
wir leider keine Garantie auf die Funktionsweise geben !

Was ist das Problem des SYSAUX Tablespace?

1. Er wächst und wächst und wächst. In der freien Wildbahn wurden auch schon Exemplare mit mehr als 70GB gesichtet
2. Viele der Statistik Tabellen bewahren die Daten zu lange auf
3. Bei einer Reorg der Objekte im gleichen Tablespace, werden häufig nicht die “vordersten” Bereiche in der Datei wiederverwendet

Lösungen:

1. Einen Scratch Tablespace erstellen, auf dem die Objekte, die weiter hinten im SYSAUX Tablespace liegen, kurzfristig verlagert werden können
2. Aufräumen von unnötigen Daten im Tablespace

Wenn Sie vorab wissen möchten welche Verwaltungstabellen welche Anzahl an Zeilen besitzt, können Sie eine Statistik darauf laufen lassen ( Dauerte bei uns ca 8 Min ! )

exec dbms_stats.gather_fixed_objects_stats(degree=>8);
exec dbms_stats.gather_dictionary_stats(degree=>8);

1. Mülleimer leeren

PURGE dba_recyclebin;

2. Plugin Violations Tabelle für CDB und alle PDBs leeren

exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS (Pdb_name => 'PDB$SEED' );
exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS (Pdb_name => 'CDB$ROOT' );
…
exec DBMS_PDB.CLEAR_PLUGIN_VIOLATIONS (Pdb_name => 'MY_PDB1' );

3. Kompletten Audit löschen (Achtung vorher evtl. eine Kopie von UNIFIED_AUDIT_TRAIL machen) und den Audit auf anderen Tablespace verschieben
Achtung es dauert einen Tag bis das Wirkung zeigt. Diesen Schritt also evtl. schon einen Tag vorab laufen lassen

BEGIN
dbms_audit_mgmt.clean_audit_trail(audit_trail_type=>
dbms_audit_mgmt.audit_trail_unified
,use_last_arch_timestamp=>FALSE);
END;
/

BEGIN
DBMS_AUDIT_MGMT.ALTER_PARTITION_INTERVAL(
interval_number => 1,
interval_frequency => 'DAY');
END;
/

BEGIN
DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION( audit_trail_type =>
DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
audit_trail_location_value =>'SYSAUX_SCRATCH');
END;
/

4. Alle alten Statistiken löschen (da wird eine Menge Platz wieder frei im SYSAUX Tablespace)

BEGIN 
dbms_stats.purge_stats(dbms_stats.purge_all);
END;
/
BEGIN 
dbms_stats.reset_col_usage (null,null);
END;
/

5. Komplette ADDM Statistiken löschen (damit hat Ihre DB die gleichen “Probleme”, als wenn Sie ganz frisch aufgesetzt wurde (sie hat keine Statistiken)

Sie können natürlich die letzten 2 Snapshots noch behalten (Infos dazu stehen in DBA_HIST_SNAPSHOTS)

REM Note 2660128.1

EXEC dbms_workload_repository.drop_snapshot_range(1,999999999); -- 10min
exec prvt_advisor.delete_expired_tasks;
WITH FUNCTION do_ddl (cmd IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- Damit ist die Funktion eigenständig und beeinflusst niemanden :-)
v_cmd VARCHAR2(32000);
BEGIN
v_cmd:=rtrim(cmd,';'); -- Semmikolon ggf.herausfiltern
    BEGIN
    EXECUTE IMMEDIATE v_cmd; -- Befehl ausführen
    EXCEPTION WHEN OTHERS THEN
    IF sqlcode=-65040 THEN
        BEGIN
        dbms_pdb.exec_as_oracle_script(v_cmd);
        EXCEPTION WHEN OTHERS THEN
            RETURN v_cmd||' --'||sqlerrm;
        END;    
    ELSE RAISE;
    END IF;
    END;
RETURN v_cmd||'; --OK'; -- Befehl erfolgreich ausgeführt
EXCEPTION WHEN OTHERS THEN
RETURN v_cmd||' --'||sqlerrm;
END;
select 'ALTER TABLE WRI$_ADV_OBJECTS MOVE' FROM dual
UNION ALL
select 'ALTER INDEX WRI$_ADV_OBJECTS_PK REBUILD' FROM dual
UNION ALL
select 'ALTER INDEX WRI$_ADV_OBJECTS_IDX_01 REBUILD' FROM dual
UNION ALL
select 'ALTER INDEX WRI$_ADV_OBJECTS_IDX_02 REBUILD' FROM dual
/

6. OATS Oracle Active Tracking System (ab 21c):

Ist auch ein großer Platzfresser. Wir räumen (durch Löschen) alles auf. (Wir waren vor Version 21c  ja auch glücklich mit unserer DB ohne dieses Feature :-) )

WITH FUNCTION del_oats (del_time date) RETURN varchar2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF dbms_activity.delete_snapshots(del_time) THEN RETURN 'OATS deleted';
ELSE RETURN 'OATS not deleted';
END IF;
END;
SELECT del_oats(sysdate-1/96) FROM dual;

7.  Scheduler Logs aufräumen:

exec DBMS_SCHEDULER.PURGE_LOG;
exec DBMS_SCHEDULER.PURGE_LOG(0,which_log=>'WINDOW_LOG');

8. Weitere Spezialfälle besprechen wir in unserer Schulung Reorg und Wartung oder im DBA II Kurs :-)

Nun kommen wir zum interessanten Teil: Verschieben der hinteren Objekte in den Scratch Tablespace

Der folgende Select erzeugt Ihnen ein Skript, mit dem sich ca. 95% der Objekte verschieben lassen.

WITH
S as (select 1000 as block_id, – Nur Objekte hinter Block Nummer 1000 verschieben 
3 as file_id,'SYSAUX_SCRATCH' as scratch_tbs,  – Name des Scratch Tablespace für die Objekte
'ONLINE' as on_off_mode – Für SE2 Benutzer bitte Spalte auf ‘’ setzen
FROM dual),
db as (select count(*) as is_pdb  from v$pdbs)
SELECT  CASE-- ## T A B L E und I N D E X  P A R T I T I O N ##
WHEN  db.is_pdb=1 THEN  'exec dbms_pdb.exec_as_oracle_script(''' END||
CASE WHEN segment_type  IN ('INDEX PARTITION','TABLE PARTITION') and e.segment_name not like 'SYS_IL0%' THEN
'ALTER ' || substr(e.segment_type,1,5)||' "' || owner || '"."' || e.segment_name || '"' ||
CASE segment_type WHEN 'INDEX PARTITION' THEN 
' REBUILD PARTITION "' ELSE ' MOVE PARTITION "' END || partition_name|| '" '||
CASE WHEN e.segment_name NOT LIKE 'WRH$%' 
AND  e.segment_name NOT LIKE 'WRM$%'  
AND e.segment_name NOT IN 
('ACTIVITY_TABLE$' ,'WRI$_OPTSTAT_HISTGRM_HISTORY' ,'SCHEDULER$_EVENT_LOG','UMF$_ATSK_HIST_MXDB_MXSN','SCHEDULER$_JOB_RUN_DETAILS') THEN 
s.on_off_mode END ||' tablespace '||nvl(s.scratch_tbs,e.tablespace_name)
WHEN segment_type IN ('TABLE','INDEX') THEN -- ###### T A B L E und I N D E X ######
'ALTER '||e.segment_type||' "'||owner||'"."'||e.segment_name||'"'||
CASE segment_TYPE
WHEN 'TABLE' THEN ' MOVE ' || s.on_off_mode ||' TABLESPACE '||nvl(s.scratch_tbs,e.tablespace_name) 
ELSE ' REBUILD ' || s.on_off_mode ||' TABLESPACE '||nvl(s.scratch_tbs,e.tablespace_name) END
WHEN  segment_type IN ('LOBSEGMENT','LOB PARTITION') THEN -- ###### L O B ######
'ALTER TABLE "'||e.owner||'"."'||(select table_name 
FROM dba_lobs l where l.segment_name=e.segment_name)||'" MOVE LOB ("'||
(select column_name FROM dba_lobs l where l.segment_name=e.segment_name)||'") STORE AS (tablespace "'||
nvl(s.scratch_tbs,e.tablespace_name) ||'")' END||
CASE WHEN db.is_pdb=1 THEN ''');--' ELSE ';'   END as sql_cmd
FROM s,db,dba_extents e
where e.file_id=s.file_id
and e.block_id>s.block_id
ORDER BY e.block_id desc;

Da werden jetzt einige Objekte sich beschweren, dass Sie nicht verschiebbar sind (z.B. auch als SYS die Fehlermeldung ORA-01031 Insufficient Privileges)
Wir haben eine Lösung dafür gefunden die aber sicher nicht supported ist, weil damit den Schutz auf sensible Spalten in den Tabellen auch ausschalten wird.

Zu guter Letzt, werden noch die Objekte repariert, die den Status INVALID aufweisen:

WITH FUNCTION do_ddl (cmd IN VARCHAR2)
RETURN VARCHAR2
IS
PRAGMA AUTONOMOUS_TRANSACTION; -- Damit ist die Funktion eigenständig und beeinflusst niemanden :-)
v_cmd VARCHAR2(32000);
BEGIN
v_cmd:=rtrim(cmd,';'); -- Semmikolon ggf.herausfiltern
EXECUTE IMMEDIATE v_cmd; -- Befehl ausführen
RETURN v_cmd||'; --OK'; -- Befehl erfolgreich ausgeführt
EXCEPTION WHEN OTHERS THEN
RETURN v_cmd||' --'||sqlerrm;
END;
SELECT do_ddl('ALTER INDEX "'||owner||'"."'||index_name||'" REBUILD;')
FROM dba_indexes i 
WHERE status='UNUSABLE'
UNION ALL
SELECT do_ddl('ALTER INDEX  "'||index_owner||'"."'||index_name||'" REBUILD PARTITION "'||partition_name||'";')
FROM dba_ind_partitions
WHERE status='UNUSABLE'
UNION ALL
select 'ALTER '|| CASE WHEN object_type='PACKAGE BODY' THEN 'PACKAGE' ELSE object_type END  
||' "'|| owner ||'"."'|| object_name ||'" COMPILE ' ||
CASE WHEN object_type='PACKAGE BODY' THEN 'BODY;' ELSE ';' END as sql_stmt
from dba_objects
where object_type in 
('PROCEDURE','FUNCTION','PACKAGE','PACKAGE BODY','VIEW')
and status='INVALID';

Mehr Informationen erhalten Sie in einem unserer Oracle Reorg und DBA II Kurse, oder wir kommen zu Ihnen und räumen den SYSAUX Tablespace persönlich auf.
Melden Sie sich gerne bei uns und holen Sie ein Angebot ein. Wir freuen uns auf Sie.