Analysis and resolution of Oracle common error codes

In the process of using Oracle, we often encounter some Oracle errors, for beginners, these errors may be a little fuzzy, and may not know how to deal with these errors, I used to appear in the more frequent error code one by one to make analysis, Hopefully it will help you find a reasonable way to solve these mistakes, and hope you can put forward your different opinions. After all, as a means of communication, personal opinions unavoidably too biased, and there must be deficiencies, mistakes are inevitable. The purpose of writing this article is to promote and make progress together through mutual communication.

Error 1:

Ora-01650:unable to extend rollback segment name by NUM intablespace name

Cause: The Oracle error described above is caused by a lack of space in the rollback segment, which is also the most common Oracle error message for Oracle data administrators. This error occurs when the user is doing a very large data operation that causes the existing rollback segment to be insufficient so that the allocated rollback segment table space is full and cannot be allocated.

　　

Workaround: Add table space to the specified data using the "ALTER tablespace tablespace_name ADD datafile filename SIZE size_of_file" command, and add one or more tablespaces, depending on the situation. Of course this and also with your host on the bare disk equipment, if you host the bare disk device has no extra use space, suggest you do not lightly increase the size of the rollback segment table space, you can use the following statements to query the remaining tablespace space how many:

Select User_name,sql_text from V$open_cursor where user_name= ';

If the extra space is more, you can appropriately append a large rollback segment to the table space to use, so as to avoid the above errors. You can also use the following statement to check the rollback segment competitive situation:

Select Class,count from V$waitstat where CALSS to (' System undo Header ', ' System undo Block ', ' Undo header ', ' undo Block ');

Or use the Next statement:

Select sum (value) from V$sysstat where name in (' Db_block_gets ', ' consistents gets ');

　

If any class in Count/sum (value) is greater than 1%, you should consider adding rollback segment.

Error 2:

Cause:failed to allocate extent from the rollback segment in tablespace action:use the ALTER tablespace ADD DataFile St 　　 Atement to add one or more files to the specified tablespace. Ora-01652:unable to extend temp segment by num in Tablespace name

Reason: There is not enough space in the Oracle Temporary segment table because Oracle always allocates contiguous space as much as possible, as long as there is not enough unallocated space or the distribution is discontinuous.

　　

Workaround: We know that because Oracle takes tablespaces as logical structures-cells, and the physical structure of tablespaces is data files, data files are physically created on disk, and all the objects in the table space are on disk, the data files must be added in order to add space to the tablespace. Look at the free space in the specified table space, using the view sys.dba_free_space, each record in the view represents the size of the free space fragment:

Sql>select file_id,block_id,blocks,bytes from Sys.dba_free_space where tablespace_name= ';

The returned information can initially determine the maximum block of free space, see if it is smaller than the size mentioned in the error message, and then look at the default table space parameters:

Sql>select initial_extent,next_extent,min_extents, pct_increase from SYS. Dba_tablespaces WHERE Tablespace_name=name;

Modify the default storage value for the temporary segment table space by using the following SQL command:

Sql>alter tablespace name DEFAULT STORAGE (INITIAL XXX NEXT YYY);

Appropriately increasing the size of the default value may resolve the error problem, or you can fix the problem by modifying the user's temporary table space size:

Sql>alter USER username temporary tablespace new_tablespace_name;

Using the Alter TABLESPACE command, once completed, the increased space can be used without exiting the database or taking the tablespace offline, but note that once you have added a data file, you cannot delete it, and to delete it, you delete the tablespace.

Error 3:

Cause:failed to allocate extent for temp segment in tablespace

　　

Action:use the ALTER tablespace ADD datafile statement to the specified tablespace or create the Object in another tablespace.

　　

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 indexed, you can simply rebuild the index:

Sql>select Owner,segment_name, Segment_type from Dba_extents where file_id= and between block_id and Block_id+blocks-1 ;

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>analyze table 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:

　　

<1> Close the database first

　　

<2> Edit Init.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.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 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.

Error 4:

Cause:the given data block is corrupted, probably due to program errors Action:try to restore the segment containing T He given data blocks, this could involve dropping the segment and recreating it, If there is a trace file, the 　　 s 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 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.