Analysis and Solution of common ORACLE error codes (1)

Analysis and Solution of common ORACLE error codes (1)

Analysis and Solution of common ORACLE error codes (1)

The ORACLE tutorial is: Analysis and Solution of common ORACLE error codes (1 ). When using ORACLE, we often encounter ORACLE errors. For Beginners, these errors may be vague and
If you do not know how to handle these errors at the moment, I will analyze the frequently used error codes one by one, hoping to help you find
We hope that you can give your different opinions while reasonably solving these mistakes. After all, as a means of communication, personal opinions will inevitably be too biased.
There must also be deficiencies, and errors are inevitable. The purpose of this article is to promote and make progress together through mutual communication.

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

Cause: the preceding ORACLE error is caused by insufficient tablespace In the rollback segment, which is also the most common ORACLE error message of the ORACLE Data Administrator. When users
When a very large data operation is performed, the existing rollback segments are insufficient, so that the table space of the available rollback segments is full and cannot be allocated.
.

Solution: run the "alter tablespace tablespace_name add datafile filename SIZE size_of_file" command
You can add one or more tablespaces as needed. Of course, this is also related to the bare disk device on your host.
There is no extra space for the disk device. We recommend that you do not increase the size of the tablespace In the rollback segment. You can use the following statements to query the remaining
Table space:

Select user_name, SQL _text from V $ open_cursor where user_name =' ';

If the extra space is large, you can append a large rollback segment to the tablespace to avoid the above errors. You can also use the following statement
To check the rollback segment competition:

Select class, count from V $ waitstat where calss in ('System undo header', 'System undo Block', 'undo
Headers ', 'undo Block'); and

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

If the value of any class in count/sum (value) is greater than 1%, you should consider adding a 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 statement to add one or more files to the specified
Tablespace.

ORA-01652: unable to extend temp segment by num in tablespace name

Cause: the tablespace in the temporary ORACLE segment is insufficient, because ORACLE always allocates continuous space as much as possible. 1. There is not enough allocable space or the database is not connected.
Continue to see the above phenomenon.

Solution: ORACLE uses the tablespace as a logical structure-unit, while the physical structure of the tablespace is a data file, and the data file is stored on the disk.
All objects in the tablespace also exist on the disk. To add space to the tablespace, you must add data files. First, check
Available space: View SYS. DBA_FREE_SPACE. Each record in the view represents the shard size of the available space:

SQL> Select file_id, block_id, blocks, bytes from sys. dba_free_space where tablespace_name =' ';

The returned information preliminarily determines the maximum block of the available space, checks whether it is smaller than the size mentioned in the error message, and then checks the default tablespace parameter.
Quantity:

SQL> SELECT INITIAL_EXTENT, NEXT_EXTENT, MIN_EXTENTS, PCT_INCREASE FROM SYS. DBA_TABLESPACES WHERE
TABLESPACE_NAME = name;

Use the following SQL command to modify the default storage value of the temporary tablespace:

SQL> ALTER TABLESPACE name DEFAULT STORAGE (INITIAL XXX NEXT YYY );

Increasing the size of the default value may solve the problem. You can also modify the size of your temporary tablespace to solve the problem:

SQL> ALTER USER username TEMPORARY TABLESPACE new_tablespace_name;

Use the alter tablespace command. After the command is complete, the added space can be used. You do not need to exit the database or take the TABLESPACE offline.
You cannot delete a data file. to delete a data file, you must 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 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.

<