unable to extend index

[rawatds]

Hi all
When i run a job in my database , following error is coming :

ORA-01654: unable to extend index SYS.I_COL3 by 50 in
tablespace SYSTEM .

I donot have much knowledge on this regard .
Can anybody please help me out what i have to do to correct it.

Thanx in advance
rawat

 

[SantaMufasa]

Rawatds,

This error means that you have run out of space in the SYSTEM tablespace. There are a couple of ways to resolve this issue: 1) Add another datafile to the SYSTEM tablespace or 2) Cause the existing datafile to AUTOEXTEND (to perhaps a 2GB maximum).

Before you choose either of these resolutions, I recommend your reviewing the existing states of your tablespace datafiles. I recommend your using the following script. (I have widened the screen so that the output of the script does not wrap.):

Section 1 -- "Freespace.sql" script:

set echo on
REM **************************************************************
REM David L. Hunt (file author) distributes this and other
REM files/scripts for educational purposes only, to illustrate the
REM use or application of various computing techniques. Neither the
REM author nor Dasages, LLC, makes any warranty regarding this
REM script's fitness for any industrial application or purpose nor is
REM there any claim that this or any similarly-distributed scripts
REM are error free or should be used for any purpose other than
REM illustration.
REM **************************************************************
set echo off
set linesize 165
set pagesize 40
set heading on
col fname heading "Filename" format a60
col fnum heading "#" format 999
col ts heading "Tablespace|Name" format a15
col tb heading "Total|Potential|File Size" format 999,999,999,999
col cb heading "Total|Current|File Size" like tb
col used heading "Bytes Used" like tb
col free heading "Potential|Bytes Free" like tb
col autoext heading "Auto|Ext." format a4
col percentfree heading "% Free|of|Pot.|Total|Bytes" format 999
break on report
compute sum of tb cb used free on report
spool TablespaceUsage.txt
select     substr(tablespace_name,1,15) ts
   ,d.file_id fnum
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize)) tb
   ,d.bytes cb
   ,decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) used
   ,decode(e.file#,null,d.bytes,(e.maxextend * blksize))
    -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)) free
   ,trunc(((decode(e.file#,null,d.bytes,(e.maxextend * blksize))
     -decode(d.bytes,0,0,d.bytes-nvl(freebytes,0)))/
      decode(e.file#,null,d.bytes,(e.maxextend * blksize))
         ),2) * 100 percentfree
   ,decode(e.file#,null,'No','Yes') autoext
   ,''''||substr(file_name,1,55)||'''' fname
from       sys.dba_data_files d
   ,(select file_id,sum(bytes) freebytes
           from sys.dba_free_space
           group by file_id) f
   ,sys.filext$ e
   ,v$datafile v
   ,(select value blksize from v$parameter
           where name = 'db_block_size') b
where      d.file_id=f.file_id(+)
  and      d.file_id=e.file#(+)
  and      v.file#=d.file_id
order by tablespace_name,creation_time
/
spool off
prompt
prompt Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential
prompt
prompt Wrote spool file "TablespaceUsage.txt".
prompt

Section 2 -- Sample output from "Freespace.sql":

                                                                                         % Free
                                                                                             of
                                Total            Total                                     Pot.
Tablespace                  Potential          Current                         Potential  Total Auto
Name               #        File Size        File Size       Bytes Used       Bytes Free  Bytes Ext. Filename
--------------- ---- ---------------- ---------------- ---------------- ---------------- ------ ---- --------------------------------------------------
DATA1              4    2,097,152,000      408,944,640      115,933,184    1,981,218,816     94 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTDATA01.DBF'
RBS                2    2,097,152,000       20,971,520           65,536    2,097,086,464     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTRBS01.DBF'
SYSTEM             1    2,097,152,000      153,092,096      149,856,256    1,947,295,744     92 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
TEMP               3    2,097,152,000       31,457,280        1,073,152    2,096,078,848     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTTEMP01.DBF'
YADA               5    2,097,152,000        4,194,304        2,359,296    2,094,792,704     99 Yes  'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTYADA01.DBF'
                     ---------------- ---------------- ---------------- ----------------
sum                    10,485,760,000      618,659,840      269,287,424   10,216,472,576

5 rows selected.


Note: Physical disk limitations may prevent an AUTOEXTEND-ing file from reaching its potential

Wrote spool file "TablespaceUsage.txt".
*******************************************************************************************************************************************************

Notice that the above output confirms how close a file is to reaching its current physical maximum and also whether or not a file is in AUTOEXTEND mode (See column 8, "Auto Ext.").

Now, following is code to turn an existing datafile for the SYSTEM tablespace into an AUTOEXTENDING file (assuming that your existing datafile is not yet AUTOEXTENDING and it has not yet reached your target maximum. I personally limit files to 2GB. But you can have virtually as many datafiles as you wish/need for a tablespace.)

ALTER DATABASE DATAFILE 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS01.DBF'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

The above code causes the existing file to begin autoextending in 10-megabyte increments to a maximum of 2GB.

To add another (autoextending) file to the SYSTEM tablespace, you can use this code:

ALTER TABLESPACE SYSTEM ADD DATAFILE 'D:\DHUNT\ORACLEDATA\DBDATA\DHUNT\DHUNTSYS02.DBF' SIZE 10M
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

Let us know if the above is helpful and resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:15 (13Jan05) UTC (aka "GMT" and "Zulu"),
@ 00:15 (13Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rawatds]

Hi Mufasa
I run the freespace.sql in my database .
The following is the result :
SYSTEM 1 83,886,080 83,886,080 83,412,992 473,088 NO '/u01/oradata/vsis/system01.dbf'
SYSTEM 81 52,428,800 52,428,800 52,414,464 14,336 0 No '/u01/oradata/vsis/system02.dbf'
SYSTEM 84 524,288,000 524,288,000 524,269,568 18,432 0 No '/u01/oradata/vsis/system03.dbf'


Here in the SYSTEM Tablespaces : The Auto extended
column is NO . So what should i do now .
Should I add another datafile in the SYSTEM and where .

Please assist .
Thanx in advance
rawat

 

[SantaMufasa]

Rawat,

You certainly have enough data files that support the SYSTEM tablespace already. Provided that you still have plenty of available space on the '/u01/oradata/vsis' file system, I recommend your placing your three SYSTEM ts files in AUTOEXTEND mode with the following commands:

ALTER DATABASE DATAFILE '/u01/oradata/vsis/system01.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

ALTER DATABASE DATAFILE '/u01/oradata/vsis/system02.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

ALTER DATABASE DATAFILE '/u01/oradata/vsis/system03.dbf'
AUTOEXTEND ON NEXT 10M MAXSIZE 2000M;

At that point, you should have plenty of freespace available to run your job without receiving your earlier error.

Please let us know your findings.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 07:56 (13Jan05) UTC (aka "GMT" and "Zulu"),
@ 00:56 (13Jan05) Mountain Time

Click here to Donate to Tsunami Relief. 100% of your contributions here go to the victims...0% to administration.
They were "The First-Responder" to the disaster, with relief deliveries arriving before Red Cross and U.S. aid.

 

[rawatds]

Hi Mufasa
Thanx a lot a ran the three above mentioned query
and now my job run successfully

Once again a lot
rawat