Analysis and resolution of common error codes for Oracle

(i) in the process of using Oracle, we often encounter a number of Oracle errors, for beginners, these errors may be a little vague, and may not know how to handle the resulting errors, I use the more frequent error code one by one to make analysis, Hopefully it will help you find a reasonable solution to these mistakes, and hopefully you will be able to come up with a different perspective. After all, as a means of communication, personal opinions are inevitably too biased, but also there must be shortcomings, mistakes are unavoidable. The purpose of writing this article is to promote and progress together through mutual communication.

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

　　Cause: The above Oracle error is caused by insufficient rollback segment tablespace, which is the most common Oracle error message for Oracle data administrators. The above error occurs when the user is doing a very large data operation that causes the existing rollback segment to be insufficient, so that the available rollback segment table space is full and can no longer be allocated.

　　 workaround Download : Use the "ALTER tablespace tablespace_name ADD datafile filename SIZE size_of_file" command to the specifieddata increases the table space, which can be increased by one or more table spaces, depending on the situation. Of course this is also related to the bare disk device on your console, if you host the baredisk equipment has no extra space, it is recommended that you do not lightly increase the size of the rollback section table space, you can use the following statements to query the remainingHow many tablespace spaces are there:Select user_name,sql_text from v$open_cursor where user_name= ' <user_name> ';if the extra space is more, you can appropriately append a large rollback segment to the table space to use, thus avoiding the above error. You can also use the following statement to detect the competitive status of rollback segment:Select class,count from V$waitstat where CALSS in (' System undo Header ', ' System undo Block ', ' Undo header ', ' Undo Block ') ;and theSelect sum (value) from V$sysstat where name in (' Db_block_gets ', ' consistents gets ');if any one of the class in Count/sum (value) is greater than 1%, you should consider increasing the rollback segment.

　　the corresponding English is as follows: cause:failed To allocate extent from the rollback segment in tablespace action:use the ALTER tablespace ADD datafile S Tatement to add one or more files to the specified tablespace. ora-01652:unable to extend temp segment by num in Tablespace name

　　Cause: Oracle Temporary segment table space is insufficient because Oracle always allocates contiguous space as much as possible, but the above phenomenon occurs when there is not enough space to allocate or the distribution is discontinuous.

　　Workaround Download: We know that because Oracle has a tablespace as a logical structure-cell, and the physical structure of the tablespace is a data file, the data file is physically created on disk, and all the objects of the tablespace exist on disk, and in order to add space to the tablespace, the data file must be added. Take a look at the available space for the specified tablespace, use view sys.dba_free_space, and each record in the view represents the fragment size of the free space:

sql>select file_id,block_id,blocks,bytes from Sys.dba_free_ Space where tablespace_name= ' <users> ';   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);

　　The appropriate increase in the size of the default value may resolve the error problem, or you can fix the problem by modifying the user's temporal tablespace size: sql>alter USER username temporary tablespace new_tablespace_name;

using the alter TABLESPACE command, once done, the added space is available, without exiting the database or taking the tablespace offline, but be aware that Once you have added the data file, you can no longer delete it and delete the table space if you want to delete it.

　　An example of an error is as follows: ora-1652:unable to extend temp segment by 207381 in Tablespace tempspace the corresponding English is as follows: cause:failed to allocate extent for temp segment in tablespace action:use the ALTER tablespace ADD datafile statement To add one or more files to the specified tablespace or create the object in another tablespace

Analysis and resolution of common error codes for Oracle