Tablespace datafiles question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Tablespace datafiles question

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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]

  3. #3
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    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
    anandkl

  4. #4
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    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!
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  5. #5
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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,
    OCP 8i, 9i DBA
    Brisbane Australia

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Great info. Thanks
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    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)

  8. #8
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    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.
    OCP 8i, 9i DBA
    Brisbane Australia

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width