If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Yes when u make any changes to the datafile it will be registered in the control file.And during the mount oracle will read the control file and during open it will match info. in the control file and datafile header to open the database.

So, when a database is opened, it reads the control file first, then checks the datafile headers. The datafiles are assembled into tablespaces with the information kept in the system tablespace objects.

Except Oracle isn't smart enought to clean up the sys.ts$ table when a TABLESPACE has been dropped. So in the sys.ts$ table you'll find ALL tablespace that have been created in your database was created... even if they currently do not exist.

Originally posted by grjohnson Except Oracle isn't smart enought to clean up the sys.ts$ table when a TABLESPACE has been dropped. So in the sys.ts$ table you'll find ALL tablespace that have been created in your database was created... even if they currently do not exist.
SELECT filetab.FILE#, filetab.ts#, ts.ts#, ts.name FROM sys.FILE$ filetab, sys.ts$ ts
WHERE filetab.ts# (+) = ts.ts#
AND filetab.FILE# IS NULL
ORDER BY ts.name
You'll find the current tablespace in your database with NO associated datafiles.
Cheers,

Yes and no.
Oracle use in table ts$ field ONLINE$=3 for seting tablespace, that hasn't any files
(this ts marks as INVALID)