|
|
DBMS_REPAIR
tips
Oracle Tips by Burleson Consulting |
DBMS_REPAIR Procedures
The DBMS_REPAIR package contains six
procedures (as of this writing) that are usable callable and no user
callable functions.
For additional information on handling
corruption, see the
BC expert notes on corruption and see MOSC Note 1088018.1 -
Handling Oracle Database Corruption Issues.
See additional articles on dbms_repair:
Tips on Oracle
dbms_repair utility
Description of the DBMS_REPAIR package
Configuring the environment for the dbms_repair utility
The procedures are listed in table 4 and the details of their use
delineated in the sections that follow.
Procedure Name |
Description |
ADMIN_TABLES |
Allows for the administrative functions
for the DBMS_REPAIR repair and orphan key tables such as create,
purge and drop. |
CHECK_OBJECT |
Used to detect and report corruptions in
tables or indexes. |
DUMP_ORPHAN_KEYS |
Used to report on index entries that point
to rows in corrupt table blocks. |
FIX_CORRUPT_BLOCKS |
Marks blocks as software corrupt that have
been flagged as corrupt by CHECK_OBJECT. |
REBUILD_FREELISTS |
Rebuilds and objects freelists. |
SKIP_CORRUPT_BLOCKS |
For tables and indexes with corrupt
blocks, tells Oracle to either ignore the blocks during scans or
to raise the ORA-01578 error. |
Table 4: Procedures in DBMS_REPAIR
The following sections describe each procedure
and give an example of its use.
The
dbms_repair Utility
Dbms_repair
is
a utility that can detect and repair block corruption within Oracle.
It is provided by Oracle as part of the standard database
installation.
Configuring the Environment
Two
tables must first be created under the SYS schema before the dbms_repair
utility can be used.
Fortunately, a procedure in the package itself (admin_tables)
creates these tables and eliminates the need to hunt for a script in
$ORACLE_HOME/rdbms/admin.
dbms_repair.ADMIN_TABLES (
table_name
IN VARCHAR2,
table_type
IN
BINARY_INTEGER,
action
IN
BINARY_INTEGER,
tablespace
IN VARCHAR2
DEFAULT NULL);
-
table_name – The name of the table to
be processed, as determined by the action
-
table_type – Either orphan_table or
repair_table
-
action – Either create_action,
purge_action or drop_action.
When create_action is specified, the table will be created in the SYS
schema. Purge_action deletes
all rows in the table that apply to objects that no longer exist.
Drop_action will drop the table.
-
tablespace – The tablespace in which
the newly created table will reside.
This tablespace must already exist.
The
following command will be used to create the two tables needed.
The command will be executed twice with different parameters, once for
the
repair table and once for the
orphan table.
begin
dbms_repair.admin_tables(
table_name => 'REPAIR_TEST',
table_type => dbms_repair.repair_table,
action
=> dbms_repair.create_action,
tablespace => 'SCOTTWORK'
);
end;
begin
dbms_repair.admin_tables(
table_name => 'ORPHAN_TEST',
table_type => dbms_repair.orphan_table,
action
=> dbms_repair.create_action,
tablespace => 'SCOTTWORK'
);
end;
The
two tables are now created.
A describe of the two tables reveals the following:
Name
Null?
Type
-----------------------------------------
-------- ---------------
OBJECT_ID
NOT NULL NUMBER
TABLESPACE_ID
NOT NULL NUMBER
RELATIVE_FILE_ID
NOT NULL NUMBER
BLOCK_ID
NOT NULL NUMBER
CORRUPT_TYPE
NOT NULL NUMBER
SCHEMA_NAME
NOT NULL VARCHAR2(30)
OBJECT_NAME
NOT NULL VARCHAR2(30)
BASEOBJECT_NAME
VARCHAR2(30)
PARTITION_NAME
VARCHAR2(30)
CORRUPT_DESCRIPTION
VARCHAR2(2000)
REPAIR_DESCRIPTION
VARCHAR2(200)
MARKED_CORRUPT
NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP
NOT NULL DATE
FIX_TIMESTAMP
DATE
REFORMAT_TIMESTAMP
DATE
SQL> desc orphan_test
Name
Null?
Type
-----------------------------------------
-------- ---------------
SCHEMA_NAME
NOT NULL VARCHAR2(30)
INDEX_NAME
NOT NULL VARCHAR2(30)
IPART_NAME
VARCHAR2(30)
INDEX_ID
NOT NULL NUMBER
TABLE_NAME
NOT NULL VARCHAR2(30)
PART_NAME
VARCHAR2(30)
TABLE_ID
NOT NULL NUMBER
KEYROWID
NOT NULL ROWID
KEY
NOT NULL ROWID
DUMP_TIMESTAMP
NOT NULL DATE
Repair tables will contain those objects that have corrupted blocks.
Orphan tables, on the other hand, are used to contain indexes that
point to corrupted data
Finding Corrupt Blocks
The
dbms_repair utility provides a mechanism to search for corrupt database blocks.
Below is the syntax for the
check_objects procedure. Note that the only OUT
parameter is the
corrupt_count.
dbms_repair.CHECK_OBJECT (
schema_name
IN VARCHAR2,
object_name
IN VARCHAR2,
partition_name
IN VARCHAR2
DEFAULT NULL,
object_type
IN BINARY_INTEGER
DEFAULT TABLE_OBJECT,
repair_table_name IN
VARCHAR2
DEFAULT 'REPAIR_TABLE',
flags
IN BINARY_INTEGER
DEFAULT NULL,
relative_fno
IN BINARY_INTEGER
DEFAULT NULL,
block_start
IN BINARY_INTEGER
DEFAULT NULL,
block_end
IN BINARY_INTEGER
DEFAULT NULL,
corrupt_count
OUT BINARY_INTEGER);
-
schema_name – Schema name of the object
to be checked for corruption.
-
object_name – Name of the table or
index that will be checked for corruption.
-
partition_name – Partition or
sub-partition name to be checked.
-
object_type – Either TABLE_OBJECT or
INDEX_OBJECT as specified as an enumeration (dbms_repair.table_object).
-
repair_table_name – The name of the
repair table to be populated in the SYS schema.
-
flags – Not used.
-
relative_fno – The relative file number
to be used when specifying a block range to be checked.
-
block_start – The first block in the
block range to begin checking.
-
block_end – The last block in the block
range to check.
-
corrupt_count
– The number of corrupt blocks discovered.
The
code below will check the
scott.employee table for corruption and report the number of
corrupted blocks.
set serveroutput on
declare corr_count binary_integer;
begin
corr_count := 0;
dbms_repair.CHECK_OBJECT (
schema_name
=> 'SCOTT',
object_name
=> 'EMPLOYEE',
partition_name
=> null,
object_type
=> dbms_repair.table_object,
repair_table_name => 'REPAIR_TEST',
flags
=> null,
relative_fno
=> null,
block_start
=> null,
block_end
=> null,
corrupt_count
=> corr_count
);
dbms_output.put_line(to_char(corr_count));
end;
/
# Corrupt Blocks =0
PL/SQL procedure successfully completed.
Once executed, the table
repair_test can be queried in order to find more about corrupt
blocks. In this case, no rows
exist in the table. The repair
table is only populated if the
check_object procedure did indeed find corrupt blocks, so no
rows in this table is good news!
ADMIN_TABLES
The ADMIN_TABLES procedure has the following
input variables and no output variables as shown in table 5:
Argument |
Type |
In/Out |
Default Value |
TABLE_NAME |
VARCHAR2 |
IN |
GENERATE_DEFAULT_TABLE_NAME |
TABLE_TYPE |
BINARY_INTEGER |
IN |
|
ACTION |
BINARY_INTEGER |
IN |
|
TABLESPACE |
VARCHAR2 |
IN |
NULL |
Table 5: ADMIN_TABLES Input Variables
The procedure is used to create, purge and
drop the REPAIR_TABLE and ORPHAN_KEY_TABLE which are used during the
repair of database tables and indexes. If the TABLE_TYPE is set to
REPAIR_TABLE then the GENERATE_DEFAULT_TABLE_NAME setting tells the
procedure to set the table name to REPAIR_TABLE if the TABLE_TYPE is
set to ORPHAN_TABLE then the TABLE_NAME is set to ORPHAN_KEY_TABLE by
the procedure if the GENERATE_DEFAULT_TABLE_NAME value is entered.
This procedure is a good example why defaulted values should be placed
at the end of your argument list. Since the TABLE_NAME attribute is
first it means that to use the default for it you must specify the
positional naming for all other parameters in order to use the default
value, take a look at Listing 7 to see what I mean.
Listing 7 Example Use of
DBMS_REPAIR.ADMIN_TABLES procedure.
First, let's build a repair table. Note that
the name specified for the table is upper case, this is required, if
you specify the name in lower case you will get an error on exception
24129.
SQL> execute
dbms_repair.admin_tables( 'REPAIR_TABLE',dbms_repair.repair_table,dbms_repair.create_action);
PL/SQL
procedure successfully completed.
SQL> desc
repair_table
Name Null? Type
----------------------------------------------------- --------
---------------
OBJECT_ID NOT NULL NUMBER
TABLESPACE_ID NOT NULL NUMBER
RELATIVE_FILE_ID NOT NULL NUMBER
BLOCK_ID NOT NULL NUMBER
CORRUPT_TYPE NOT NULL NUMBER
SCHEMA_NAME NOT NULL
VARCHAR2(30)
OBJECT_NAME NOT NULL
VARCHAR2(30)
BASEOBJECT_NAME
VARCHAR2(30)
PARTITION_NAME
VARCHAR2(30)
CORRUPT_DESCRIPTION
VARCHAR2(2000)
REPAIR_DESCRIPTION
VARCHAR2(200)
MARKED_CORRUPT NOT NULL
VARCHAR2(10)
CHECK_TIMESTAMP NOT NULL DATE
FIX_TIMESTAMP DATE
REFORMAT_TIMESTAMP DATE
Now let's create an orphan key table. The same
admonishment about use of upper case applies.
SQL> execute
dbms_repair.admin_tables('ORPHAN_KEY_TABLE',dbms_repair.orphan_table,dbms_repair.create_action);
PL/SQL
procedure successfully completed.
SQL> desc
orphan_key_table
Name Null? Type
----------------------------------------------------- --------
---------------------------------
SCHEMA_NAME NOT NULL
VARCHAR2(30)
INDEX_NAME NOT NULL
VARCHAR2(30)
IPART_NAME
VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL
VARCHAR2(30)
PART_NAME
VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
Here is an example of using the 'GENERATE_DEFAULT_TABLE_NAME
default value.
SQL> execute
dbms_repair.admin_tables('GENERATE_DEFAULT_TABLE_NAME',dbms_repair.orphan_table,
dbms_repair.create_action);
PL/SQL
procedure successfully completed.
SQL> desc
orphan_key_table;
Name Null? Type
----------------------------------------------------- --------
---------------
SCHEMA_NAME NOT NULL
VARCHAR2(30)
INDEX_NAME NOT NULL
VARCHAR2(30)
IPART_NAME
VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL
VARCHAR2(30)
PART_NAME
VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
And here is an example using the default
value, which requires the positional specification type procedure
call. If the defaults would have been placed after the required
fields, this could have been avoided.
SQL> execute
dbms_repair.admin_tables(
table_type=>dbms_repair.orphan_table,action=>dbms_repair.drop_action);
PL/SQL procedure successfully completed.
SQL> execute
dbms_repair.admin_tables( table_type=>dbms_repair.orphan_table,action=>dbms_repair.create_action);
PL/SQL
procedure successfully completed.
SQL> desc
orphan_key_table
Name Null? Type
----------------------------------------------------- --------
---------------
SCHEMA_NAME NOT NULL
VARCHAR2(30)
INDEX_NAME NOT NULL
VARCHAR2(30)
IPART_NAME
VARCHAR2(30)
INDEX_ID NOT NULL NUMBER
TABLE_NAME NOT NULL
VARCHAR2(30)
PART_NAME
VARCHAR2(30)
TABLE_ID NOT NULL NUMBER
KEYROWID NOT NULL ROWID
KEY NOT NULL ROWID
DUMP_TIMESTAMP NOT NULL DATE
The other actions such as purge, are accessed
the same way as is demonstrated in Listing 7 for the CREATE_ACTION.
CHECK_OBJECT
The CHECK_OBJECT procedure has up to nine
possible input values and one output value. Again, due to placement of
the arguments in order to use the default values you must use
positional specifications for any calls to this procedure unless you
specify values for all of the parameters. Table 6 shows the parameters
for the CHECK_OBJECT procedure.
Argument |
Type |
In/Out |
Default? |
SCHEMA_NAME |
VARCHAR2 |
IN |
|
OBJECT_NAME |
VARCHAR2 |
IN |
|
PARTITION_NAME |
VARCHAR2 |
IN |
NULL |
OBJECT_TYPE |
BINARY_INTEGER |
IN |
TABLE_OBJECT |
REPAIR_TABLE_NAME |
VARCHAR2 |
IN |
REPAIR_TABLE |
FLAGS |
BINARY_INTEGER |
IN |
NULL |
BLOCK_START |
BINARY_INTEGER |
IN |
NULL |
BLOCK_END |
BINARY_INTEGER |
IN |
NULL |
CORRUPT_COUNT |
BINARY_INTEGER |
OUT |
|
Table 6: CHECK_OBJECT Procedure IN and OUT
Parameters
As you can see from doing a DESCRIBE on the
procedure, the positioning of the CORRUPT_COUNT OUT variable after all
of the values that have default values will force us to use positional
nomenclature or specify values for all of the required input
variables. Sometimes I wish the guys that write this stuff had to use
it on a daily basis. AN example run of the procedure CHECK_OBJECT is
shown in Listing 8. Another problem is that if you have more than
32,767 problem entries this procedure will fail on numeric overflow
since the output is specified as a BINARY_INTEGER instead of a plain
old NUMBER.
Listing 8 Example Execution of the
CHECK_OBJECT Procedure
Can we get by without specifying the out
value?
SQL> execute
dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS');
BEGIN dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS');
END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHECK_OBJECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Nope, I guess not, let's put a variable and
see what happens.
SQL> var x
number;
SQL> execute
dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS',:x);
BEGIN dbms_repair.check_object('GRAPHICS_DBA','INTERNAL_GRAPHICS',:x);
END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'CHECK_OBJECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
Nope, Oracle is too smart for us. Let's use
positional notation once again due to the inconsideration of the
developer:
SQL> execute
dbms_repair.check_object(schema_name=>'GRAPHICS_DBA',
object_name=>'INTERNAL_GRAPHICS', corrupt_count=>:x);
PL/SQL
procedure successfully completed.
SQL> print x
X
---------
0
So all that work just to find out we don't
have a problem?oh well, I guess it is better not to have a problem.
Dbms_repair
offers procedures that enable
potential detection and repairing of
corrupt blocks in tables and indexes.
Nevertheless, DBAs must judge for
themselves whether the database
corruption is significant enough to
warrant using RMAN for a recovery
versus attempting to correct via
dbms_repair. For
example, a restore offers a 100%
guaranteed consistent recover within a
generally predictable timeframe. The
DBA might spend time with
dbms_repair to try to
fix the isolated problems and still
end up needing to do the recovery. So
examine closely the nature and extent
of the problem and what time
constraints must be met. Then pick
appropriately. For those who do decide
to use
dbms_repair, there a few
minor but key limitations to be keenly
aware of:
-
Index-organized tables and LOB
indexes are not supported
-
LOB and out-of-line columns, i.e.
nested tables and varrays, are not
supported
-
Check_object does not work for
clusters
-
Dump_orphan_keys does not work for
bitmap or function-based indexes
-
Dump_orphan_keys does not work for
indexes where length > 3950 bytes
There are a few enumerated constants
that one must know to use this
package:
--
Object Type Specification
TABLE_OBJECT
constant binary_integer := 1;
INDEX_OBJECT
constant binary_integer := 2;
CLUSTER_OBJECT
constant binary_integer := 4;
-- Flags
Specification
SKIP_FLAG
constant binary_integer :=
1;
NOSKIP_FLAG
constant binary_integer :=
2;
-- Admin Action
Specification
CREATE_ACTION
constant binary_integer := 1;
PURGE_ACTION
constant binary_integer :=
2;
DROP_ACTION
constant binary_integer :=
3;
-- Admin Table
Type Specification
REPAIR_TABLE
constant binary_integer :=1;
ORPHAN_TABLE
constant binary_integer :=2;
-- Object Id
Specification
ALL_INDEX_ID
constant binary_integer :=0;
-- Lock Wait
Specification
LOCK_NOWAIT
constant binary_integer := 0;
LOCK_WAIT
constant binary_integer :=
1;
Rather than simply listing the package
procedure and functions available, a
simple workflow of the common use
cases will be shown because how and
what is done depends upon the status
of the database. So it is more
important to understand the workflow
required than anything else.
First, handle some mandatory
prerequisite activities. Much like
doing explain plans (plan_table)
and table constraint checking
(EXCEPTIONS INTO), the
dbms_repair package
requires the use of some highly
specific tables to record its
findings. So use the
admin_table procedure to
create them. Below are the parameters
for this procedure and an example.
Argument
|
Type
|
In / Out
|
Default Value
|
TABLE_NAME
|
VARCHAR2
|
IN
|
GENERATE_DEFAULT_TABLE_NAME
|
TABLE_TYPE
|
BINARY_INTEGER
|
IN
|
|
ACTION
|
BINARY_INTEGER
|
IN
|
|
TABLESPACE
|
VARCHAR2
|
IN
|
NULL
|
Table 6.129:
Admin_table Parameters
SQL> execute
dbms_repair.admin_tables
('REPAIR_TABLE',
dbms_repair.repair_table,
dbms_repair.create_action);
PL/SQL procedure
successfully completed.
SQL> desc
repair_table
Name
Null?
Type
-----------------------------------------
--------
----------------------------
OBJECT_ID
NOT NULL NUMBER
TABLESPACE_ID
NOT NULL NUMBER
RELATIVE_FILE_ID
NOT NULL NUMBER
BLOCK_ID
NOT NULL NUMBER
CORRUPT_TYPE
NOT NULL NUMBER
SCHEMA_NAME
NOT NULL VARCHAR2(30)
OBJECT_NAME
NOT NULL VARCHAR2(30)
BASEOBJECT_NAME
VARCHAR2(30)
PARTITION_NAME
VARCHAR2(30)
CORRUPT_DESCRIPTION
VARCHAR2(2000)
REPAIR_DESCRIPTION
VARCHAR2(200)
MARKED_CORRUPT
NOT NULL VARCHAR2(10)
CHECK_TIMESTAMP
NOT NULL DATE
FIX_TIMESTAMP
DATE
REFORMAT_TIMESTAMP
DATE
And if the problem requires orphaned
index pointer-type repairs, then
create a table for that need as well
as shown here.
SQL> execute
dbms_repair.admin_tables
('ORPHAN_KEY_TABLE',
dbms_repair.orphan_table,
dbms_repair.create_action);
PL/SQL procedure
successfully completed.
SQL> desc
orphan_key_table
Name
Null?
Type
-----------------------------------------
--------
----------------------------
SCHEMA_NAME
NOT NULL VARCHAR2(30)
INDEX_NAME
NOT NULL VARCHAR2(30)
IPART_NAME
VARCHAR2(30)
INDEX_ID
NOT NULL NUMBER
TABLE_NAME
NOT NULL VARCHAR2(30)
PART_NAME
VARCHAR2(30)
TABLE_ID
NOT NULL NUMBER
KEYROWID
NOT NULL ROWID
KEY
NOT NULL ROWID
DUMP_TIMESTAMP
NOT NULL DATE
Second, verify if there are any
problems. Even if the database has
reported an error on some other
operation that indicates a problem,
verify that the error can be seen and
fixed with
dbms_repair. So the
first step is to examine a suspect
object to determine its status using
the
check_object procedure.
Below are the parameters for this
procedure and an example.
Argument
|
Type
|
In / Out
|
Default Value
|
SCHEMA_NAME
|
VARCHAR2
|
IN
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
OBJECT_TYPE
|
BINARY_INTEGER
|
IN
|
TABLE_OBJECT
|
REPAIR_TABLE_NAME
|
VARCHAR2
|
IN
|
REPAIR_TABLE
|
FLAGS
|
BINARY_INTEGER
|
IN
|
NULL
|
BLOCK_START
|
BINARY_INTEGER
|
IN
|
NULL
|
BLOCK_END
|
BINARY_INTEGER
|
IN
|
NULL
|
CORRUPT_COUNT
|
BINARY_INTEGER
|
OUT
|
|
Table 6.130:
Check_object Parameters
SET SERVEROUTPUT
ON
DECLARE
corrupt_count INT;
BEGIN
Corrupt_count :=
0;
DBMS_REPAIR.CHECK_OBJECT (
SCHEMA_NAME =>
'BERT',
OBJECT_NAME =>
'JUNK',
REPAIR_TABLE_NAME =>
'REPAIR_TABLE',
CORRUPT_COUNT =>
corrupt_count);
DBMS_OUTPUT.PUT_LINE('Corrupt
count = ' || TO_CHAR
(corrupt_count));
END;
/
Then query the repair table to see
what problems exist as follows:
SQL> SELECT
OBJECT_NAME, BLOCK_ID,
CORRUPT_TYPE, MARKED_CORRUPT,
CORRUPT_DESCRIPTION,
REPAIR_DESCRIPTION FROM
REPAIR_TABLE;
If the corrupt count returns a
non-zero value, then one needs to fix
those issues. If the problem is
corrupt blocks, then use the
fix_corrupt_blocks
procedure. Below are the parameters
for this procedure and an example.
Argument
|
Type
|
In / Out
|
Default Value
|
SCHEMA_NAME
|
VARCHAR2
|
IN
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
OBJECT_TYPE
|
BINARY_INTEGER
|
IN
|
TABLE_OBJECT
|
REPAIR_TABLE_NAME
|
VARCHAR2
|
IN
|
REPAIR_TABLE
|
FLAGS
|
BINARY_INTEGER
|
IN
|
NULL
|
FIX_COUNT
|
BINARY_INTEGER
|
OUT
|
|
Table 6.131:
Fix_corrupt_blocks Parameters
SET SERVEROUTPUT
ON
DECLARE
fix_count INT;
BEGIN
Fix_count := 0;
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME =>
'BERT',
OBJECT_NAME=>
'JUNK',
OBJECT_TYPE =>
dbms_repair.table_object,
REPAIR_TABLE_NAME =>
'REPAIR_TABLE',
FIX_COUNT=>
fix_count);
DBMS_OUTPUT.PUT_LINE('Fix count =
' || TO_CHAR(fix_count));
END;
/
If the problem is bad index entries
instead like orphans, then use the
dump_orphan_keys
procedure. Below are the parameters
for this procedure and an example.
Argument
|
Type
|
In / Out
|
Default Value
|
SCHEMA_NAME
|
VARCHAR2
|
IN
|
|
OBJECT_NAME
|
VARCHAR2
|
IN
|
|
PARTITION_NAME
|
VARCHAR2
|
IN
|
NULL
|
OBJECT_TYPE
|
BINARY_INTEGER
|
IN
|
TABLE_OBJECT
|
REPAIR_TABLE_NAME
|
VARCHAR2
|
IN
|
REPAIR_TABLE
|
ORPHAN_TABLE_NAME
|
VARCHAR2
|
IN
|
ORPHAN_KEYS_TABLE
|
FLAGS
|
BINARY_INTEGER
|
IN
|
NULL
|
KEY_COUNT
|
BINARY_INTEGER
|
OUT
|
|
Table 6.132:
Dump_orphan_keys Parameters
SET SERVEROUTPUT ON
DECLARE key_count INT;
BEGIN
key_count := 0;
DBMS_REPAIR.DUMP_ORPHAN_KEYS (
SCHEMA_NAME => 'BERT',
OBJECT_NAME => 'JUNK',
OBJECT_TYPE =>
dbms_repair.index_object,
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
ORPHAN_TABLE_NAME=> 'ORPHAN_KEY_TABLE',
KEY_COUNT => key_count);
DBMS_OUTPUT.PUT_LINE('Orphan key
count: ' || TO_CHAR(key_count));
END;
/
|
|
|
|
|
|
Get the Complete
Oracle Utility Information
The landmark book
"Advanced Oracle
Utilities The Definitive Reference" contains over 600 pages of
filled with valuable information on Oracle's secret utilities.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
|
|