Common Oracle Database Error operations and Solutions

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 ORACLE Data Administrator. When a user is performing a very large data operation, resulting in insufficient existing rollback segments, the table space of the available rollback segments is full and cannot be allocated, the preceding error occurs.

Solution: run the "alter tablespace tablespace_name add datafile filename SIZE size_of_file" command to ADD a TABLESPACE to the specified data. You can ADD one or more tablespaces as needed. Of course, this is also related to the bare disk device on your host. If there is no additional space for the bare disk device on your host, we recommend that you do not increase the size of the tablespace In the rollback segment. You can use the following statements to first query the remaining tablespace space: Select user_name, SQL _text from V $ open_cursor where user_name = '';

If there is more space, you can append a large rollback segment to the tablespace to avoid the above errors. You can also use the following statement to detect

The competition 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 the value of any class in count/sum (value) is greater than 1%, you should consider adding a rollback segment.

ORA-01652: unable to extend temp segment by num in tablespace name cause: ORACLE temporary segment tablespace is insufficient, because ORACLE always tries to allocate continuous space, once there is not enough allocable space or the allocation is not continuous, the above phenomenon will occur. Solution: ORACLE uses the tablespace as a logical structure-unit, while the physical structure of the tablespace is a data file, which is physically created 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 the available space of the specified tablespace and use the 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 can preliminarily determine the maximum block of the available space, check whether it is smaller than the size mentioned in the error message, and then check the default tablespace parameters:

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;

After the alter tablespace command is run, the added space can be used without exiting the database or taking the TABLESPACE offline. However, once a data file is added, you cannot delete it. To delete it, you need to delete the tablespace. An error example is as follows: ORA-1652: unable to extend temp segment by 207381 in tablespace TEMPSPACE

ORA-01628: max # of extents num reached for rollback segment num cause: this error is typically caused by a rollback segment and a tablespace that has reached the limit set by the MAXEXTENTS parameter. Note that MAXEXTENTS is not the hardware limit of the rollback segment or tablespace. The hardware limit depends on the value of the DB_BLOCK_SIZE parameter specified in the init. ora file when the database is created.

Solution: Use the SQL command ALTER TABLESPACE... STORAGE (maxextents xxxx) to add MAXEXTENTS, where the "XXXX" VALUE must be greater than the VALUE specified in the error message, but cannot be greater than the VALUE of largest maxextent, if it has reached the VALUE of largest maxextent, the solution is to re-create a large range and use the EXPort tool with COMPRESS = Y to EXPort the table. If the table space has available space, back up the table first, use alter tablespace tablespace_name to change its name, and then load the table back to the database.

Check where the error occurs. If it appears in a rollback segment or index, you must delete and recreate it. If it appears in a temporary tablespace, modify the storage field of the temporary tablespace, you can solve this problem. An error example is as follows: ORA-1628: max # extents 50 reached for rollback segment RBS_1

ORA-00600: internal error code, arguments: [num], [?], [?], [?], [?] Cause: this error is usually an internal ORACLE error and is only useful for OSS and ORACLE development. ORA-600 errors are often accompanied by a State dump of the trace file (system and process state) where system state storage will include information about the current object held by oracle rdbms, the Process status dump will display the objects held by the extraordinary process. When the process meets an error condition, it is often because some information is taken from a block held by it, if we know the blocks held by these error processes, we can easily track the source of the problem.

Solution: in general, this error cannot be solved. We only need to improve the system to solve this internal problem, such as adding hardware devices and adjusting system performance, using OPS (of course, OPS is not a good solution in a sense. The first variable of the ORA-600 error is used to mark the location of the error in the Code (the first variable in each part of the code is different), from the second to the fifth variables display additional information, tell the OSS code where an error occurs. An example of an error is as follows: ORA-00600: internal error code, arguments: [1237], [], [], [], [], [], [], []

ORA-03113: end-of-file on communication channel cause: communication ends abnormally, resulting in communication channel termination solution:

1>. Check whether the server process is abnormal or not. You can find out from alert. log.

2>. Check whether the SQL * Net Driver is connected to the ORACLE executable program.

3>. Check whether the server network is normal, for example, the network is disconnected or unstable.

4>. check whether there are two nodes with the same name on the same network.

5>. check if there are duplicate IP addresses on the same web ORA-00942: table or view does not exist cause: this is because the mounted table or view does not exist, mostly CATEXP. the SQL statement has not been run, so the Export view cannot be executed. For example, CATEXP. if the SQL statement has been run, it may be a version error.

Solution: because some views shared by Import and Export run CATEXP. SQL to load (they have the same view) and do not generate a separate CATEXP. so that the view and Export code is not synchronized, it is difficult to maintain compatibility between each other, the user must establish their own Export application, so as to avoid ORA-00942 errors.

The preceding errors are common issues when using rollback segments. The ORA-01598 specifies that the currently used rollback segments are in the not online status and cannot be used, change it to the online status for use; The ORA-01636 indicates that the current rollback segment is already in the online status and can be used directly without gathering it.

ORA-1636 signalled during: alter rollback segment rb00 we may also encounter the following problems when doing statistics: a rollback segment status is "Needs Recovery" phenomenon, this is because ORACLE fails to roll back a transaction that has not been committed in a transaction table. Generally, the reason is that a datafile or tablespace is in the offline status, an undo target is damaged, or a rollback segment is damaged. The solution is to set all tablespace and datafile to the online State. If the solution cannot be solved, do the following:

1>. Add event = "10015 trace name context forever lever 10" to initsid. ora ";

2> shut down the database and restart it;

3>. Under $ ORACLE_HOME/rdbms/log, locate the trace file generated when startup;

4>. In the trace file, find the following information: "error recovery tx (#, #) object #";

5>. Find the object name in the sys. dba_objects Table Based on object # (same as the object_id in the sys. dba_objects table;

6>. drop the object;

7>. In the init. ora file, put the rollback segment back into the rollback_segments parameter, delete the event; 8>. shutdown the database and restart it. In this case, the "Needs Recovery" problem should be completely solved; otherwise, the rollback segment is damaged.

ORA-01688: unable to extend table name. name partition NAME by NUM in tablespace NAME cause: the specified tablespace space is full and cannot be expanded. Solution: run the "alter tablespace add datafile" command to ADD file system files and original partitions, or increase the INITIAL size (for example, alter tablespace CDRS101 default storage (next 500 M pctincrease 1 )) it should be able to solve the problem. Otherwise, someone will use your tablespace to create a large data file, resulting in insufficient space usage. An example of an error is as follows:

ORA-1688: unable to extend table RMMCDR. LOCAL_CDR partition LOCAL_CDR101 by 460800 in tablespace CDRS101