Analysis and Solution of Oracle Common Error Codes (1)
Article is from Century Easy Network
In the process of using Oracle, we will often encounter some Oracle errors. For beginners, these errors may be a bit blurry, and
I don't know how to deal with these mistakes, I have made analysis in the use of more frequent error codes, I hope to help you find a
Rationally solve these errors, and I hope you can make your different opinions. After all, as a means of communication, personal opinions are inevitable too much, and
And it is necessary to exist in an unfair, and it is inevitable. The purpose of writing this article is to promote and make progress together by mutual exchanges.
ORA-01650: Unable to Extend Rollback Segment Name by Num Intablespace Name
Cause: The above Oracle error is caused by the shortcomings of the split segment table space, which is also the most common Oracle error message for Oracle data administrators. As a user
In a very huge data operation, the existing returning segment has caused the spatial split segment table space that is full and cannot be assigned, and the above will appear.
mistake.
Solution: Use the "Alter TableSpace TableSpace_name Add DataFile FileName Size Size_OF_FILE" command to the specified
Data increases the tablespace, which can increase one or more table spaces depending on the specific situation. Of course, this is related to the bare disk device on your host, if your host's naked
The disk device has no extra usage space. It is recommended that you don't intend to increase the size of the split segment table space. You can use the following statement first query the remaining.
How much is the TABLESPACE space:
Select user_name, SQL_Text from V $ OPEN_CURSOR where user_name = '
If the extra space is more, you can properly add a large returning segment to the table space to avoid the above errors. You can also use the following statement
To detect the competitive situation of rollback segment:
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%, you should consider adding Rollback Segment.
The corresponding English is as follows:
Cause: failed to allocate extent from the rollback segment in tablesspace
Action: Use the alter tablespace add datafile statement to add one or more files to the specified
TableSpace.
ORA-01652: Unable to extend symp segment by num in tablespace name
Cause: Oracle Temporary Section Table Space is insufficient, because Oracle always assigns continuous space, one but not enough, allocated space or allocation is not connected
The above phenomenon will occur.
Workaround: We know that due to Oracle's table space as a logical structure-unit, and the physical structure of the table space is a data file, the data file is on the disk.
Create and all objects in the table space are also present on the disk, and the data file must be added to add space to the tablespace. First look at the free space of the specified table space, use the view sys.dba_free_space, each record in the view represents the fragmentation of the available space:
SQL> SELECT FILE_ID, BLOCK_ID, Blocks, Bytes from sys.dba_free_space where tablespace_name = '
The returned information can initially determine the maximum block of the available space, see if it is less than the size mentioned in the error message, then check the default tablespace
number:
SQL> SELECT INITIAL_EXTENT, NEXT_EXTENT, Min_EXTENTS, PCT_INCREASE, SYS.DBA_TABLES WHERE
TABLESPACE_NAME = Name;
Modify the default storage value of the episode table space by the following SQL command:
SQL> ALTER TABLESPACE NAME DEFAULT Storage (Initial XXX NEXT YYY);
Appropriately increase the size of the default value is possible to solve the error problem, or you can solve this problem by modifying the user's temporary table space size:
SQL> ALTER USER Username Temporary TableSpace New_Tablespace_name;
Using the alter tablespace command, one but complete, the added space can be used, no need to exit the database or get the table space offline, but pay attention to
The data file cannot be deleted again. To delete, you have to delete the table space.
An error example is as follows:
ORA-1652: Unable to extend symp 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.
