Looking for datafile name in dictionary _TABLE_

[pss]

Hi!

I'm looking for a data dictionary _TABLE_ that has datafile names in it (sys.file$ does not have). The reason why I need only table is because dictionary views (v$datafile, dba_data_files) do not have information about deleted files (files from deleted tablespaces) and because of compatibility issues between versions 7.2, 7.3, 8.0, 8.1 and 9i.
Waiting for an answer...

Sergey.

[Shestakov]

select * from sys.FILE$;

[Edited by Shestakov on 12-12-2001 at 01:24 PM]

[pss]

It is not about _DATABASE_ name but about _DATAFILE_ names. BWT, v$database is a view (synonym for v_$database view).
Thanks, anyway.

Waiting for another answer...


Sergey.

[pss]

Originally posted by Shestakov
select * from DBA_DATA_FILES;


[Edited by Shestakov on 12-12-2001 at 01:14 PM]

I need TABLE, not a VIEW!!! Views don't have deleted datafiles inside.

[marist89]

I would be surprised if there is any table/view that has deleted files. Once you specify you want to "delete" a file, I would imagine oracle gets rid of it from the data dictionary.

[Shestakov]

Originally posted by pss

Originally posted by Shestakov
select * from DBA_DATA_FILES;


[Edited by Shestakov on 12-12-2001 at 01:14 PM]

I need TABLE, not a VIEW!!! Views don't have deleted datafiles inside.

select * from sys.FILE$;

[pss]

Originally posted by marist89
I would be surprised if there is any table/view that has deleted files. Once you specify you want to "delete" a file, I would imagine oracle gets rid of it from the data dictionary.

Ok. sys.file$ has same as sys.ts$. The tables have information about deleted tablespaces/datafile with special status column value as well as about active ones. Just do select count(*) from v$datafile and select count(*) sys.file$ against someone database that has deleted tablespace and compare the results.

[pss]

Originally posted by Shestakov
select * from sys.FILE$;
[/B]

Ok. It does not have file name inside, only status information.

Sergey.

[marist89]

Originally posted by pss

Originally posted by marist89
I would be surprised if there is any table/view that has deleted files. Once you specify you want to "delete" a file, I would imagine oracle gets rid of it from the data dictionary.

Ok. sys.file$ has same as sys.ts$. The tables have information about deleted tablespaces/datafile with special status column value as well as about active ones. Just do select count(*) from v$datafile and select count(*) sys.file$ against someone database that has deleted tablespace and compare the results.

The sys.file$.file# is not unique for the life of the instance. A particular file# will get re-used once a new tablespace is created.

For example:

SQL>  select file#, status$ from sys.file$;

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2
         8          2
         9          2
        10          2

10 rows selected.

SQL> drop tablespace indx;

Tablespace dropped.

SQL> drop tablespace test;

Tablespace dropped.

SQL> drop tablespace test2 including contents;

Tablespace dropped.

SQL> drop tablespace test3 including contents;

Tablespace dropped.

SQL> drop tablespace xyz_lmt;

Tablespace dropped.

SQL>  select file#, status$ from sys.file$;

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          1
         7          1
         8          1
         9          1
        10          1

10 rows selected.

SQL> create tablespace test1 datafile 'e:\oracle\oradata\nt817\test101.dbf' size 5M;

Tablespace created.

SQL> select file#, status$ from sys.file$;

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          1
         8          1
         9          1
        10          1

10 rows selected.

SQL> create tablespace test27 datafile 'e:\oracle\oradata\nt817\test2701.dbf' size 5M;

Tablespace created.

SQL>  create tablespace testxyz  datafile 'e:\oracle\oradata\nt817\testxyz01.dbf' size 5M;

Tablespace created.

SQL>  select file#, status$ from sys.file$;

     FILE#    STATUS$
---------- ----------
         1          2
         2          2
         3          2
         4          2
         5          2
         6          2
         7          2
         8          2
         9          1
        10          1

10 rows selected.

SQL>

Therefore, it would be impossible to get a complete history of file names that were dropped.

(Crossing my fingers that jmodic doesn't read this, because he will surely prove me wrong!)

[Edited by marist89 on 12-12-2001 at 02:41 PM]

[jmodic]

Originally posted by marist89
(Crossing my fingers that jmodic doesn't read this, because he will surely prove me wrong!)

Jeff, I really did my best, but I had no success on this one. I admit my defeat!

You are absolutely correct. And what you are saying is not true only for db files, it goes the same also for redo log files.

Originally posted by pss Ok. sys.file$ has same as sys.ts$. The tables have information about deleted tablespaces/datafile with special status column value as well as about active ones.

Here is where you are wrong, pss. Oracle really stores complete information about dropped tablespaces, but it does not do the sam for data/redolog files! When you drop the tablespace, its entry (together with tablespace name) remains in the SYS.TS$ and it can not be reused unless you create a new tablespace with the same name as the dropped one have had. While with datafile it behaves differently - the slot in SYS.FILE$ remains occupied, but it can be reused by any new datafile, therefore the datafile name is not preserved.

BTW, the way Oracle treats dropped tablespaces (by preserving their names in SYS.TS$) can be realy annoying, it can have some nasty side effects on a system where there are a lot of adding and dropping partitions. On such a system, the number of rows in SYS.TS$ can grow to a considerble amount. This by itself should not bother you too much, but each tablespace entry in SYS.C_TS# cluster occupies at least one block. And having in mind that SMON scans TS$ every five minutes, you can immagine that this can become a problem if you have couple of hundreds (or tousands?) dropped tablespaces entries whose name s are never reused. Thank god they've chosen a different strategy with daropped datafiles....