The Oracle tutorial being looked at is: analysis and resolution of Oracle common error codes two.
--------------------------------------------------------------------------------
P align= "Justify" >ora-01578:oracle data block corrupted (file # num,block # num)
Reason: When Oracle accesses a block of data, due to 1, hardware I/O error, 2, os I/O error or buffering problem, 3, memory or paging problem, 4, Oracle attempts to access an unformatted system block failed; 5, Part of the data file overflow, such as one of the above situations caused the logical bad block or physical bad block, then will report ORA-01578 error.
Solution: Because Oracle only has access to the problem of the data file will be an error, so the time of the error is likely to be more than the actual errors of time later, if the ORA-01578 error message indicates that the data bad block points to the user's own data files, the following methods to solve:
If the bad block detected by the following SQL statement appears on the index, you can simply rebuild the index
sql$#@62; Select Owner,segment_name,segment_type from dba_extents where file_id=$#@60; f$#@62; and $#@60; b$#@62; Between block_id and Block_id+blocks-1 ($#@60; f$#@62; and $#@60; B$#@62: The file number and block number of the bad blocks that ORA-01578 reported respectively
If the bad block appears on the table, first use the following statement to analyze whether it is a permanent bad block (one or two more are recommended) to help identify the data bad block is permanent (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 you execute this command, you may receive the following results:
ORA-01578: Has the same parameter as the original error message, is a permanent physical or logical bad block, has different parameters than the original error message, and may be related to memory, page space, and I/O devices.
If the user has the latest backup of this table, it is best to use this backup to restore the table, or to use event 10231来 to remove data other than the bad block:
$#@60;1$#@62;. Close the database First
$#@60;2$#@62;. Edit init$#@60;sid$#@62;. Ora file, 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 table name for bad table)
$#@60 #@62; Take the event from init$#@60;sid$#@62; Delete the Ora file and restart the database
$#@60;6$#@62;. Rename bad table, rename the table name of the temporary table into a bad table
$#@60;7$#@62;. Create index on the table, etc.
If the ORA-01578 error message indicates that the data bad block points to a data dictionary or a rollback segment, you should immediately contact Oracle to discuss a good solution.
The solution here is only a more common one, and some more specific solutions can look at Oracle's troubleshooting manual, which contains Zhuo Shui and methods for using the rowID method to remove data outside of the bad block, as described here.
The corresponding English is as follows:
Cause:the given data block is corrupted,probably due to program errors
Action:try to restore the segment containing of the given data block,this may involve dropping the segment and recreating it, If There is a trace file,report the messages recorded into it to customer support.
Ora-01628:max # of extents Num reached for rollback segment num
Cause: This error is usually a rollback segment and a table space has reached the limit set by the maxextents parameter. Note that this maxextents is not the hardware limit for the rollback segment or table space, and the hardware limit depends on the value of the Db_block_size parameter specified in the Init.ora file when the database was created.
WORKAROUND: Use SQL command alter tablespace ... STORAGE (maxextents xxxx) to increase the maxextents, where the "XXXX" value must be greater than the value indicated in the error message, but not greater than the largest maxextent if it has reached largest maxextent VALUE, the solution is to recreate the larger scope size, using the Export tool with the option compress=y, if the table space has free space, first make a backup of the table, change its name with alter Tablespace Tablespace_name , and then mount the table back to the database.
To see where the error occurred, if it appears on the rollback segment or index, you must delete and rebuild it, and if it appears in the temporary table space, modify the storage field in the temporary tablespace to resolve the problem.
An example of an error is as follows:
Ora-1628:max # Extents reached for rollback segment Rbs_1
The corresponding English is as follows:
Cause:an attempt is made to extend a rollback segment this already has reached its maximum size or spaces could not is Al Located 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 D Ictionary table lacking space and alter the storage Parameters,as described in the Oracle8 Server Administrator ' s Guide.
