Oracle Ora-01652/oracle Table Space

Reference:

Oracel bigfile tablespace: (recommended)

Http://blog.chinaunix.net/uid-20779720-id-3078273.html

ora-01652 Solution

Http://www.cnblogs.com/songling/archive/2011/03/04/1970965.html

Appears:ora-01652 cannot extend temp segment by 128 (in tablespace XXX)

This error may have been in two reasons: (The following two cases for normal smallfile tablespace)

Note: By default, when we create a tablespace, it is smallfile tablespace unless we specify a bigfile for create tablespace

1. If the data file for the XXX tablespace does not reach the upper limit (typically 32G), it may be that the temporary tablespace size is not sufficient, in which case we can generally resolve the problem by modifying the size of the temporary tablespace or changing the temporary tablespace to autoextend mode. Temporary tablespace is prone to this situation if the table space is too small when doing operations such as big data sorting. In addition, if it is a temporary tablespace, it is possible that the temporary table space is full, which can be resolved by removing the temporary tablespace reconstruction in this simple and brutal way.

-- --Delete table space and data files Drop  and Cascade constraints; -- --Create a temporary table space Create Temporary ' /oracle11r2/oradata/pmptgdb/worktmp.dbf '  on Next 100m MaxSize unlimited;

Temporary table space

The primary purpose of a temporary tablespace is to sort operations in a database [such as creating indexes, order by and group BY, DISTINCT, union/intersect/minus/, Sort-merge, and joins, analyze commands] , managing indexes [such as creating indexes, IMP for data import], accessing views, and so on provide temporary computing space, and the system will automatically clean up when the operation is complete.

2. If the number of XXX tablespace data file is too large to reach the upper limit (General 32G), the table space cannot be automatically expanded at this time, in this case, you need to add a new data file to the tablespace to solve this problem.

-- --Add a data file to the specified table space Alter  Work Add ' /media/hdd1/oradata/pmptgdb/work1.dbf '  on Next 100m MaxSize unlimited;

To view the related SQL statements for the tablespace:

----View table spaceSelect*  fromdba_tablespaces;----View table space detailsSelectTablespace_name,file_name, bytes/1024x768/1024x768File_size,autoextensible fromDba_data_files;--Dba_temp_file
Select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from Dba_temp_file;
----View user tablespace information
SelectUsername,temporary_tablespace,default_tablespace fromdba_users;
----      Modify table space to auto-expand
 alter database tempfile '/oracle11r2/oradata/pmptgdb/worktmp.dbf  ' autoextend  on next 100m MaxSize unlimited;

Oracle Ora-01652/oracle Table Space