getting ORA-01653: unable to extend table SCOTT.S by 128 in tablespace SYSAUX; despite there is still free 49 mb

Posted on 2009-07-16
Last Modified: 2012-05-07
I am trying to imlment fixes mentioned bt metalink doc id 336133.1
to force format a corruopt block not used by any segments.
these blocks are in the sysaux TS which still has 49 mb but we are failing to insert any more rows please advise
SQL> alter table scott.s

  2       allocate extent (DATAFILE 'C:\APP\MXA15\ORADATA\ORCL\SYSAUX01.DBF' SIZ

E 1M);

alter table scott.s


ERROR at line 1:

ORA-01653: unable to extend table SCOTT.S by 128 in tablespace SYSAUX


SQL> select df.tablespace_name tspace,

  2         decode(df.maxbytes,

  3                 0,

  4                 df.bytes/(1024*1024),

  5                 df.maxbytes/(1024*1024)) tot_ts_size,

  6         decode(df.maxbytes,

  7                 0,

  8                 sum(fs.bytes)/(1024*1024),

  9                 (df.maxbytes-(df.bytes-sum(fs.bytes) ) )

 10                         /(1024*1024)) free_ts_size,

 11         decode(df.maxbytes,

 12                 0,

 13                 round(sum(fs.bytes)*100/df.bytes),

 14                 round( (df.maxbytes-(df.bytes-sum(fs.bytes) ) )

 15                         *100/df.maxbytes)) ts_pct,

 16         decode(df.maxbytes,

 17                 0,

 18                 round( (df.bytes-sum(fs.bytes))*100/df.bytes),

 19                 round( (df.maxbytes-(df.maxbytes-(df.bytes-sum(fs.bytes) ) )


 20                         *100/df.maxbytes)) ts_pct1

 21  from dba_free_space fs inner join

 22         (select

 23                 tablespace_name,

 24                 sum(bytes) bytes,

 25                 sum(decode(maxbytes,0,bytes,maxbytes))  maxbytes

 26          from dba_data_files

 27          group by tablespace_name ) df

 28  on fs.tablespace_name = df.tablespace_name

 29  where df.tablespace_name='SYSAUX'

 30  group by df.tablespace_name,df.maxbytes,df.bytes;

TSPACE                         TOT_TS_SIZE FREE_TS_SIZE     TS_PCT    TS_PCT1

------------------------------ ----------- ------------ ---------- ----------

SYSAUX                                1132       48.375          4         96



  2  FOR i IN 1..1 LOOP

  3  INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from


  4  commit ;

  5  END LOOP;

  6  END;


  8  /



ERROR at line 1:

ORA-01653: unable to extend table SCOTT.S by 128 in tablespace SYSAUX

ORA-06512: at line 3

Open in new window

Question by:it-rex
  • 5
  • 5
  • 2
  • +1
LVL 40

Accepted Solution

mrjoltcola earned 250 total points
ID: 24872968
Which tablespace is SCOTT's schema in?

It appears you've incorrectly created the SCOTT user in SYSAUX. It should really be in USERS or another tablespace.

You need to export it, drop the user and recreate it, or either move the tables.

alter table scott.s move tablespace users;
LVL 40

Expert Comment

ID: 24872996
Secondly, the ways to extend tablespace or add space is to do one of:

1) alter database tablespace sysaux resize <size>;  -- to a larger size
2) alter tablespace sysaux add datafile ...
3) Or turn on autoextend on the datafile:
   alter database datafile '....' autoextend on;

LVL 11

Author Comment

ID: 24873028
mrjoltcola as you see the s table needs to be created in the sysaux TS to force format the corrupt blocks.
LVL 47

Assisted Solution

schwertner earned 250 total points
ID: 24873159
The correct way to allow growt of a tablespace is to increase maxsize with a number of bytes, to set it autoincrement and make maxsize unlimited OR to add new file to the tablespace.
This what you do is unusual approach and I am afraid this causes the mess at the DB.
Use simple and proven methods.

I will also kindly advise do not publish the whole texts of documents that are proprietary of american companies and under restriction. This can cause you troubles and if i were you I will ask this post to be deleted. There are many other sources that could be cited and because you are not object of hate nobody will make you troubles. But the decission is in your hands, I only advise.
LVL 47

Expert Comment

ID: 24873201
DBMS_REPAIR possibly will help you to repair.
LVL 11

Author Comment

ID: 24873273
Ok thanks I never knew that.
metalink is advising to format corrupt blocks which do not belong to any segment by creating a dummy table in the tS with coruuption and keep filling it with rows till the blocks get forammted by a new insertion.
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

LVL 40

Expert Comment

ID: 24873336
I understand. No worries. :)

Since I, schwertner, sdstuder and most of the top Oracle guys here have Metalink accounts, you can provide the Metalink article # we can read for ourselves.

I would follow schwertner's advice to see if you can use DBMS_REPAIR to mark those blocks for skipping and then run RMAN again to see if it still reports.

After that, you may want to open an SR with Oracle.
LVL 11

Author Comment

ID: 24873383
ID: 336133.1
mrjoltcola the issue I have here is that i can see that there is still free space like 49 mb but i recieve
ORA-01653: unable to extend table SCOTT.S by 128 in tablespace SYSAUX
the common solution for this ora is usually incease the space ,add data file or turn on auto extend ,
which is exactly defying what I need to do as I need to force the insertion into the remaining space.

I do not think DBMS_REPAIR will do anything here..
LVL 40

Expert Comment

ID: 24874403
I read the Metalink article, and it did say it _may_ work but did not guarantee. At this point I would either consider recovering the SYSAUX tablespace, or opening an SR with Oracle. You might open an SR anyway just to see what they say prior.
LVL 40

Expert Comment

ID: 24874409
Given there are 2 inconsistent issues with the tablespace:

1) Reporting bad blocks from an unallocated segment
2) Reporting 49mb free but failing with ORA-01653

I think the tablespace may have some corruption and you should open the SR.

Expert Comment

ID: 24877021
You are tryimg to allocate space from a specific data file. Questions

Does the SYSAUX tablespace hae more than one datafile?
If yes, than it may be that th 49mb free space is available in other datafiles and not in SYSAUX01 datafile.
Try checking how much space is available in SYSAUX01 datafile.

LVL 11

Author Comment

ID: 24880176
it is in the 'C:\APP\MXA15\ORADATA\ORCL\SYSAUX01.DBF'  datafile
LVL 11

Author Comment

ID: 24927229
SQL> SELECT tablespace_name, bytes/1024/1024 tssize
 FROM dba_free_space
 WHERE tablespace_name='SYSAUX'
 ORDER BY 2 desc;

shows that
SYSAUX .9375
SYSAUX .8125
SYSAUX .6875
SYSAUX .6875
SYSAUX .5625
SYSAUX .5625
SYSAUX .5625

DBA_FREE_SPACE shows that around 48MB of free space is available with in SYSAUX tablespace but this free spac
e is splitted into around 150 chunks and the max free chunk available is just 1.
5 MB of size. So it seems that there is no big enough extent available that can
be allocated to table SCOTT.S'. Also the datafile is not autoextensible which is
not allowing the extent to be allocated.

hence the option to limit sysaux TS and insert to force format theblock will not work;

I did drop the em repo and then recreate it and it did fix the corruption problem.

thanks all

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
grouping on time windows 6 43
execute immediate plsql block 5 34
help on oracle query 5 33
PL SQL Search Across Columns 4 21
This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now