Invalid and unavailable objects for Oracle Database Reconstruction

Invalid and unavailable objects for Oracle Database Reconstruction

Invalid and unavailable objects

Invalid PL/SQL objects and unavailable indexes may affect performance. Invalid PL/SQL objects must be recompiled before they can be used. This requires a Compilation Time Before executing the first operation to access the PL/SQL package, process, or function. If the PL/SQL re-compilation fails, the operation fails due to an error. The optimizer ignores unavailable indexes. If the performance of an SQL statement depends on the index that has been marked as unavailable, the performance can be improved only by rebuilding the index.

Invalid PL/SQL object: You can query the data dictionary to view the current status of the PL/SQL object.

You can use the following command line to find invalid PL/SQL objects:

SELECT object_name, object_type FROM DBA_OBJECTS

WHERE status = 'invalid ';

By default, the "invalid object count of the owner" metric is checked every 24 hours. If the number of objects of an owner exceeds two, an alert is issued.

If you find the PL/SQL object in INVALID status, the first question to be answered is: "Was this object a VALID ?" Application developers often forget to clear code that does not work. If the PL/SQL object is invalid due to a code error, there is no other way except to correct the error. If the process has been valid in the past and has recently become invalid, you can use either of the following methods to solve the problem:

1. No processing is performed. If needed, most PL/SQL objects will be automatically re-compiled during the call. The user will experience a short delay when the object is re-compiled. (In most cases, this latency is not very obvious .)

2. manually re-compile the invalid object.

You can use Enterprise Manager or SQL commands to manually recompile invalid PL/SQL objects.

Alter procedure hr. add_job_history COMPILE;

To manually recompile PL/SQL data packets, perform the following two steps:

Alter package hr. maintainemp COMPILE;

Alter package hr. maintainemp compile body;

Unavailable index: You can query the DBA_INDEXES data dictionary view to find invalid indexes:

SELECT index_name, table_name FROM DBA_INDEXES

WHERE status = 'unusable ';

For partition indexes, the status is saved in the DBA_IND_PARTITIONS view.

You can re-calculate the pointer by recreating the unavailable index to make the unavailable index valid. Rebuilding an unavailable index will re-create the index at the new location, and then delete the unavailable index.

You can use Enterprise Manager or SQL commands to complete this operation:

Alter index hr. emp_empid_pk REBUILD;

Alter index hr. emp_empid_pk rebuild online;

Alter index hr. email rebuild tablespace users;

If the TABLESPACE clause is omitted, the index is rebuilt in the same TABLESPACE that already exists.

With the rebuild online clause, you can continue to update the index table during reconstruction. (If you do not use the ONLINE keyword, you must wait until the reconstruction is complete before executing DML on the affected table. If the index is unavailable, the index will not be used during reconstruction even if the ONLINE keyword is used .)

Enterprise Manager uses the Reorganize operation to repair UNUSABLE indexes.

Note: You must have free space for re-indexing. Verify that there is sufficient space before trying to recreate. Enterprise Manager automatically checks the space requirements.

-------------------------------------- Split line --------------------------------------

Rlwrap

SQLPLUS spool to dynamic Log File Name

Oracle SQLPLUS prompt settings

Accelerate SQL return by setting SQLPLUS ARRAYSIZE (row prefetch)

-------------------------------------- Split line --------------------------------------