Data storage area
by Brian Laskey, David C. Kreines
Oracle Database Administration: The Essential Refe
- Dedication
- Preface
- I. DBA Tasks
- 1. Introduction
- 2. Installation
- 3. Maximizing Oracle Performance
- 4. Preventing Data Loss
- 5. Oracle Networking
- Oracle Names
- MultiProtocol Interchange
- Connecting to Non-Oracle Databases
- Network Manager Data Storage
- Using Network Manager
- Files Created by Network Manager
- Oracle Net8 Assistant
- Manual Network Configuration
- Sample SQL*Net Files
- SQL*Net Troubleshooting
- 6. Security and Monitoring
- 7. Auditing
- 8. Query Optimization
- 9. Oracle Tools
- 10. The Oracle Instance
- 11. The Oracle Database
- 12. Initialization Parameters
- 13. SQL Statements for the DBA
- SQL Commands by Task
- SQL Command Syntax
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- Reference Section
- column_constraint_clause
- auto_extend_clause
- for_clause
- filespec
- autoextend_clause
- global_index_clause
- local_index_clause
- table_constraint_clause
- table_ref_clause
- column_ref_clause
- column_constraint_clause
- index_organization_clause
- segment_attrib_clause
- partition_clause
- autoextend_clause
- 14. The Oracle Data Dictionary
- Static Data Dictionary Views
- Dynamic Performance Data Dictionary Views
- 15. System Privileges and Initial Roles
- 16. Tools and Utilities
Data storage area
The balance of the block is used for data storage—for example, to store the actual rows of a table. The calculation of the available storage in each block is not straightforward, since it is dependent on several factors, including:
Oracle blocksize (DB_BLOCK_SIZE)
Percent free space (PCTFREE)
Average row length
Number of rows stored per block
The average row length can be estimated as:
| 3 bytes row header |
| + 1 byte per non-LONG column |
| + 3 bytes per LONG column |
| + average length of all table columns |
The header space per row can be calculated as:
| 3 + (number of non-LONG columns) + |
| 3 × (number of LONG columns) |
The number of rows per block can then be calculated as:
| ((blocksize - (57 + 23 × INITRANS)) |
| - (blocksize - (57 + 23 × INITRANS)) |
| × (PCTFREE/100) - 4 - 2 × rows per block) |
| / (average row length + header space per row) |
Finally, the available space in the block can be calculated as:
| (blocksize - (57 + 23 × INITRANS)) |
| - ((blocksize - (57 + 23 × INITRANS)) × PCTFREE × 100) |
| - 4 - 2 × rows per block |
Even this description simplifies the calculations a bit, since we do not take into account the possibility of trailing NULLs, LONG strings, and so on, which may have an impact on the exact calculation.
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.