asked on

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

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
 
================================
 
SQL> BEGIN
  2  FOR i IN 1..1 LOOP
  3  INSERT /*+ APPEND */ INTO scott.s select i, lpad('REFORMAT',3092, 'R') from
 dual;
  4  commit ;
  5  END LOOP;
  6  END;
  7
  8  /
BEGIN
*
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

ASKER CERTIFIED SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mrjoltcola

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;

it-rex

ASKER

mrjoltcola as you see the s table needs to be created in the sysaux TS to force format the corrupt blocks.

SOLUTION

schwertner

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

schwertner

DBMS_REPAIR possibly will help you to repair.

it-rex

ASKER

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.

mrjoltcola

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.

it-rex

ASKER

Doc
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..

mrjoltcola

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.

mrjoltcola

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.

vishal68

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.

HTH
Vishal

it-rex

ASKER

vishal68:
it is in the 'C:\APP\MXA15\ORADATA\ORCL\SYSAUX01.DBF' datafile

it-rex

ASKER

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 .875
SYSAUX .8125
SYSAUX .75
SYSAUX .75
SYSAUX .6875
SYSAUX .6875
SYSAUX .625
SYSAUX .625
SYSAUX .625
SYSAUX .625
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