RE: Oracle Internals ??

You have to physically remove the datafile from the operating system after dropping it.

Regards

Lee         

+--------------------------------------------------------------------+
	 | Lee Robertson	SMS Platinum Services Manager	|
	 | E'Mail	Lee_Robertson_at_qspgms.com	|
	 | Direct Dial	(+44) 0191 4023292		|
	 | Facsimile	(+44) 0191 4023028		|
	
+--------------------------------------------------------------------+
	 | QSP Ltd				|
	 | Talipot House				|
	 | 5th Avenue Business Park			|
	 | Team Valley, Gateshead			|
	 | Tyne & Wear, NE11 0XA			|	
 
+---------------------------------------------------------------------+

> -----Original Message-----
> From: Rajesh Dayal [mailto:Rajesh_at_ohitelecom.com]
> Sent: 21 June 2000 12: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
Received on Wed Jun 21 2000 - 05:56:40 CDT