ORA-01157: Cannot identify/lock data file 203 & ORA-01110:datafile 203:'..../dbs/TS_TEMP'

Posted on 2004-11-12
Medium Priority
Last Modified: 2013-12-11

Working Oracle 9 & Linux Red Hat. I cannot access any TEM partitions & the above error are  displayed.

At the same time the SYSTEM ROLLBACK statements display a new extension with a size of 0 bytes & I do not know
how to modify such value as an error ORA-30019 is displayed when I tried to modify it.

I also did a shutdown immediate  & and then a RESTART but the same error still happens.

Can you give me any advise?




Question by:egomeze

Expert Comment

ID: 12565744
What is the exact version of Oracle 9 that you are using?

Also what are the exact (and full) error messages?

Is there any additional information in the alert log?

This sounds like it might be similar to a bug I ran into earlier this year with a tablespace (TEMP or otherwise) that extends over certain size boundries.



Author Comment

ID: 12582116

Database Version   with LINUX RED HAT         : Oracle9i Enterprise Edition Release

Detailed Errors Description:
There are several error messages for instance logging in as system/manager and trying to display the TABLE PARTITIONS
running the TOAD tool it displays:
ORA-01157: no se puede identificar/bloquear el archivo de datos 203 - consulte el archivo de rastreo del DBWR
ORA-01110: archivo de datos 203: '/oracle/app/oracle/product/9.2.0/dbs/TS_tec_temp'.

Other Table Partitions seems to be OK.

If i try to see (with TOAD)  the SYSTEM ROLLBACK SEGMENTS

Status,                                                    ONLINE
Owner                                                     SYS
Tablespace,                                             SYSTEM
Segment Id,                                             0
Block Number,                                          9
Initial Extent,                                            114688
 Next Extent,                                             0
 Min Extents,                                             1
 Max Extents,                                            32765
 Pct Increase of Next Extent,                       0
 Parallel Server Instance,                           0
 Datafile name                                          /usr1/system/.......System01.dbf

So it seems to me that NEXT EXTENSION has a size o 0 bytes and I do not know how to change that.

Regarding the ALERT.LOG file I am not sure If I Am displaying the right one (sorry) :
The file /oracle/product/9.2.0/rdbms/log/alert_BBDD01.log  is NOT DISPLAYING any errors.


LVL 48

Accepted Solution

schwertner earned 375 total points
ID: 12592882
If this is a TEMPORARY tablespace then using OEM
create anothet TEMP tablespace TEMP1, making it default temporary
and after that drop the current one.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by 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…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month13 days, 22 hours left to enroll

809 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