Home » RDBMS Server » Server Administration » Sysaux tablespace full (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Sysaux tablespace full [message #648713] Tue, 01 March 2016 10:38 Go to next message
manishdba007
Messages: 27
Registered: September 2014
Location: India
Junior Member
Hello All,

We are having our Sysaux tablespace full. Please find below details
For now i have added a datafile in this to keep going.
Tablespace                      Size (MB)  Free (MB)     % Free     % Used
------------------------------ ---------- ---------- ---------- ----------
SYSAUX                              32320  1582.4375          5         95


OCCUPANT_DESC                                                    SPACE_USAGE_KBYTES
---------------------------------------------------------------- ------------------
Server Manageability - Automatic Workload Repository                       30462464
Server Manageability - Optimizer Statistics History                          448320
XDB                                                                          129984
Server Manageability - Advisor Framework                                     101952
Oracle Spatial                                                                67712
Enterprise Manager Repository                                                 46528
Analytical Workspace Object Table                                             39104
OLAP API History Tables                                                       39104
Oracle Multimedia ORDDATA Components                                          13888
LogMiner                                                                      13696
Server Manageability - Other Components                                       11200
Unified Job Scheduler                                                         10432
OLAP Catalog                                                                   5248
Expression Filter System                                                       3712
Oracle Text                                                                    3712
Workspace Manager                                                              3584
Transaction Layer - SCN to TIME mapping                                        3328
SQL Management Base Schema                                                     1728
PL/SQL Identifier Collection                                                   1728
Logical Standby                                                                1408
Oracle Streams                                                                 1024
Enterprise Manager Monitoring User                                              832
Oracle Multimedia ORDSYS Components                                             448
Automated Maintenance Tasks                                                     320

Now i told my client that this is because of AWR info gatherd in Sysaux and we have to purge it. Once i got confirmation from customer i executed below package -

BEGIN
dbms_workload_repository.drop_snapshot_range(low_snap_id => ****, high_snap_id=>*****);
END;
/

But it was hanged for long so i canceled it then i checked again view dba_hist_snapshot and nothing was there but when i checked the actual size of tablespace , space was still occupied and not released.

Please let me know whatever info required on this.

Please help me out in this. This is a prod system and client is killing me.

Rgds
Manish
*BlackSwan added {code} tags. Please do so yourself in the future.
How to use {code} tags and make your code easier to read

[Updated on: Tue, 01 March 2016 10:43] by Moderator

Report message to a moderator

Re: Sysaux tablespace full [message #648714 is a reply to message #648713] Tue, 01 March 2016 10:56 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I bet drop_snapshot_range just execute some deletes and deletes do not return the space.
You have to manually shrink the underlying tables (if this is possible).

Re: Sysaux tablespace full [message #648726 is a reply to message #648714] Wed, 02 March 2016 01:22 Go to previous messageGo to next message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

http://www.orafaq.com/forum/t/200176/

Re: Sysaux tablespace full [message #648728 is a reply to message #648713] Wed, 02 March 2016 01:27 Go to previous messageGo to next message
John Watson
Messages: 7264
Registered: January 2010
Location: Global Village
Senior Member
You remove the AWR with the catnoawr.sql script, drop and replace the tblespace, and then run catawr.sql.
Re: Sysaux tablespace full [message #648730 is a reply to message #648728] Wed, 02 March 2016 01:29 Go to previous messageGo to next message
manishdba007
Messages: 27
Registered: September 2014
Location: India
Junior Member
Does drop Sysaux will cause any issue to ongoing database operations ?
Re: Sysaux tablespace full [message #648732 is a reply to message #648730] Wed, 02 March 2016 01:32 Go to previous messageGo to next message
John Watson
Messages: 7264
Registered: January 2010
Location: Global Village
Senior Member
You can test, you know. Just take the tablespace offline for a while.
Re: Sysaux tablespace full [message #648733 is a reply to message #648732] Wed, 02 March 2016 01:33 Go to previous messageGo to next message
manishdba007
Messages: 27
Registered: September 2014
Location: India
Junior Member
OK. Thanks John and Michel for your valuable inputs.
Re: Sysaux tablespace full [message #648802 is a reply to message #648733] Fri, 04 March 2016 03:14 Go to previous messageGo to next message
manishdba007
Messages: 27
Registered: September 2014
Location: India
Junior Member
One question here. Can i do it with exp/imp ?

Take exp of Sysaux
Drop Sysaux
Imp dump of sysaux

Will it help ? Will occupied space will be released ?

Manish
Re: Sysaux tablespace full [message #648803 is a reply to message #648802] Fri, 04 March 2016 03:19 Go to previous message
Michel Cadot
Messages: 65389
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

SYS objects can't be exported.

Previous Topic: 12c Advanced Row Compression - IOT
Next Topic: CPU 100% AND CUSTOMER APPLICATION SLOW
Goto Forum:
  


Current Time: Fri Feb 23 16:45:31 CST 2018

Total time taken to generate the page: 0.02438 seconds