Tablespace datafiles question

[KenEwald]

When you change a datafile name, does it change it in the control file or system tablespace, or both?

Is the "real" link between a datafile and it's tablespace the file number?

Does the "mount" process read the control files to match tablespaces with datafiles?

[Shestakov]

1) Yes
2) No ( Oracle use ts# field in sys.file and sys.ts$ tables)
3) Yes

[Edited by Shestakov on 04-03-2002 at 12:19 PM]

[anandkl]

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.

cheese
anandkl

[KenEwald]

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.

That makes sense.

You dude's have some mighty big brains!

[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.

So if you do

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,

[KenEwald]

Great info. Thanks

[Shestakov]

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)

[grjohnson]

Oracle doesn't remove the TABLESPACE name from the ts$ table and Oracle recomments NOT removing the row from the ts$ table.

So I still think yes... and yes. It's basically something that exists be we shouldn't but we shouldn;t be worried by it.