RE: Oracle Internals ??

From: Rajesh Dayal <Rajesh_at_ohitelecom.com>
Date: Wed, 21 Jun 2000 16:45:10 +0400
Message-Id: <10535.109968@fatcity.com>

Hi Lee,

That's great, It worked, But...

Again to reconfirm
1. I created same Tablespace OHIR_temp2 with diff. Datafile name

'D:\DATA\OHIR\OHIR_TEMP03.DBF' 2. Dropped the Tablespace OHIR_temp2.

3. Deleted the old datafile D:\DATA\OHIR\OHIR_TEMP02.DBF at OS level.

4. Recreated the Tablespace OHIR_temp2 with Datafile

'D:\DATA\OHIR\OHIR_TEMP02.DBF' 5. Now trying to create a tablespace as follow:

SQL> create tablespace OHIR_temp3 TEMPORARY datafile 'D:\DATA\OHIR\OHIR_TEMP03.DBF' size 10M default storage (

 initial      256k
 next         256k

pctincrease 0
);
create tablespace OHIR_temp3 TEMPORARY datafile *
ERROR at line 1:
ORA-01119: error in creating database file 'D:\DATA\OHIR\OHIR_TEMP03.DBF'
ORA-27038: skgfrcre: file exists
OSD-04010: <create> option specified, file already exists

When I query file$ this time the result is

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         2         5

6 rows selected.

This time again Oracle is getting the info of presence of Datafile at OS level (even after DB being Rebounced) but from where ?? (Not from file$ at least...). But may be actual check of Datafile's presence (at OS Level) gave the error this time. But why this dual behaviour from Oracle regarding storing info of dropped datafilfes in FILE$ ??? Appreciate your help :-)).(Yes REUSE is working as usual..)

Rajesh
-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Lee Robertson
Sent: Wednesday, June 21, 2000 4:08 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Internals ??

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

Regards

Lee

> -----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
> also send the HELP command for other information (like subscribing).
>

-- 
Author: Lee Robertson
  INET: Lee.Robertson_at_qspgms.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

Received on Wed Jun 21 2000 - 07:45:10 CDT