Analysis and resolution of Oracle Common Error codes (II.)

Source: Internet
Author: User
Tags create index rollback table name backup
Oracle| Error | resolution
Analysis and resolution of Oracle Common Error codes (II.)



The article originates from the century easy net

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 ask

4, Oracle attempts to access an unformatted system block failed; 5, data file partial overflow, etc. one of these situations causes a logical bad block or

Physical bad block, then will report ORA-01578 error.



WORKAROUND: Because Oracle only makes an error when accessing a problematic data file, the time for the error is likely to be later than the actual error, such as

Fruit ORA-01578 Error message indicates that the data bad block points to the user's own data file, the following methods are used to resolve:





If the bad block detected by the following SQL statement appears on the index, you can simply rebuild the index



Sql>select Owner,segment_name,segment_type from dba_extents where file_id=<f> and <B> between block_id and

Block_id+blocks-1 (<F> and <B> respectively is the file number and block number of the bad blocks that are reported by ORA-01578)





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 times recommended) to help identify data bad blocks are permanent (hard drive

The physical bad block on it) is also random (caused by memory or hardware errors):



Sql>analyze table <table_name> 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, a permanent physical or logical bad block; a different parameter to the original error message, possibly with memory,

Page space is associated with 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:



&LT;1&GT; Close the database first



<2> Edit Init<sid>.ora file, add:



event= "10231 Trace name context forever,level 10"



<3>.startup Restrict



<4> Create a temporary table: Sql>create table Errortemp as SELECT * from error; (Error is table name of bad table)



<5> Remove the event from the Init<sid>.ora file and restart the database



<6>.rename bad table, rename the table name of the temporary table into a bad table



<7> CREATE index on a 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

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 has Zhuo Shui and

The method of using the rowID method to remove data other than bad blocks is not 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 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 a rollback segment and a table space has reached the limit set by the maxextents parameter. Note that this maxextents is not

Is the hardware limit of the rollback segment or tablespace, 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 "XXXX" value must be greater than

The value indicated in the error message, but not greater than largest maxextent, if the largest maxextent value has been reached, the solution is

is to recreate the larger range size, using the Export tool with the option compress=y, and if there is space available in the table space, first make a preparation for the table

, change the 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, then you must delete and rebuild it, and if it appears in the temporary table space, modify the temporary table empty

Can resolve this problem by storing the fields between.



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 that already has reached it maximum size or space

Could is 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 spaces and alter the storage Parameters,as described in the Oracle8

Server Administrator ' s Guide.


Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.