Analysis and resolution of common ORACLE error codes (I)

  • 2020-05-06 11:50:56
  • OfStack

The ORACLE tutorial you are looking at is: analysis and resolution of common ORACLE error codes (part 1). In the course of using ORACLE, we will often encounter some errors generated by ORACLE, which may be a little vague for beginners, and can be
Can do not know how to deal with these errors, I use the more frequent error code to analyze one by one, I hope to help you find an
Reasonable ways to solve these errors, but also hope that you can offer your different views. After all, as a means of communication, personal opinions are inevitably too biased And there must be shortcomings, mistakes are inevitable. 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

Why it occurs: the ORACLE error above is caused by insufficient rollback segment table space, which is the most common ORACLE error message for ORACLE data administrators. When a user

above occurs when a very large data operation results in insufficient existing rollback segments, so that the allocatable rollback segment table space is full and cannot be allocated anymore The error.

Solution: use the "ALTER TABLESPACE tablespace_name ADD DATAFILE filename SIZE size_of_file" command to the specified
Data increments table Spaces, one or more table Spaces depending on the situation. Of course this is also related to the bare disk device on your host, if your host is bare
There is no spare space for disk devices. 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
How many tablespace Spaces:

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

If there is too much spare space, you can appropriately append a large rollback segment to the table space to avoid the above error. You can also use the following statement
Check out rollback segment's competitive status:

Select class,count from V$waitstat where calss in(' system undo header','system undo block','undo
header ', 'undo block'); And

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%, rollback segment should be considered.

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

Reason: ORACLE temporary segment table space is insufficient, because ORACLE always allocates as much contiguous space as possible, but not enough allocates or allocates without connecting
Continue to appear above phenomenon.

Solution: we know that because ORACLE treats the table space as a logical structure-unit, the physical structure of the table space is the data file, which is
on disk Logically created, all objects of the table space also exist on disk, and in order to add space to the table space, you must add data files. Take a look at
for the specified table space Available space, using view SYS.DBA_FREE_SPACE, each record in the view represents the size of the fragmentation of available space:

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

The information returned initially determines the maximum size of the available space, seeing if it is smaller than the size mentioned in the error message, and looking at the default tablespace reference
Count:

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 tablespace with the following SQL command:

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

An appropriate increase in the default size is possible to solve the error problem, as is changing the size of the user's temporary table space:

SQL>ALTER USER username TEMPORARY TABLESPACE new_tablespace_name;

Using the ALTER TABLESPACE command, once completed, the added space can be used without exiting the database or taking the table space offline, but note that once
is added The data file, it can not be deleted, to delete, to delete the table space.

An example of error reporting is

ORA-1652:unable to extend temp segment by 207381 in tablespace TEMPSPACE

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.


Related articles: