Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Ram.

Asked: September 30, 2017 - 11:07 am UTC

Last updated: October 10, 2017 - 2:07 pm UTC

Version: Any

Viewed 1000+ times

You Asked

Hi TOM,

Trying to understand the difference between FILE_ID & RELATIVE_FNO in dba_data_files and dba_extents.

and Connor said...

It is a reflection of history.

Back in early versions of Oracle, the rowid (which locates a table row) had 2 bytes that referred to the file where that row was. This set a limit of 65536 files across an entire database, which set a limit on the total possible size of databases.

In Oracle 8, as databases got larger and larger we were approaching the time where people were starting to near that limit, so we altered the definition of the rowid. But of course, we didn't want to tell people "Hey, the rowid is changing, you'll need to reload all of your data". So the new rowid had to be backward compatible.

So the new rowid, modified the concept of file from "absolute" file number (in the database) to "relative" file number, ie, the file number *within* the tablespace. So until you get to a massive number of files, they will be the same, but we can now accomodate much larger databases.

A nice write up it here

http://docs.oracle.com/database/122/CNCPT/logical-storage-structures.htm#GUID-684E6324-A874-4304-8015-5634199BEE81

http://www.orafaq.com/wiki/ROWID

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ram Dittakavi, October 01, 2017 - 5:03 am UTC


Refered wiki http://www.orafaq.com/wiki/ROWID not 100% correct

Marc Martens, October 10, 2017 - 8:24 am UTC

It says that from Oracle 8 onwards the block number (in the new rowid format) has a maximum value of (2**20=1M).
How can that be? With a block size of 8K that would result into a maximum file size of 8G ... however we all know that the max file size with 8K blocks is 32G, so the block number should have 22 bits and not 20 as stated on the Wiki.
Connor McDonald
October 10, 2017 - 2:07 pm UTC

Yeah, they got their carve up of the bits slightly wrong.

You get a ceiking of 4194304 blocks in a (smallfile) datafile, so typically this means 32G for an 8k block size.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.