Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

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:

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:

 

SQL> desc repair_test;

 

 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.

 

   dbms_repair.sql

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.
 


 

 
��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational