Analysis and Solution 2 of common ORACLE error codes

The ORACLE tutorial is: Analysis and Solution 2 of common ORACLE error codes.

--------------------------------------------------------------------------------

P align = "JUSTIFY"> ORA-01578: Oracle data block upted (file # num, block # num)

Cause: When ORACLE accesses a data block, due to 1. Hardware I/O errors; 2. Operating System I/O errors or buffer problems; 3. memory or paging problems; 4. ORACLE failed to access an unformatted system block; 5, part of the data file overflow and other situations such as a cause of the logical bad block or physical bad block, then it will report the ORA-01578 error.

Solution: Because ORACLE Reports an error only when it accesses a problematic data file, the error may be reported later than the actual error time, if the ORA-01578 error message prompts that the bad data block points to the user's own data file, use the following method to solve:

If the bad block found through the following SQL statement has an index, you only need to re-create the index.

SQL $ # @ 62; Select owner, segment_name, segment_type from dba_extents where file_id =_id #@ 60; F $ # @ 62; and $ # @ 60; B $ # @ 62; between block_id and block_id + blocks-1; ($ # @ 60; F $ # @ 62; and $ # @ 60; B $ # @ 62; are the bad block reported by the ORA-01578 file number and block number)

If the bad block appears on the table, use the following statement to check whether it is a permanent bad block (it is recommended to execute it once or twice to help identify whether the bad data block is permanent (the physical bad block on the hard disk) or random (caused by memory or hardware errors )):

SQL $ # @ 62; Analyze table $ # @ 60; table_name $ # @ 62; validate structure cascade;

After executing this command, the following results may appear:

ORA-01578: has the same parameter as the original error message, is a permanent physical or logical bad block; has a different parameter with the original error information, may be with memory, page space is related to the I/O device.

If you have the latest backup of this table, it is best to use this backup to restore the table, or use event 10231 to retrieve data other than bad blocks:

$ #@ 60; 1 $ # @ 62;. Shut down the database first.

$ # @ 60; 2 $ # @ 62;. Edit the init $ # @ 60; sid $ # @ 62;. ora file and add:

Event = "10231 trace name context forever, level 10"

$ # @ 60; 3 $ # @ 62;. startup restrict

$ # @ 60; 4 $ # @ 62;. create a temporary table: SQL $ # @ 62; create table errortemp as select * from error; (error is the name of a bad table)

$ #@ 60 # @ 62;. Delete the event from the init $ # @ 60; sid $ # @ 62;. ora file and restart the database.

$ #@ 60; 6 $ # @ 62;. rename bad table, rename the temporary table as the name of the bad table

$ #@ 60; 7 $ # @ 62;. Create a table INDEX, etc.

If the ORA-01578 error message prompts that the bad data block points to a data dictionary or rollback segment, you should immediately contact ORACLE to discuss a good solution.

The solution mentioned here is only a common one. For more specific solutions, you can refer to the ORACLE troubleshooting manual, there are methods for retrieving data other than bad blocks using the ROWID method, which will not be described here.

The corresponding English is as follows:

Cause: The given data block was has upted, probably due to program errors

Action: Try to restore the segment containing the given data block, This may involve dropping the segment and recreating it, If there is a trace file, report the messages recorded in it to customer support.

ORA-01628: max # of extents num reached for rollback segment num

Cause: this error is usually caused by a rollback segment and a tablespace that has reached the limit set by the MAXEXTENTS parameter. Note that MAXEXTENTS is not the hardware limit of the rollback segment or tablespace. The hardware limit depends on the value of the DB_BLOCK_SIZE parameter specified in the init. ora file when the database is created.

Solution: Use the SQL command ALTER TABLESPACE... STORAGE (maxextents xxxx) to add MAXEXTENTS, where the "XXXX" VALUE must be greater than the VALUE specified in the error message, but cannot be greater than the VALUE of largest maxextent. If it has reached the VALUE of largest maxextent, the solution is to re-create a large range and use the Export tool with COMPRESS = Y to Export the table. If the table space has available space, back up the table first, use alter tablespace tablespace_name to change its name, and then load the table back to the database.

Check where an error occurs. If it appears in a rollback segment or index, you must delete and recreate it. If it appears in a temporary tablespace, modify the storage field of the temporary tablespace, you can solve this problem.

An example of an error is as follows:

ORA-1628: max # extents 50 reached for rollback segment RBS_1

The corresponding English is as follows:

Cause: An attempt was made to extend a rollback segment that already has reached its maximum size or space cocould not be allocated in the data dictionary to contain the definition of the object.

Action: If possible, increase the value of either the MAXEXTENTS or PCTINCREASE initialization parameters or find the data dictionary table lacking space and alter the storage parameters, as described in the Oracle8 Server Administrator's Guide.