Re: ORA-01157

From: <>
Date: Mon, 07 Jul 2008 14:08:42 +0200
Message-ID: <>

On Mon, 7 Jul 2008 04:09:13 -0700 (PDT), wrote:

>Have a tablespace to repair - its a new database so dataloss no
>The guy who created it originally is "not available"
>I get this error:
>SQL> conn / as sysdba
>SQL> shutdown
>ORA-01109: database not open
>Database dismounted.
>ORACLE instance shut down.
>SQL> startup
>ORACLE instance started.
>Total System Global Area 5133828096 bytes
>Fixed Size 1985752 bytes
>Variable Size 939529000 bytes
>Database Buffers 4177526784 bytes
>Redo Buffers 14786560 bytes
>Database mounted.
>ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
>ORA-01110: data file 6:
>And quite right this file is not present - I expect it was deleted at
>the unix prompt.
>And is should not be created there in the first place I suppose.
>So I thought log in and find out which tablespace own this datafile:
>sqlplus system/XXXXX
>SQL*Plus: Release - Production on Mon Jul 7 12:04:32 2008
>Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
>ORA-01033: ORACLE initialization or shutdown in progress
>Unsurprisingly cannot log in - database not open.
>From the conn / as sysdba no joy either.
>SQL> select tablespace_name from dba_tablesspaces;
>select tablespace_name from dba_tablesspaces
> *
>ERROR at line 1:
>ORA-01219: database not open: queries allowed on fixed tables/views
>So er what can I do to troubleshoot this problem?
>Thanks In Anticipation

shutdown abort

startup mount

use ts$, file$ and possible v$datafiles (not sure about the last one)

Sybrand Bakker
Senior Oracle DBA
Received on Mon Jul 07 2008 - 07:08:42 CDT

Original text of this message