Message-Id: <10535.109949@fatcity.com> From: Rajesh Dayal Date: Wed, 21 Jun 2000 14:14:43 +0400 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: 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 :