Archive for the ‘10gR2’ Category

Error altering tempfile: ORA-00376: file 202 cannot be read at this time

I noticed a lot of ORA-01652 errors while gathering some table statistics (Oracle 10g Release 2 –

BEGIN DBMS_STATS.GATHER_TABLE_STATS('OWNER','TABLE', cascade=>true, estimate_percent=>60); END;

ERROR at line 1:
ORA-01652: Unable to extend temp segment by 256 in tablespace TEMP

ORA-06512: at "SYS.DBMS_STATS", line 13437
ORA-06512: at "SYS.DBMS_STATS", line 13457
ORA-06512: at line 1

When I’m trying to resize the tempfile I get an ORA-00376 error:

SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G;
alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G
ERROR at line 1:
ORA-00376: file 202 cannot be read at this time
ORA-01110: datafile 202: '/app/oracle/oradata/temp01.dbf'

I checked the file on the filesystem and it exists and has the correct permissions:

$ ls -lrth /app/oracle/oradata/temp01.dbf
-rw-r----- 1 oracle dba 15G 17 nov 02:01 /app/oracle/oradata/temp01.dbf

After bouncing the database, I try to alter again the tempfile and now I get an ORA-01516:

SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G;
alter database tempfile '/app/oracle/oradata/temp01.dbf' resize 20G
ERROR at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile '/app/oracle/oradata/temp01.dbf'

But the file is there as I checked before. With this new ORA-01516 error I checked the status of the tempfile and the error was there:

SQL> select tablespace_name , status from dba_tablespaces where tablespace_name = 'TEMP';

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

SQL> select name ,status from v$tempfile where name like '%temp%';

NAME                                          STATUS
/app/oracle/oradata/temp01.dbf                OFFLINE

The tempfile is offline. This is because our server ran out of space this morning, as you can see in the alert log:

Errors in file /app/oracle/admin/db1/udump/db11_ora_18815.trc:
ORA-01114:IO error writing block to file 202 (block # 1218066)
ORA-27072: I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 1218066
Additional information: -1

After freeing disk space, I try to bring back the datafile online:

SQL> alter database datafile '/app/oracle/oradata/temp01.dbf' online;
 alter database datafile '/app/oracle/oradata/temp01.dbf' online
 ERROR at line 1:
 ORA-01516: nonexistent log file, datafile or tempfile '/app/oracle/oradata/temp01.dbf'

I neither can take the TEMP tablespace offline:

SQL> alter tablespace temp offline;
 alter tablespace temp offline
ERROR at line 1:
ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

The solution is to recreate the tempfile:

  • First, delete the existing tempfile from the database
  • Remove the file from the filesystem (rm)
  • Create a new tempfile.
SQL> alter database tempfile '/app/oracle/oradata/temp01.dbf' drop;

Database modified.

SQL> !ls -lrt /app/oracle/oradata/temp01.dbf

-rw-r----- 1 oracle dba 15728644096 17 nov 02:01 /app/oracle/oradata/temp01.dbf

SQL> !rm /app/oracle/oradata/temp01.dbf

SQL> alter tablespace temp add tempfile '/app/oracle/oradata/temp01.dbf' size 15000M;

Tablespace modified.

SQL> select name, status from v$tempfile;

NAME                                     STATUS
---------------------------------------- -------
/app/oracle/oradata/temp01.dbf           ONLINE

Now the tablespace is online and everithing returns to normal.

Any questions? Please feel free to comment below.

Categories: 10gR2, oracle Tags: ,

EXP-00008: ORACLE error 1455 encountered

January 31, 2014 1 comment

I’m trying to export a user on a Oracle 11g Relase 2 database ( using my local client, Oracle

With certain users I’m getting this error:

$ exp system@sid file=sgirgeaext_"$(date +%Y%m%d)".dmp log=sgirgeaext_exp_"$(date +%Y%m%d)".log consistent=Y statistics=none owner=sgirgeaext

Export: Release - Production on Fri Jan 31 11:13:33 2014

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to: Oracle Database 11g Enterprise Edition Release - 64bit Production
With the Partitioning and Real Application Testing options
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SGIRGEAEXT
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SGIRGEAEXT
About to export SGIRGEAEXT's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SGIRGEAEXT's tables via Conventional Path ...
EXP-00008: ORACLE error 1455 encountered
ORA-01455: converting column overflows integer datatype
EXP-00000: Export terminated unsuccessfully

I tried also not to exporting indexes (indexes=N), as some other blogs suggested, but no luck.

This is due to the different versions of my client export and the database target.

I tried the same export using the 11gR2 client and the export finished without errors.

Categories: 10gR2, 11gR2, oracle Tags: , ,