RE: Oracle Internals ??

you have to use the reuse option in order to create the same datafile which is having the same name,even if you drop the tablespace,it drops it from the dictionary but not from the operating system.either you will have to physically remove it or say reuse when you you are creating the same datafile

>From: ddorr.cs_at_clearstream.com
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: RE: Oracle Internals ??
>Date: Wed, 21 Jun 2000 05:36:09 -0800
>
>This has nothing to see with oracle internals :
>
>Did you try : oerr ORA 27038
>Did you look at the CREATE TABLESPACE command syntax.
>
>Hint : look at the REUSE option.
>
>
>
> > ----------
> > From: Rajesh Dayal[SMTP:Rajesh_at_ohitelecom.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Wednesday, June 21, 2000 13:34
> > To: Multiple recipients of list ORACLE-L
> > Subject: 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
> > also send the HELP command for other information (like subscribing).
> >
>--
>Author:
> INET: ddorr.cs_at_clearstream.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
>also send the HELP command for other information (like subscribing).