Analysis and resolution of Oracle Common error codes (i) _oracle

The Oracle tutorial being looked at is: analysis and resolution of Oracle Common error codes (i). In the process of using Oracle, we often encounter errors from Oracle, which may be a bit blurry for beginners, and can be
Can not know how to deal with these errors, I used to appear in the more frequent error code one by one to make analysis, hoping to help you find a
A reasonable way to solve these mistakes, but also hope you can put forward your different views. After all, as a means of communication, personal opinions are inevitably too biased, and
And there must be deficiencies, mistakes are inevitable. The purpose of writing this article is to promote and make progress together through mutual communication.

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

Cause: The Oracle error described above is caused by a lack of space in the rollback segment, which is also the most common Oracle error message for Oracle data administrators. When the user
In doing a very large data operation caused the existing rollback segment is insufficient, so that the allocation of the rollback segment table space is full, can not be allocated, it will appear above
of errors.

Workaround: Use the "ALTER tablespace tablespace_name ADD datafile filename SIZE size_of_file" command to the specified
Data adds table space and can add one or more tablespaces depending on the situation. Of course this is related to the bare disk device on your host, if your host's bare
Disk equipment has no extra space to use, it is recommended that you do not lightly increase the size of the rollback segment table space, you can use the following statements to query the remaining
How many tablespace spaces are there:

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 statement
To check the rollback segment's competitive status:

Select Class,count from V$waitstat where CALSS to (' System undo Header ', ' System undo Block ', ' Undo
Header ', ' undo Block ');

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 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: There is not enough space in the Oracle Temporary segment table because Oracle always allocates contiguous space as much as possible, but not enough unallocated space or distribution is not connected
The above phenomenon will continue to occur.

Workaround: We know that because Oracle takes a tablespace as a logical structure-a cell, and the physical structure of the table space is a data file, the data file is on disk
To create, all the objects of the table space also exist on the disk, in order to add space to the table space, you must add data files. First look at the specified table space
Free space, using 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
Number:

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;

With the Alter TABLESPACE command, the additional space can be used as soon as it is completed without having to exit the database or take the tablespace offline, but be aware that once you add
Data file, you can no longer delete it, and to delete it, you delete the tablespace.

An example of an error is as follows:

Ora-1652:unable to extend temp 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.

<