 |
|
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. |
 |
|
