ORA-01110: data file 2504 Errors occurred during index rebuild

This morning i came across this :

ORA-01110: data file 2504 Errors occurred during index rebuild

I examined and I followed steps below after that index rebuild was success again.

## first checked for the properties since this is a 10g environment:
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A30
COLUMN description FORMAT A50
SET LINESIZE 200

SELECT *
FROM   database_properties
WHERE  property_name like ‘%TABLESPACE’;SQL> SQL> SQL> SQL> SQL>   2    3  

PROPERTY_NAME                  PROPERTY_VALUE                 DESCRIPTION
—————————— —————————— ————————————————–
DEFAULT_TEMP_TABLESPACE        TEMP                           Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   SYSTEM                         Name of default permanent tablespace

##Created a new temporary tablespace:
CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE  ‘/db/MYDB/temp/temp_99.dbf’ size 1024M;

##Made this the new default temporary tablespace
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

## checked if temp tablespace was in use (would have to kill those sessions in case of)
SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;

##Dropped the old tablespace.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

## Removed the file on Unix. i do know i might have settled for a reuse, given the fact of possible corrupt thought this was better.
rm temp_01.dbf

## Created  new temp tablespace.
CREATE TEMPORARY TABLESPACE TEMP TEMPFILE ‘/db/MYDB/temp/temp_01.dbf’ size 2000M;

## Removed all temp files on the os
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_02.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_03.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_04.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_05.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_06.dbf
oracle@mysrvr1:/db/MYDB/temp [OPTMYDB]# rm  temp_07.dbf

##  temp files added
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_02.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_03.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_04.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_05.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_06.dbf’ size 2000m;
ALTER TABLESPACE temp ADD TEMPFILE ‘/db/MYDB/temp/temp_07.dbf’ size 2000m;

## Default tablespace was put back to temp again
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

##  Temp2 tablespace dropped
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

## This tablepace also a temp one had issues in same way:
DROP TABLESPACE tools_temp  INCLUDING CONTENTS AND DATAFILES;

rm  tools_temp_01.dbf

## tools_temp hatte auch macke deshalb die auch raus und rein.
CREATE TEMPORARY TABLESPACE TOOLS_TEMP  TEMPFILE ‘/db/MYDB/temp/tools_temp_01.dbf’ size 1024M;

## All back to normal again .

Happy reading,

Mathijs

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