Common error operations and solutions for Oracle databases

Ora-01650:unable to extend rollback segment name by NUM intablespace name

Cause: The Oracle error described above is caused by an insufficient number of tablespaces in the rollback segment, which is also the most common Oracle error message for Oracle Data Administrator. This error occurs when the user is doing a very large data operation that causes the existing rollback segment to be insufficient so that the allocated rollback segment table space is full and cannot be allocated.

Workaround: Add table space to the specified data using the "ALTER tablespace tablespace_name ADD datafile filename SIZE size_of_file" command, and add one or more tablespaces, depending on the situation. This, of course, has to do with the bare-disk devices on your mainframe, if you host the bare disk device has no extra space, it is recommended that you do not lightly increase the size of the rollback segment table space, you can use the following statements first query the remaining tablespace space how many: Select user_name, Sql_text from V$open_cursor where user_name= ';

If the extra space is more, you can appropriately append a large rollback segment to the table space to use, so as to avoid the above errors. You can also use the following statements to detect

The competition for rollback segment: Select class,count from V$waitstat where CALSS in (' System undo Headers ', ' 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.

Ora-01652:unable to extend temp segment from num in Tablespace name: Insufficient Oracle temporary segment Tablespace because Oracle always allocates contiguous space as much as possible. The above phenomenon occurs when there is not enough space to allocate or the distribution is not continuous. Workaround: We know that because Oracle takes tablespaces as logical structures-cells, and the physical structure of tablespaces is data files, data files are physically created on disk, and all the objects in the table space are on disk, the data files must be added in order to add space to the tablespace. Look at the free space in the specified table space, using the view sys.dba_free_space, each record in the view represents the size of the free space fragment: 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 free space, see if it is smaller than the size mentioned in the error message, and then look at the default table space parameters:

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 table space by using the following SQL command:

Sql>alter tablespace name DEFAULT STORAGE (INITIAL XXX NEXT YYY);

Appropriately increasing the size of the default value may resolve the error problem, or you can fix the problem by modifying the user's temporary table space size:

Sql>alter USER username temporary tablespace new_tablespace_name;

Use the Alter tablespace command, one but complete, the increased space can be used, no need to exit the database or make the tablespace offline, but to pay attention to, once the data file has been added, you can not delete it, to delete, you need to delete the tablespace. An example of an error 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 causes: This error is usually a rollback segment and a table space has reached the limit set by the maxextents parameter. Notice that this maxextents is not the hardware limit for the rollback segment or tablespace, and the hardware limit depends on the value of the Db_block_size parameter that was specified in the Init.ora file when the database was created.

WORKAROUND: Use SQL command alter tablespace ... STORAGE (maxextents xxxx) to increase the maxextents, where "XXXX" value must be greater than the value indicated in the error message, but not greater than the largest maxextent, if it has reached largest maxextent VALUE, the solution is to recreate the larger scope size, using the Export tool with the option Compress=y exports the table, if the table space has free space, first to make a backup of the table, with alter Tablespace tablespace_name change its name , and then mount the table back to the database.

To see where the error occurred, if the rollback segment or index is present, then it must be deleted and rebuilt, which can be resolved if it appears in the temporary table space and modifies the storage field of the temporary tablespace. An example of an error is as follows: Ora-1628:max # extents reached for rollback segment Rbs_1

Ora-00600:internal error code,arguments:[num],[?],[?],[?],[?] Cause: This error is usually an internal error of Oracle and is only useful for OSS and Oracle development. ORA-600 errors are often accompanied by a state dump of the trace file (System State and process State), the system state store will include information about the current object held by the Oracle RDBMS, and the process state dump will show objects held by the extraordinary process, and when the process conforms to an error condition, Often it is because some information is taken from a block that it holds, and if we know the block that the error process holds, we can easily trace the source of the problem.

Workaround: In general, this error can not be solved by itself, only by improving the system itself to solve this internal problem, such as adding hardware devices, adjusting system performance, using OPS (of course, OPS is not a good solution in a sense) and so on. 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), displaying additional information from the second to fifth variable, telling the OSS code where an error occurred. An example of an error is as follows: Ora-00600:internal error code, arguments: [1237], [], [], [], [], [], [], []

Ora-03113:end-of-file on communication channel causes: communication does not end properly, resulting in a communication channel termination resolution:

1>: Check if there is a service process abnormal panic, you can learn from Alert.log

2> Check sql*net driver is connected to an Oracle executable

3> Check that the server network is normal, such as network or unstable

4>: Check if there are two nodes with the same name on the same web

5>. Check if there is a duplicate IP address on the same network ora-00942:table or view does not exist cause: this is because the loaded table or view does not exist, most of the catexp.sql is not running and cannot perform the export view, If Catexp.sql is already running, it may be a version error.

Workaround: Because some views of import and export sharing are loaded by running Catexp.sql (they have the same view), does not generate a separate catexp.sql, resulting in the view and export code is not synchronized, it is difficult to maintain compatibility between each other, users must establish their own export applications, so as to avoid ORA-00942 errors.

All of the above errors are common problems when using rollback segments, ORA-01598 indicate that the status of the rollback segment currently in use is not online and cannot be used; ORA-01636 indicates that the current rollback segment is already online State, which can be used directly without having to set it again.

ORA-1636 signalled during:alter rollback segment online We are also likely to encounter the following problems when doing statistics: a rb00 rollback status of "segment Needs" phenomenon , this is caused by the failure of Oracle to back out of a list of things that have not been committed. Usually the reason for a datafile or tablespace is to be destroyed in the state of offline or a goal of undo or rollback segment destroyed. The solution is to put all the tablespace and datafile into the online state, if not resolved to do the following work:

1>. Add event= "10015 Trace name Context Forever lever 10" in Initsid.ora;

2>.shutdown database and reboot;

3> under $oracle_home/rdbms/log, trace file generated at startup is found.

4>. In the trace file, locate the following information "Error recovery TX (#,#) object #";

5>: The name of the object is identified in the Sys.dba_objects table according to the object# (same as object_id in the Sys.dba_objects table);

6> Drop the object off;

7>: In the Init.ora file, put the rollback segment back in the rollback_segments parameter, delete the Event;8>.shutdown database and restart. At this time "Needs Recovery" problem should be completely solved, otherwise it is rollback segment was destroyed.

Ora-01688:unable to extend table Name.name partition name by NUM in Tablespace name causes: The specified tablespace space is already occupied and cannot be extended. Workaround: Add File System files and original partitions using the ALTER tablespace add datafile command, or increase the size of the initial (for example, alter tablespace CDRS101 default storage (next 500M Pctincrease 1) should be able to resolve otherwise someone using your tablespace creates a larger data file that causes your tablespaces to be out of space. 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