Common Oracle database errors and solutions (1)

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 the 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=’<user_name>’;

If the extra space is large, you can append a large rollback segment to the tablespace to avoid the above errors. You can also use the following statement to check the rollback segment competition:

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: the tablespace in the temporary ORACLE segment is insufficient, because ORACLE always tries its best to allocate continuous space. This problem occurs when there is not enough allocable space or the allocation is not consecutive.

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 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=’<users>’;

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 example of an error is as follows:

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

ORA-01578: Oracle data block upload upted (file # num, block # num)

Cause: When ORACLE accesses a data block:

1. Hardware I/O errors;

2. OS I/O errors or buffer problems;

3. memory or paging problems;

4. ORACLE failed to access an unformatted system block. 5. One of the above situations, such as data file overflow, caused a logical or physical bad block, at this time will be reported ORA-01578 error.

Solution: Because ORACLE Reports an error only when it accesses a problematic data file, the error may be reported later than the actual error time, if the ORA-01578 error message prompts that the bad data block points to the user's own data file, use the following method to solve:

If the bad block found through the following SQL statement has an index, you only need to re-create the index.

SQL> Select owner, segment_name, segment_type from dba_extents where file_id = <F> and <B> between block_id and block_id + blocks-1; (<F> and <B> are the file number and block number of the Bad block reported by the ORA-01578 respectively)

If the bad block appears on the table, use the following statement to analyze whether it is a permanent bad block. It is recommended that you execute it once or twice to identify whether the bad data block is a permanent physical bad block on the hard disk) or random memory or hardware errors )):

SQL> Analyze table Validate structure cascade;

After executing this command, the following results may appear:

ORA-01578: has the same parameter as the original error message, is a permanent physical or logical bad block; has a different parameter with the original error information, may be with memory, page space is related to the I/O device.

If you have the latest backup of this table, it is best to use this backup to restore the table, or use event 10231 to retrieve data other than bad blocks:

<1>. Shut down the database first.

<2> edit init Add the. ora file:

event=”10231 trace name context forever,level 10”

<3>. startup restrict

<4>. create a temporary table: SQL> create table errortemp as select * from error; (error is the name of the bad table)

<5>. Extract the event from init Delete the. ora file and restart the database.

<6>. rename bad table: rename the temporary table to the name of the bad table.

<7>. Create Table Indexes

If the ORA-01578 error message prompts that the bad data block points to a data dictionary or rollback segment, you should immediately contact ORACLE to discuss a good solution.

The solution mentioned here is only a common one. For more specific solutions, you can refer to the ORACLE troubleshooting manual, there are methods for retrieving data other than bad blocks using the ROWID method, which will not be described here.