RE: Oracle Internals ??

Hi,
not so internal.... Try to remove (from O.S.) the datafile before recreating it or add the REUSE clause to the second CREATE sentence.

HTH Joselu
www.OraEs.com
Madrid (SPAIN)

> -----Mensaje original-----
> De: Rajesh Dayal [SMTP:Rajesh_at_ohitelecom.com]
> Enviado el: miércoles 21 de junio de 2000 13:34
> Para: Multiple recipients of list ORACLE-L
> Asunto: Oracle Internals ??
>
> Hi Fellow DBAs
> Today I created a TEMP Tablespace in TEST env. Dropped the
> Tablespace, and again I tried to create the same tablespace (with same
> datafile), it gives error. I checked dba_data_files and v$datafile there
> was no entry for that datafile but there was an entry in file$. Why this
> is so ???
> Could someone explain when the entry from file$ goes off? I even
> bounced tha database but no luck? Does it mean that I will never be able
> to use a datafile with same name??
>
> Here is the sequence of commands that I executed on prompt :
>
> SQL> create tablespace OHIR_temp2 TEMPORARY datafile
> 'D:\DATA\OHIR\OHIR_TEMP02.DBF' size 10M
> default storage (
> initial 256k
> next 256k
> pctincrease 0
> );
>
> Tablespace created.
>
> SQL> DROP TABLESPACE OHIR_TEMP2 INCLUDING CONTENTS ;
>
> Tablespace dropped.
>
> SQL> create tablespace OHIR_temp2 TEMPORARY datafile
> 'D:\DATA\OHIR\OHIR_TEMP02.DBF' size 10M
> default storage (
> initial 256k
> next 256k
> pctincrease 0
> );
> create tablespace OHIR_temp2 TEMPORARY datafile
> *
> ERROR at line 1:
> ORA-01119: error in creating database file
> 'D:\DATA\OHIR\OHIR_TEMP02.DBF'
> ORA-27038: skgfrcre: file exists
> OSD-04010: <create> option specified, file already exists
>
>
> SQL> SELECT SUBSTR(FILE_NAME,1,32),SUBSTR(TABLESPACE_NAME,1,12),
> BYTES,STATUS FROM DBA_DATA_FILES ;
>
> SUBSTR(FILE_NAME,1,32) SUBSTR(TABLE BYTES STATUS
> -------------------------------- ------------ --------- ---------
> D:\DATA\OHIR\OHIRSYSTEM01.DBF SYSTEM 157286400 AVAILABLE
> D:\DATA\OHIR\OHIR_RBS01.DBF OHIR_RBS 104857600 AVAILABLE
> D:\DATA\OHIR\OHIR_TEMP01.DBF OHIR_TEMP 104857600 AVAILABLE
> D:\DATA\OHIR\OHIR_TOOLS01.DBF OHIR_TOOLS 26214400 AVAILABLE
> D:\DATA\OHIR\OHIR_USERS01.DBF OHIR_USERS 52428800 AVAILABLE
>
> 5 rows selected.
>
> SQL> SELECT FILE#,STATUS$,TS# FROM FILE$ ;
>
> FILE# STATUS$ TS#
> --------- --------- ---------
> 1 2 0
> 2 2 1
> 3 2 2
> 4 2 3
> 5 2 4
> 6 1
>
> Will appreciate your input on subject :
>
> Thanks in advance,
> Rajesh
> --
> Author: Rajesh Dayal
> INET: Rajesh_at_ohitelecom.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
Received on Wed Jun 21 2000 - 06:20:37 CDT