Introduction to Data Blocks, Extents, and Segments

Overview of Data Blocks

Oracle manages the storage space in the datafiles of a database in units called data blocks. A data block is the smallest unit of data used by a database. In contrast, at the physical, operating system level, all data is stored in bytes. Each operating system has a block size. Oracle requests data in multiples of Oracle data blocks, not operating system blocks.

The standard block size is specified by the DB_BLOCK_SIZE initialization parameter


Data Block Format

The Oracle data block format is similar regardless of whether the data block contains table, index, or clustered data. Figure 2-2illustrates the format of a data block.
Figure 2-2 Data Block Format
Description of Figure 2-2 follows
 
 

PCTFREE, PCTUSED, and Row Chaining



The PCTFREE Parameter

The PCTFREE parameter sets the minimum percentage of a data block to be reserved as free space for possible updates to rows that already exist in that block. For example, assume that you specify the following parameter within a CREATE TABLE statement:
PCTFREE 20
This states that 20% of each data block in this table's data segment be kept free and available for possible updates to the existing rows already within each block. New rows can be added to the row data area, and corresponding information can be added to the variable portions of the overhead area, until the row data and overhead total 80% of the total block size. Figure 2-3illustrates PCTFREE.
Figure 2-3 PCTFREE
Description of Figure 2-3 follows
 

The PCTUSED Parameter

The PCTUSED parameter sets the minimum percentage of a block that can be used for row data plus overhead before new rows are added to the block. After a data block is filled to the limit determined by PCTFREE, Oracle considers the block unavailable for the insertion of new rows until the percentage of that block falls beneath the parameter PCTUSED. Until this value is achieved, Oracle uses the free space of the data block only for updates to rows already contained in the data block. For example, assume that you specify the following parameter in a CREATE TABLE statement:
PCTUSED 40
In this case, a data block used for this table's data segment is considered unavailable for the insertion of any new rows until the amount of used space in the block falls to 39% or less (assuming that the block's used space has previously reached PCTFREE). Figure 2-4 illustrates this.
Figure 2-4 PCTUSED
Description of Figure 2-4 follows
Description of "Figure 2-4 PCTUSED"
How PCTFREE and PCTUSED Work Together
PCTFREE and PCTUSED work together to optimize the use of space in the data blocks of the extents within a data segment. Figure 2-5 illustrates the interaction of these two parameters.
Figure 2-5 Maintaining the Free Space of Data Blocks with PCTFREE and PCTUSED
Description of Figure 2-5 follows
 
 
After you issue a DELETE or UPDATE statement, Oracle processes the statement and checks to see if the space being used in the block is now less than PCTUSED. If it is, then the block goes to the beginning of the transaction free list, and it is the first of the available blocks to be used in that transaction. When the transaction commits, free space in the block becomes available for other transactions
 
 
Source: ORACLE Documents

Comments

Post a Comment

Popular posts from this blog

Queries For Oracle Interface Errors Records.

Below are the queries to get the error records information while processing interface data. AP Invoices:   SELECT    airl.last_update_date process_date,    HU.NAME,   nvl(ail.invoice_line_id,aii.invoice_id) process_id,   'AP' module,   'Import AP Invoice' interface_name,   aii.source source,   NVL(SUBSTR( ail.dist_code_concatenated,1,instr(ail.dist_code_concatenated,'.',1,1)-1),'ALL') company,   'ERROR' status,   ail.line_type_lookup_code ,   airl.parent_table ,   ail.description ,   NVL(ail.distribution_set_name,dist_code_concatenated) ,   pvsa.vendor_site_code ,   listagg(NVL(airl.reject_lookup_code,'AP_INV_ERROR') ,';') within group (order by NVL(airl.reject_lookup_code,'AP_INV_ERROR')) error_code,   listagg(flv.meaning,';')  within group (order by NVL(flv.meaning,'AP_INV_ERROR'))error_summary,   NULL error_details,   pv.vendor_name ,   pv.segment1 ,   aii.invoice_amount,   aii.i
Read more

Customising PO Output For Communication Report in Oracle Purchasing

Image
PO Print PDF Report: Oracle EBS provided  a standard functionality to print the PO report directly from the PO form itself by clicking tools menu and view PDF option. Once we select that option output will be rendered in PDF format in the browser instantly. Internally it will run the concurrent program 'PO Output For Communication'. This concurrent program is of JAVA type which will uses the seeded package  'PO_COMMUNICATION_PVT'  for generating the XML data and later this XML data will be processed and formatted by using the XSL template of data definition 'Standard Purchase Order Data Source' in case of standard PO's. Oracle by default will provide one standard BI publisher template for this. In case if we want to have custom layout for this report we can build a new template for the same data definition and this template has to be used while doing the setup for PO Document types. Creating Custom Template and Setup this for PO Document type: To c
Read more

Oracle APPS Useful Queries

                       ACCOUNT PAYABLES Payable Open Interface Rejection:   SELECT ail.* FROM AP_INVOICES_INTERFACE ai ,   ap_invoice_lines_interface ail ,   ap_interface_rejections AIR WHERE ai.GROUP_ID='EO_AUG-13_010813_443' AND ai.status    ='REJECTED' AND ai.invoice_id=ail.invoice_id   --and ail.dist_code_concatenated='66810-00-999-00000000-00000000-00000-FI100' AND air.parent_id = DECODE(air.parent_table,'AP_INVOICES_INTERFACE',AI.INVOICE_ID,'AP_INVOICE_LINES_INTERFACE',ail.invoice_line_id,NULL) ORDER BY 2; AP Invoices Query: /*+INDEX (AP_INVOICES_ALL [XXDG_TEST]) */ SELECT  apia.invoice_id,        alc.displayed_field "Invoice Type",        apia.invoice_num "Invoice Number",        apia.invoice_date "Invoice Date",        apia.gl_date   "Invoice GL Date",        nvl(apia.VOUCHER_NUM,doc_sequence_value)"Voucher Number",        apia.description inv_Description,  
Read more