No question at this time
DBA Top 10
1 M. Cadot 5400
2 P. Wisse 1300
3 P. Tsongayinwe 1100
4 B. Vroman 500
4 J. Schnackenberg 500
6 M. Pagac 400
7 A. Deledda 200
7 D. Walgude 200
7 B. M 200
10 J. Alcroft 100
10 G. Trivedi 100
10 A. Hudspith 100
10 V. Sharma 100
10 L. Ywema 100
About
DBA-Village
Advertise here
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48742
Total active users1337
Act. users last 24h1
Act. users last hour0
Registered user hits last week108
Registered user hits last month471
Go up

SYSAUX tablespace full
Next thread: Dataguard Role Transition Timestamp
Prev thread: database can't open due to sysaux tablespace is full

Message Score Author Date
Hi One of our databases has a full SYSAUX tablesp...... James Alcroft Apr 27, 2023, 12:24
Hi James, short version: No, do not move anythi...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts Jan Schnackenberg Apr 27, 2023, 13:47
Thanks Ian I thought it would be more involved th...... Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts James Alcroft Apr 27, 2023, 14:06

Follow up by mail Click here

Subject: SYSAUX tablespace full
Author: James Alcroft, United Kingdom
Date: Apr 27, 2023, 12:24, 37 days ago
Os info: Windows 2019
Oracle info: 19.3
Message: Hi
One of our databases has a full SYSAUX tablespace (31.5G). Whilst I try and work out why it is so full, is it safe to create a new tablespace and move some tables into this TS instead of adding a new datafile - all our other tablespaces are only about 5-8Gb, so don't really want to add new datafile

Thanks

James
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: SYSAUX tablespace full
Author: Jan Schnackenberg, Germany
Date: Apr 27, 2023, 13:47, 37 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Hi James,

short version: No, do not move anything from SYSAUX to other tablespaces


long version:
It is possible to move _SOME_ of the objects in SYSAUX to other tablespaces. But even if it is allowed, you must follow a process (different for each of the owners of these objects) and you must never "just move" them.


Your best bet is to look at V$SYSAUX_OCCUPANTS and try to figure out which component is at fault. Then you know what to do to clean it up.

Regards,
Jan
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here
Subject: Re: SYSAUX tablespace full
Author: James Alcroft, United Kingdom
Date: Apr 27, 2023, 14:06, 37 days ago
Score:   Score: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 PtsScore: 100 Pts
Message: Thanks Ian
I thought it would be more involved than just moving objects, glad I checked.

The biggest culprit is
AUDSYS AUDSYS schema objects AUDSYS DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION Move Procedure for Unified Audit Trail 20341440

I change AUTO_STATS_ADVISOR_TASK -> EXECUTION_DAYS_TO_EXPIRE down to 10 yesterday and ran prvt_advisor.delete_expired_tasks; Cleared about 2G

Looks like I'm going to have to check what auditing options are enabled and disable some

James
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here