BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 
 

REBUILD_FREELISTS

Oracle Tips by Burleson Consulting

By Mike Ault

The procedure REBUILD_FREELISTS is used to rebuild the freelists of tables and redistribute them evenly among the multiple freelists. This procedure has four possible inputs, two of which have default values. Fortunately, for this procedure, Oracle put the defaults at the end of the variable list so positional naming isn’t required. However, the developer who created all of these procedures has obviously never heard of the UPPER function, so you must specify your arguments in uppercase or an error will occur. A sample run of this procedure is shown in Listing 15.5.

 Argument Name      Type                    In/Out Default?
 ------------------ ----------------------- ------ --------
 SCHEMA_NAME        VARCHAR2                IN   
 OBJECT_NAME        VARCHAR2                IN   
 PARTITION_NAME     VARCHAR2                IN     NULL
 OBJECT_TYPE        BINARY_INTEGER          IN     TABLE_OBJECT
      

LISTING 15.5      Example run of the REBUILD_FREELISTS procedure.

SQL> execute dbms_repair.rebuild_freelists('GRAPHICS_Remote DBA',
'INTERNAL_GRAPHICS');

PL/SQL procedure successfully completed.

If there is only one freelist group, the master freelist is updated with all free blocks; the other freelists are zeroed. If the object has multiple freelist groups then the master freelist in each freelist group is updated in a round-robin fashion and the rest of the freelists are zeroed. My question: Since this procedure will be required to be executed after any run of the FIX_CORRUPT_BLOCKS procedure, why wasn’t the functionality simply added to that procedure?

SEGMENT_FIX_STATUS

The procedure SEGMENT_FIX_STATUS was added to the DBMS_REPAIR package in version 9i. SEGMENT_FIX_STATUS corrects bitmap entry problems caused by corrupt blocks. Oracle took notice of all the complaints generated from its lack of concern for calling values being out of order (nullable intermixed with nonnullable) and have sequenced the nondefault values first. This means you don't have to use the positional calling nomenclature when using SEGMENT_FIX_STATUS. The calling parameters for SEGMENT_FIX_STATUS are:

DBMS_REPAIR.SEGMENT_FIX_STATUS (
   segment_owner   IN VARCHAR2,
   segment_name    IN VARCHAR2,
   segment_type    IN BINARY_INTEGER DEFAULT TABLE_OBJECT,
   file_number     IN BINARY_INTEGER DEFAULT NULL,
   block_number    IN BINARY_INTEGER DEFAULT NULL,
   status_value    IN BINARY_INTEGER DEFAULT NULL,
   partition_name  IN VARCHAR2 DEFAULT NULL,);

The parameters for the SEGMENT_FIX_STATUS procedure are defined as:

schema_owner. Schema name (owner) of the segment. 

segment_name. Segment name.  

partition_name. (Optional) Name of an individual partition. NULL for nonpartitioned objects. Default is NULL. 

segment_type. (Optional) Type of the segment (for example, TABLE or INDEX). Default is NULL. 

file_number. (Optional) The tablespace-relative file number of the data block whose status has to be fixed. If omitted, all the blocks in the segment will be checked for state correctness and fixed.  

block_number. (Optional) The file-relative file number of the data block whose status has to be fixed. If omitted, all the blocks in the segment will be checked for state correctness and fixed.  

status_value. (Optional) The value to which the block status described by the file_number and block_number will be set. If omitted, the status will be set based on the current state of the block. This is almost always the case, but if there is a bug in the calculation algorithm, the value can be set manually. Status values: 

   1 = block is full

   2 = block is 0-25 percent free

   3 = block is 25-50 percent free

   4 = block is 50-75 percent free

   5 = block is 75-100 percent free

The status for bitmap blocks, segment headers, and extent map blocks cannot be altered. The status for blocks in a fixed hash area cannot be altered. For index blocks, there are only two possible states: 1 = block is full, and 3 = block has freespace. 

SKIP_CORRUPT_BLOCKS  

The final procedure in the DBMS_REPAIR package is the SKIP_CORRUPT_BLOCKS procedure. The SKIP_CORRUPT_BLOCKS procedure is used to mark the corrupt blocks software corrupt and tell Oracle to skip those blocks during table and index scans. If the object specified is a cluster, it applies to all of the tables in the cluster and their respective indexes. The SKIP_CORRUPT_BLOCKS procedure has four possible inputs, two of which have default values. Fortunately, Oracle put the defaults at the end of the variable list so positional naming isn’t required. However, the developer who created all of these procedures has obviously never heard of the UPPER function, so you must specify your arguments in uppercase or an error will occur. A sample run of the SKIP_CORRUPT_BLOCKS procedure is shown in Listing 15.6.

 Argument Name     Type                    In/Out DefaultValue?
 ----------------- ----------------------- ------ --------
 SCHEMA_NAME       VARCHAR2                IN   
 OBJECT_NAME       VARCHAR2                IN   
 OBJECT_TYPE       BINARY_INTEGER          IN     TABLE_OBJECT
 FLAGS             BINARY_INTEGER          IN     SKIP_FLAG

LISTING 15.6 Example run of the SKIP_CORRUPT_BLOCKS procedure.

SQL> execute dbms_repair.skip_corupt_blocks('GRAPHICS_Remote DBA',
'INTERNAL_GRAPHICS');

 PL/SQL procedure successfully completed.


See Code Depot for Full Scripts


This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

   
 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.



Hit Counter