RE: Oracle Internals ??

From: Oracle DBA - Oweson Flynn <OracleDBA_at_flysaa.com>
Date: Wed, 21 Jun 2000 13:25:13 +0200
Message-Id: <10535.109960@fatcity.com>

Hi,

Dropping the tablespace doesn't delete the file at the operating-system level! You must do that yourself. (If you read the error message, that is exactly what it says!)

So either you must delete the file, or use the REUSE clause!

Hope this helps!

Regards
Oweson
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Oweson Flynn
Senior Oracle DBA
Email: OracleDBA_at_flysaa.com

Tel:    978-9826
Cell:   082-600-7-006
Fax:    978-1568

-----Original Message-----
From: Rajesh Dayal [mailto:Rajesh_at_ohitelecom.com] Sent: Wednesday, June 21, 2000 1:34 PM
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 - 06:25:13 CDT