Shulou(Shulou.com)06/01 Report--

(a) in the process of using ORACLE, we will often encounter some ORACLE errors, for beginners, these errors may be a bit vague, and may not know how to deal with these errors, I will use the more frequent error code one by one to make analysis, I hope to help you find a reasonable solution to these errors, but also hope you can put forward your different views. After all, as a means of communication, personal opinions are inevitably biased, and there must be deficiencies, mistakes are inevitable. The purpose of writing this article is to promote and progress together through mutual exchanges.

ORA-01650:unable to extend rollback segment NAME by NUM intablespace NAME

Cause: The above ORACLE error is caused by insufficient table space of rollback segment, which is also the most common ORACLE error message of ORACLE data administrator. This error occurs when the user is performing a very large data operation that causes an existing rollback segment to run out of space, making the available rollback segment table space full and unable to allocate any more.

Workaround Download: Use the ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE_of_file command to add tablespaces to the specified data, one or more tablespaces depending on the situation. Of course, this is also related to the bare disk device on your host. If the bare disk device on your host has no extra space to use, it is recommended that you do not lightly increase the size of the rollback segment table space. You can use the following statement to query the remaining table space: Select user_name,sql_text from V$open_cursor where user_name=''; If the excess space is relatively large, you can appropriately add a large rollback segment to the table space to avoid the above error. You can also check for rollback segment contention by using the following statements: Select class,count from V$waitstat where calss in ('system undo header','undo header','undo block'); and Select sum(value) from V$sysstat where name in ('db_block_gets','constants gets'); if any class in count/sum(value) is greater than 1%, consider adding a rollback segment.

The corresponding English text reads as follows: Cause:Failed to allocate extent from the rollback segment in tablespace Action:Use the ALTER TABLESPACE ADD DATAFILE statement 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 tries to allocate continuous space, but there is not enough space to allocate or the allocation is discontinuous.

Solution Download: We know that because ORACLE will table space as a logical structure-unit, and the physical structure of table space is data files, data files are physically created on disk, all objects of table space also exist on disk, in order to increase space for table space, you must increase the data file. First look at the available space for the specified table space, using the view SYS.DBA_FREE_SPACE, where each record represents the fragment size of the available space:

SQL>Select file_id,block_id,blocks,bytes from sys.dba_free_space where tablespace_name =''; returns information that initially determines the largest block of free space, to see if it is smaller than the size mentioned in the error message, and to check the default tablespace parameters: SQL>SELECT INITIAL_EXTENT,NEXT_EXTENT,MIN_EXTENTS,PCT_INCREASE FROM SYS.DBA_TABLESPACES WHERE TABLESPACE_NAME=name;

Modify the default storage values for temporary segment tablespaces with the following SQL command: SQL>ALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY);

Increasing the default size appropriately may solve the error problem that occurs, or you can solve this problem by modifying the user's temporary table space size: SQL>ALTER USER username TEMPORARY TABLESPACE new_table space_name;

With the ALTER TABLESPACE command, the added space is available as soon as it is complete, without exiting the database or taking the tablespace offline, but note that once you have added a data file, you cannot delete it, and if you want to delete it, delete the tablespace.

An example of an error is as follows: ORA-1652:unable to extend temp segment by 207381 in tablespace TEMPSPACE The corresponding English text is as follows: Cause: Failed to allocate extent for temp segment in namespace Action:Use the ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the specified namespace or create the object in another namespace

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.