Analysis and resolution of common ORACLE error codes (I)
- 2020-05-06 11:50:56
- OfStack
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.