Re: Oracle Internals ??

From: Ron Rogers <RROGERS_at_galottery.org>
Date: Wed, 21 Jun 2000 08:08:41 -0400
Message-Id: <10535.109965@fatcity.com>

Rajesh,
Did you delete the datafile created by the "CREATE" statement using the os = delete command. The file D:\DATA\OHIR\OHIR_TEMP02.DBF' still exists and = can not be created again. You could also use the REUSE option in the = CREATE command to reuse the os file if it exists. Could be dangerous if = you make a typo and reuse a critical file. Ron
DBA
ATL.GA
>>> Rajesh_at_ohitelecom.com 06/21/00 07:34AM >>>
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
--=20
Author: Rajesh Dayal
  INET: Rajesh_at_ohitelecom.com=20

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 - 07:08:41 CDT