Oracle9i DBA -- Database: Fundamentals I (10)

Storage Structure and Relationships

##CONTINUE##

• The default size for PCTFREE is 10 and of PCTUSED is 40 ,and the PCTINCREASE 50
• Enabling automatic segment space management uses bitmaps instead of freelists to manage free space.
• MAXTRANS specifies the number of concurrent transactions per block.
• INITRANS, MAXTRANS,PCTFREE,PCTUSED are associated with the block.
• Increasing the PCTFREE parameter can control Row migration in blocks
• Optimal Flexible Architecture (OFA) : Oracle's recommended standard database architecture layout

  Three major rules:

  O Establish a directory structure where any database file can be stored on any disk resource

  O Separate objects with different behavior into different tablespaces

  O Maximize database reliability and performance by separating database components across different disk resources

• The Oracle blocks are the finest level of data storage identity that contains multiple operating system blocks.
• The Dictionary Managed Tablespace automatically assume the size of NEXT clause as 5 times the DB_BLOCK_SIZE and similarly INITIAL has to be twice of DB_BLOCK_SIZE
• Extents are deallocated whenever the segments are altered, dropped or truncated.
• Extents are allocated to segments when the segment are altered, created or extended.
• Bootstrap segment (Cache segment), Table , Index and IOTs have their separate segments. Nested Tables stored in their own segment.
• The effects of using TRUNCATE command are as follows:

  1. All rows in the table are deleted.

  2. No undo data is generated and the command commits implicitly because TRUNCATE TABLE is a DDL command.

  3. Corresponding indexes are also truncated.

  4. A table that is being referenced by a foreign key cannot be truncated.

  5. The delete triggers do not fire when this command is used.

  6. It reduces the number of extents allocated to the table to the original setting for MINEXTENTS.