RE: Oracle Internals ??

Rajesh,

seems that rows are not deleted from file$ upon tablespace deletion, only the status$ column is updated from 2 (AVAILABLE) to 1 (INVALID).

Since the ts# column is also updated to null, the "logically deleted" row does not appear in dba_data_files view, that joins file$ and ts$ on the ts# column.
v$datafile view reads directly from controlfile, therefore it reflects the deletion immediately.

Unfortunately I dont know when entries from file$ are deleted (if ever).

The same story is with ts$ table, where rows are updated on tablespace delete, and you can see the whole history of past tablespaces in ts$.

Milos Martinovic
DBA -----Original Message-----
From: Rajesh Dayal [mailto:Rajesh_at_ohitelecom.com] Sent: Thursday, June 22, 2000 8:06 AM
To: Multiple recipients of list ORACLE-L Subject: RE: Oracle Internals ??

I am very much convinced.

 But this time the problem is that the file entry is not shown in FILE$. While earlier after dropping the TS the file entry was shown in FILE$. Can someone tell that on what conditions the entry from FILE$ is deleted, One thing is sure that entries from dba_data_files and v$datafile is deleted immediately after delete from Oracle.

Thanks for your replies. (Probably this could be one of the Oracle Internal questions).

Rajesh

-----Original Message-----
Robertson
Sent: Wednesday, June 21, 2000 6:30 PM
To: Multiple recipients of list ORACLE-L

You are still leaving a datafile at OS level. You now need to remove 'D:\DATA\OHIR\OHIR_TEMP03.DBF'
before creating your new tablespace.

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 14:51
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Oracle Internals ??
>
>
> 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-----
> Robertson
> Sent: Wednesday, June 21, 2000 4:08 PM
> To: Multiple recipients of list ORACLE-L
>
>
> 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
> (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: 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
(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: 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