ORA-01658 In SYSAUX Tablespace

When I was trying create a table for a testcase in test database I get the next error;

 
TEST12> CREATE TABLE UNODOS AS SELECT * FROM DBA_OBJECTS;
CREATE TABLE UNODOS AS SELECT * FROM DBA_OBJECTS
                                               *
ERROR en línea 1:
ORA-01658: no se ha podido crear extensión INITIAL para el segmento en el tablespace SYSAUX

The problem is SYSAUX tablespace. Of course, it look run of free space, the solution is add more space (add datafile…) but it is a good idea look for the object/s whichs is growing.

With the next query you can see the objects and porcentege of use the sysaux;

 
SELECT 
   occupant_name,                    
   round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
 FROM v$sysaux_occupants                  
group by occupant_name
order by 2 desc nulls last
/

The output is like show below;

              
TEST12> SELECT 
  2     occupant_name,                    
  3     -- occupant_desc,                         
  4     -- space_usage_kbytes/1024 as "Usage Gb" ,
  5     round (sum(space_usage_kbytes) * 100 / sum (sum(space_usage_kbytes)) over (), 2) Pct
  6   FROM v$sysaux_occupants                  
  7  group by occupant_name
  8  order by 2 desc nulls last
  9  /

OCCUPANT_NAME                                             PCT
__________________________________________________ __________
SM/AWR                                                  40,46
SM/OPTSTAT                                              25,96
XDB                                                     13,28
SM/OTHER                                                10,97
SM/ADVISOR                                               3,13
LOGMNR                                                   2,93
SMON_SCN_TIME                                             ,69
SQL_MANAGEMENT_BASE                                       ,51
XSOQHIST                                                  ,41
AO                                                        ,41
PL/SCOPE                                                  ,33
LOGSTDBY                                                  ,32
STREAMS                                                   ,21
JOB_SCHEDULER                                             ,21
EM_MONITORING_USER                                        ,12
AUTO_TASK                                                 ,07
XSAMD                                                       0
WM                                                          0
ULTRASEARCH_DEMO_USER                                       0
ULTRASEARCH                                                 0
TSM                                                         0
TEXT                                                        0
STATSPACK                                                   0
SDO                                                         0
ORDIM/SI_INFORMTN_SCHEMA                                    0
ORDIM/ORDPLUGINS                                            0
ORDIM/ORDDATA                                               0
ORDIM                                                       0
EXPRESSION_FILTER                                           0
EM                                                          0
AUDSYS                                                      0
AUDIT_TABLES                                                0

In this case the space for AWR has a high conmsuption of space, it is a good idea to revise what AWR is doing. Note Note 287679.1 from MOS/MetaLink can help.

HTH – Antonio NAVARRO

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s