Skip to Main Content
  • Questions
  • Can one segment contain data from more then one object & Block size of Buffer cache

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, nilesh.

Asked: December 18, 2018 - 8:51 am UTC

Last updated: December 19, 2018 - 3:14 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello Tom,
I have a few doubts regarding Segments, extents & data_block

1. Can a Segment contain data from more then one object (Table, Index or MV)
i) if no then new segments must be created with each new objects and upon DDL operations (like truncate) what happens to "High Water Mark" level.
ii) if yes then how data within segments are differentiated for different objects, and can data of one object reside within more then one segment.

2. Objects like views, procedures don't store data so they would or wouldn't have segments assigned to them.

3. Extents are logical continuous data blocks i.e. they are continuous data blocks on Hard Disk or not, and what is the difference between logical and physical continuity of data blocks.

4. The Buffer cache reads data from datafile in the size of db_block_size, so block_size within buffer cache should be same as that of db_block_size. TRUE?
i) If yes, then what will be buffer cache size if the value specified in my sp_file is not an multiple of db_block_size
ii) IF No, then what how is the block_size within buffer cache calculated. And what will be block_size within buffer cache if my db_block_size is say 8K.

and Connor said...

1. Can a Segment contain data from more then one object (Table, Index or MV)


Typically no. A special case is a cluster, where data from more than 1 table can sit in the same block, and hence the same segment. But typically one segment = one table/index/mv. Note that for a *partitioned* object, there is a segment *per partition*.

i) if no then new segments must be created with each new objects and upon DDL operations (like truncate) what happens to "High Water Mark" level.


New objects get new segments (as you create data in them). If you truncate a table, then its segment is retained by the high watermark gets reset to "zero", ie, the space can be reused by that table. We can also choose to fully release the space (ie, the segment disappears) but the moment you add new data, we'd create a fresh segment for you.

2. Objects like views, procedures don't store data so they would or wouldn't have segments assigned to them.


Correct.

3. Extents are logical continuous data blocks i.e. they are continuous data blocks on Hard Disk or not, and what is the difference between logical and physical continuity of data blocks.

From the perspective of the *database* they are continuous. The underlying OS or disk technology might not respect that, but the database would never be aware of it. It will ask the OS "Give me 8 block starting from X in file Y" and the OS/disk layer would manage the mapping if that is not truly the case.

4. The Buffer cache reads data from datafile in the size of db_block_size, so block_size within buffer cache should be same as that of db_block_size. TRUE?


Correct.

i) If yes, then what will be buffer cache size if the value specified in my sp_file is not an multiple of db_block_size


We round up to a logical size. You might say "I want 100,000,000 bytes for cache" and on start up we'll do something like: "OK, we want a multiple of 128 megabytes, so we'll round that up to 134,217,728"



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

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database